Anti SQL injection generates parameterized generic paging query statements

Using this generic stored procedure paging query, want to prevent SQL injection, can only on the parameters of input filter, such as a single quotes "" into two single quotes" "'", but this practice is unsafe, fierce hackers can bypass the single quotes by encoding the filtering, to effectively prevent SQL injection, only parameterized queries is the final solution. However, the problem is that this general paging stored procedure is to splice SQL statements within the stored procedure, which cannot be modified to parameterized query statements, so this general paging stored procedure is undesirable. But if you don't use a generic paging stored procedure, that means you have to write one paging stored procedure for each specific paging query, which adds a lot of work.
After a few days of consideration, I came up with a solution that used code to generate parameterized generic paging query statements. The code is as follows:

 
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> 
///  A primary key  
/// </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> 
///  The current number of pages  
/// </summary> 
public int PageIndex 
{ 
get { return _pageIndex; } 
set { _pageIndex = value; } 
} 
/**//// <summary> 
///  Page size  
/// </summary> 
public int PageSize 
{ 
get { return _pageSize; } 
set { _pageSize = value; } 
} 
/**//// <summary> 
///  To generate cache 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> 
///  Generates the total number of query records SQL statements  
/// </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> 
///  Generate a paging query statement that contains the total number of records  
/// </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> 
///  Generate paging query statements  
/// </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)); 
} 
} 

Usage:

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

The statement generated by the a) GenerateCountSql () method is:
Select count(0) from TestTable Where 1=1 and ID= @ID
The statement generated by the b) GenerateSql() method is:
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
The statement generated by the c) GenerateSqlIncludetTotalRecords() method is:
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;

Note: the SQL statement generated by the above code was used for SQL SERVER version 2005 and above. I hope you find this code useful