题 参数化SQL IN子句


如何参数化包含的查询 IN 带有可变数量参数的子句,就像这个一样?

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

在此查询中,参数的数量可以是1到5之间的任何值。

我宁愿不为此(或XML)使用专用存储过程,但如果有一些特定的优雅方式 SQL Server 2008,我对此持开放态度。


951
2017-12-03 16:16


起源


对于MySQL,请参阅 MySQL Prepared具有可变大小变量列表的语句。 - outis


答案:


这是我用过的一种快速而肮脏的技术:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

所以这是C#代码:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

两个警告:

  • 表现很糟糕。 LIKE "%...%" 查询未编入索引。
  • 确保你没有 |,空白或空标签或这不起作用

有其他方法可以实现这一点,有些人可能认为更清洁,所以请继续阅读。


288
2017-12-03 16:41



那将是hella慢 - Matt Rogish
是的,这是一个表扫描。伟大的10行,糟糕的10万。 - Will Hartung
确保测试其中包含管道的标签。 - Joel Coehoorn
这甚至没有回答这个问题。当然,很容易看到添加参数的位置,但是如果它甚至不打算参数化查询,你怎么能接受这个解决方案呢?它看起来比@Mark Brackett简单,因为它没有参数化。 - tvanfosson
如果你的标签是'ruby | rails'怎么办?它会匹配,这将是错误的。当你推出这样的解决方案时,你需要确保标签不包含管道,或者明确地过滤掉它们:select * from Tags where where | | ruby​​ | rails | scruffy | ruby​​onrails |'喜欢'%|' +名称+'|%'并且名称不像'%!%' - A-K


你可以参数化  价值,所以像这样:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

哪个会给你:

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

不,这不是开放的 SQL注入。 CommandText中唯一注入的文本不是基于用户输入。它完全基于硬编码的“@tag”前缀和数组的索引。该指数将 总是 是一个整数,不是用户生成的,是安全的。

用户输入的值仍然填充到参数中,因此没有漏洞。

编辑:

除了注入问题之外,请注意构建命令文本以容纳可变数量的参数(如上所述)会妨碍SQL服务器利用缓存查询的能力。最终结果是你几乎肯定会失去首先使用参数的价值(而不是仅仅将谓词字符串插入SQL本身)。

并不是说缓存的查询计划没有价值,但是IMO这个查询并不是很复杂,足以从中看到很多好处。虽然编译成本可能接近(甚至超过)执行成本,但您仍然在谈论毫秒。

如果你有足够的RAM,我希望SQL Server可能会缓存一个常见的参数计数的计划。我想你总是可以添加五个参数,让未指定的标签为NULL - 查询计划应该是相同的,但对我来说这似乎很难看,我不确定它是否值得进行微优化(尽管如此,在Stack Overflow上 - 它可能非常值得)。

此外,SQL Server 7及更高版本将 自动参数化查询,从性能的角度来看,使用参数并不是必需的 - 但是,它是 危急 从安全角度来看 - 尤其是用户输入的数据。


676
2017-12-03 16:35



基本上与我对“相关”问题的答案相同,显然是最佳解决方案,因为它是建设性的,有效的而不是解释性的(更难)。 - tvanfosson
这就是LINQ to SQL的用途,BTW - Mark Cidade
@Pure:这一点的全部意义在于避免使用SQL注入,如果使用动态SQL,则会容易受到攻击。 - Ray
@God of Data - 是的,我想如果你需要超过2100个标签,你需要一个不同的解决方案。但是如果平均标签长度<3个字符,那么Basarb只能达到2100(因为你也需要一个分隔符)。 msdn.microsoft.com/en-us/library/ms143432.aspx - Mark Brackett
@bonCodigo - 您选择的值在数组中;你只需遍历数组并为每个数组添加一个参数(后缀为索引)。 - Mark Brackett


对于SQL Server 2008,您可以使用 表值参数。这有点工作,但它可以说比清洁更干净 我的另一种方法

首先,您必须创建一个类型

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

然后,您的ADO.NET代码如下所示:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";

// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";

// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
    if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
    var firstRecord = values.First();
    var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

235
2017-12-03 16:53



我们测试了这个并且表值参数是DOG慢。执行5个查询比执行一个TVP要快得多。 - Jeff Atwood
@JeffAtwood - 您是否尝试过将查询重新调整为类似的内容 SELECT * FROM tags WHERE tags.name IN (SELECT name from @tvp);?从理论上讲,这确实应该是最快的方法。您可以使用相关索引(例如标记名称的索引) INCLUDE计数是理想的),SQL Server应该做一些寻求抓住所有标签及其计数。该计划是什么样的? - Nick Chammas
我也测试了它,它快速闪电(与构建一个大的IN字符串相比)。我在设置参数时遇到了一些问题,因为我经常收到“无法将参数值从Int32 []转换为IEnumerable`1。”。无论如何,解决了这个,这是我做的一个样本 pastebin.com/qHP05CXc - Fredrik Johansson
@FredrikJohansson - 在130个赞成票中,你可能是唯一一个真正试图运行它的游戏!我在阅读文档时犯了一个错误,你实际上需要一个IEnumerable <SqlDataRecord>,而不仅仅是IEnumerable。代码已更新。 - Mark Brackett
@MarkBrackett非常棒,有更新!准确地说,这段代码真的为我节省了一天,因为我正在查询一个Lucene搜索索引,它有时会返回超过50.000左右的点击,需要对SQL服务器进行双重检查 - 所以我创建了一个int []数组(文档/ SQL键)然后上面的代码进来。整个OP现在需要不到200ms :) - Fredrik Johansson


最初的问题是 “我如何参数化查询......”

让我在这里说,这是 不是答案 原来的问题。在其他好的答案中已经有一些示范。

有了这个说法,请继续标记这个答案,将其归类,将其标记为不是答案......做任何你认为正确的事情。

请参阅Mark Brackett的答案,了解我(及其他231人)投票的首选答案。他的回答中给出的方法允许1)有效使用绑定变量,2)用于可搜索的谓词。

选择答案

我想在这里讨论的是Joel Spolsky的回答中给出的方法,答案“选择”作为正确的答案。

Joel Spolsky的方法很聪明。并且它合理地工作,它将展示可预测的行为和可预测的性能,给定“正常”值,以及规范边缘情况,例如NULL和空字符串。并且它对于特定应用可能就足够了。

但是从概括这种方法的角度来看,让我们也考虑更加模糊的角落情况,比如说 Name column包含一个通配符(由LIKE谓词识别。)我看到最常用的通配符是 % (百分号)。现在让我们来处理这个问题,然后继续讨论其他案例。

%字符的一些问题

考虑Name的值 'pe%ter'。 (对于此处的示例,我使用文字字符串值代替列名。)名称值为“pe%ter”的行将由以下形式的查询返回:

select ...
 where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

但同一行会  如果搜索条款的顺序颠倒,则返回:

select ...
 where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

我们观察到的行为有点奇怪。更改列表中搜索词的顺序会更改结果集。

几乎不言而喻,我们可能不想要 pe%ter 配花生酱,不管他喜欢多少。

朦胧的角落案例

(是的,我会同意这是一个模糊的案例。可能是一个不太可能被测试的案例。我们不希望列值中出现通配符。我们可以假设应用程序阻止存储这样的值。但是根据我的经验,我很少看到数据库约束明确禁止在右侧被视为通配符的字符或模式 LIKE 比较运算符。

修补一个洞

修补这个洞的一种方法是逃避 % 通配符。 (对于不熟悉运算符的escape子句的人来说,这里有一个链接 SQL Server文档

select ...
 where '|peanut|butter|'
  like '%|' + 'pe\%ter' + '|%' escape '\'

现在我们可以匹配文字%。当然,当我们有一个列名时,我们需要动态转义通配符。我们可以使用 REPLACE 函数来查找出现的 %字符并在每个字符前插入一个反斜杠字符,如下所示:

select ...
 where '|pe%ter|'
  like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'

这样就解决了%wildcard的问题。几乎。

逃离逃生

我们认识到我们的解决方案引入了另一个问题逃脱角色。我们看到我们还需要逃避任何出现的转义字符本身。这次,我们用了!作为转义字符:

select ...
 where '|pe%t!r|'
  like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'

也是下划线

现在我们已经开始了,我们可以添加另一个 REPLACE 处理下划线通配符。只是为了好玩,这一次,我们将使用$作为转义字符。

select ...
 where '|p_%t!r|'
  like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

我更喜欢这种方法来逃避,因为它适用于Oracle和MySQL以及SQL Server。 (我通常使用\反斜杠作为转义字符,因为这是我们在正则表达式中使用的字符。但是为什么会被约定约束!

那些讨厌的括号

SQL Server还允许将通配符作为文字处理,方法是将它们括在括号中 []。所以我们还没有完成修复,至少对于SQL Server来说。由于括号对具有特殊含义,我们也需要逃避它们。如果我们设法正确地逃避括号,那么至少我们不必打扰连字符 - 和克拉 ^ 在括号内。我们可以留下任何东西 %_ 括号内的字符被转义,因为我们基本上已经禁用了括号的特殊含义。

找到匹配的括号对不应该那么难。这比处理单例%和_的出现要困难一些。 (请注意,仅仅转义所有出现的括号是不够的,因为单个括号被认为是文字,并且不需要进行转义。逻辑变得比我可以处理的更模糊而不运行更多的测试用例。)

内联表达式变得混乱

SQL中的内联表达式越来越长,越来越丑陋。我们可能会让它发挥作用,但是天堂帮助了落后的可怜的灵魂并且必须破译它。作为内联表达的粉丝,我倾向于不在这里使用,主要是因为我不想留下评论解释混乱的原因,并为此道歉。

一个功能在哪里?

好的,所以,如果我们不将它作为SQL中的内联表达式处理,那么我们最接近的替代方法是用户定义的函数。我们知道不会加速任何事情(除非我们可以像在Oracle上那样定义索引。)如果我们必须创建一个函数,我们可能最好在调用SQL的代码中这样做声明。

并且该功能可能在行为上有一些差异,这取决于DBMS和版本。 (向所有Java开发人员致敬,他们热衷于能够交替使用任何数据库引擎。)

领域知识

我们可能对该列的域具有专门知识(即,为该列强制执行的一组允许值。我们可能知道 先验 存储在列中的值永远不会包含百分号,下划线或括号对。在这种情况下,我们只是简单地包含这些案例的快速评论。

存储在列中的值可以允许%或_字符,但是约束可能要求对这些值进行转义,可能使用定义的字符,以使值为LIKE比较“安全”。再次,快速评论允许的值集,特别是哪个字符用作转义字符,并与Joel Spolsky的方法一起使用。

但是,如果缺乏专业知识和保证,至少应考虑处理那些模糊不清的角落案件,并考虑行为是否合理并且“符合规范”,这一点非常重要。


其他问题概括

我相信其他人已经充分指出了其他一些常被考虑的关注领域:

  • SQL注入 (看看用户提供的信息,包括在SQL文本中的信息,而不是通过绑定变量提供它们。使用绑定变量不是必需的,它只是一种方便的方法来阻止SQL注入。还有其他方法可以处理它:

  • 优化器计划使用索引扫描而不是索引搜索,可能需要表达式或函数来转义通配符(可能的表达式或函数索引)

  • 使用文字值代替绑定变量会影响可伸缩性


结论

我喜欢Joel Spolsky的做法。这很聪明。它有效。

但是当我看到它时,我立刻就看到了它的一个潜在问题,让它滑动不是我的本性。我并不是要批评别人的努力。我知道很多开发人员都非常个性化,因为他们投入了大量资金,而且他们非常关心它。所以请理解,这不是个人攻击。我在这里发现的是在生产而不是测试中出现的问题类型。

是的,我离原问题远远不够。但是还有什么地方可以留下这个关于我认为是一个问题的“选定”答案的重要问题的说明?


176
2018-05-29 23:18



如果您使用或喜欢参数化查询,请告诉我们?在这种特殊情况下,跳过“使用参数化查询”的规则并使用原始语言进行消毒是否正确?非常感谢 - Luis Siquot
@Luis:是的,我更喜欢在SQL语句中使用绑定变量,并且在使用绑定变量时只会避免绑定变量导致性能问题。我对原始问题的规范模式是在IN列表中动态创建具有所需占位符数的SQL语句,然后将每个值绑定到其中一个占位符。请参阅Mark Brackett的答案,这是我(和其他231人)投票的答案。 - spencer7593


您可以将参数作为字符串传递

所以你有字符串

DECLARE @tags

SET @tags = ‘ruby|rails|scruffy|rubyonrails’

select * from Tags 
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc

然后你要做的就是将字符串作为1参数传递。

这是我使用的分割功能。

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

123
2017-12-03 16:27



您也可以使用此方法加入表函数。 - Michael Haren
我在Oracle中使用类似于此的解决方案。它不必像其他一些解决方案那样重新解析。 - Leigh Riffel
这是一种纯数据库方法,另一种需要在数据库之外的代码中工作。 - David Basarab
这对表扫描还是可以利用索引等? - Pure.Krome
更好的是对SQL表函数使用CROSS APPLY(至少在2005年以后),它基本上连接到返回的表 - adolf garlic


我听说Jeff / Joel今天在播客上谈到这个问题(第34集,2008-12-16(MP3,31 MB),1小时03分38秒 - 1小时06分45秒),我想我回想起Stack Overflow正在使用 的LINQ to SQL,但也许它被抛弃了。这与LINQ to SQL中的内容相同。

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;

而已。而且,是的,LINQ已经足够落后了,但是 Contains 条款对我来说似乎特别倒退。当我不得不对工作中的项目进行类似的查询时,我自然会尝试通过在本地数组和SQL Server表之间进行连接来做错误的方法,认为LINQ to SQL转换器足够智能来处理翻译不知何故。它没有,但它确实提供了一个描述性的错误信息,并指出我使用 包含

无论如何,如果你在强烈推荐的情况下运行它 LINQPad,并运行此查询,您可以查看SQL LINQ提供程序生成的实际SQL。它会显示每个参数化的值 IN 条款。


63
2017-12-19 05:40





如果您使用.NET进行调用,则可以使用 精致小点网

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags 
where Name in @names
order by Count desc", new {names});

Dapper在这里思考,所以你没有必要。可能有类似的东西 的LINQ to SQL, 当然:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tags
           where names.Contains(tag.Name)
           orderby tag.Count descending
           select tag;

43
2018-06-15 11:04



这恰好是我们在这个页面上使用的,对于实际问题(小巧玲珑) i.stack.imgur.com/RBAjL.png - Sam Saffron
请注意,dapper现在也 支持表值参数作为一等公民 - Marc Gravell♦
如果名字很长,这就会失败 - cs0815