很多人都知道SQL注入，也知道SQL參數化查詢(xún)可以防止SQL注入，可為什么能防止注入卻并不是很多人都知道的。
本文主要講述的是這個(gè)問(wèn)題，也許你在部分文章中看到過(guò)這塊內容，當然了看看也無(wú)妨。
首先：我們要了解SQL收到一個(gè)指令后所做的事情：
具體細節可以查看文章：Sql Server 編譯、重編譯與執行計劃重用原理
在這里，我簡(jiǎn)單的表示為： 收到指令 -> 編譯SQL生成執行計劃 ->選擇執行計劃 ->執行執行計劃。
具體可能有點(diǎn)不一樣，但大致的步驟如上所示。
接著(zhù)我們來(lái)分析為什么拼接SQL 字符串會(huì )導致SQL注入的風(fēng)險呢？
首先創(chuàng )建一張表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');
假設我們有個(gè)用戶(hù)登錄的頁(yè)面，代碼如下：
驗證用戶(hù)登錄的sql 如下：
select COUNT(*) from Users where Password = 'a' and UserName = 'b'
這段代碼返回Password 和UserName都匹配的用戶(hù)數量，如果大于1的話(huà)，那么就代表用戶(hù)存在。
本文不討論SQL 中的密碼策略，也不討論代碼規范，主要是講為什么能夠防止SQL注入，請一些同學(xué)不要糾結與某些代碼，或者和SQL注入無(wú)關(guān)的主題。
可以看到執行結果：
這個(gè)是SQL profile 跟蹤的SQL 語(yǔ)句。
注入的代碼如下：
select COUNT(*) from Users where Password = 'a' and UserName = 'b' or 1=1—'
這里有人將UserName設置為了 “b' or 1=1 –”.
實(shí)際執行的SQL就變成了如下：
可以很明顯的看到SQL注入成功了。
很多人都知道參數化查詢(xún)可以避免上面出現的注入問(wèn)題，比如下面的代碼：
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; //為每一條數據添加一個(gè)參數 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í)際執行的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—'
可以看到參數化查詢(xún)主要做了這些事情：
1：參數過(guò)濾，可以看到 @UserName='b'' or 1=1—'
2：執行計劃重用
因為執行計劃被重用，所以可以防止SQL注入。
首先分析SQL注入的本質(zhì)，
用戶(hù)寫(xiě)了一段SQL 用來(lái)表示查找密碼是a的，用戶(hù)名是b的所有用戶(hù)的數量。
通過(guò)注入SQL，這段SQL現在表示的含義是查找(密碼是a的，并且用戶(hù)名是b的，) 或者1=1 的所有用戶(hù)的數量。
可以看到SQL的語(yǔ)意發(fā)生了改變，為什么發(fā)生了改變呢？，因為沒(méi)有重用以前的執行計劃，因為對注入后的SQL語(yǔ)句重新進(jìn)行了編譯，因為重新執行了語(yǔ)法解析。所以要保證SQL語(yǔ)義不變，即我想要表達SQL就是我想表達的意思，不是別的注入后的意思，就應該重用執行計劃。
如果不能夠重用執行計劃，那么就有SQL注入的風(fēng)險，因為SQL的語(yǔ)意有可能會(huì )變化，所表達的查詢(xún)就可能變化。
在SQL Server 中查詢(xún)執行計劃可以使用下面的腳本：
DBCC FreeProccacheselect total_elapsed_time / execution_count 平均時(shí)間,total_logical_reads/execution_count 邏輯讀,usecounts 重用次數,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) 語(yǔ)句執行 from sys.dm_exec_cached_plans across apply sys.dm_exec_query_plan(a.plan_handle) c,sys.dm_exec_query_stats bcross apply sys.dm_exec_sql_text(b.sql_handle) d--where a.plan_handle=b.plan_handle and total_logical_reads/execution_count>4000ORDER BY total_elapsed_time / execution_count DESC;
博客園有篇文章： Sql Server參數化查詢(xún)之where in和like實(shí)現詳解
在這篇文章中有這么一段：
這里作者有一句話(huà)：”不過(guò)這種寫(xiě)法和直接拼SQL執行沒(méi)啥實(shí)質(zhì)性的區別”
任何拼接SQL的方式都有SQL注入的風(fēng)險，所以如果沒(méi)有實(shí)質(zhì)性的區別的話(huà)，那么使用exec 動(dòng)態(tài)執行SQL是不能防止SQL注入的。
比如下面的代碼：
private static void TestMethod(){ using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand comm = new SqlCommand(); comm.Connection = conn; //使用exec動(dòng)態(tài)執行SQL //實(shí)際執行的查詢(xún)計劃為(@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(); }}
執行的SQL 如下：
exec sp_executesql N'exec(''select * from Users(nolock) where UserID in ('' @UserID '')'')',N'@UserID varchar(max) ',@UserID='1,2,3,4'
可以看到SQL語(yǔ)句并沒(méi)有參數化查詢(xún)。
如果你將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;--'
不要以為加了個(gè)@UserID 就代表能夠防止SQL注入，實(shí)際執行的SQL 如下：
任何動(dòng)態(tài)的執行SQL 都有注入的風(fēng)險，因為動(dòng)態(tài)意味著(zhù)不重用執行計劃，而如果不重用執行計劃的話(huà)，那么就基本上無(wú)法保證你寫(xiě)的SQL所表示的意思就是你要表達的意思。
這就好像小時(shí)候的填空題，查找密碼是(____) 并且用戶(hù)名是(____)的用戶(hù)。
不管你填的是什么值，我所表達的就是這個(gè)意思。
最后再總結一句：因為參數化查詢(xún)可以重用執行計劃，并且如果重用執行計劃的話(huà)，SQL所要表達的語(yǔ)義就不會(huì )變化，所以就可以防止SQL注入,如果不能重用執行計劃，就有可能出現SQL注入，
存儲過(guò)程也是一樣的道理，因為可以重用執行計劃。
