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