WordPress prepared statement with IN() condition

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?

Try this code:

// Create an array of the values to use in the list
$villes = array("paris", "fes", "rabat");    

// Generate the SQL statement.
// The number of %s items is based on the length of the $villes array
$sql = "
  SELECT DISTINCT telecopie
  FROM `comptage_fax`
  WHERE `ville` IN(".implode(', ', array_fill(0, count($villes), '%s')).")
";

// Call $wpdb->prepare passing the values of the array as separate arguments
$query = call_user_func_array(array($wpdb, 'prepare'), array_merge(array($sql), $villes));

echo $query;

implode()
array_fill()
call_user_func_array()
array_merge()

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

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.

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/

WordPress prepared statement with IN() condition

Related

Recent Posts