Anti-SQL injection: Generating a parameterized Universal paging query statement

Some time ago, I saw brother Yukai. This article, "so efficient and common paging stored procedure with SQL injection vulnerability", suddenly reminds me that a project is also using a tired common paging stored procedure. To use this generic stored procedure for paging queries, to prevent SQL injection, you can filter only the input parameters, such as converting a single quote "'" to two single quotes "", but this is unsafe, and powerful hackers can encode to bypass the single quote filter to effectively prevent SQL injection. Only parameterized queries are the final solution. The problem, however, is that this common paging stored procedure is not desirable because it is a concatenation of SQL statements within a stored procedure and cannot be modified to a parameterized query statement. However, if you do not use a common paging stored procedure, it means that you must write a paging stored procedure for each specific paging query, which can add a lot of work.

After several days of consideration, I thought of a solution that uses 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>
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>
Current page
</summary>
public int PageIndex
{
get {return _pageindex;}
set {_pageindex = value;}
}

/**////<summary>
Paging size
</summary>
public int PageSize
{
get {return _pagesize;}
set {_pagesize = value;}
}

/**////<summary>
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>
SQL statement that generates the total number of query records
</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>
Raw page query statement, including 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 () [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>
Raw Content Page Query statement
</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 () [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));
}
}