(轉)動態 SQL 威力展示 (1) - 基本概念 | von_blog - 點部落

2016-10-26

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

1896
0
T-SQL

動態 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 則可以傳參數，結構較嚴謹，因此得以撰寫更安全的動態查詢，提高執行計劃重用的機會，相對來說執行效率會更好，當然也是官方較建議的方式。除此之外，兩者有一些重要的特性：

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

說起來 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 兩個資料表，觀察結果：

可以看到對 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

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

注意：實務環境裡你幾乎不需要手動清除計劃快取，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 會各自產生一份查詢計劃：

編譯執行計劃會耗用 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 型別。附上簡單示意圖給大家參考：

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 次：

除此之外，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 的說明文件，幾乎一開頭就特別強調這一點：

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

結語

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

參考資料

資料隱碼相關文件：
語法參考文件：
- SQL Server 線上叢書 - EXECUTE (Transact-SQL)
  - 詳見1：參考此篇的「權限」小節。
- SQL Server 線上叢書 - sp_executesql (Transact-SQL)
  - 詳見2：參考此篇的「備註」小節。
外國網站關於 Dynamic SQL 參考文件：
- SQL Team：Introduction to Dynamic SQL - Part 1
- SQL Team：Introduction to Dynamic SQL - Part 2

系列文章

T-SQL

回首頁

von_blog

von的筆記