mySQL bind_param with IN(?)

问题:

Using bind_param on all my queries, I now want to use an IN(?) where the number of elements in the list can vary.

The SQLout function I'm using here basically does a $sql_db->prepare, ->bind_param, ->execute(), ->store_result(), ->bind_result

// the code below does not work as the query only matches on element 'a':
$locations = ('a','b','c','d','e');

SQLout ("SELECT Name FROM Users WHERE Locations IN (?)",
    array('s', $locations), array(&$usrName));


// the code below does work as a brute-force method,
// but is not a viable solution as I can't anticipate the number of elements in $locations going forward:

SQLout ("SELECT Name FROM Users WHERE Locations IN (?,?,?,?,?)",
    array('sssss', $locations[0],$locations[1],$locations[2],$locations[3],$locations[4]), array(&$usrName));

Has anyone come up with a more elegant solution to this?

回答1:

This is one place placeholders fall on their faces. Minus the auto-escaping, they're almost literally just a string replacement operationt internally, meaning that if you have WHERE x IN (?), and pass in 1,2,3,4, you'll get the equivalent of

WHERE x IN ('1,2,3,4')  // note, it's a string, not individual comma-separated ints

logically equivalent to

WHERE x = '1,2,3,4' // again, just a string

isntead of the more expected

WHERE (x = 1) or (x = 2) or (x = 3) or (x = 4)

The only practical solution is to build your own list of ?, e.g:

$placeholders = implode(array_fill(0, count($values), '?'));
$sql = "SELECT ... WHERE x IN ($placeholders)";

and then bind your parameters are usual.

回答2:

As Hazmat said, you need to build up the parameters and then pass them by calling call_user_func_array on the prepared statement, but slightly closer to working code than his example :)

//In the calling code
$queryString = "SELECT Name FROM Users WHERE Locations IN (";
$queryString .= getWhereIn($locations);
$queryString .= " )";

$parametersArray = array();

foreach($locations as $location){
    $parameter = array();
    $parameter[0] = 's'; //It's a string
    $parameter[1] = $location;

    $parametersArray[] = $parameter;
}



//This is a function in a class that wraps around the class mysqli_statement
function    bindParameterArray($parameterArray){

    $typesString = '';
    $parameterValuesArray = array();

    foreach($parameterArray as $parameterAndType){
        $typesString .= $parameterAndType[0];
        $parameterValuesArray[] = $parameterAndType[1];
    }

    $finalParamArray = array($typesString);
    $finalParamArray = array_merge($finalParamArray, $parametersArray);
    call_user_func_array(array($this->statement, "bind_param"), $finalParamArray);
}

function getWhereIn($inArray){
    $string = "";
    $separator = "";
    for($x=0 ; $x<count($inArray) ; $x++){
        $string .= $separator."?";
        $separator = ", ";
    }
    return  $string;
}

回答3:

You can "build" in IN clause before you prepare/bind it.

$sql = 'SELECT Name FROM Users WHERE Locations IN (' . implode(array_fill(0, count($locations), '?')) . ')';

Then you can use call_user_func_array to bind the parameters, without ever knowing how many there are.

// Parameters for SQLOut
$params = array(
    # The SQL Query
    $sql,
    # The params for bind_param
    array(str_repeat('s', count($locations))),
    # The params for bind_result
    array(&$usrName)
);

// Add the locations into the parameter list
foreach($locations as &$loc){
    // not sure if this is needed, but bind_param
    // expects its parameters to be references
    $params[1][] = &$loc;
}

// Call your function
call_user_func_array('SQLout', $params);

_{Note: This is untested}

回答4:

IN is usually slow and not prepared statement friendly. The better solution is to build a table of the items that would be in the IN and use a JOIN to get the same effect.

回答5:

Has anyone come up with a more elegant solution to this?

Sure. I am.

Mysqli practically unusable with prepared statements, especially with such complex cases.
So, it's better to get rid of prepared statements and implement your own placeholders with support of all real life cases a developer can meet.

So, safeMysql has a solution you're looking for (and also solutions for dozen other headaches as well).

In your particular case it would be as easy as this single line of code

// the code works all right:
$locations=array('a','b','c','d','e');
$usrName=$db->getCol("SELECT Name FROM Users WHERE Locations IN (?a)",$locations);

unlike ugly codes you can get playing with some PHP and API functions (and still get disturbing warnings depends on the PHP version you're using at the moment) this code is neat and readable. This is important matter. You can tell what does this code do even after a year passed.