sp_executesql 与 'IN'陈述

Question

我试图使用 sp_executesql 来防止 SQL 2005 中的 SQL 注入，我有一个像这样的简单查询：

SELECT * from table WHERE RegionCode in ('X101', 'B202')

但是，当我使用 sp_executesql 执行以下命令时，它不会返回任何内容。

Set @Cmd = N'SELECT * FROM table WHERE RegionCode in (@P1)'
SET @ParamDefinition = N'@P1 varchar(100)';
DECLARE @Code as nvarchar(100);
SET @Code = 'X101,B202'
EXECUTE sp_executesql @Cmd, @ParamDefinition, @P1 = @Code

这是我测试过的：

SET @Code = 'X101'   <-- This works, it returns a single region
SET @Code = 'X101,B202'   <--- Returns nothing
SET @Code = '''X101'',''B202'''  <-- Returns nothing

请帮忙......我做错了什么？

Answer 1

它不起作用的原因是因为 @P1 被视为一个单一值。

例如，当 @Code 为 X101,B202 时，查询将按以下方式运行：
SELECT * FROM 表 WHERE RegionCode IN ('X101,B202')
因此，它正在寻找具有 @P1 包含的值的 RegionCode。即使包含单引号，这也意味着它在 RegionCode 中搜索的值应该包含这些单引号。

您需要将 @Code 变量实际连接到 @Cmd sql 命令文本中，以便它按照您的想法工作：

SET @Code = '''X101'',''B202'''
SET @Cmd = 'SELECT * FROM Table WHERE RegionCode IN (' + @Code + ')'
EXECUTE (@Cmd)

但显然，这只会让您面临 SQL 注入，因此您需要非常小心如果您采取这种方法来确保防止这种情况发生。

如果您想要传递要搜索的动态值列表，则可以使用其他方法来处理这种情况。

查看我的博客上的示例< /a> 可以用于 SQL Server 2005 的 2 种方法。其中一种涉及以“Value1,Value2,Value3”形式传入 CSV 列表，然后使用用户定义的函数将其拆分为 TABLE 变量（有很多如果您快速谷歌或搜索此网站，就会提到这种方法）。拆分后，您可以将该 TABLE var 加入到您的主查询中。第二种方法是传入包含值的 XML blob 并使用 SQL Server 的内置 XML 功能。这两种方法都通过该链接中的性能指标进行了演示，并且它们不需要动态 SQL。

如果您使用的是 SQL Server 2008，则表值参数将是最佳选择 - 这是我在该链接中演示的第三种方法，效果最好。

Answer 2

SQL Server 中有多种分割字符串的方法。本文涵盖了几乎每种方法的优点和缺点：

"数组和列表SQL Server 2005 及更高版本，当表值参数无法分割时”作者：Erland Sommarskog

您需要创建一个拆分函数。这是分割函数的使用方式：

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

我更喜欢数字表方法在 TSQL 中分割字符串，但在 SQL Server 中分割字符串的方法有很多种，请参阅前面的链接，其中解释了每种方法的优点和缺点。

要使 Numbers Table 方法发挥作用，您需要执行此一次时间表设置，这将创建一个包含 1 到 10,000 行的 Numbers 表：

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

设置 Numbers 表后，创建此拆分函数：

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO 

您现在可以轻松地将 CSV 字符串拆分为表并连接它或根据需要使用它，甚至可以在动态 sql 中。以下是如何在您的问题的动态参数化查询中使用它：

DECLARE @Cmd as nvarchar(1000),@ParamDefinition nvarchar(1000);
Set @Cmd = N'SELECT * FROM table WHERE RegionCode in (SELECT ListValue FROM dbo.FN_ListToTable('','',@P1))'
SET @ParamDefinition = N'@P1 varchar(100)';
DECLARE @Code as nvarchar(1000);
SET @Code = 'X101,B202'
EXECUTE sp_executesql @Cmd, @ParamDefinition, @P1 = @Code

这是一个要尝试的工作示例（必须首先设置数字表和分割函数）：

CREATE TABLE YourTable (PK int primary key, RowValue varchar(5))
INSERT YourTable VALUES (1,'A')
INSERT YourTable VALUES (2,'BB')
INSERT YourTable VALUES (3,'CCC')
INSERT YourTable VALUES (4,'DDDD')
INSERT YourTable VALUES (5,'EEE')
INSERT YourTable VALUES (6,'FF')
INSERT YourTable VALUES (7,'G')

DECLARE @SQL              nvarchar(1000)
       ,@ParamDefinition  nvarchar(1000)
       ,@ParamValue       varchar(100)
SELECT @SQL = N'SELECT * FROM YourTable WHERE PK IN (SELECT ListValue FROM dbo.FN_ListToTable('','',@P1))'
      ,@ParamDefinition = N'@P1 varchar(100)'
      ,@ParamValue = '2,4,,,6,,8,,2,,4'
EXECUTE sp_executesql @SQL, @ParamDefinition, @P1 = @ParamValue

输出：

PK          RowValue
----------- --------
2           BB
4           DDDD
6           FF

(3 row(s) affected)

Answer 3

看来问题出在单个参数上。实际上，您最终会得到：

SELECT * from table WHERE RegionCode in ('X101,B202')

或者

SELECT * from table WHERE RegionCode in ('''X101'', ''B202''')

也就是说，RegionCode 必须等于 'X101,B202' 或 ''X101','B202'' （完整的字符串）来工作。

最好的选择是在这里使用两个参数：

Set @Cmd = N'SELECT * FROM table WHERE RegionCode in (@P1,@P2)'
SET @Code1 = 'X101'
SET @Code2 = 'B202'

如果您要在该列表中包含两个以上的项目，那么您可能需要采用另一条路线，可能使用临时表或表值参数。