2

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).

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
chris
  • 110
  • 10
  • I think the answer is here: [stackoverflow.com/MySQL Prepared statements with a variable size variable list](http://stackoverflow.com/questions/327274/mysql-prepared-statements-with-a-variable-size-variable-list) – manji Nov 04 '09 at 20:21

3 Answers3

1

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.

Lukáš Lalinský
  • 40,587
  • 6
  • 104
  • 126
0

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!

Josh Stodola
  • 81,538
  • 47
  • 180
  • 227
-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|%'
davek
  • 22,499
  • 9
  • 75
  • 95
  • -1 Terrible idea. No way Joel recommended this. What if your |1|2|3| values are dynamic? – Josh Stodola Nov 04 '09 at 20:18
  • now I've found it - he got 28 upvotes for it! http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause/337817#337817 – davek Nov 04 '09 at 20:20
  • 2
    @Dave K: Joel's answer is proof that people will blindly vote (and accept answers) based on who you are rather than the content provided. – OMG Ponies Nov 04 '09 at 20:37