PreparedStatement IN子句替代？ [英] PreparedStatement IN clause alternatives?

问题描述

使用带有 java.sql.PreparedStatement 实例的SQL IN 子句的最佳解决方法是什么？由于SQL注入攻击安全问题，不支持多个值：一个？占位符代表一个值，而不是值列表。

What are the best workarounds for using a SQL IN clause with instances of java.sql.PreparedStatement, which is not supported for multiple values due to SQL injection attack security issues: One ? placeholder represents one value, rather than a list of values.

考虑以下SQL语句：

SELECT my_column FROM my_table where search_column IN (?)

使用 preparedStatement.setString（1，'A'，'B'，'C' ）; 本质上是一种非工作尝试，首先解决了使用？的原因。

Using preparedStatement.setString( 1, "'A', 'B', 'C'" ); is essentially a non-working attempt at a workaround of the reasons for using ? in the first place.

有哪些可用的解决方法？

What workarounds are available?

推荐答案

各种分析可用的选项，以及各自的优缺点可在此处获得。

An analysis of the various options available, and the pros and cons of each is available here.

建议的选项是：

• 准备 SELECT my_column FROM my_table WHERE search_column =？，为每个值执行它，对结果客户端执行UNION。只需要一份准备好的声明。慢慢痛苦。


• 准备 SELECT my_column FROM my_table WHERE search_column IN（？，？，？）并执行它。每个IN-list大小需要一个准备好的语句。快速而明显。


• 准备 SELECT my_column FROM my_table WHERE search_column =？ ; SELECT my_column FROM my_table WHERE search_column =？ ; ... 并执行它。 [或者使用 UNION ALL 代替这些分号。 - 每个大小的IN列表需要一个准备好的语句。愚蠢的慢，严格比 WHERE search_column IN（？，？，？），所以我不知道为什么博主甚至建议它。


• 使用存储过程构造结果集。


• 准备N个不同大小的IN列表查询;比方说，有2个，10个和50个值。要搜索包含6个不同值的IN列表，请填充size-10查询，使其看起来像 SELECT my_column FROM my_table WHERE search_column IN（1,2,3,4,5,6,6 ，6,6,6-）。任何体面的服务器都会在运行查询之前优化重复值。

• Prepare SELECT my_column FROM my_table WHERE search_column = ?, execute it for each value and UNION the results client-side. Requires only one prepared statement. Slow and painful.
• Prepare SELECT my_column FROM my_table WHERE search_column IN (?,?,?) and execute it. Requires one prepared statement per size-of-IN-list. Fast and obvious.
• Prepare SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ... and execute it. [Or use UNION ALL in place of those semicolons. --ed] Requires one prepared statement per size-of-IN-list. Stupidly slow, strictly worse than WHERE search_column IN (?,?,?), so I don't know why the blogger even suggested it.
• Use a stored procedure to construct the result set.
• Prepare N different size-of-IN-list queries; say, with 2, 10, and 50 values. To search for an IN-list with 6 different values, populate the size-10 query so that it looks like SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6). Any decent server will optimize out the duplicate values before running the query.

但这些选项都不是超级好的。

None of these options are super great, though.

在这些地方已经回答了一些有同样理智的替代方案的重复问题，但仍然没有一个非常棒：

Duplicate questions have been answered in these places with equally sane alternatives, still none of them super great:

• 带有IN子句中参数列表的PreparedStatement


• 如何在预准备语句中设置参数列表？

• PreparedStatement with list of parameters in a IN clause
• How to set list of parameters on prepared statement?

正确答案，如果您使用的是JDBC4和服务器支持 x = ANY（y），是使用 PreparedStatement.setArray ，如下所述：

The Right Answer, if you are using JDBC4 and a server that supports x = ANY(y), is to use PreparedStatement.setArray as described here:

• PreparedStatement IN子句替代方案？

• PreparedStatement IN clause alternatives?

似乎没有办法让 setArray 使用IN列表。

There doesn't seem to be any way to make setArray work with IN-lists, though.

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