mysql in 参数化_参数化SQL IN子句

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

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

话虽如此，请继续并标记这个答案，将其归类，将其标记为不是答案......做任何你认为正确的事情。

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

选择答案

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

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

但是在概括这种方法的术语中，我们还要考虑更加模糊的边角情况，例如当Name列包含通配符时(由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的做法。这很聪明。它有效。

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

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