小编典典

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

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


阅读 212

收藏
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