Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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')
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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);

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...