SQL参数化查询

SQL参数化查询

一、以往的防御方式


以前对付这种漏洞的方式主要有三种:

  • 字符串检测:限定内容只能由英文、数字等常规字符,如果检查到用户输入有特殊字符,直接拒绝。但缺点是,系统 中不可避免地会有些内容包含特殊字符,这时候总不能拒绝入库。
  • 字符串替换:把危险字符替换成其他字符,缺点是危险字符可能有很多,一一枚举替换相当麻烦,也可能有漏网之 鱼。
  • 存储过程:把参数传到存储过程进行处理,但并不是所有数据库都支持存储过程。如果存储过程中执行的命令也是通 过拼接字符串出来的,还是会有漏洞。

二、什么是参数化查询?


    一个简单理解参数化查询的方式是把它看做只是一个T-SQL查询,它接受控制这个查询返回什么的参数。通过使用不同的参数,一个参数化查询返回不同的结果。要获得一个参数化查询,你需要以一种特定的方式来编写你的代码,或它需要满足一组特定的标准。
    有两种不同的方式来创建参数化查询。第一个方式是让查询优化器自动地参数化你的查询。另一个方式是通过以一个特定方式来编写你的T-SQL代码,并将它传递给sp_executesql系统存储过程,从而编程一个参数化查询。


         这样的解释还是有点模糊,先看一例:

例一:参数化查询


    参数化查询(Parameterized Query 或 Parameterized Statement)是访问数据库时,在需要填入数值或数据的地方,使用参数 (Parameter) 来给值。

    在使用参数化查询的情况下,数据库服务器不会将参数的内容视为SQL指令的一部份来处理,而是在数据库完成SQL指令的编译后,才套用参数运行,因此就算参数中含有指令,也不会被数据库运行。Access、SQL Server、MySQL、SQLite等常用数据库都支持参数化查询。
[csharp]  view plain  copy
  1. //在ASP.NET程序中使用参数化查询  
  2.   
  3. //ASP.NET环境下的查询化查询也是通过Connection对象和Command对象完成。如果数据库是SQL Server,就可以用有名字的参数了,格式是“@”字符加上参数名。  
  4.   
  5. SqlConnection conn = new SqlConnection("server=(local)\\SQL2005;user id=sa;pwd=12345;initial catalog=TestDb");  
  6. conn.Open();  
  7.   
  8. SqlCommand cmd = new SqlCommand(“SELECT TOP 1 * FROM [User] WHERE UserName = @UserName AND Password = @Password“);  
  9. cmd.Connection = conn;  
  10. cmd.Parameters.AddWithValue(”UserName”, “user01″);  
  11. cmd.Parameters.AddWithValue(”Password”, “123456″);  
  12.   
  13. SqlDataReader reader = cmd.ExecuteReader();  
  14. reader.Read();  
  15. int userId = reader.GetInt32(0);  
  16.   
  17. reader.Close();  
  18. conn.Close();  

参数化查询被喻为最有效防止SQL注入的方法,那么存储过程一定是参数化过后的吗?

如果存储过得利用传递进来的参数,再次进行动态SQL拼接,这样还算做是参数化过后的吗?如果存储过程一定是参数化过后的,那么是不是意味着,只要使用存储过程就具有参数化查询的全部优点了?
如下存储过程:
[sql]  view plain  copy
  1. create procedure pro_getCustomers  
  2. (  
  3.     @whereSql nvarchar(max)  
  4. )  
  5. as  
  6. declare @sql nvarchar(max)  
  7. set @sql=N'select * from dbo.Customer ' + @whereSql  
  8. exec(@sql)  
  9. Go  
  10. --如果我要在ADO.NET中参数化查询这个存储过程,以防止SQL注入,我该怎么办呢?比如:  
  11. exec pro_getCustomers 'where Name=@name'  
这种方法没有办法防止注入,你能做的就是对字符串进行过滤.
拼接SQL是:
[sql]  view plain  copy
  1. "select * from customer where 1=1"   + " and name=@name" + " and sex=@sex"  
也就是判断参数化查询。只不过是动态地组装查询限制条件。

动态拼接SQL,而且是参数化查询的SQL语句是没有问题的。

ADO.NET中被SQL注入的问题,必须过于关键字。原作者的测试代码如下:
[sql]  view plain  copy
  1. USE [B2CShop]  
  2. GO  
  3. SET ANSI_NULLS ON  
  4. GO  
  5. SET QUOTED_IDENTIFIER ON  
  6. GO  
  7. ALTER procedure [dbo].[pro_getCustomers]  
  8. (  
  9.     @whereSql nvarchar(max),  
  10.     @paramNameList nvarchar(max),  
  11.     @paramValueList nvarchar(max)  
  12. )  
  13. as  
  14. declare @sql nvarchar(max)  
  15. set @sql=N'select * from dbo.Customer ' + @whereSql  
  16. exec sp_executesql @sql, @paramNameList , @paramValueList  
  17. go  

[csharp]  view plain  copy
  1. /// <summary>  
  2.     /// 动态执行存储过程  
  3.     /// </summary>  
  4.     /// <param name="searchedName">要查询的姓名的关键字</param>  
  5.     /// <returns>实体集合</returns>  
  6.     public static List<Customer> ExecDynamicProc(string searchedName)  
  7.     {  
  8.       SqlParameter[] values = new SqlParameter[]  
  9.       {  
  10.         new SqlParameter("@whereSql""where name like @name"),  
  11.         new SqlParameter("@paramNameList","@name nvarchar(50)"),  
  12.         new SqlParameter("@paramValueList","@name='%"+ searchedName +"%'")  
  13.       };  
  14.       return DBHelper.ExecuteProc("proc_GetCustomerPagerBySearch",values);  
  15.     }  

[csharp]  view plain  copy
  1. /// <summary>  
  2.     /// 从搜索类里面拼接参数化的SQL字符串  
  3.     /// </summary>  
  4.     /// <param name="search">搜索类</param>  
  5.     /// <param name="sqlParams">搜索的参数,不能传入Null</param>  
  6.     /// <returns>安全的SQL语句</returns>  
  7.     private static string GetSafeSqlBySearchItem(CustomerSearch search, ref List<SqlParameter> sqlParams)  
  8.     {  
  9.       StringBuilder safeSqlAppend = new StringBuilder();  
  10.       if (search != null)  
  11.       {  
  12.         if (!string.IsNullOrEmpty(search.NameEquals))  
  13.         {  
  14.           safeSqlAppend.Append(" and Name=@nameEquals");  
  15.           sqlParams.Add(new SqlParameter("@nameEquals", search.NameEquals));  
  16.         }  
  17.         if (!string.IsNullOrEmpty(search.NameContains))  
  18.         {  
  19.           safeSqlAppend.Append(" and Name like @nameContains");  
  20.           sqlParams.Add(new SqlParameter("@nameContains""%" + search.NameContains + "%"));  
  21.         }  
  22.       }  
  23.       return safeSqlAppend.ToString();  
  24.     }  

[csharp]  view plain  copy
  1. /// <summary>  
  2.     /// 得到分页用的SQL语句  
  3.     /// </summary>  
  4.     /// <param name="columnNameItems">要查询的列名,多个列名用逗号分隔。传入Empty或Null时,则默认查询出所有的列</param>  
  5.     /// <param name="tableName">表名,不能为Null和Empty,默认的SQL别名为a</param>  
  6.     /// <param name="joinOtherTable">连接其他的表,可以传入Null或Empty。调用的时候,可以类似如:inner join departInfo as b on a.departInfoId=b.Id</param>  
  7.     /// <param name="whereSql">搜索条件,即在“where 1=1 ”后面写条件,可以传入Null或Empty。调用的时候,可以类似如:and b.Price=@beginPrice </param>  
  8.     /// <param name="orderColumnNameAndAscOrDesc">排序的列名以及Asc或Desc,即在“order by”后面写排序项,不能为Null和Empty。比如“Id asc, name desc”</param>  
  9.     /// <param name="pageNumber">当前页的页码,最小值应该为1</param>  
  10.     /// <param name="pageSize">每页显示的记录数,最小值应该为1</param>  
  11.     /// <returns>SQL语句</returns>  
  12.     internal static string GetPagerTSql(string columnNameItems, string tableName, string joinOtherTable, string whereSql, string orderColumnNameAndAscOrDesc, int pageNumber, int pageSize)  
  13.     {  
  14.       if (string.IsNullOrEmpty(tableName))  
  15.       {  
  16.         throw new ArgumentNullException("tableName", String.Format(CultureInfo.CurrentCulture, DALResource.Common_NullOrEmpty));  
  17.       }  
  18.       if (string.IsNullOrEmpty(orderColumnNameAndAscOrDesc))  
  19.       {  
  20.         throw new ArgumentNullException("orderColumnNameAndAscOrDesc", String.Format(CultureInfo.CurrentCulture, DALResource.Common_NullOrEmpty));  
  21.       }  
  22.       if (string.IsNullOrEmpty(columnNameItems))  
  23.       {  
  24.         columnNameItems = "a.*";  
  25.       }  
  26.       if (pageNumber < 1)  
  27.       {  
  28.         pageNumber = 1;  
  29.       }  
  30.       if (pageSize < 1)  
  31.       {  
  32.         pageSize = 1;  
  33.       }  
  34.       int beginNumber = (pageNumber - 1) * pageSize + 1;  
  35.       int endNumber = pageNumber * pageSize;  
  36.       string sqlPager = string.Format("select * from (select row_number() over(order by {1}) as __MyNewId, {0} from {2} as a {3} where 1=1 {4}) as __MyTempTable where __MyNewId between {5} and {6} order by __MyNewId asc;", columnNameItems, orderColumnNameAndAscOrDesc, tableName, joinOtherTable, whereSql, beginNumber, endNumber);  
  37.       string sqlPagerCount = string.Format("select @__returnCount=COUNT(*) from {0} as a {1} where 1=1 {2};",tableName, joinOtherTable, whereSql);  
  38.       return sqlPager + sqlPagerCount;  
  39.     }  

例二:登录错误次数限制及参数化传递防止SQL注入


[csharp]  view plain  copy
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.Data;  
  5. using System.Drawing;  
  6. using System.Linq;  
  7. using System.Text;  
  8. using System.Windows.Forms;  
  9. using System.Configuration;  
  10. using System.Data.SqlClient;  
  11.   
  12. namespace 复习登录  
  13. {  
  14.     public partial class login : Form  
  15.     {  
  16.         public login()  
  17.         {  
  18.             InitializeComponent();  
  19.         }  
  20.         string str = ConfigurationManager.ConnectionStrings["sqlserver2008"].ConnectionString;  
  21.         DateTime dt1;  
  22.         private void btn_login_Click(object sender, EventArgs e)  
  23.         {  
  24.             using(SqlConnection cnn=new SqlConnection(str))  
  25.             {  
  26.                 using (SqlCommand cmd=cnn.CreateCommand())  
  27.                 {  
  28.                     cmd.CommandText = "select * from T_User where username=@username";  
  29.                     cmd.Parameters.AddWithValue("@username", txt_username.Text);  
  30.                     cnn.Open();  
  31.                     using (SqlDataReader reader = cmd.ExecuteReader())  
  32.                     {  
  33.                         if (reader.Read())  
  34.                         {  
  35.                             int Error = Convert.ToInt32(reader["Error"].ToString());  
  36.                             if (Error >= 3)  
  37.                             {  
  38.   
  39.                                 string sqltime = reader["Errortime"].ToString();  
  40.                                 dt1 = DateTime.Parse(sqltime);  
  41.                                 DateTime dt2 = DateTime.Now;  
  42.                                 TimeSpan ts = dt2 - dt1;  
  43.                                 if (ts.TotalMinutes < 5)  
  44.                                 {  
  45.                                     MessageBox.Show("对不起,你已经输入3次连续错误密码,系统已经将账户冻结,请在五分钟后再试");  
  46.                                     return;  
  47.                                 }  
  48.                                 else  
  49.                                 {  
  50.                                     clearerror();  
  51.                                 }  
  52.   
  53.                             }  
  54.                             string sqlpassword = reader["Password"].ToString();  
  55.                             if (sqlpassword == txt_password.Text)  
  56.                             {  
  57.                                 clearerror();  
  58.                                 if (txt_username.Text.ToUpper() == "ADMIN")  
  59.                                 {  
  60.                                     this.Hide();  
  61.                                     main m = new main();  
  62.                                     m.Show();  
  63.                                 }  
  64.                                 else  
  65.                                 {  
  66.                                     MessageBox.Show("登录成功");  
  67.                                 }  
  68.                             }  
  69.                             else  
  70.                             {  
  71.                                 MessageBox.Show("密码错误");  
  72.                                 adderror();  
  73.                             }  
  74.                         }  
  75.                         else  
  76.                         {  
  77.                             MessageBox.Show("用户名不存在");  
  78.                         }  
  79.                           
  80.                     }  
  81.                 }  
  82.             }  
  83.         }  
  84.   
  85.         private void adderror()  
  86.         {  
  87.             dt1 = DateTime.Now;  
  88.             using (SqlConnection cnn=new SqlConnection(str))  
  89.             {  
  90.                 using (SqlCommand cmd=cnn.CreateCommand())  
  91.                 {  
  92.                     cnn.Open();  
  93.                     cmd.CommandText = "update T_User set Error=Error+1,Errortime=@Errortime where username=@username";  
  94.                     cmd.Parameters.AddWithValue("@Errortime", dt1);  
  95.                     cmd.Parameters.AddWithValue("@username", txt_username.Text);  
  96.                     cmd.ExecuteNonQuery();  
  97.   
  98.                 }  
  99.             }  
  100.         }  
  101.         private void clearerror()  
  102.         {  
  103.             using (SqlConnection cnn=new SqlConnection(str))  
  104.             {  
  105.                 using (SqlCommand cmd=cnn.CreateCommand())  
  106.                 {  
  107.                     cnn.Open();  
  108.                     cmd.CommandText = "update T_User set Error=0 where username=@username";  
  109.                     cmd.Parameters.Add(new SqlParameter("username", txt_username.Text));  
  110.                     cmd.ExecuteNonQuery();  
  111.                 }  
  112.             }  
  113.         }  
  114.     }  
  115. }  
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值