SQL anti-injection Code (1/2)

The following code generates SQL statements that were previous to SQL Server 2005, and I hope that the code is useful to everyone

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 temps Tutorial QL =
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));
}
}

Home 1 2 last page