参数化查询为什么能够防止SQL注入 -电脑资料

电脑资料时间：2019-01-01 我要投稿

【www.unjs.com - 电脑资料】

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

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

。

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

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

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

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

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

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

首先创建一张表Users:

<span>CREATE</span><span>TABLE</span>[dbo].[Users]([Id] [uniqueidentifier]<span>NOT</span><span>NULL</span>,[UserId] [<span>int</span>]<span>NOT</span><span>NULL</span>,[UserName] [<span>varchar</span>](50)<span>NULL</span>,[Password] [<span>varchar</span>](50)<span>NOT</span><span>NULL</span>,<span>CONSTRAINT</span>[PK_Users]<span>PRIMARY</span><span>KEY</span><span>CLUSTERED</span>([Id]<span>ASC</span>)<span>WITH</span>(PAD_INDEX  =<span>OFF</span>, STATISTICS_NORECOMPUTE  =<span>OFF</span>, IGNORE_DUP_KEY =<span>OFF</span>, ALLOW_ROW_LOCKS  =<span>ON</span>, ALLOW_PAGE_LOCKS  =<span>ON</span>)<span>ON</span>[<span>PRIMARY</span>])<span>ON</span>[<span>PRIMARY</span>]

插入一些数据：

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

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

验证用户登录的sql 如下：

<span>select</span><span>COUNT</span>(*)<span>from</span>Users<span>where</span>Password =<span>'a'</span><span>and</span>UserName =<span>'b'</span>

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

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

可以看到执行结果：

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

注入的代码如下：

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

这里有人将UserName设置为了 “b'or1=1 –”.

实际执行的SQL就变成了如下：

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

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

<span>class</span>Program{<span>private</span><span>static</span><span>string</span>connectionString =<span>"Data Source=.;Initial Catalog=Test;Integrated Security=True"</span>;<span>static</span><span>void</span>Main(<span>string</span>[] args)    {        Login(<span>"b"</span>,<span>"a"</span>);        Login(<span>"b' or 1=1--"</span>,<span>"a"</span>);    }<span>private</span><span>static</span><span>void</span>Login(<span>string</span>userName,<span>string</span>password)    {<span>using</span>(SqlConnection conn =<span>new</span>SqlConnection(connectionString))        {            conn.Open();            SqlCommand comm =<span>new</span>SqlCommand();            comm.Connection = conn;<span>//为每一条数据添加一个参数</span>comm.CommandText =<span>"select COUNT(*) from Users where Password = @Password and UserName = @UserName"</span>;            comm.Parameters.AddRange(<span>new</span>SqlParameter[]{<span>new</span>SqlParameter(<span>"@Password"</span>, SqlDbType.VarChar) { Value = password},<span>new</span>SqlParameter(<span>"@UserName"</span>, SqlDbType.VarChar) { Value = userName},            });            comm.ExecuteNonQuery();        }    }}

实际执行的SQL 如下所示：

<span>exec</span>sp_executesql N<span>'select COUNT(*) from Users where Password = @Password and UserName = @UserName'</span>,N<span>'@Password varchar(1),@UserName varchar(1)'</span>,@Password=<span>'a'</span>,@UserName=<span>'b'</span>

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

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

1：<strong>参数过滤</strong>，可以看到 @UserName=<span>'b'</span><span>' or 1=1—'</span>

2：<strong><span>执行计划重用</span></strong>

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

电脑资料

《参数化查询为什么能够防止SQL注入》(https://www.unjs.com)。

首先分析SQL注入的本质，

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

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

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

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

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

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

红黑联盟有篇文章： Sql Server参数化查询之where in和like实现详解

在这篇文章中有这么一段：

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

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

比如下面的代码：

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

执行的SQL 如下：

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

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

如果你将UserID设置为”

1,2,3,4); delete from Users;—-

”,那么执行的SQL就是下面这样：

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

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

任何动态的执行SQL 都有注入的风险，因为动态意味着不重用执行计划，而如果不重用执行计划的话，那么就基本上无法保证你写的SQL所表示的意思就是你要表达的意思。

这就好像小时候的填空题，查找密码是(____) 并且用户名是(____)的用户。

不管你填的是什么值，我所表达的就是这个意思。

最后再总结一句：因为参数化查询可以重用执行计划，并且如果重用执行计划的话，SQL所要表达的语义就不会变化，所以就可以防止SQL注入,如果不能重用执行计划，就有可能出现SQL注入，存储过程也是一样的道理，因为可以重用执行计划

相关文章