Prepare IN and NOT IN Statements in WordPress

Why should I use prepared statements?

Using some type of prepared statement protects you against SQL injection attacks when you need to interact with the database using parameters passed in from the client side. Depending on the library you are using, how you do this will vary slightly, however when working directly with the database in WordPress it’s best to use the $wpdb global object to abstract the actual interface. The deprecated mysql_* extensions are still officially being used, however as of release of WordPress 3.9 when used in conjunction with PHP 5.5, you bumped over to the newer and improved mysqli. Either way, you can use $wpdb->prepare( $statement, $arg1, $arg2... ) and the WordPress API will handle the details of the implementation.

DO NOT DO THIS AT HOME (OR ANYWHERE)

This is an example of code that is susceptible to a SQL injection.

global $wpdb;
// is this really a numeric ID?
$id = $_POST['id'];
$sql = "SELECT post_name FROM {$wpdb->posts} WHERE ID = $id;";
$name = $wpdb->get_var( $sql );

Imagine what would happen if the value of $_POST['id'] was not a number like we expect, and is instead the string 0; DELETE FROM wp_posts;. The resulting $sql value that is executed would look like the following.

SELECT post_name
FROM wp_posts
WHERE ID = 0;
DELETE FROM wp_posts;

The right way, using prepare()

The correct way to write the above code so that it is not susceptible to SQL injection attacks would be to use $wpdb->prepare(), and it would look like this:

global $wpdb;
// is this really a numeric ID?
$id = $_POST['id'];
// Use %d for digits, or %s for strings when calling prepare()
$sql = $wpdb->prepare( "SELECT post_name FROM {$wpdb->posts} WHERE ID = %d", $id );
$name = $wpdb->get_var( $sql );

Using Prepared Statements with IN and NOT IN

Things get a bit more complicated when you need to pass an array of values into an IN or NOT IN clause however. The best way to deal with this situation is to use call_user_func_array() to pass an array as a list of arguments to $wpdb->prepare(). Since there may be other values we want to escape as well, I usually define a new function to handle this special case and end up calling prepare() twice. This particular function assumes you want to pass in a list of post ID’s, potentially having the list twice (an OR statement for example) – so you may need to adjust for your needs.

function my_function(){
	global $wpdb;
	$id = $_POST['id'];
	$id_array = $_POST['id_array'];

	$sql = "SELECT post_name FROM {$wpdb->posts} WHERE ID = %d or ID IN ([IN])";
	$sql = $wpdb->prepare( $sql, $id );
	$sql = prepare_in( $sql, $id_array );

	// SELECT post_name FROM wp_posts WHERE ID = 9 or ID IN ( 10, 11, 12 )
}

function prepare_in( $sql, $vals ){
	global $wpdb;
	$not_in_count = substr_count( $sql, '[IN]' );
	if ( $not_in_count > 0 ){
		$args = array( str_replace( '[IN]', implode( ', ', array_fill( 0, count( $vals ), '%d' ) ), str_replace( '%', '%%', $sql ) ) );
		// This will populate ALL the [IN]'s with the $vals, assuming you have more than one [IN] in the sql
		for ( $i=0; $i < substr_count( $sql, '[IN]' ); $i++ ) {
			$args = array_merge( $args, $vals );
		}
		$sql = call_user_func_array( array( $wpdb, 'prepare' ), array_merge( $args ) );
	}
	return $sql;
}