代码如下
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 //过滤and25 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 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 strDic = new Dictionary ();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 #endregion74 } 使用方法
ReSql resql = new ReSql();sql = resql.RewriteSql(tsql);
//出自http://www.cnblogs.com/ahjesus
尊重作者辛苦劳动成果,转载请注明出处,谢谢!