根据EntityFramework写的重写sql语句的类

代码如下

View Code

 1 class ReSql {
 2         #region 防sql注入重写sql语句//出自http://www.cnblogs.com/ahjesus 尊重作者辛苦劳动成果,转载请注明出处,谢谢!
 3         public string RewriteSql(string sql) {
 4             sql = Regex.Replace(sql, @"\s+", " ");
 5             string[] sqlArr = sql.Replace("where", "拆").Split('拆');
 6             string newsql = "";
 7             for (int i = 0; i < sqlArr.Length; i++) {
 8                 if (i > 0) {
 9                     string item = "where" + sqlArr[i];
10                     sqlArr[i] = item;
11                 }//出自http://www.cnblogs.com/ahjesus 尊重作者辛苦劳动成果,转载请注明出处,谢谢! 
12                 if (i < sqlArr.Length - 1) {
13                     newsql += sqlArr[i];
14                 }
15             }
16             if (!string.IsNullOrWhiteSpace(newsql)) {
17                 sql = newsql.Replace("'", "''");
18             }
19 
20             string where = "";
21             if (sqlArr.Length > 1) {
22                 where = sqlArr[sqlArr.Length - 1];
23 
24                 //过滤and
25                 where = RegexReplacewithEvaluator(where);
26 
27                 string regexStr = @"(?<==)\s*[^\w*\.]\w*[^\)]|(?<=like)\s*\W*\w*\W*\s*";//出自http://www.cnblogs.com/ahjesus 尊重作者辛苦劳动成果,转载请注明出处,谢谢! 
28                 //原来的，不够优化//(?<==)\s*\w*[^\.|^\)]\s*|(?<==)\s*\W\w*\W\s*|(?<==)\s*\w*\s*|(?<=like)\s*\W*\w*\W*\s*
29                 MatchEvaluator evaluator = new MatchEvaluator(ReplaceMatchEvaluator);
30                 where = Regex.Replace(where, regexStr, evaluator).Replace("'", "''");
31 
32 
33                 sql = "exec sp_executesql N'" + sql + where + "'";
34 
35                 string paramsType = "";
36                 string paramsKey = "";
37                 string paramsValue = "";
38                 foreach (KeyValuePair<string, string> item in strDic) {
39                     paramsType = ",N'";
40                     paramsKey += "@" + item.Key + " nvarchar(max),";
41 
42                     paramsValue += ",@" + item.Key + "=" + item.Value;
43                 }
44                 if (!string.IsNullOrWhiteSpace(paramsKey)) {
45                     sql += paramsType + paramsKey.Substring(0, paramsKey.Length - 1) + "'";
46                     sql += paramsValue;
47                 }
48                 //出自http://www.cnblogs.com/ahjesus 尊重作者辛苦劳动成果,转载请注明出处,谢谢! 
49                 sql = Regex.Replace(sql, "过滤项", 过滤项);
50             }
51             return sql;
52         }
53         Dictionary<string, string> strDic = new Dictionary<string, string>();
54         private string ReplaceMatchEvaluator(Match m) {
55             string guid = Guid.NewGuid().ToString("N");
56             strDic.Add(guid, m.Value);
57             return " @" + guid + " ";
58             //出自http://www.cnblogs.com/ahjesus 尊重作者辛苦劳动成果,转载请注明出处,谢谢! 
59         }
60 
61         private string RegexReplacewithEvaluator(string where) {
62             string regexStr = @"and\s{1}\(.*\)";
63             MatchEvaluator evaluator = new MatchEvaluator(Replace过滤项);
64             return Regex.Replace(where, regexStr, evaluator);
65         }
66         private string 过滤项 = "";
67         private string Replace过滤项(Match m) {
68             过滤项 = m.Value.Replace("'", "''");
69             return "过滤项";
70             //出自http://www.cnblogs.com/ahjesus 尊重作者辛苦劳动成果,转载请注明出处,谢谢! 
71         }
72 
73         #endregion
74     }

使用方法

ReSql resql = new ReSql();
sql = resql.RewriteSql(tsql);

//出自http://www.cnblogs.com/ahjesus
尊重作者辛苦劳动成果,转载请注明出处,谢谢!

posted on 2012-11-27 13:46 深海大虾阅读(715) 评论(0) 编辑收藏举报