Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.