sql防注入代码,top分页方法

sql防注入代码

但是有利也有弊,它要求排序字段必须构成唯一记录,且select后的字段列表中,不允许出现与排序字段同名的字段。虽然sql2k5及以上版本已经提供了rownum()来进行分页处理,但是使用sql2k进行开发的还是较多的复制代码 代码如下:uusing
system.collections.generic;sing system;using system.text;/// summary///
构造分页后的sql语句/// /summarypublic static class paginghelper{ ///
summary /// 获取分页sql语句,排序字段需要构成唯一记录 /// /summary ///
param name=”_recordcount”记录总数/param /// param
name=”_pagesize”每页记录数/param /// param
name=”_pageindex”当前页数/param /// param
name=”_safesql”sql查询语句/param /// param
name=”_orderfield”排序字段,多个则用“,”隔开/param ///
returns分页sql语句/returns public static string createpagingsql(int
_recordcount, int _pagesize, int _pageindex, string _safesql, string
_orderfield) { //重新组合排序字段,防止有错误 string[] arrstrorders =
_orderfield.split(new char[] { ‘,’ },
stringsplitoptions.removeemptyentries); stringbuilder sboriginalorder =
new stringbuilder(); //原排序字段 stringbuilder sbreverseo教程rder = new
stringbuilder(); //与原排序字段相反,用于分页 for (int i = 0; i
arrstrorders.length; i++) { arrstrorders[i] =
arrstrorders[i].trim(); //去除前后空格 if (i != 0) {
sboriginalorder.append(“, “); sbreverseorder.append(“, “); }
sboriginalorder.append(arrstrorders[i]);

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

int index = arrstrorders[i].indexof(” “); //判断是否有升降标识 if
(index 0) { //替换升降标识,分页所需 bool flag =
arrstrorders[i].indexof(” desc”, stringcomparison.ordinalignorecase)
!= -1; sbreverseorder.appendformat(“{0} {1}”,
arrstrorders[i].remove(index), flag ? “asc” : “desc”); } else {
sbreverseorder.appendformat(“{0} desc”, arrstrorders[i]); } }

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 temps教程ql =
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)); } }

//计算总页数 _pagesize = _pagesize == 0 ? _recordcount : _pagesize;
int pagecount = (_recordcount + _pagesize – 1) / _pagesize;

首页 1 2 末页

//检查当前页数 if (_pageindex 1) { _pageindex = 1; } else if
(_pageindex pagecount) { _pageindex = pagecount; }

发表评论

电子邮件地址不会被公开。 必填项已用*标注