PreparedStatement can't handle "IN" in "WHERE" clause

[salaryman]

I have a simple SQL statement:

SELECT DISTINCT id FROM TheTable WHERE id in ('1', '2', '3', '4');

If I run this in my SQL query window it correctly returns the set [1,
2, 3, 4].

If I execute the SQL directly using Statement.executeQuery, I get the
correct results. However, if I try and substitute the IN clause using
a PreparedStatement:

PreparedStatement statement = connection.prepareStatement("SELECT
DISTINCT id from TheTable WHERE id IN (?)");
statement.setString(1, "'1', '2', '3', '4'");

The result set is empty. I also tried:

statement.setString(1, "1, 2, 3, 4");

And this returns the set [1].

I am using MySQL and if I step into the code for
PreparedStatement.setString(), I can see that single quotes are added
to the front and end of the string, which explains why I am seeing the
behaviour. But it doesn't seem right!

Please can someone tell me whether or not this is expected behaviour,
or is MySQL doing something strange. As far as I can tell, there is
no solution for this problem, other than using a different query.

Cheers.

 

[Michael Borgwardt]

PreparedStatement statement = connection.prepareStatement("SELECT
DISTINCT id from TheTable WHERE id IN (?)");
statement.setString(1, "'1', '2', '3', '4'");

The result set is empty. I also tried:

statement.setString(1, "1, 2, 3, 4");

And this returns the set [1].

I am using MySQL and if I step into the code for
PreparedStatement.setString(), I can see that single quotes are added
to the front and end of the string, which explains why I am seeing the
behaviour. But it doesn't seem right!

It *is* right, though. You have declared your PreparedStatement to have *one*
parameter, so whatever you add will be inserted as one parameter. The whole
point of PreparedStatement is that parameters are NOT interpreted as part of
the SQL string. That's what makes precompilation possible and SQL insertion
attacks impossible.

 

[Thomas S.]

DISTINCT id from TheTable WHERE id IN (?)
statement.setString(1, "'1', '2', '3', '4'");

This is right:

"DISTINCT id from TheTable WHERE id IN (?,?,?,?)"

statement.setString( 1, '1' );
statement.setString( 2, '2' );
statement.setString( 3, '3' );
statement.setString( 4, '4' );

A PreparedStatement does no plain String actions but generates the
fitting Parameters for the specific JDBC interface (every interface
could use a different synthax for their parameters).

Thomas