php - Trouble binding an imploded array into a mysql prepared statement

Question

I am beating my head over the below syntax error. I am trying to bind an imploded array into a prepared statement, but I am getting the following syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

Here is my code. Can anyone see where I am going wrong?

<?php 
include('config.php');

$selected = $_POST['selected'];

if ($stmt = $mysqli->prepare("DELETE FROM email_addresses WHERE email_addresses IN ?")) {

    $stmt->bind_param("s", "('" . implode("', '", $selected) . "')" );

    $stmt->execute();

    $stmt->close();

    print "ok";

} else {
    print $mysqli->error;
}

$mysqli->close();

?>

As a test, I tried:

print "('" . implode("', '", $selected) . "')";

Which correctly gives me

('me@me.com', 'you@you.com')

Answer 1

Let me save you some trouble and tell you what you're trying to do won't work anyway. You are only binding one parameter to your IN() function call. You think you're passing a comma separated list but you are actually only passing a comma separated string which is treated as one value. This means you will be search for one record with a value of "'me@me.com', 'you@you.com'" instead of records that match "me@me.com" or "you@you.com".

To overcome this you need to:

1. Dynamically generate your types string
2. Use call_user_func_array() to bind your parameters

You can generate the types string like this:

$types = str_repeat('s', count($selected));

All this does is create a string of s's that is as many characters as the number of elements in the array.

You would then bind your parameters using call_user_func_array() like this (notice I put the parenthesis back in for the IN() function):

if ($stmt = $mysqli->prepare("DELETE FROM email_addresses WHERE email_addresses IN (?)")) {
    call_user_func_array(array($stmt, "bind_param"), array_merge($types, $selected));

But if you try this you will get an error about mysqli_stmt::bind_param() expecting parameter two to be passed by reference:

Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given

This is kind of annoying but easy enough to work around. To work around that you can use the following function:

function refValues($arr){ 
    $refs = array(); 
    foreach($arr as $key => $value) 
        $refs[$key] = &$arr[$key]; 
    return $refs; 
} 

It just creates an array of values that are references to the values in the $selected array. This is enough to make mysqli_stmt::bind_param() happy:

if ($stmt = $mysqli->prepare("DELETE FROM email_addresses WHERE email_addresses IN (?)")) {
    call_user_func_array(array($stmt, "bind_param"), array_merge($types, refValues($selected)));

Edit

As of PHP 5.6 you can now use the ... operator to make this even simpler:

$stmt->bind_param($types, ...$selected);