参数化查询为什么能够防止SQL注入

安全 数据安全
很多人都知道SQL注入,也知道SQL参数化查询可以防止SQL注入,可为什么能防止注入却并不是很多人都知道的。

很多人都知道SQL注入,也知道SQL参数化查询可以防止SQL注入,可为什么能防止注入却并不是很多人都知道的。

本文主要讲述的是这个问题,也许你在部分文章中看到过这块内容,当然了看看也无妨。

首先:我们要了解SQL收到一个指令后所做的事情:

具体细节可以查看文章:Sql Server 编译、重编译与执行计划重用原理

在这里,我简单的表示为: 收到指令 -> 编译SQL生成执行计划 ->选择执行计划 ->执行执行计划。

具体可能有点不一样,但大致的步骤如上所示。

接着我们来分析为什么拼接SQL 字符串会导致SQL注入的风险呢?

首先创建一张表Users:

  1. CREATE TABLE [dbo].[Users](  
  2. [Id] [uniqueidentifier] NOT NULL,  
  3. [UserId] [intNOT NULL,  
  4. [UserName] [varchar](50) NULL,  
  5. [Password] [varchar](50) NOT NULL,  
  6.  CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED   
  7. (  
  8. [Id] ASC 
  9. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
  10. ON [PRIMARY

3F3ECD42B7A24B139ECA0A7D584CA195

插入一些数据:

  1. INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),1,'name1','pwd1');    
  2. INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),2,'name2','pwd2');    
  3. INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),3,'name3','pwd3');    
  4. INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),4,'name4','pwd4');    
  5. INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),  

假设我们有个用户登录的页面,代码如下:

验证用户登录的sql 如下:

  1. select COUNT(*) from Users where Password = 'a' and UserName = 'b'  

这段代码返回Password 和UserName都匹配的用户数量,如果大于1的话,那么就代表用户存在。

本文不讨论SQL 中的密码策略,也不讨论代码规范,主要是讲为什么能够防止SQL注入,请一些同学不要纠结与某些代码,或者和SQL注入无关的主题。

可以看到执行结果:

15C19A6170754E21A52A79AAA01B9B48

这个是SQL profile 跟踪的SQL 语句。

5CB6FB63846740C494C6466FE27D2B3C

注入的代码如下:

select COUNT(*) from Users where Password = 'a' and UserName = 'b' or 1=1—'

这里有人将UserName设置为了 “b' or 1=1 –”.
 
实际执行的SQL就变成了如下:

782A96FEE0784A39B5500CAE267B90EE

5A8FCD361FFE414AB18AEE5C9ED681DE

可以很明显的看到SQL注入成功了。

很多人都知道参数化查询可以避免上面出现的注入问题,比如下面的代码:

  1. class Program  
  2. {  
  3.     private static string connectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";  
  4.  
  5.     static void Main(string[] args)  
  6.     {  
  7.         Login("b""a");  
  8.         Login("b' or 1=1--""a");  
  9.     }  
  10.  
  11.     private static void Login(string userName, string password)  
  12.     {  
  13.         using (SqlConnection conn = new SqlConnection(connectionString))  
  14.         {  
  15.             conn.Open();  
  16.             SqlCommand comm = new SqlCommand();  
  17.             comm.Connection = conn;  
  18.             //为每一条数据添加一个参数  
  19.             comm.CommandText = "select COUNT(*) from Users where Password = @Password and UserName = @UserName";  
  20.             comm.Parameters.AddRange(  
  21.             new SqlParameter[]{                          
  22.                 new SqlParameter("@Password", SqlDbType.VarChar) { Value = password},  
  23.                 new SqlParameter("@UserName", SqlDbType.VarChar) { Value = userName},  
  24.             });  
  25.  
  26.             comm.ExecuteNonQuery();  
  27.         }  
  28.     }  

实际执行的SQL 如下所示:

  1. exec sp_executesql N'select COUNT(*) from Users where Password = @Password and UserName = @UserName',N'@Password varchar(1),@UserName varchar(1)',@Password='a',@UserName='b' 
  2. exec sp_executesql N'select COUNT(*) from Users where Password = @Password and UserName = @UserName',N'@Password varchar(1),@UserName varchar(11)',@Password='a',@UserName='b'' or 1=1—'  

可以看到参数化查询主要做了这些事情:

1:参数过滤,可以看到 @UserName='b'' or 1=1—'

2:执行计划重用

因为执行计划被重用,所以可以防止SQL注入。

首先分析SQL注入的本质,

用户写了一段SQL 用来表示查找密码是a的,用户名是b的所有用户的数量。

通过注入SQL,这段SQL现在表示的含义是查找(密码是a的,并且用户名是b的,) 或者1=1 的所有用户的数量。

可以看到SQL的语意发生了改变,为什么发生了改变呢?,因为没有重用以前的执行计划,因为对注入后的SQL语句重新进行了编译,因为重新执行了语法解析。所以要保证SQL语义不变,即我想要表达SQL就是我想表达的意思,不是别的注入后的意思,就应该重用执行计划。

如果不能够重用执行计划,那么就有SQL注入的风险,因为SQL的语意有可能会变化,所表达的查询就可能变化。

在SQL Server 中查询执行计划可以使用下面的脚本:

  1. DBCC FreeProccache  
  2.  
  3. select total_elapsed_time / execution_count 平均时间,total_logical_reads/execution_count 逻辑读,  
  4. usecounts 重用次数,SUBSTRING(d.text, (statement_start_offset/2) + 1,  
  5.          ((CASE statement_end_offset   
  6.           WHEN -1 THEN DATALENGTH(text)  
  7.           ELSE statement_end_offset END   
  8.             - statement_start_offset)/2) + 1) 语句执行 from sys.dm_exec_cached_plans a  
  9. cross apply sys.dm_exec_query_plan(a.plan_handle) c  
  10. ,sys.dm_exec_query_stats b  
  11. cross apply sys.dm_exec_sql_text(b.sql_handle) d  
  12. --where a.plan_handle=b.plan_handle and total_logical_reads/execution_count>4000  
  13. ORDER BY total_elapsed_time / execution_count DESC

18EFAED775BF4DB9A36C57B39EC6913D

有篇文章《Sql Server参数化查询之where in和like实现详解》中有这么一段:

image

这里作者有一句话:”不过这种写法和直接拼SQL执行没啥实质性的区别”

任何拼接SQL的方式都有SQL注入的风险,所以如果没有实质性的区别的话,那么使用exec 动态执行SQL是不能防止SQL注入的。

比如下面的代码:

  1. private static void TestMethod()  
  2. {  
  3.     using (SqlConnection conn = new SqlConnection(connectionString))  
  4.     {  
  5.         conn.Open();  
  6.         SqlCommand comm = new SqlCommand();  
  7.         comm.Connection = conn;  
  8.         //使用exec动态执行SQL   
  9.         //实际执行的查询计划为(@UserID varchar(max))select * from Users(nolock) where UserID in (1,2,3,4)    
  10.         //不是预期的(@UserID varchar(max))exec('select * from Users(nolock) where UserID in ('+@UserID+')')      
  11.         comm.CommandText = "exec('select * from Users(nolock) where UserID in ('+@UserID+')')";  
  12.         comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });  
  13.         //comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4); delete from Users;--" });  
  14.         comm.ExecuteNonQuery();  
  15.     }  

执行的SQL 如下:

  1. exec sp_executesql N'exec(''select * from Users(nolock) where UserID in (''+@UserID+'')'')',N'@UserID varchar(max) ',@UserID='1,2,3,4' 

D25E99E053D549AF955518AD0A320259

可以看到SQL语句并没有参数化查询。

如果你将UserID设置为”1,2,3,4); delete from Users;—-”,那么执行的SQL就是下面这样:

exec sp_executesql N'exec(''select * from Users(nolock) where UserID in (''+@UserID+'')'')',N'@UserID varchar(max) ',@UserID='1,2,3,4); delete from Users;--'

不要以为加了个@UserID 就代表能够防止SQL注入,实际执行的SQL 如下:

3C50EFE68418448496BAC7773067AB6F

任何动态的执行SQL 都有注入的风险,因为动态意味着不重用执行计划,而如果不重用执行计划的话,那么就基本上无法保证你写的SQL所表示的意思就是你要表达的意思。 这就好像小时候的填空题,查找密码是(____) 并且用户名是(____)的用户。不管你填的是什么值,我所表达的就是这个意思。 最后再总结一句:因为参数化查询可以重用执行计划,并且如果重用执行计划的话,SQL所要表达的语义就不会变化,所以就可以防止SQL注入,如果不能重用执行计划,就有可能出现SQL注入,存储过程也是一样的道理,因为可以重用执行计划。

责任编辑:蓝雨泪 来源: 红黑联盟
相关推荐

2013-01-15 10:53:36

2009-07-24 16:59:57

iBatis模糊查询

2020-09-28 09:30:13

mybatis

2009-02-04 16:51:48

2020-08-07 08:13:08

SQL攻击模式

2010-10-22 15:18:18

SQL注入漏洞

2016-07-26 09:10:47

2011-12-30 11:04:14

2014-05-26 09:32:15

2009-03-10 08:05:19

2011-05-06 16:22:58

2017-03-01 14:16:20

2013-04-26 11:26:00

2013-08-01 09:15:42

Xen Hypervi 虚拟化

2009-11-02 13:47:09

2009-03-14 16:50:38

网站安全meter程序

2023-08-01 08:00:00

SQLWeb应用安全

2010-06-17 14:43:29

SQL Server参

2019-02-19 09:25:42

2018-01-30 08:47:46

存储查询性能
点赞
收藏

51CTO技术栈公众号