防SQL注入：ASP.NET代码生成参数化的通用分页查询语句 -

使用这种通用的存储过程进行分页查询，想要防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以上版本的，希望这些代码对大家有用
data doctor recovery memory card crackbest buy jawbone 2r4i adapter for nintendo dsidell latitude e6400 sd card readerwii wireless set up100 ethernet switch storesundowner truck accessoriesbest buy company informationnetwork cable adapterwiki wireless usbrouter pcmcia card driverwireless mighty mouseatherosbajar crack sims 2connect ps2 slim onlinecomcast digital cable hd channelstelecom cablingpostal service wikilg 17 crt monitor1gb usb penrc flight sim reviewsedge router definitionhdcp hdmiused push button switchauto roll kitsony playstation 3 gamestophi speed usb 2.0 pocket hubmicro dvi to video adapterdownload high definition movie trailersralink rt61 rt2500 driver downloadorinoco pc card drivershow to connect your wii to internetusb 3.0 vs esatadell computer driver windows xphome phone cablelaptop connect card pricessmallville season 6 wikiguitar cable speaker cablecircuit city vying for liquidation bargains businesshdmi switch two outputplaystation xbox 360 adaptergraphics pci cardsdell monitor best buyxbox 360 hdmi vs componentsony cyber shot w220 digital camera silverwireless credit card swipefree gps windows mobiletransforming usb flash memorhdmi vs rgbbelden cable reviewps2 slide card anleitungdigital tv cable amplifierdb15 vga inputscomcast urlcomcast descrambler softwarehdmi pin diagramrj45 coaxialtoronto tv listingsrollover cable crossover cablereplacement dell partsvirtual guitar softwareinternational travel power converterpc card usb adaptercablecard pc tvrigid coaxial cablereplace sound cardgreeting card builder softwarebnc splitter boxevdo linksys routerreplace pci video cardswitch wiringbest hdtv bargainspower switchmotor tutorlg rumor bluetooth pairingbest gaming network cardbusiness thesaurusconnect two computers using lanbridge two network cardslogitech bluetooth remoteusb wireless network receiverright angle plugplaystation 2 game compatibilityseries vs parallel circuitshp scanner drivers for macfix broken ds screennvidia driver archivefritz card pci treibersprint wireless internet usb cardjawbone earloopsdual cigarette lighter adapterradio shack laptop chargeradwords desktop tooldiy reverse trikegreen pc power supplyusb bluetooth dongle class 2load ps2 saves ps3keyboard macro softwarebest hdmi graphics card 2009composite video capture cardinstalling cable connectorsound card download xpnetwork link testtelephone cable spliceatt dsl downloadups batterycable broadband providersvista keyboard helplogitech playstation 3 controllerbroadcom netxtreme gigabit ethernet pci expresspc dvr systempqi s520hd channel listcomcast hd channels pixelatedhtc touch pro usb driversoptimum online channel listsmall lcd panelsblackberry storm circuit cityrogers digital cable helpmacbook pro esatahd ready converterunscramble freeview channelsadhesive rubber plasticmake usb ethernet cableusb spy camera12in1 flash media reader writerhigh speed comcast40gb playstation 3rj45 cross cable connectionsuper audio converter crackremove wires harnesssmart car reviewsrfid supplierscat6 vs cat5e connectortoshiba hdtv 42anycom solar bluetoothdifference between 2 wire 4 wire transmitterdoes dvi domonitor internet usage freewarebest pc parts64gb ssdusb guitar cable reviewbest mobile graphics cardtoslink to mini plugviewsonic vx910 problemspci bus controller driverxm fm direct adapterbroadband card routerright angle surge protectorcompaq presario sound cards