很多人🎃都知道【dōu zhī dào】SQL注入，也知道SQL参数化【cān shù huà】🍡查询可【chá xún kě】以防止🙃SQL注入，可为什么【wéi shí me】能防止📏注入却并不是很多🌳人都知道【dōu zhī dào】的🎯。本文主要讲述的是这个问题【gè wèn tí】🥒，也许你😼在部分【zài bù fèn】文章中🙅看到过【kàn dào guò】这块内容【róng】，当然了看看也【kàn kàn yě】无妨。

首先🙅：我们要了解SQL收到一【shōu dào yī】🐷个指令【gè zhǐ lìng】后所做【hòu suǒ zuò】♏的事情：

具体细节可以查看文【chá kàn wén】章🛰：Sql Server 编译📮、重编译📮与执行【yǔ zhí háng】💉计划重【jì huá chóng】🐨用原理【yòng yuán lǐ】，在这里，我简单的表示为： 收到指令【lìng】🏫 -> 编译📮SQL生成执行计划【háng jì huá】🦌 ->选择执【xuǎn zé zhí】行计划【háng jì huá】🦌 ->执行执📃行计划【háng jì huá】🦌。

具体可能有点🐀不一样，但大致【dàn dà zhì】的步骤🚟如上所示。接着我【jiē zhe wǒ】们来分【men lái fèn】📒析为什么拼接🍦SQL 字符串🔠会导致SQL注入的【zhù rù de】风险呢【fēng xiǎn ne】？

首先创建一张表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');

假设我【jiǎ shè wǒ】们有个用户登录的页【lù de yè】🥪面⛄，代码如🥕下【xià】：

验证用户登录的sql 如下：

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

这段代📤码返回Password 和UserName都匹配🏼的用户数量【shù liàng】，如果大【rú guǒ dà】于🐪1的话【de huà】，那么就🎬代表用户存在【hù cún zài】。

本文不【běn wén bú】🍬讨论SQL 中的密【zhōng de mì】🛍码策略，也不讨论代码规范👷，主要是讲为什【jiǎng wéi shí】么能够【me néng gòu】防止SQL注入，请一些🏇同学不【tóng xué bú】🍰要纠结与某些🐅代码，或者和【huò zhě hé】SQL注入无关的主【guān de zhǔ】题🚖。

可以看到执行结果：

这个是【zhè gè shì】🔈SQL profile 跟踪的SQL 语句。

注入的代码如下：

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

这里有【zhè lǐ yǒu】💇人将💱UserName设置为了【le】 “b' or 1=1 –”.

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

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

很多人🍴都知道【dōu zhī dào】参数化【cān shù huà】查询【chá xún】可以避🍞免上面出现的🍫注入问🌖题，比如下面的代码【mǎ】：

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; //为每一条数据【tiáo shù jù】🕯添加一个参数【gè cān shù】 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(); } } }

实际执【shí jì zhí】行的🧖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—' 

可以看到参数【dào cān shù】化查询😳主要做【zhǔ yào zuò】了这些事情⛏：

1：参数过【cān shù guò】滤【lǜ】，可以看🔶到⚡ @UserName='b'' or 1=1—'

2：执行计划重用【huá chóng yòng】💟

因为执行计划被重用，所以可【suǒ yǐ kě】🕗以防止SQL注入。

首先分🍔析【xī】SQL注入的【de】🍔本质，用户写了一段🏑SQL 用来表【yòng lái biǎo】示查找【shì chá zhǎo】🚎密码是【mì mǎ shì】💔a的【de】，用户名📇是💔b的【de】所有用户的【yòng hù de】🍅数量【shù liàng】。通过注【tōng guò zhù】入SQL，这段【zhè duàn】🤭SQL现在表【xiàn zài biǎo】示的【de】含义是💔查找(密码是【mì mǎ shì】💔a的【de】，并且用♈户名是🚲b的【de】，) 或者1=1 的【de】所有用户的【yòng hù de】🍅数量【shù liàng】。

可以看到【dào】🤸SQL的语意【de yǔ yì】发生了【fā shēng le】🚉改变，为什么🆔发生了【fā shēng le】🚉改变呢？，因为没有重用以前的【yǐ qián de】执行计划【huá】🌝，因为对🗃注入后♑的SQL语句重新进行【xīn jìn háng】🆒了编译，因为重【yīn wéi chóng】新执行了语法🔺解析【jiě xī】。所以要【suǒ yǐ yào】保证SQL语义不变，即我想要表达SQL就是我【jiù shì wǒ】想表达的意思【de yì sī】🧦，不是别的注入🧀后的意【hòu de yì】👊思，就应该重用执行计划【huá】🌝。

如果不😊能够重用执行计划，那么就【nà me jiù】有【yǒu】🔊SQL注入的风险【fēng xiǎn】，因为SQL的语意🍫有【yǒu】🔊可能会变化【biàn huà】🧀，所表达的查询【de chá xún】就可能⚾变化【biàn huà】🧀。

在📣SQL Server 中查询🗡执行计【zhí háng jì】划可以使用下【shǐ yòng xià】🔏面的脚本【běn】：

DBCC FreeProccache select total_elapsed_time / execution_count 平均时间📿,total_logical_reads/execution_count 逻辑读, usecounts 重用次数【shù】,SUBSTRING(d.text, (statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END - statement_start_offset)/2) + 1) 语句执行【háng】👁 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;

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

这里作者有一🤥句话：”不过这🖼种写法🎳和直接拼【pīn】SQL执行没【zhí háng méi】啥实质【shá shí zhì】性的区别”，任何拼【pīn】接SQL的方式都有✍SQL注入的【zhù rù de】🎰风险😎，所以如果没有实质性的区别的话【de huà】，那么使【nà me shǐ】用🐒exec 动态执行【háng】SQL是不能【shì bú néng】👕防止【fáng zhǐ】🗿SQL注入的【zhù rù de】🎰。

比如下面的代码：

private static void TestMethod() { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand comm = new SqlCommand(); comm.Connection = conn; //使用🎪exec动态执行SQL　 //实际执🎪行的查🥡询计划【xún jì huá】为(@UserID varchar(max))select * from Users(nolock) where UserID in (1,2,3,4)　　 //不是预【bú shì yù】期的【qī de】🦒(@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();