问题描述
使用带有 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 useUNION ALL
in place of those semicolons. --ed] Requires one prepared statement per size-of-IN-list. Stupidly slow, strictly worse thanWHERE 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:
- 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 clause alternatives?
似乎没有办法让 setArray
使用IN列表。
There doesn't seem to be any way to make setArray
work with IN-lists, though.
这篇关于PreparedStatement IN子句替代?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!