sp_executesql，带有“ IN”语句

小编典典

sp_executesql，带有“ IN”语句

sql

我正在尝试使用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

请帮助…。我做错了什么？

阅读 209

2021-04-22

共1个答案

小编典典

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

例如，当@Code为X101，B202时，查询将按以下方式运行：SELECT * FROM Table 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注入的大门，因此，如果您采取这种方法来确保自己免受这种情况的侵扰，则需要非常小心。

有其他方法可以处理这种情况，您需要传递动态值列表以进行搜索。

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

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

2021-04-22