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?
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 . ")"
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()
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.
Here is my approach for sanitizing IN (...)
values for $wpdb
.
$wpdb->prepare()
to ensure that it's properly escaped.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 )
) );
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With