T-SQL使用逗號分隔字串當作WHERE IN條件

寫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的程式設計師,很可怕,不要問!

歡迎推文分享:
Published 13 March 2016 10:16 PM 由 Jeffrey
Filed under:
Views: 9,536



意見

# eric said on 13 March, 2016 11:39 PM

另外一種寫法是只用LIKE

DECLARE @deptIds VARCHAR(128) = '1,3,5,7' -- 前後不能有空白

SELECT * FROM HumanResources.Department

WHERE ',' + @deptIds + ',' LIKE '%,' + CAST(DepartmentID AS varchar(10)) + ',%'

# Peter Chang said on 14 March, 2016 04:54 AM

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

閻羅王是?! @@"?! 我突然怕怕的!

# Jeffrey said on 14 March, 2016 06:06 AM

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

# 007 said on 15 March, 2016 01:41 AM

之前查過這個方法也是不錯,把條件加入表格變數

sql server - Set variable with multiple values and use IN - Stack Overflow

stackoverflow.com/.../set-variable-with-multiple-values-and-use-in

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<March 2016>
SunMonTueWedThuFriSat
282912345
6789101112
13141516171819
20212223242526
272829303112
3456789
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication