Today I found myself wanting to use a query similar to SELECT DISTINCT post_id FROM $wpdb->posts WHERE meta_key IN ( 'string_x', 'string_y', 'string_z' ), but where I needed to construct the values for the list of strings from an array of untrusted values (because we never trust inputs, we always look suspiciously on them if they’re going near the DB). Looking through the core code, it seems like there’s currently one place it does this and, while less elegant than the WPDB class prepare method it’s worth me remembering for the future, so…
The technique is to run the elements of the array through the esc_sql and sanitize_title_for_query functions which WordPress provides; esc_sql essentially throws things over to a method on the WPDB object to add slashes, and sanitize_title_for_query which by default (it’s extendable through filters) runs it through sanitize_title_with_dashes to replace whitespace and a few other characters with dashes, limits the output to alphanumeric characters, underscore (_) and dash (-) converting whitespace to a dash.
Here’s the code I’m using:
// $meta_keys is an array of strings equating
// to meta_keys in the postmeta table
$meta_keys = array_map( 'esc_sql', $meta_keys );
$meta_keys = array_map( 'sanitize_title_for_query', $meta_keys );
$meta_keys_str = "'". implode( "','", $meta_keys ) . "'";
$post_ids = $wpdb->get_col( "SELECT DISTINCT post_id FROM $wpdb->postmeta WHERE meta_key IN ( $meta_keys_str )" );
Let me know if I’m doing_it_wrong, but otherwise I hope this helps someone else, probably that someone will be me in the future!