寫Stored Procedure時有一個麻煩情境是由外界傳入參數當作WHERE IN條件,由於參數數量不定,難以事先寫成WHERE … IN (@val1, @val2, @val3),開發者往往會走上用傳入參數組裝SQL指令的險路,稍有不慎就搞出SQL Injection,導致難以想像的災難。(是的,SQL Injection不只會出現在ASP/ASP.NET/PHP/Java/C++,也可能藏在Stored Procedure裡)

最常見的例子是開放使用者勾選一個到多個類別作為篩選條件,例如使用者選取了「主機」、「螢幕」與「耗材」,要轉換成SELECT … FROM Products WHERE Category IN ('主機','螢幕','耗材')。如果你會Dapper,這需求絕對是小菜一碟,cn.Query<T>("SELECT … FROM Products WHERE Category IN @categories", new { categories = "主機,螢幕,耗材".Split(',') })搞定收工,而且是以SqlParameter方式傳遞字串參數,完全沒有SQL Injection疑慮。然而同樣場景搬到T-SQL,卻沒有類似的現成簡便解法可用。

前幾天學會用SQLXML拆解字串的技巧,剛好可以拿來解決難題,將CSV逗號分隔字串先轉成XML型別,再使用.nodes()拆成多筆,就能當成WHERE IN的比對陣列來源囉,如以下範例:

DECLARE @deptIds VARCHAR(128)
SET @deptIds = '1,3,5,7'
DECLARE @x XML
SET @x = CONVERT(XML, '<n>' + replace(@deptIds, ',', '</n><n>') + '</n>')
SELECT * FROM HumanResources.Department
WHERE DepartmentID IN (
    SELECT T.n.value('.','varchar(5)') FROM @x.nodes('n') T(n)
)

拿SQL Server的AdventureWorks範例資料庫來練槍,真的可以用"1,3,5,7"字串查出DepartmentID是1,3,5,7的部門資料!

實務應用時,大家應該會選擇將CSV拆多筆資料的邏輯包成函式,方便重覆利用。

CREATE FUNCTION [dbo].[SplitCsv]
(
    @csvString nvarchar(2048),
    @delimiter nchar(1)
)
RETURNS @values TABLE (value nvarchar(2048))
AS
BEGIN
DECLARE @x XML
SET @x = CONVERT(XML, 
    '<n>' + replace(@csvString, @delimiter, '</n><n>') + '</n>')
INSERT INTO @values
SELECT T.n.value('.','varchar(2048)') FROM @x.nodes('n') T(n)
RETURN
END
 
--使用範例
SELECT * FROM HumanResources.Department
WHERE DepartmentID IN (
    SELECT value FROM dbo.SplitCsv('1,3,5,7', ',')
)

學會這招,以後就不用再因為WHERE IN挺而走險組SQL字串囉~ (補充:以上方法不考慮CSV字串夾帶XML內容的罕見情境,如不幸遇上請自行克服)

溫馨小提醒:因為你因為不可抗力因素必須走上組SQL這條路,請用生命擔保它沒有SQL Injection風險,以免造業。

有小道消息指出,閻羅王去年研發了一批新刑具,專門對付寫出SQL Injection的程式設計師,很可怕,不要問!


Comments

# by eric

另外一種寫法是只用LIKE DECLARE @deptIds VARCHAR(128) = '1,3,5,7' -- 前後不能有空白 SELECT * FROM HumanResources.Department WHERE ',' + @deptIds + ',' LIKE '%,' + CAST(DepartmentID AS varchar(10)) + ',%'

# by Peter Chang

" 閻羅王去年研發了一批新刑具,專門對付寫出SQL Injection的程式設計師,很可怕,不要問! " 閻羅王是?! @@"?! 我突然怕怕的!

# by Jeffrey

to eric, WHERE ',' + @deptIds + ',' LIKE '%,' + CAST(DepartmentID AS varchar(10)) + ',%'寫法有個小缺點,SQL必須讀取每一筆資料的DepartmentID欄位運算後比對,如此只能走Index Scan,無法善用索引加速。

# by 007

之前查過這個方法也是不錯,把條件加入表格變數 sql server - Set variable with multiple values and use IN - Stack Overflow http://stackoverflow.com/questions/7431341/set-variable-with-multiple-values-and-use-in

Post a comment


30 + 22 =