Hunterpo's IT Vision

歡迎一起分享 C#、T-SQL、ASP.NET 開發心得

2010-03-03

動態 SQL 威力展示 (3) - 進階議題

動態 SQL 威力展示 (3) - 進階議題

這一篇從農曆年前打草稿到現在，本來打算有空再寫了，直到那天出現一道浪 (基於保護當事人立場，圖片已經過處理)：

dynamic_sql_intro 

裡面幾位噗友提到的一些做法，跟我既有的認知不太一致，讓我內心激起一陣陣漣漪，終於再度喚醒心中沉睡已久的 T-SQL 魂。

## 警告

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


動態篩選效能

查詢資料時，很常面臨必須開放給使用者自由指定篩選條件與排序的狀況，此時正是利用動態 SQL 的好時機 (例如系列文章第二篇的範例)。這樣的說法，很多人應該會提出質疑，因為其實靜態查詢也可以處理，若再考慮到動態 SQL 容易遭到攻擊的風險，或許大部分的人會選擇較保守的方式，不過，我希望跟大家證明，在這方面動態 SQL 所帶來的優勢。

直接用 Northwind 範例資料庫說明，簡單撰寫回傳訂單資料的預存程序，提供了 OrderID、OrderDate 這兩個選用篩選條件 (注意，Northwind 資料庫建立起來後，Orders 資料表預設已經擁有多個索引，其中包含 PK_Orders 叢集索引、OrderDate 非叢集索引) ，常見的靜態查詢作法大概是這樣： 

	@OrderID int = NULL,
	@OrderDate datetime = NULL
WITH RECOMPILE
AS
	SELECT OrderID, CustomerID, EmployeeID, OrderDate
	FROM dbo.Orders
	WHERE (OrderID = COALESCE(@OrderID, OrderID))
		AND (OrderDate = COALESCE(@OrderDate, OrderDate));
	-- 效果約略同下
	--WHERE (OrderID = CASE WHEN (@OrderID IS NULL) THEN OrderID ELSE @OrderID END)
	--	AND (OrderDate = CASE WHEN(@OrderDate IS NULL) THEN OrderDate ELSE @OrderDate END);
GO

COALESCE 函數可以參考 MSDN Library 說明，其作用是傳回引數中第一個非 NULL 運算式，因此當 @OrderID、@OrderDate 有給值的話，就會依據給定的值篩選，否則會呈現 <column> = <column> 恆等式，這樣即構成選擇性篩選條件；同樣目的，我再用另一種靜態查詢的寫法建立另一支預存程序： 

	@OrderID int = NULL,
	@OrderDate datetime = NULL
WITH RECOMPILE
AS
	SELECT OrderID, CustomerID, EmployeeID, OrderDate
	FROM dbo.Orders
	WHERE (OrderID = @OrderID OR @OrderID IS NULL)
		AND (OrderDate = @OrderDate OR @OrderDate IS NULL);
GO

注意，兩種寫法都指定 WITH RECOMPILE 選項，這樣產生的程序將不會快取查詢計劃，每次執行都會重新編譯 (為了確保不同篩選條件會自行編譯一次)。現在分別用這兩支程序下查詢，觀察查詢計劃：

dynamic_sql_bad_plan1
dynamic_sql_bad_plan2 

實際觀察發現，uspQueryOrder1 的查詢計劃是以叢集索引掃描 (Clustered Index Scan) 的方式查找，這非常沒有效率，反觀 uspQueryOrder2 有好一點，兩種索引都用上了，可惜 OrderDate 這個 Index 仍然是以逐筆掃描方式找資料。

接下來，我來建立一個動態 SQL 版本的回傳訂單資料程序 -- uspGetOrders： 

	@OrderID INT = NULL,
	@OrderDate DATETIME = NULL
AS
	DECLARE @cmd NVARCHAR(208);
	DECLARE @filter NVARCHAR(120);
	
	SET @cmd = N'
	SELECT OrderID, CustomerID, EmployeeID, OrderDate 
	FROM dbo.Orders 
	WHERE (1 = 1) {0};';
	
	SET @filter = N'';
	
	-- 組合篩選字串
	IF (LEN(@OrderID) > 0)
	BEGIN
		SET @filter = @filter + N'AND (OrderID = @OrderID) ';
	END
	
	IF (LEN(@OrderDate) > 0)
	BEGIN
		SET @filter = @filter + N'AND (OrderDate = @OrderDate) ';
	END
	
	-- 置換篩選條件
	SET @cmd = REPLACE(@cmd, N'{0}', @filter);
	
	DECLARE @Stmt nvarchar(256),	-- 動態查詢
			@Params nvarchar(144)	-- 參數定義
	
	SET @Stmt = @cmd;
	SET @Params = N'@OrderID INT, @OrderDate DATETIME ';
	
	-- 執行動態查詢
	EXEC dbo.sp_executesql
		@Stmt,
		@Params,
		@OrderID,
		@OrderDate;
GO

此程序會根據 @OrderID、@OrderDate 是否給值，動態組出相對應的 WHERE 子句，串到查詢陳述式後由 sp_executesql 執行。實際執行看看查詢計劃是怎麼跑的：

dynamic_sql_good_plan1
dynamic_sql_good_plan2

看到搜尋 (Seek) 大概可以放心了，至少針對單一索引來說是最好的查找方式，但整段查詢來看是否為最佳解，還得看使用的索引組合，不過 SQL Server 查詢最佳化工具 (Query Optimizer) 會處理這個部分 (在現有索引中找出最有效的組合)，通常還處理得很不錯…

回頭來看我們的例子，受惠於動態 SQL 判斷語句，WHERE 子句變得非常明確，因此 Optimizer 處理起來效率也高，舉個例子，假設女王給你一道指示：「情人節大餐吃甚麼比較好？」，這種問題不好回答，你可能得考慮以前的經驗，女王喜歡吃甚麼、去年情人節大餐吃甚麼…等等，最後可能挑了上閤屋這種不太會得罪人的地方；假如女王是說：「情人節大餐吃牛排你覺得怎麼樣？」，我想你馬上可以說：「太好了，台塑牛排這種地方當然要跟女王一起吃！」，女王還不被你逗得花枝亂顫嗎？…咳！我要說的是，Optimizer 也是人設計的，它的分析邏輯絕大部分跟人相似，收到指令時，它也會分析合理性 (語法是否正確)、收集數據 (索引統計資料)、評估可能組合 (不是全部)、挑出夠好的計劃 (Good Enough Plan)，跟上面例子是不是很類似？

執行計劃重用

動態 SQL 透過 sp_executesql 執行可以提高執行計劃重用率，這一點我在第一篇提過，但本篇還想跟各位談談細節的部分。執行底下的查詢，然後觀察計劃快取： 

DBCC FREEPROCCACHE;

-- 2. 執行查詢
EXEC dbo.uspGetOrders @OrderID = 10248, @OrderDate = '19960704';
EXEC dbo.uspGetOrders @OrderDate = '19970214';
EXEC dbo.uspGetOrders @OrderID = 10447;
EXEC dbo.uspGetOrders;

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

步驟 2 . 的每一段查詢都會產生一份查詢計劃 (還記得嗎？動態查詢批次會另行處理)： 
cacheobjtype   objtype	 usecounts   sql

Compiled Plan  Prepared	 1  (@OrderID INT, @OrderDate DATETIME ) SELECT ...<略>... WHERE (1 = 1) 
								  AND (OrderID = @OrderID) AND (OrderDate = @OrderDate) 
Compiled Plan  Prepared	 1  (@OrderID INT, @OrderDate DATETIME ) SELECT ...<略>... WHERE (1 = 1) 
								  AND (OrderDate = @OrderDate) 
Compiled Plan  Prepared	 1  (@OrderID INT, @OrderDate DATETIME ) SELECT ...<略>... WHERE (1 = 1) 
								  AND (OrderID = @OrderID) 
Compiled Plan  Prepared	 1  (@OrderID INT, @OrderDate DATETIME ) SELECT ...<略>... WHERE (1 = 1)
uspGetOrders 共有 2 個選用參數，每一參數都有給值或不給值兩種選擇，因此會有 2^2 = 4 種可能結果 (n 個選用參數則會有 2^n 個結果)，所以上列就是 uspGetOrders 可能產生的全部查詢計劃了，接下來再執行 uspGetOrders 除非觸發了重新編譯的條件，否則必定會重用執行計劃，底下查詢可以證明這一點： 

EXEC dbo.uspGetOrders @OrderDate = '19960712';

SELECT cacheobjtype, objtype, usecounts, [sql]
FROM sys.syscacheobjects
WHERE (objtype IN ('Prepared'))
GO

輸出結果： 
cacheobjtype   objtype	 usecounts   sql

Compiled Plan  Prepared	 1  (@OrderID INT, @OrderDate DATETIME ) SELECT ...<略>... WHERE (1 = 1)
                                                                  AND (OrderID = @OrderID) AND (OrderDate = @OrderDate) 
Compiled Plan  Prepared	 2  (@OrderID INT, @OrderDate DATETIME ) SELECT ...<略>... WHERE (1 = 1)
                                                                  AND (OrderDate = @OrderDate)
Compiled Plan  Prepared	 2  (@OrderID INT, @OrderDate DATETIME ) SELECT ...<略>... WHERE (1 = 1)
                                                                  AND (OrderID = @OrderID)
Compiled Plan  Prepared	 1  (@OrderID INT, @OrderDate DATETIME ) SELECT ...<略>... WHERE (1 = 1)
當然，如果你願意的話也可以針對所有組合全部寫成靜態查詢 (以上例來說就是寫程 4 支程序)，這樣也可以取得有效率、可重用的查詢計劃，更重要的是沒有動態 SQL 的安全性問題，各方面都很理想、很完美…除了一個問題：時間，當選用參數有 5 個的時候，你願意花時間寫 32 (2^5) 支程序嗎？後續維護時，再回頭改 32 支程序？

Detect SQL Injection

既然動態 SQL 容易遭受資料隱碼攻擊，不談防範之道似乎說不過去，不過我也無意發表長篇大論講如何防範，原因在第一篇說過，網路上有許多資源可以參考，在這裡只提供一個偵測 SQL Injection 的自訂函數，可以驗證字元資料型別，若偵測到可能的隱碼攻擊內容將回傳 1，回傳 0 則代表未發現任何攻擊意圖： 

(
	@text NVARCHAR(4000)
)
RETURNS bit
AS
BEGIN
	DECLARE @result bit
	SET @result = 0;
	
	IF UPPER(@text) LIKE UPPER(N'%;%')
		OR UPPER(@text) LIKE UPPER(N'%''%')
		OR UPPER(@text) LIKE UPPER(N'%--%')
		OR UPPER(@text) LIKE UPPER(N'%/*%*/%') 
		OR UPPER(@text) LIKE UPPER(N'%0x%')
		OR UPPER(@text) LIKE UPPER(N'%EXEC%')
		OR UPPER(@text) LIKE UPPER(N'%xp_%')
		OR UPPER(@text) LIKE UPPER(N'%sp_%')
		OR UPPER(@text) LIKE UPPER(N'%CREATE%')
		OR UPPER(@text) LIKE UPPER(N'%ALTER%')
		OR UPPER(@text) LIKE UPPER(N'%DROP%')
		OR UPPER(@text) LIKE UPPER(N'%SELECT%')
		OR UPPER(@text) LIKE UPPER(N'%INSERT%')
		OR UPPER(@text) LIKE UPPER(N'%UPDATE%')
		OR UPPER(@text) LIKE UPPER(N'%DELETE%')
		OR UPPER(@text) LIKE UPPER(N'%TRUNCATE%')
		-- TODO: 持續檢視、加入其他驗證規則
	BEGIN
		SET @result = 1;
	END
	
	RETURN @result
END

要特別聲明一下，未發現不代表沒有，駭客創意總是出人意表，攻擊手法會不斷翻新，並不保證這支自訂函數可以讓你一勞永逸，永遠不會遭受攻擊，反倒應該在你知道有遭受攻擊的可能時，時常回頭檢視、修訂驗證規則才是。

總結

本系列文章與其說是鼓吹大家使用動態 SQL，倒不如說是分享我知道的訣竅，有些議題其實牽涉甚廣，在這邊提出來也許有助於釐清一些疑問。動態 SQL 你也可以看成是在資料庫裡寫程式，這也是它擁有強大能力的原因，話說回來，你是否應該成為 SQL 魔人，將所有程式寫在資料庫端？恐怕也不是，每一種技術皆有其存在的背景、特定目的，其實也就代表著最適性，當你能掌握多種技術的精隨時，才可能做正確的取捨，寫出來的程式才能更接近高品質，共勉之！

參考資料


系列文章


系列文章

標籤雲