0





144
1

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.

Consider the following SQL statement:

SELECT my_column FROM my_table where search_column IN (?)

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?

Question author Chris-mazzola | Source

0


1

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

The suggested options are:

  • 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:

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

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

Answer author D%c3%b3nal

Ask about this question here!