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

时间:2022-02-23 16:17:43

使用这种通用的存储过程进行分页查询,想要防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)); 


使用方法: 

复制代码代码如下:


PagerQuery query = new PagerQuery(); 
query.PageIndex = 1; 
query.PageSize = 20; 
query.PK = "ID"; 
query.SelectClause = "*"; 
query.FromClause = "TestTable"; 
query.SortClause = "ID DESC"; 
if (!string.IsNullOrEmpty(code)) 

query.WhereClause.Append(" 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 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; 

注意:以上代码生成的SQL语句是曾对SQL SERVER 2005以上版本的,希望这些代码对大家有用.