Multiple values in where with prepared SQL-Statements?

Question

Is there a way to select multiple values with prepared statements in (My-)SQL?

I'm trying to select a couple of rows from a table with the IN-keyword, something like:

SELECT * 
  FROM table 
 where id IN (1, 2, 3)

The "1, 2, 3" should be passed as a parameter of the statement. Is this possible with PHP/PDO or do I have to concaterate the values and insert it directly in the statement (I've got a bad feeling about this because of injections).

Answer 1

Try passing you in-list as aconcatenated string and do this (not very performant but it should work: I think I saw an answer from Joel Spolsky somewhere using this technique):

SELECT * FROM table where concat('|',id,'|') like '%|1|2|3|%'

Answer 2

I would pass in an array of integers, and then do String.Join to bring them together within your prepared statement. You can't inject anything into an integer!

Answer 3

If you have an array of "something" that comes from the user, you can build a list of placeholders with array_fill, generate a string like "?, ?, ?, ..." by calling implode on the array. Alternatively you can make sure everything in the array is an integer (using intval, for example) and use it directly to build the query.

Answer 4

I think the answer is here:

stackoverflow.com/MySQL Prepared statements with a variable size variable list