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


Solution: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.


Solution: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!


Solution:3

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|%'  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »