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

2 Views

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;”).

Read More

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

Post Views: 2

Related posts

Leave a Reply

2 comments

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

  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.