防SQL注入：生成參數化的通用分頁查詢語句

前些時間看了玉開兄的“如此高效通用的分頁存儲過程是帶有sql注入漏洞的”這篇文章，才突然想起 某個項目也是使用了累似的通用分頁存儲過程。使用這種通用的存儲過程進行分頁查詢，想要防SQL注入 ，只能對輸入的參數進行過濾，例如將一個單引號“'”轉換成兩個單引號“''”，但這種做法是不安全 的，厲害的黑客可以通過編碼的方式繞過單引號的過濾，要想有效防SQL注入，只有參數化查詢才是最終 的解決方案。但問題就出在這種通用分頁存儲過程是在存儲過程內部進行SQL語句拼接，根本無法修改為 參數化的查詢語句，因此這種通用分頁存儲過程是不可取的。但是如果不用通用的分頁存儲過程，則意味 著必須為每個具體的分頁查詢寫一個分頁存儲過程，這會增加不少的工作量。

經過幾天的時間考慮之後，想到了一個用代碼來生成參數化的通用分頁查詢語句的解決方案。代碼如 下：

public class PagerQuery
　　{
　　　　private int _pageIndex;
　　　　 private int _pageSize = 20;
　　　　private string _pk;
　　　　private string _fromClause;
　　　　private string _groupClause;
　　　　private string _selectClause;
　　　　private string _sortClause;
　　　　private StringBuilder _whereClause;
　　　　public DateTime DateFilter = DateTime.MinValue;

　　　　 protected QueryBase()
　　　　{
　　　　　　_whereClause = new StringBuilder();
　 　　　}

　　　　/**//// <summary>
　　　　/// 主鍵
　　　　/// </summary>
　　　　public string PK
　　　　{
　　　　　　get { return _pk; }
　　　　　　set { _pk = value; }
　　　　}

　　　　public string SelectClause
　　　　{
　　　　　　get { return _selectClause; }
　　　　　　set { _selectClause = value; }
　　　　}

　　　　public string FromClause
　　　　 {
　　　　　　get { return _fromClause; }
　　　　　　set { _fromClause = value; }
　　　　}

　　　　public StringBuilder WhereClause
　　　　{
　　　　　　get { return _whereClause; }
　　　　　　set { _whereClause = value; }
　　　　}

　　 　　public string GroupClause
　　　　{
　　　　　　get { return _groupClause; }
　 　　　　　set { _groupClause = value; }
　　　　}

　　　　public string SortClause
　　　　{
　　　　　　get { return _sortClause; }
　　　　　　set { _sortClause = value; }
　　　　}

　　　　/**//// <summary>
　　　　/// 當 前頁數
　　　　/// </summary>
　　　　public int PageIndex
　　　　{
　　 　　　　get { return _pageIndex; }
　　　　　　set { _pageIndex = value; }
　　　　}

　　　　/**//// <summary>
　　　　/// 分頁大小
　　　　/// </summary>
　　　　public int PageSize
　　　　{
　　　　　　get { return _pageSize; }
　　　　　　set { _pageSize = value; }
　　　　}

　　　　/**//// <summary>
　　　　/// 生成緩存Key
　　　　/// </summary>
　　　　/// <returns></returns>
　　　　public override string GetCacheKey()
　　　　 {
　　　　　　const string keyFormat = "Pager-SC:{0}-FC:{1}-WC:{2}-GC:{3}-SC:{4}";
　 　　　　　return string.Format(keyFormat, SelectClause, FromClause, WhereClause, GroupClause, SortClause);
　　　　}

　　　　/**//// <summary>
　　　　/// 生成查詢記錄總數的SQL語句
　　　　/// </summary>
　　　　/// <returns></returns>
　　　　public string GenerateCountSql()
　　　　{
　　　　　　StringBuilder sb = new StringBuilder();

　　　　　　sb.AppendFormat(" from {0}", FromClause);
　　　　　　if (WhereClause.Length > 0)
　　　　　　　　 sb.AppendFormat(" where 1=1 {0}", WhereClause);

　　　　　　if (! string.IsNullOrEmpty(GroupClause))
　　　　　　　　sb.AppendFormat(" group by {0}", GroupClause);

　　　　　　return string.Format("Select count(0) {0}", sb);
　　　 　}

　　　　/**//// <summary>
　　　　/// 生成分頁查詢語句，包含記錄總數
　　　　/// </summary>
　　　　/// <returns></returns>
　　　　 public string GenerateSqlIncludeTotalRecords()
　　　　{
　　　　　　StringBuilder sb = new StringBuilder();
　　　　　　if (string.IsNullOrEmpty(SelectClause))
　　　　　 　　　SelectClause = "*";

　　　　　　if (string.IsNullOrEmpty(SortClause))
　　　 　　　　　SortClause = PK;

　　　　　　int start_row_num = (PageIndex - 1)*PageSize + 1;

　　　　　　sb.AppendFormat(" from {0}", FromClause);
　　　　　　if (WhereClause.Length > 0)
　　　　　　　　sb.AppendFormat(" where 1=1 {0}", WhereClause);

　　　　　　if (!string.IsNullOrEmpty(GroupClause))
　　　　　　　　 sb.AppendFormat(" group by {0}", GroupClause);

　　　　　　string countSql = string.Format("Select count(0) {0};", sb);
　　　　　　string tempSql =
　　　　　　　 　string.Format(
　　　　　　　　　　"WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,{1}{2}) Select * from t where row_number BETWEEN {3} and {4};",
　　　　　 　　　　　SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));

　　　　　　return tempSql + countSql;
　　　　}

　　　　/**//// <summary>
　　　　/// 生成分頁查詢語句
　　　　/// </summary>
　　　 　/// <returns></returns>
　　　　public override string GenerateSql()
　 　　　{
　　　　　　StringBuilder sb = new StringBuilder();
　　　　　　if (string.IsNullOrEmpty(SelectClause))
　　　　　　　　SelectClause = "*";

　　　　 　　if (string.IsNullOrEmpty(SortClause))
　　　　　　　　SortClause = PK;

　　　 　　　int start_row_num = (PageIndex - 1)*PageSize + 1;

　　　　　　sb.AppendFormat(" from {0}", FromClause);
　　　　　　if (WhereClause.Length > 0)
　　　　　　　　 sb.AppendFormat(" where 1=1 {0}", WhereClause);

　　　　　　if (! string.IsNullOrEmpty(GroupClause))
　　　　　　　　sb.AppendFormat(" group by {0}", GroupClause);

　　　　　　return
　　　　　　　　string.Format(
　　　　　　　 　　　"WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,{1}{2}) Select * from t where row_number BETWEEN {3} and {4}",
　　　　　　　　　　SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));
　　　　}
　　}