防SQL注入 生成参数化的通用分页查询语句

使用这种通用的存储过程进行分页查询，想要防SQL注入，只能对输入的参数进行过滤，例如将一个单引号“'”转换成两个单引号“''”，但这种做法是不安全的，厉害的黑客可以通过编码的方式绕过单引号的过滤，要想有效防SQL注入，只有参数化查询才是最终的解决方案。但问题就出在这种通用分页存储过程是在存储过程内部进行SQL语句拼接，根本无法修改为参数化的查询语句，因此这种通用分页存储过程是不可取的。但是如果不用通用的分页存储过程，则意味着必须为每个具体的分页查询写一个分页存储过程，这会增加不少的工作量。
经过几天的时间考虑之后，想到了一个用代码来生成参数化的通用分页查询语句的解决方案。代码如下：
代码如下:

<br>public class PagerQuery <br>{ <br>private int _pageIndex; <br>private int _pageSize = 20; <br>private string _pk; <br>private string _fromClause; <br>private string _groupClause; <br>private string _selectClause; <br>private string _sortClause; <br>private StringBuilder _whereClause; <br>public DateTime DateFilter = DateTime.MinValue; <br>protected QueryBase() <br>{ <br>_whereClause = new StringBuilder(); <br>} <br>/**//// <summary> <br>/// 主键 <br>/// </summary> <br>public string PK <br>{ <br>get { return _pk; } <br>set { _pk = value; } <br>} <br>public string SelectClause <br>{ <br>get { return _selectClause; } <br>set { _selectClause = value; } <br>} <br>public string FromClause <br>{ <br>get { return _fromClause; } <br>set { _fromClause = value; } <br>} <br>public StringBuilder WhereClause <br>{ <br>get { return _whereClause; } <br>set { _whereClause = value; } <br>} <br>public string GroupClause <br>{ <br>get { return _groupClause; } <br>set { _groupClause = value; } <br>} <br>public string SortClause <br>{ <br>get { return _sortClause; } <br>set { _sortClause = value; } <br>} <br>/**//// <summary> <br>/// 当前页数 <br>/// </summary> <br>public int PageIndex <br>{ <br>get { return _pageIndex; } <br>set { _pageIndex = value; } <br>} <br>/**//// <summary> <br>/// 分页大小 <br>/// </summary> <br>public int PageSize <br>{ <br>get { return _pageSize; } <br>set { _pageSize = value; } <br>} <br>/**//// <summary> <br>/// 生成缓存Key <br>/// </summary> <br>/// <returns></returns> <br>public override string GetCacheKey() <br>{ <br>const string keyFormat = "Pager-SC:{0}-FC:{1}-WC:{2}-GC:{3}-SC:{4}"; <br>return string.Format(keyFormat, SelectClause, FromClause, WhereClause, GroupClause, SortClause); <br>} <br>/**//// <summary> <br>/// 生成查询记录总数的SQL语句 <br>/// </summary> <br>/// <returns></returns> <br>public string GenerateCountSql() <br>{ <br>StringBuilder sb = new StringBuilder(); <br>sb.AppendFormat(" from {0}", FromClause); <br>if (WhereClause.Length > 0) <br>sb.AppendFormat(" where 1=1 {0}", WhereClause); <br>if (!string.IsNullOrEmpty(GroupClause)) <br>sb.AppendFormat(" group by {0}", GroupClause); <br>return string.Format("Select count(0) {0}", sb); <br>} <br>/**//// <summary> <br>/// 生成分页查询语句，包含记录总数 <br>/// </summary> <br>/// <returns></returns> <br>public string GenerateSqlIncludeTotalRecords() <br>{ <br>StringBuilder sb = new StringBuilder(); <br>if (string.IsNullOrEmpty(SelectClause)) <br>SelectClause = "*"; <br>if (string.IsNullOrEmpty(SortClause)) <br>SortClause = PK; <br>int start_row_num = (PageIndex - 1)*PageSize + 1; <br>sb.AppendFormat(" from {0}", FromClause); <br>if (WhereClause.Length > 0) <br>sb.AppendFormat(" where 1=1 {0}", WhereClause); <br>if (!string.IsNullOrEmpty(GroupClause)) <br>sb.AppendFormat(" group by {0}", GroupClause); <br>string countSql = string.Format("Select count(0) {0};", sb); <br>string tempSql = <br>string.Format( <br>"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};", <br>SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1)); <br>return tempSql + countSql; <br>} <br>/**//// <summary> <br>/// 生成分页查询语句 <br>/// </summary> <br>/// <returns></returns> <br>public override string GenerateSql() <br>{ <br>StringBuilder sb = new StringBuilder(); <br>if (string.IsNullOrEmpty(SelectClause)) <br>SelectClause = "*"; <br>if (string.IsNullOrEmpty(SortClause)) <br>SortClause = PK; <br>int start_row_num = (PageIndex - 1)*PageSize + 1; <br>sb.AppendFormat(" from {0}", FromClause); <br>if (WhereClause.Length > 0) <br>sb.AppendFormat(" where 1=1 {0}", WhereClause); <br>if (!string.IsNullOrEmpty(GroupClause)) <br>sb.AppendFormat(" group by {0}", GroupClause); <br>return <br>string.Format( <br>"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}", <br>SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1)); <br>} <br>} <br> <br>使用方法： <br><br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>PagerQuery query = new PagerQuery(); <br>query.PageIndex = 1; <br>query.PageSize = 20; <br>query.PK = "ID"; <br>query.SelectClause = "*"; <br>query.FromClause = "TestTable"; <br>query.SortClause = "ID DESC"; <br>if (!string.IsNullOrEmpty(code)) <br>{ <br>query.WhereClause.Append(" and ID= @ID"); <br>} <br> <br>a) GenerateCountSql ()方法生成的语句为： <br>Select count(0) from TestTable Where 1=1 and ID= @ID <br>b) GenerateSql()方法生成的语句为： <br>WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY ECID DESC) as row_number, * from TestTable where 1=1 and ID= @ID) Select * from t where row_number BETWEEN 1 and 20 <br>c) GenerateSqlIncludetTotalRecords()方法生成的语句为： <br>WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY E.ECID DESC) as row_number,* from TestTable where 1=1 and ID= @ID) Select * from t where row_number BETWEEN 1 and 20;Select count(0) from ECBasicInfo where 1=1 and ID= @ID; <br><br>注意：以上代码生成的SQL语句是曾对SQL SERVER 2005以上版本的，希望这些代码对大家有用</li><li>            </li><li>                                </li></ol></pre>