How do you use prepare when asking for a list of id's

Question

I'm looking to get a number of records back from the database given a list of numbers and was wondering how I would use $wpdb->prepare to do this (to take advantage of the sanitising vs SQL injection attacks that it gives me).

A wrong way of going about what I'd like to achieve is $wpdb->query($wpdb->prepare('SELECT * FROM wp_postmeta WHERE post_id IN (' . implode(',', $ids) .') but this gives the chance of an SQL Injection attack (imagine one of the ids having the value "0); DROP TABLE wp_posts;").

NOTE: I'm not trying to select data from wp_postmeta, I'm just using it as example.

Answer 1

$wpdb->prepare() use the same syntax for formatting like php's printf(). SSo you will need something like ... WHERE post_id IN (%d,%d,%d) ...

This will fit for three ids. But what if we got more or less than three ids? We will use the php tools to create the needed formatting string (assuming $ids is an array with the ids):

Count the IDs

count( $ids )

Create a string with a '%d,' for every ID in $ids

str_repeat( '%d,', count( $ids ) )

Now we have a string like %d,%d,%d,. There is one comma to much at the end. Let's remove it

$ids_format_string = rtrim( str_repeat( '%d,', count( $ids ) ), ',' );

And use this in this way '... WHERE post_id IN (' . $ids_format_string . ') ...'

Answer 2

In your case you know what kind of data you expect, so I would force that type:

$ids = array_map( 'absint', $ids );

And then you can use these values without further preparation.