(轉)動態 SQL 威力展示 (1) - 基本概念

動態 SQL 威力展示 (1) - 基本概念

出處: https://dotblogs.com.tw/hunterpo/2010/02/05/13488

動態 SQL (Dynamic SQL) 是 T-SQL Programming 領域中非常有用的技巧,其主要目的是依據使用者所輸入的參數,變換組合 SQL 語法,可以讓開發人員撰寫出靈活、具有彈性的查詢語句,若能加以妥善操控的話,對查詢優化、效能提升也有幫助,甚至完成難以用其他做法達成的工作。 
 

## 警告

本主題包含的所有範例僅為概念展示,並沒有全面性的規劃與防範可能的漏洞,不建議直接使用於實務環境,你應當自行評估且加以修改補強,特別是動態 SQL 非常容易遭受 SQL Injection 攻擊。

 

重要特性

Microsoft SQL Server 提供兩種方式可以讓你執行動態建立的查詢字串:一是 EXECUTE 命令 (通常用簡寫 EXEC),另一是 sp_executesql。大致上來說,EXEC 限制較少,使用簡單,只要組成字串且是合法的查詢指令即可執行 (強調一下,合法指的是可以成功剖析、編譯,不代表沒有惡意);而 sp_executesql 則可以傳參數,結構較嚴謹,因此得以撰寫更安全的動態查詢,提高執行計劃重用的機會,相對來說執行效率會更好,當然也是官方較建議的方式。除此之外,兩者有一些重要的特性:

  1. 權限 - 使用動態查詢時,所有參考到的安全性實體 (Securables),執行的使用者必須具有直接存取權。例如,包含 INSERT 指令時,使用者就必須有該資料表的 INSERT 權限,即使此一動態查詢包含於預存程序當中也一樣,一旦發現 EXEC、sp_executesql 陳述式就會檢查權限。(詳見1)
  2. 自成批次 - 動態查詢的執行是與進行呼叫的批次分開的,換句話說另成一個獨立單元進行剖析、最佳化、編譯執行計劃,而且是等到執行 EXEC 或 sp_executesql 陳述式時才會編譯。可想而知,這一份計劃不同於含有 EXEC 或 sp_executesql 陳述式的批次所產生的。(詳見2)
  3. 區域變數可及性 - 承上所述,包含 EXEC 或 sp_executesql 陳述式的批次指令是為外部批次,動態查詢則為內部批次,所有區域變數只有在被宣告的批次裡可以取用,內外層級皆然。
  4. 區域暫存資料表可及性 - 外部批次所建立的區域暫存表可以被內部的動態查詢所存取,但內部批次所建立的區域暫存表只要超出批次作用範圍將自動銷毀。

說起來 3、4 不專屬於 EXEC 或 sp_executesql,其分別是區域變數、區域暫存表本來就有的特性,只是對動態查詢來說還滿重要的就是了。在進一步了解之前先做聲明,文中大部分的 SQL 查詢都可以採用靜態查詢方式直接處理掉,那是為了簡化、方便討論之故,相信看完本文之後,你將具備動態查詢的預備知識並明瞭其適用時機。 
 

EXEC 基本語法

EXEC 詳細使用語法請參考文件。使用時機有兩個,一是用來執行預存程序,另一則是本篇所關注的焦點 - 執行動態查詢字串。簡化的語法表示會像 EXEC('tsql_string'),括號內只能是字串,包含純字元字串,或字串變數串接皆可,但不能有函數或任何命令,若有這樣的需求可以先拉到外部批次處理,例如: 

DECLARE @cmd varchar(50),
		@schema varchar(10),
		@table varchar(20)

SET @cmd = 'SELECT COUNT(*) AS rows FROM ';
SET @schema = 'dbo';
SET @table = 'Categories';

-- 建構陳述式:SELECT COUNT(*) AS rows FROM [dbo].[Categories]
SET @cmd = @cmd + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ';';
EXEC (@cmd);

-- 底下為錯誤用法
--EXEC (@cmd + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ';');


 

動態 SQL 權限

為了突顯動態 SQL 對於權限驗證的行為,請執以下指令碼新建一個登入 (Login) 及對應的使用者 (User),故意僅授與查詢 Northwind.dbo.Categories 的權限: 

CREATE LOGIN ReadOnlyUser 
WITH 
	PASSWORD = 'P@ssw0rd', 
	DEFAULT_DATABASE = Northwind;

-- 建立使用者 (對應到同名的登入帳號)
USE Northwind;
CREATE USER ReadOnlyUser;

-- 僅授與查詢 Categories 資料表的權限
GRANT SELECT ON OBJECT::[Northwind].[dbo].[Categories] TO ReadOnlyUser;
GO


完成後另開 SSMS 以新建立的帳號登入,分別查詢 Categories、Products 兩個資料表,觀察結果: 

dynamic_sql_authorization_01
dynamic_sql_authorization_02

可以看到對 Products 資料表的查詢因為沒有直接存取權,不管是 EXEC 或 sp_executesql 陳述式,皆一如預期地遭到拒絕 (denied)。 
 

內、外批次

先建立一個簡單的預存程序 usp_executesql,可接受字串型態的 @cmd 參數,簡單做個驗證之後分別以 EXEC、sp_executesql 執行(一共兩次): 

GO

CREATE PROC [dbo].[usp_executesql]
	@cmd NVarChar(100)
AS
BEGIN
	IF LEN(@cmd) > 0
	BEGIN
		EXEC (@cmd)
		EXEC sp_executesql @cmd
	END
	ELSE
		RAISERROR ('您必須指定查詢指令。', -- 錯誤訊息
               16, -- 錯誤層級碼
               1 -- 錯誤狀態碼
               );
END
GO


接著依序清除計劃快取、執行 usp_executesql、再查看快取: 

DBCC FREEPROCCACHE;
GO

-- 2. 叫用自訂程序
DECLARE @cmd NVarChar(50)
SET @cmd = N'SELECT TOP 1 * FROM dbo.Categories;';
EXEC usp_executesql @cmd
GO

-- 3. 查看快取
SELECT cacheobjtype, objtype, usecounts, [sql] 
FROM sys.syscacheobjects
WHERE (objtype = 'Proc') OR (objtype = 'Adhoc')
GO


快取物件的最新資訊如下,證明動態查詢以及進行呼叫的批次是分開處理的: 

dynamic_sql_inner_outer_03
 

注意:實務環境裡你幾乎不需要手動清除計劃快取,SQL Server 會自行處理,請參考:DBCC FREEPROCCACHE (Transact-SQL) 文件。

 

EXEC 與區域變數

關於區域變數的說明文件在這裡,在備註的地方有提到:

本機區域變數的範圍是宣告它的批次。(原文:The scope of a local variable is the batch in which it is declared.)

這是區域變數本來就有的特性,所以若是執行底下的指令: 

GO

DECLARE @ProductID int
SET @ProductID = 1;

DECLARE @cmd VarChar(60);
SET @cmd = 'SELECT * FROM dbo.Products WHERE (ProductID = @ProductID);';
EXEC(@cmd);
GO


會收到必須宣告變數的錯誤訊息:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@ProductID".

 

使用 EXEC 時,若想取用變數,就只能把變數串接到動態查詢裡,因此改用底下的方式就能順利執行: 

SET @ProductID = 1;

DECLARE @cmd VarChar(60);
SET @cmd = 'SELECT * FROM dbo.Products WHERE (ProductID = ' + CAST(@ProductID AS VarChar(5)) + ');';
EXEC(@cmd);


不過這樣做很危險!特別是變數為字元型別時 (含 char、nchar、varchar、nvarchar、text、ntext),極可能招致資料隱碼 (SQL Injection) 攻擊,此為 EXEC 執行動態查詢的其中一個缺點;另一個缺點是效能問題 -- 再度清除計劃快取,然後把上面的動態查詢多執行幾次,每次都給不同的 @ProductID: 

DBCC FREEPROCCACHE;
GO

-- 2. 查詢產品資料 3 次
DECLARE @ProductID int
DECLARE @cmd VarChar(60)

SET @ProductID = 1;
SET @cmd = 'SELECT * FROM dbo.Products WHERE (ProductID = ' + CAST(@ProductID AS VarChar(5)) + ');';
EXEC(@cmd);

SET @ProductID = 2;
SET @cmd = 'SELECT * FROM dbo.Products WHERE (ProductID = ' + CAST(@ProductID AS VarChar(5)) + ');';
EXEC(@cmd);

SET @ProductID = 3;
SET @cmd = 'SELECT * FROM dbo.Products WHERE (ProductID = ' + CAST(@ProductID AS VarChar(5)) + ');';
EXEC(@cmd);
GO

-- 3. 查看快取
SELECT cacheobjtype, objtype, usecounts, [sql] 
FROM sys.syscacheobjects
WHERE (objtype = 'Proc') OR (objtype = 'Adhoc')
GO


取回的資訊如下,不同的 @ProductID 會各自產生一份查詢計劃: 

dynamic_sql_nonreusable_plan

編譯執行計劃會耗用 CPU 資源,如果這是在線環境裡非常頻繁使用的查詢,可就不妙了…! 
 

sp_executesql

該讓主角出場了。除非有個好理由,否則應該少用 EXEC 方式執行動態查詢,例如在 SQL Server 2000 的版本中,字元字串限制在 4000 個字元,唯一突破限制的方式是宣告多個字元變數 @cmd1、@cmd2,再丟到 EXEC(@cmd1 + @cmd2) 執行;還好 SQL Server 2005 以後出現了 NVarChar(MAX) 型別,此限制已不存在,因此實務上所有的動態查詢都應該交由 sp_executesql 來執行。同樣地你可以查看文件了解其語法,這邊讓我稍微簡化一下:

sp_executesql 
    [@stmt =] <stmt>  -- 動態查詢批次,類似於 Proc 主要執行內容
    [ 
       {,[@params =] <params_def>}  -- 內嵌參數定義,可視為 Proc 裡的參數宣告
        {,[@param1 =] <param1_val>[ ,...n ]}  -- 給定內嵌參數值,跟使用 Proc 時給參數值的用法一樣
    ]

我個人的理解方式是,想像 sp_executesql 是一個空殼預存程序,查詢批次內容隨你指定,需要參數時也不用客氣,儘管在查詢字串以 @param 的格式指定,記得在內嵌參數定義字串裡一一宣告,最後再逐一給參數值即可,所有輸入字串部分都只接受 Unicode 型別。附上簡單示意圖給大家參考: 

demo_empty_proc_new
 

sp_executesql 對於效能的改善

前面看過 EXEC 執行串接參數的動態查詢時,只要參數值改變就會產生新的查詢計劃,現在修改內容用 sp_executesql 執行看看: 

DBCC FREEPROCCACHE;
GO

-- 2. 查詢產品資料 3 次
DECLARE @stmt NVarChar(60),
		@paramsDefinition NVarChar(20),
		@ProductID int

SET @stmt = N'SELECT * FROM dbo.Products WHERE (ProductID = @ProductID);'; 
SET @paramsDefinition = N'@ProductID int';

SET @ProductID = 1;
EXEC dbo.sp_executesql
		@stmt,
		@paramsDefinition,
		@ProductID;

SET @ProductID = 2;
EXEC dbo.sp_executesql
		@stmt,
		@paramsDefinition,
		@ProductID;

SET @ProductID = 3;
EXEC dbo.sp_executesql
		@stmt,
		@paramsDefinition,
		@ProductID;
GO

-- 3. 查看快取
SELECT cacheobjtype, objtype, usecounts, [sql] 
FROM sys.syscacheobjects
WHERE (objtype IN ('Proc', 'Prepared'))
GO


快取資訊如下,同一份執行計劃重複利用了 3 次: 

dynamic_sql_reusable_plan_05

除此之外,sp_executesql 支援輸出參數的能力,簡化了回傳值的方式。使用 EXEC 時,看看要回傳值需怎麼做: 

SET @sql = N'INSERT INTO #T SELECT COUNT(*) FROM [dbo].[Products];'; 

-- 建立區域暫存表
CREATE TABLE #T(cnt int);

-- 利用外部批次建立的暫存表,內部可見的特性,將值寫入
EXEC(@sql);
SELECT cnt AS [total_products] FROM #T;

DROP TABLE #T;
GO


反觀利用 sp_executesql 回傳值則優雅多了: 

,		@paramsDef NVarChar(20),
		@cnt int

SET @sql = N'SELECT @Cnt = COUNT(*) FROM [dbo].[Products];'; 
SET @paramsDef = N'@Cnt int OUTPUT'; -- 內嵌參數宣告為輸出參數

EXEC dbo.sp_executesql
		@Stmt = @sql,
		@Params = @paramsDef,
		@Cnt = @cnt OUTPUT;
SELECT @cnt AS [total_products];


輸出參數的使用習慣就跟一般預存程序一樣,也不會有暫存資料,這多少會影響效能。 
 

又是資料隱碼?!

動態 SQL 無疑是開放使用者參與建構查詢指令,其實都是為了彈性,但也代表相當程度的資料隱碼攻擊風險,因此針對外來的輸入加以驗證是絕對必要的手段,君不見官方對於 EXEC 、sp_executesql 的說明文件,幾乎一開頭就特別強調這一點: 

warnig_sql_injection  

這代表你必須審慎評估彈性與安全性之間的取捨。在此我不打算說明 SQL Injection,這部分內容已經有很多資源可以參考,僅隨附於文末參考資料連結。 
 

結語

老實說,撰寫本文之前我曾掙扎過是否該談論此議題,但我也不認為因為有風險就禁止使用動態 SQL 是明智的抉擇,反過來說,好好了解此一技術的能耐以及避險方式比較實際點。總而言之,使用動態 SQL 考驗著開發人員在程式設計上的成熟度,你不應該輕率為之,但只要正確地處理,它可以展現出強大的威力,有機會再跟大家分享相關內容,今天就到此為止吧。 
 


參考資料

 


系列文章