很多人都知道SQL注入，也知道SQL參數(shù)化查詢(xún)可以防止SQL注入，可為什么能防止注入卻并不是很多人都知道的。本文主要講述的是這個(gè)問(wèn)題，也許你在部分文章中看到過(guò)這塊內(nèi)容，當(dāng)然了看看也無(wú)妨。

首先：我們要了解SQL收到一個(gè)指令后所做的事情：

具體細(xì)節(jié)可以查看文章：Sql Server 編譯、重編譯與執(zhí)行計(jì)劃重用原理，在這里，我簡(jiǎn)單的表示為： 收到指令 -> 編譯SQL生成執(zhí)行計(jì)劃 ->選擇執(zhí)行計(jì)劃 ->執(zhí)行執(zhí)行計(jì)劃。

具體可能有點(diǎn)不一樣，但大致的步驟如上所示。接著我們來(lái)分析為什么拼接SQL 字符串會(huì)導(dǎo)致SQL注入的風(fēng)險(xiǎn)呢？

首先創(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]

插入一些數(shù)據(jù)：

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');

假設(shè)我們有個(gè)用戶登錄的頁(yè)面，代碼如下：

驗(yàn)證用戶登錄的sql 如下：

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

這段代碼返回Password 和UserName都匹配的用戶數(shù)量，如果大于1的話，那么就代表用戶存在。

本文不討論SQL 中的密碼策略，也不討論代碼規(guī)范，主要是講為什么能夠防止SQL注入，請(qǐng)一些同學(xué)不要糾結(jié)與某些代碼，或者和SQL注入無(wú)關(guān)的主題。

可以看到執(zhí)行結(jié)果：

這個(gè)是SQL profile 跟蹤的SQL 語(yǔ)句。

注入的代碼如下：

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

這里有人將UserName設(shè)置為了 “b' or 1=1 –”.

實(shí)際執(zhí)行的SQL就變成了如下：

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

很多人都知道參數(shù)化查詢(xún)可以避免上面出現(xià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;
            //為每一條數(shù)據(jù)添加一個(gè)參數(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í)際執(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—' 

可以看到參數(shù)化查詢(xún)主要做了這些事情：

1：參數(shù)過(guò)濾，可以看到 @UserName='b'' or 1=1—'

2：執(zhí)行計(jì)劃重用

因?yàn)閳?zhí)行計(jì)劃被重用，所以可以防止SQL注入。

首先分析SQL注入的本質(zhì)，用戶寫(xiě)了一段SQL 用來(lái)表示查找密碼是a的，用戶名是b的所有用戶的數(shù)量。通過(guò)注入SQL，這段SQL現(xiàn)在表示的含義是查找(密碼是a的，并且用戶名是b的，) 或者1=1 的所有用戶的數(shù)量。

可以看到SQL的語(yǔ)意發(fā)生了改變，為什么發(fā)生了改變呢？，因?yàn)闆](méi)有重用以前的執(zhí)行計(jì)劃，因?yàn)閷?duì)注入后的SQL語(yǔ)句重新進(jìn)行了編譯，因?yàn)橹匦聢?zhí)行了語(yǔ)法解析。所以要保證SQL語(yǔ)義不變，即我想要表達(dá)SQL就是我想表達(dá)的意思，不是別的注入后的意思，就應(yīng)該重用執(zhí)行計(jì)劃。

如果不能夠重用執(zhí)行計(jì)劃，那么就有SQL注入的風(fēng)險(xiǎn)，因?yàn)镾QL的語(yǔ)意有可能會(huì)變化，所表達(dá)的查詢(xún)就可能變化。

在SQL Server 中查詢(xún)執(zhí)行計(jì)劃可以使用下面的腳本：

DBCC FreeProccache

select total_elapsed_time / execution_count 平均時(shí)間,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) 語(yǔ)句執(zhí)行 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;

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

這里作者有一句話：”不過(guò)這種寫(xiě)法和直接拼SQL執(zhí)行沒(méi)啥實(shí)質(zhì)性的區(qū)別”，任何拼接SQL的方式都有SQL注入的風(fēng)險(xiǎn)，所以如果沒(méi)有實(shí)質(zhì)性的區(qū)別的話，那么使用exec 動(dòng)態(tài)執(zhí)行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)執(zhí)行SQL　
        //實(shí)際執(zhí)行的查詢(xún)計(jì)劃為(@UserID varchar(max))select * from Users(nolock) where UserID in (1,2,3,4)　　
        //不是預(yù)期的(@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();