SQL anti-injection code
SQL anti-injection code
The SQL statements generated by the following code were used for SQL server 2005 and later versions. I hope these codes will be useful to you.
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 ();
}
/**////
/// Primary key
///
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 ;}
}
/**////
/// Current page number
///
Public int pageindex
{
Get {return _ pageindex ;}
Set {_ pageindex = value ;}
}
/**////
/// Page size
///
Public int pagesize
{
Get {return _ pagesize ;}
Set {_ pagesize = value ;}
}
/**////
/// Generate the cache key
///
///
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 );
}
/**////
/// SQL statement that generates the total number of query records
///
///
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 );
}
/**////
/// Generate a paging query statement, including the total number of records
///
///
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 temql =
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;
}
/**////
/// Generate a paging query statement
///
///
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 ));
}
}