How to use prepared statements in queries with an IN clause in PHP

Question

I need to make a simple query

$array_of_ids = array();
//poulate $array_of_ids, they don't come from another db but from Facebook
//so i can't use a subquery for the IN clause
$wpdb->prepare("SELECT id from table where id IN (%d, %d)", $array_of_ids [0], $array_of_ids [1]);

The question is, if i have 200 elements in the array, what is the correct way to handle this?Do i have to manually build the query with 200 %d? I need this query because i must "sync" my database with facebook data and i have to check if the user i have in the db are present, update those that are present, insert new users and delete those that are not my friend.

See if [this question][1] doesn't answer your question [1]: http://stackoverflow.com/questions/327274/mysql-prepared-statements-with-a-variable-size-variable-list — Tudor Constantin, May 08 '12 at 15:15

eggyal · Answer 1 · 2012-05-08T15:22:09.247

4

If you know for certain that the array elements are numeric:

$wpdb->prepare("SELECT id FROM table WHERE id IN ("
  . implode(',',$array_of_ids) . ")");

Otherwise, you can use the vsprintf form of prepare to pass in the array of parameters:

$wpdb->prepare("SELECT id FROM table WHERE id IN ("
  . str_repeat("%d,", count($array_of_ids)-1) . "%d)" , $array_of_ids);

edited May 08 '12 at 15:22

answered May 08 '12 at 15:15

eggyal

122,705
18
212
237

4

Kinda defeating the whole purpose of prepared statements there, aren'tcha. – cHao May 08 '12 at 15:16
You can do an `implode(',', array_map('intval', $array_of_ids))` to ensure that they are, in fact, numeric. – Sam Dufel May 08 '12 at 15:18
Fair points; also showing an alternative approach in my revised answer. – eggyal May 08 '12 at 15:25
I'm just curious, why don't you do `str_repeat("%d,", count($array_of_ids)) . ")"` ? Is there some edge case i'm missing? – Nicola Peluchetti May 08 '12 at 16:04
@NicolaPeluchetti: You'd end up with a trailing comma you'd need to strip. – eggyal May 08 '12 at 16:04
:) thanks that's what i was missing – Nicola Peluchetti May 08 '12 at 16:06
@eggyal - Then how about `implode(',', array_pad(array(), count($array_of_ids), '%d'))`? See here: http://viper-7.com/kfNFfM – Joseph Silber Sep 30 '12 at 05:30
1

@JosephSilber: Yes, that would work... but almost certainly less performant to iteratively construct an array solely for the purpose of then imploding its contents into a string... – eggyal Sep 30 '12 at 08:56
In the situation I have where the array is strings rather than decimal I think the second alternative in this solution 8adapted to %s of course) is the best. Though you should handle the case that the array is empty as a special case first I think – Adam Oct 18 '13 at 13:58

score 1 · Answer 2 · answered May 08 '12 at 15:21

I'm not sure that this is a good approach, but you could do it in this fashion:

$sql = "SELECT id from table where id IN (" 
     . implode(',', array_fill(0, count($array_of_ids), "%d"))
     . ")";

call_user_func_array(array($wpdb, 'prepare'), $array_of_ids);

This builds a string with the appropriate number of %d, then uses call_user_func_array to do it dynamically.

That said, I'm not sure this is really a case where prepared statements are worth the hassle, given how easy it is to sanitise integers.

array_map() does the trick nicely i think, but if i use array map in that way i could easily skip the prepared statement :) — Nicola Peluchetti, May 08 '12 at 15:35

score 0 · Answer 3 · answered May 08 '12 at 15:18

Yes, dynamic sql is the way here. Fortunately, integers are easy to not screw up with.

$vals = array_filter(array_map('intval', $vals));

make sure you have at least one value and then implode it. Not need for a prepared statement here, just execute the sql.

score 0 · Answer 4 · answered Jan 30 '14 at 13:14

Since this has no accepted answer yet I'll go with my approach with array_filter

$array_of_ids = array(0,1,1,2,3,5,8,13);

echo "SELECT id from table where id IN (".implode(',', array_filter($array_of_ids,'is_int')).")";

will output

SELECT id from table where id IN (0,1,1,2,3,5,8,13)

while

$array_of_ids = array('zero',1,true,2,3,5,8,'thirteen');

echo "SELECT id from table where id IN (".implode(',', array_filter($array_of_ids,'is_int')).")";

will output

SELECT id from table where id IN (1,2,3,5,8)

Please note that is_int doesn't work with $_GET variables so use is_numeric instead

score -1 · Answer 5 · answered May 08 '12 at 15:23

-1

You can do this :

$query = $wpdb->prepare("SELECT id from table where id IN :param");
$query->bindParam("param", "(".implode(',', array_map('intval', $array_of_ids)).")");

answered May 08 '12 at 15:23

Cyril N.

38,875
36
142
243

1

Surely `array_map('intval', ...)` guarantees safety from SQL injection? – eggyal May 08 '12 at 15:24
Seems to me that if you bind `param`, it'll be SQL-escaped, quoted, and treated as a string...not as a list of stuff. Unless MySQL does some magic to interpret strings as lists, this probably won't work. – cHao May 12 '12 at 05:07
$wpdb->prepare() does not actually "prepare" a statement in the database, but rather just sanitizes the parameters. Also your $query->bindParam() call would generate an error as $wpdb->prepare() returns a string and not the PDOStatement object as your answer would suggest – thorne51 Dec 02 '14 at 07:58
@thorne51 your comment is not clear. This code works and I don't see how it would fail like you say. @cHao : Not really, you are building a list from `$array_of_ids` but if one entry contains ",", you may have a fail in your request. Maybe not an SQL injection, but something to work on security side. The "intval" prevents it. – Cyril N. Dec 02 '14 at 08:26

How to use prepared statements in queries with an IN clause in PHP

5 Answers5

Linked