参数化查询为什么能够防⽌SQL注⼊
很多⼈都知道SQL注⼊，也知道SQL参数化查询可以防⽌SQL注⼊，可为什么能防⽌注⼊却并不是很多⼈都知道的。
本⽂主要讲述的是这个问题，也许你在部分⽂章中看到过这块内容，当然了看看也⽆妨。
⾸先：我们要了解SQL收到⼀个指令后所做的事情：
具体细节可以查看⽂章：
在这⾥，我简单的表⽰为：收到指令 -> 编译SQL⽣成执⾏计划 ->选择执⾏计划 ->执⾏执⾏计划。
具体可能有点不⼀样，但⼤致的步骤如上所⽰。
接着我们来分析为什么拼接SQL 字符串会导致SQL注⼊的风险呢？
⾸先创建⼀张表Users:
CREATE TABLE [dbo].[Users](
[Id] [uniqueidentifier] NOT NULL,
[UserId] [int] NOT NULL,
[UserName] [varchar](50) NULL,
[Password] [varchar](50) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
插⼊⼀些数据：
INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),1,'name1','pwd1');
INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),2,'name2','pwd2');
INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),3,'name3','pwd3');
INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),4,'name4','pwd4');
INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),5,'name5','pwd5');
假设我们有个⽤户登录的页⾯，代码如下：
验证⽤户登录的sql 如下：
select COUNT(*) from Users where Password = 'a'and UserName = 'b'
这段代码返回Password 和UserName都匹配的⽤户数量，如果⼤于1的话，那么就代表⽤户存在。
本⽂不讨论SQL 中的密码策略，也不讨论代码规范，主要是讲为什么能够防⽌SQL注⼊，请⼀些同学不要纠结与某些代码，或者和SQL注⼊⽆关的主题。
可以看到执⾏结果：
这个是SQL profile 跟踪的SQL 语句。
注⼊的代码如下：
select COUNT(*) from Users where Password = 'a'and UserName = 'b'or 1=1—'
这⾥有⼈将UserName设置为了 “b' or 1=1 –”.
实际执⾏的SQL就变成了如下：
可以很明显的看到SQL注⼊成功了。
很多⼈都知道参数化查询可以避免上⾯出现的注⼊问题，⽐如下⾯的代码：
class Program
{
private static string connectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";
static void Main(string[] args)
{
Login("b", "a");
Login("b' or 1=1--", "a");
}
private static void Login(string userName, string password)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//为每⼀条数据添加⼀个参数
comm.CommandText = "select COUNT(*) from Users where Password = @Password and UserName = @UserName";
comm.Parameters.AddRange(
new SqlParameter[]{
new SqlParameter("@Password", SqlDbType.VarChar) { Value = password},
new SqlParameter("@UserName", SqlDbType.VarChar) { Value = userName},
});
comm.ExecuteNonQuery();
}
}
}
实际执⾏的SQL 如下所⽰：
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'
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 中查询执⾏计划可以使⽤下⾯的脚本：
DBCC FreeProccache
select total_elapsed_time / execution_count 平均时间,total_logical_reads/execution_count 逻辑读,
usecounts 重⽤次数,, (statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(text)
ELSE statement_end_offset END
- statement_start_offset)/2) + 1) 语句执⾏from sys.dm_exec_cached_plans a
cross apply sys.dm_exec_query_plan(a.plan_handle) c
,sys.dm_exec_query_stats b
cross apply sys.dm_exec_sql_text(b.sql_handle) d
--where a.plan_handle=b.plan_handle and total_logical_reads/execution_count>4000
ORDER BY total_elapsed_time / execution_count DESC;
博客园有篇⽂章：
在这篇⽂章中有这么⼀段：
这⾥作者有⼀句话：”不过这种写法和直接拼SQL执⾏没啥实质性的区别”
任何拼接SQL的⽅式都有SQL注⼊的风险，所以如果没有实质性的区别的话，那么使⽤exec 动态执⾏SQL是不能防⽌SQL注⼊的。
⽐如下⾯的代码：
private static void TestMethod()
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//使⽤exec动态执⾏SQL　
//实际执⾏的查询计划为(@UserID varchar(max))select * from Users(nolock) where UserID in (1,2,3,4) 
//不是预期的(@UserID varchar(max))exec('select * from Users(nolock) where UserID in ('+@UserID+')')
comm.CommandText = "exec('select * from Users(nolock) where UserID in ('+@UserID+')')";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
//comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4); delete from Users;--" });
comm.ExecuteNonQuery();
}offset指令是什么意思
}
执⾏的SQL 如下：
exec sp_executesql N'exec(''select * from Users(nolock) where UserID in (''+@UserID+'')'')',N'@UserI
D varchar(max) ',@UserID='1,2,3,4'
可以看到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 如下：
任何动态的执⾏SQL 都有注⼊的风险，因为动态意味着不重⽤执⾏计划，⽽如果不重⽤执⾏计划的话，那么就基本上⽆法保证你写的SQL所表⽰的意思就是你要表达的意思。
这就好像⼩时候的填空题，查密码是(____) 并且⽤户名是(____)的⽤户。
不管你填的是什么值，我所表达的就是这个意思。
最后再总结⼀句：因为参数化查询可以重⽤执⾏计划，并且如果重⽤执⾏计划的话，SQL所要表达的语义就不会变化，所以就可以防⽌SQL注⼊，如果不能重⽤执⾏计划，就有可能出现SQL注⼊，存储过程也是⼀样的道理，因为可以重⽤执⾏计划。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。