防SQL注入 生成參數(shù)化的通用分頁查詢語句

使用這種通用的存儲(chǔ)過程進(jìn)行分頁查詢，想要防SQL注入，只能對(duì)輸入的參數(shù)進(jìn)行過濾，例如將一個(gè)單引號(hào)“'”轉(zhuǎn)換成兩個(gè)單引號(hào)“''”，但這種做法是不安全的，厲害的黑客可以通過編碼的方式繞過單引號(hào)的過濾，要想有效防SQL注入，只有參數(shù)化查詢才是最終的解決方案。但問題就出在這種通用分頁存儲(chǔ)過程是在存儲(chǔ)過程內(nèi)部進(jìn)行SQL語句拼接，根本無法修改為參數(shù)化的查詢語句，因此這種通用分頁存儲(chǔ)過程是不可取的。但是如果不用通用的分頁存儲(chǔ)過程，則意味著必須為每個(gè)具體的分頁查詢寫一個(gè)分頁存儲(chǔ)過程，這會(huì)增加不少的工作量。
經(jīng)過幾天的時(shí)間考慮之后，想到了一個(gè)用代碼來生成參數(shù)化的通用分頁查詢語句的解決方案。代碼如下：
代碼如下:

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> 
/// 當(dāng)前頁數(shù) 
/// </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> 
/// 生成查詢記錄總數(shù)的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> 
/// 生成分頁查詢語句，包含記錄總數(shù) 
/// </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語句是曾對(duì)SQL SERVER 2005以上版本的，希望這些代碼對(duì)大家有用
 
 




聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com