參數化查詢為什麼能夠防止SQL注入(1)

很多人都知道SQL注入，也知道SQL參數化查詢可以防止SQL注入，可為什麼能防止注入卻並不是很多人都知道的。

本文主要講述的是這個問題，也許你在部分文章中看到過這塊內容，當然了看看也無妨。

首先：我們要瞭解SQL收到一個指令後所做的事情：

具體細節可以查看文章：Sql Server 編譯、重編譯與執行計畫重用原理

在這裡，我簡單的表示為： 收到指令 -> 編譯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 重用次數,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) 語句執行 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 Server參數化查詢之where in和like實現詳解