【存储过程】ALTER PROCEDURE search_gal @cc nvarchar (1), @sWhat nvarchar (20) AS if @cc = '0' select * from gal where 游戏名 like '%' + @sWhat + '%' order by gameid desc if @cc = '1' select * from gal where 年份 like '%' + @sWhat + '%' order by gameid desc if @cc = '2' select * from gal where key1 like '%' + @sWhat + '%' order by gameid desc if @cc = '3' select * from gal where description like '%' + @sWhat + '%' order by gameid desc if @cc = '4' select * from gal where 游戏名 like '%' + @sWhat + '%' or 年份 like '%' + @sWhat + '%' or key1 like '%' + @sWhat + '%' or description like '%' + @sWhat + '%' order by gameid desc 【cs内的应用】 SqlCommand cmd = new SqlCommand("search_gal", myconn); cmd.CommandType = CommandType.StoredProcedure; //cmd.CommandText = "search_gal"; //cmd.Connection = myconn; SqlDataAdapter da = new SqlDataAdapter(cmd); da.SelectCommand.Parameters.Add("@key", SqlDbType.NVarChar,20); da.SelectCommand.Parameters["@key"].Value = this.TextBox1.Text.Trim().Replace("'", "''"); DataSet ds = new DataSet(); da.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind();
上面的【存储过程】应该是这个,刚刚贴错了。存储过程:Create PROCEDURE search_gal @key nvarchar (20) AS select * from gal where key1 like '%'+ @key + '%'
//防注入类 public class ProcessRequest { public static void StartProcessRequest() { string sqlErrorPage = "ErrorPage.htm"; try { string getkeys = ""; if (System.Web.HttpContext.Current.Request.QueryString != null) { for (int i = 0; i < System.Web.HttpContext.Current.Request.QueryString.Count; i++) { getkeys = System.Web.HttpContext.Current.Request.QueryString.Keys[i]; if (!ProcessSqlStr(System.Web.HttpContext.Current.Request.QueryString[getkeys])) { System.Web.HttpContext.Current.Response.Redirect(sqlErrorPage + ""); System.Web.HttpContext.Current.Response.End(); } } } if (System.Web.HttpContext.Current.Request.Form != null) { for (int i = 0; i < System.Web.HttpContext.Current.Request.Form.Count; i++) { getkeys = System.Web.HttpContext.Current.Request.Form.Keys[i]; if (!ProcessSqlStr(System.Web.HttpContext.Current.Request.Form[getkeys])) { System.Web.HttpContext.Current.Response.Redirect(sqlErrorPage + ""); System.Web.HttpContext.Current.Response.End(); } } } } catch { System.Web.HttpContext.Current.Response.Redirect(sqlErrorPage + ""); System.Web.HttpContext.Current.Response.End(); } } public static bool ProcessSqlStr(string Str) { bool ReturnValue = true; try { if (Str != "") { string SqlStr = ";|and|exec|insert|select|delete|update|count|*|chr|mid|master|truncate|char|declare"; string[] anySqlStr = SqlStr.Split('|'); foreach (string s in anySqlStr) { if (Str.IndexOf(s) > -1) { ReturnValue = false; } } } } catch { ReturnValue = false; } return ReturnValue; } } protected void Page_Load(object sender, EventArgs e) { ProcessRequest.StartProcessRequest(); 。 }
其一,过滤页面参数,把页面传来的参数中的“=、'、@、>、<”这些都虑掉。
其二,sql,使用@方式传递参数。
/// <summary>
/// SQLString 的摘要说明。
/// </summary>
public class SqlStringFormat
{
/// <summary>
/// 公有静态方法,将文本转换成适合在Sql语句里使用的字符串。
/// </summary>
/// <returns>转换后文本</returns>
public static String GetQuotedString(String pStr)
{
return ("'" + pStr.Replace("'","''") + "'");
}
/// <summary>
///SQL注入过滤
/// </summary>
/// <param name="InText">要过滤的字符串 </param>
/// <returns>如果参数存在不安全字符,则返回true </returns>
public static bool SqlFilter(string InText)
{
string word = "and|exec|insert|select|delete|update|chr|mid|master|or|truncate|char|declare|join";
if (InText == null)
return false;
foreach (string i in word.Split('|'))
{
if ((InText.ToLower().IndexOf(i + " ") > -1) || (InText.ToLower().IndexOf(" " + i) > -1))
{
return true;
}
}
return false;
}
public static string substr(string str, int length)
{
if (str.Length < length)
{
return str;
}
else
{
return str.Substring(0, length); //+ "…";
}
}
}
http://community.csdn.net/Expert/topicview.asp?id=5569710
http://topic.csdn.net/u/20080510/16/29c515de-c4d2-41fe-a1dc-df84de18f9b7.html
http://topic.csdn.net/u/20080521/09/dad3eaba-bfc7-483c-98cd-d310f9a76ff0.html?seed=596201967
{
string str = theString;
if (str == string.Empty || str == null)
return "";
//str = str.Replace("&", "&");
str = str.Replace("'", "'");
str = str.Replace("\"\"", """);
//str = str.Replace("<", "<");
//str = str.Replace(">", ">");
str = str.Replace("xp_cmdshell", "");
str = str.Replace("exec master", "");
str = str.Replace("net localgroup administrators", "");
str = Regex.Replace(str, "(w)(here)", "$1here", RegexOptions.IgnoreCase);
str = Regex.Replace(str, "(s)(elect)", "$1elect", RegexOptions.IgnoreCase);
str = Regex.Replace(str, "(i)(nsert)", "$1nsert", RegexOptions.IgnoreCase);
str = Regex.Replace(str, "(c)(reate)", "$1reate", RegexOptions.IgnoreCase);
str = Regex.Replace(str, "(d)(rop)", "$1rop", RegexOptions.IgnoreCase);
str = Regex.Replace(str, "(a)(lter)", "$1lter", RegexOptions.IgnoreCase);
str = Regex.Replace(str, "(d)(elete)", "$1elete", RegexOptions.IgnoreCase);
str = Regex.Replace(str, "(u)(pdate)", "$1pdate", RegexOptions.IgnoreCase);
return str;
}
@cc nvarchar (1),
@sWhat nvarchar (20)
AS
if @cc = '0'
select * from gal where 游戏名 like '%' + @sWhat + '%' order by gameid desc
if @cc = '1'
select * from gal where 年份 like '%' + @sWhat + '%' order by gameid desc
if @cc = '2'
select * from gal where key1 like '%' + @sWhat + '%' order by gameid desc
if @cc = '3'
select * from gal where description like '%' + @sWhat + '%' order by gameid desc
if @cc = '4'
select * from gal where 游戏名 like '%' + @sWhat + '%' or 年份 like '%' + @sWhat + '%' or key1 like '%' + @sWhat + '%' or description like '%' + @sWhat + '%' order by gameid desc
【cs内的应用】
SqlCommand cmd = new SqlCommand("search_gal", myconn);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.CommandText = "search_gal";
//cmd.Connection = myconn;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.SelectCommand.Parameters.Add("@key", SqlDbType.NVarChar,20);
da.SelectCommand.Parameters["@key"].Value = this.TextBox1.Text.Trim().Replace("'", "''");
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
@key nvarchar (20)
AS
select * from gal where key1 like '%'+ @key + '%'
public class ProcessRequest
{
public static void StartProcessRequest()
{
string sqlErrorPage = "ErrorPage.htm";
try
{
string getkeys = "";
if (System.Web.HttpContext.Current.Request.QueryString != null)
{
for (int i = 0; i < System.Web.HttpContext.Current.Request.QueryString.Count; i++)
{
getkeys = System.Web.HttpContext.Current.Request.QueryString.Keys[i];
if (!ProcessSqlStr(System.Web.HttpContext.Current.Request.QueryString[getkeys]))
{
System.Web.HttpContext.Current.Response.Redirect(sqlErrorPage + "");
System.Web.HttpContext.Current.Response.End();
}
}
}
if (System.Web.HttpContext.Current.Request.Form != null)
{
for (int i = 0; i < System.Web.HttpContext.Current.Request.Form.Count; i++)
{
getkeys = System.Web.HttpContext.Current.Request.Form.Keys[i];
if (!ProcessSqlStr(System.Web.HttpContext.Current.Request.Form[getkeys]))
{
System.Web.HttpContext.Current.Response.Redirect(sqlErrorPage + "");
System.Web.HttpContext.Current.Response.End();
}
}
}
}
catch
{
System.Web.HttpContext.Current.Response.Redirect(sqlErrorPage + "");
System.Web.HttpContext.Current.Response.End();
}
} public static bool ProcessSqlStr(string Str)
{
bool ReturnValue = true;
try
{
if (Str != "")
{
string SqlStr = ";|and|exec|insert|select|delete|update|count|*|chr|mid|master|truncate|char|declare";
string[] anySqlStr = SqlStr.Split('|');
foreach (string s in anySqlStr)
{
if (Str.IndexOf(s) > -1)
{
ReturnValue = false;
}
}
}
}
catch
{
ReturnValue = false;
}
return ReturnValue;
}
}
protected void Page_Load(object sender, EventArgs e)
{
ProcessRequest.StartProcessRequest();
。 }