WordPress prepared statement with IN() condition

Question

I have three values in a string like this:

$villes = '"paris","fes","rabat"';

When I feed it into a prepared statement like this:

$sql    = 'SELECT distinct telecopie FROM `comptage_fax` WHERE `ville` IN(%s)';
$query  = $wpdb->prepare($sql, $villes);

echo $query; shows:

SELECT distinct telecopie FROM `comptage_fax` WHERE `ville` IN('\"CHAPELLE VIVIERS \",\"LE MANS \",\"QUEND\"')

It is not writing the string as three separate values -- it is just one string with the double quotes escaped.

How can I properly implement a prepared statement in WordPress with multiple values?

Answer 1

WordPress already has a function for this purpose, see esc_sql(). Here is the definition of this function:

Escapes data for use in a MySQL query. Usually you should prepare queries using wpdb::prepare(). Sometimes, spot-escaping is required or useful. One example is preparing an array for use in an IN clause.

You can use it like this:

$villes = ["paris", "fes", "rabat"];
$villes = array_map(function($v) {
    return "'" . esc_sql($v) . "'";
}, $villes);
$villes = implode(',', $villes);
$query = "SELECT distinct telecopie FROM `comptage_fax` WHERE `ville` IN (" . $villes . ")"

Answer 2

FUNCTION:

function escape_array($arr){
    global $wpdb;
    $escaped = array();
    foreach($arr as $k => $v){
        if(is_numeric($v))
            $escaped[] = $wpdb->prepare('%d', $v);
        else
            $escaped[] = $wpdb->prepare('%s', $v);
    }
    return implode(',', $escaped);
}

USAGE:

$arr = array('foo', 'bar', 1, 2, 'foo"bar', "bar'foo");

$query = "SELECT values
FROM table
WHERE column NOT IN (" . escape_array($arr) . ")";

echo $query;

RESULT:

SELECT values
FROM table
WHERE column NOT IN ('foo','bar',1,2,'foo\"bar','bar\'foo')

May or may not be more efficient, however it is reusable.

Answer 3

First is a modern set of a non-WordPress techniques using a mysqli prepared statement with an unknown number of values in an array. The second snippet will be the WordPress equivalent.

Let's assume that the indexed array of input data is untrusted and accessible from $_GET['villes']. A prepared statement is the modern standard and preferred by professional developers over old/untrusted escaping techniques. The snippet to follow will return rows that have one of the ville values specified in the array. If the array is not declared or is empty, it will return ALL rows in the database table.

Native PHP techniques:

$sql = "SELECT DISTINCT telecopie FROM comptage_fax";
if (!empty($_GET['villes'])) {
    $count = count($_GET['villes']);
    $commaDelimitedPlaceholders = implode(',', array_fill(0, $count, '?'));
    $stmt = $conn->prepare("$sql WHERE ville IN ($commaDelimitedPlaceholders)");
    $stmt->bind_param(str_repeat('s', $count), ...$_GET['villes']);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $result = $conn->query($sql);
}

From this point, you can access the rows of distinct telecopie values (which is technically an iterable result set object) as if iterating an indexed array of associative arrays with a simple foreach().

foreach ($result as $row) {
    echo $row['telecopie'];
}

---

With WordPress's helper methods the syntax is simpler because the variable binding and query execution is handled by get_results():

$sql = "SELECT DISTINCT telecopie FROM comptage_fax";
if (!empty($_GET['ville']) {
    $commaDelimitedPlaceholders = implode(',', array_fill(0, count($_GET['ville']), '%s'));
    $sql = $wpdb->prepare("$sql WHERE ville IN ($commaDelimitedPlaceholders)", $_GET['ville']);
}
$result = $wpdb->get_results($sql, ARRAY_A);

From this point, $result is an indexed array of associative arrays -- specifically because of ARRAY_A. $result is not a result set object like in the first native php snippet. This means that you can use both classic looping language constructor or the full sweet of array_ functions on the data.

Useful References:

• https://developer.wordpress.org/reference/classes/wpdb/prepare/
• https://developer.wordpress.org/reference/classes/wpdb/get_results/

Answer 4

Here is my approach for sanitizing IN (...) values for $wpdb.

1. I use a helper function that passes each value of the list through $wpdb->prepare() to ensure that it's properly escaped.
2. The prepared value-list is inserted into the SQL query via sprintf().

The helper function:

// Helper function that returns a fully sanitized value list.
function _prepare_in ( $values ) {
    return implode( ',', array_map( function ( $value ) {
        global $wpdb;

        // Use the official prepare() function to sanitize the value.
        return $wpdb->prepare( '%s', $value );
    }, $values ) );
};

Sample usage:

// Sample 1 - note that we use "sprintf()" to build the SQL query!
$status_cond = sprintf(
    'post_status IN (%s)',
    _prepare_in( $status )
);
$posts = $wpdb->get_col( "SELECT ID FROM $wpdb->posts WHERE $status_cond;" );


// Sample 2:
$posts = $wpdb->get_col( sprintf( "
    SELECT ID
    FROM $wpdb->posts
    WHERE post_status IN (%s) AND post_type IN (%s)
    ;",
    _prepare_in( $status ),
    _prepare_in( $post_types )
) );

Answer 5

I created a tiny function that will generate the placeholders for a prepared statement from an array, i.e., something like (%s,%d,%f), and conveniently, it will know exactly which placeholder to use depending on each item of the array.

function generate_wpdb_prepare_placeholders_from_array( $array ) {
    $placeholders = array_map( function ( $item ) {
        return is_string( $item ) ? '%s' : ( is_float( $item ) ? '%f' : ( is_int( $item ) ? '%d' : '' ) );
    }, $array );
    return '(' . join( ',', $placeholders ) . ')';
}

Considering the example from the question, first you'd need to convert the values to an array:

$villes = array( "paris", "fes", "rabat" );   

$in_str = generate_wpdb_prepare_placeholders_from_array( $villes );
$sql    = "SELECT distinct telecopie FROM `comptage_fax` WHERE `ville` IN {$in_str}";
$query  = $wpdb->prepare( $sql, $villes );