下面小编跟大家分享防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 = ;
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 (keyFormat, SelectClause, FromClause, WhereClause, GroupClause, SortClause);
}
/**//// summary
/// 生成查询记录总数的SQL语句
/// /summary
/// returns/returns
public string GenerateCountSql()
{
StringBuilder sb = new StringBuilder();
(" from {0}", FromClause);
if ( 0)
(" where 1=1 {0}", WhereClause);
if (!())
(" group by {0}", GroupClause);
return ("Select count(0) {0}", sb);
}
/**//// summary
/// 生成分页查询语句，包含记录总数
/// /summary
/// returns/returns
public string GenerateSqlIncludeTotalRecords()
{
StringBuilder sb = new StringBuilder();
if (())
SelectClause = "*";
if (())
SortClause = PK;
int start_row_num = (PageIndex - 1)*PageSize + 1;
(" from {0}", FromClause);
if ( 0)
(" where 1=1 {0}", WhereClause);
if (!())
(" group by {0}", GroupClause);
string countSql = ("Select count(0) {0};", sb);
string tempSql =
(
"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 (())
SelectClause = "*";
if (())
SortClause = PK;
int start_row_num = (PageIndex - 1)*PageSize + 1;
(" from {0}", FromClause);
if ( 0)
(" where 1=1 {0}", WhereClause);
if (!())
(" group by {0}", GroupClause);
return
(
"WITH t AS (SELECTROW_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));
}
} 使用方法：
PagerQuery query = new PagerQuery();
= 1;
= 20;
query.PK = "ID";
= "*";
= "TestTable";
= "ID DESC";
if (!())
{
(" and ID= @ID");
}
a)GenerateCountSql ()方法生成的语句为：
Select count(0) from TestTable Where 1=1 and ID= @ID
b)GenerateSql()方法生成的语句为：
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
c)GenerateSqlIncludetTotalRecords()方法生成的语句为：
WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY 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;
注意：以上代码生成的SQL语句是曾对SQL SERVER 以上版本的，希望这些代码对大家有用
