防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));
}
}

使用方法：

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以上版本的，希望这些代码对大家有用