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