sp_executesql，带有"IN"语句 [英] sp_executesql with 'IN' statement

问题描述

我正在尝试使用sp_executesql防止SQL 2005中的SQL注入，我有一个简单的查询，如下所示:

I am trying to use sp_executesql to prevent SQL injection in SQL 2005, I have a simple query like this:

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

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

However, when I use sp_executesql to execute the following, it doesn't return anything.

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

请帮助....我做错了什么?

Please help.... what did I do wrong?

推荐答案

之所以不起作用，是因为@ P1被视为一个单一值.

The reason it doesn't work is because @P1 is treated as one, single value.

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

e.g. when @Code is X101,B202 then the query is just being run as: SELECT * FROM Table WHERE RegionCode IN ('X101,B202') So, it's looking for a RegionCode with the value that is contained with @P1. Even when you include single quotes, all that means is the value it searches for in RegionCode is expected to contain those single quotes.

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

You'd need to actually concatenate the @Code variable into the @Cmd sql command text in order for it to work the way you are thinking:

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

但是，显然，这只是使您可以进行SQL注入，因此，如果您采取这种方法来确保自己免受这种情况的侵扰，则需要非常小心.

Obviously though, this just opens you up to SQL injection so you'd need to be very careful if you took this approach to make sure you guard against that.

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

There are alternative ways of dealing with this situation where you want to pass in a dynamic list of values to search for.

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

Check out the examples on my blog for 2 approaches you could use with SQL Server 2005. One involves passing in a CSV list in the form "Value1,Value2,Value3" which you then split out into a TABLE variable using a user defined function (there's a lot of mentions of this approach if you do a quick google or search of this site). Once split out, you then join that TABLE var in to your main query. The second approach is to pass in an XML blob containing the values and use the built-in XML functionality of SQL Server. Both these approaches are demonstrated with performance metrics in that link, and they require no dynamic SQL.

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

If you were using SQL Server 2008, Table Value Parameters would be the way to go - that's the 3rd approach I demonstrate in that link which comes out best.

这篇关于sp_executesql，带有"IN"语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！