sql防注入代码(1/2)

sql防注入代码

以下代码生成的sql语句是曾对sql server 2005以上版本的，希望这些代码对大家有用

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(); } /**//// /// 主键 /// 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; } } /**//// /// 当前页数 /// public int pageindex { get { return _pageindex; } set { _pageindex = value; } } /**//// /// 分页大小 /// public int pagesize { get { return _pagesize; } set { _pagesize = value; } } /**//// /// 生成缓存key /// /// 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); } /**//// /// 生成查询记录总数的sql语句 /// /// 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); } /**//// /// 生成分页查询语句，包含记录总数 /// /// 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 temql = 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; } /**//// /// 生成分页查询语句 /// /// 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)); } }