PHP MySQLi prepare/bind where-clause

Question

I try to use mysqli::prepare() for savety reasons, but if I try to prepare the where-clause with "?" mysqli::bind_params() fails (without any errors):

Call to the query function:

$messages = query('SELECT * FROM messages WHERE (receiverID=? AND receiverType=?) OR (senderID=? AND senderType=?) ORDER BY date DESC', false, '', (int)$_SESSION['ID'], (string)$_SESSION['mode'][0], (int)$_SESSION['ID'], (string)$_SESSION['mode'][0]);

Query function:

function query($sql, $fetch, $error, ...$values) {
    global $language; //get translations
    global $mysqli; //get database
    $returnResult = (strtolower(substr(trim($sql), 0, 6)) === 'select'); //check for select ---> retrun result
    if(!empty($error)) $error .= '\n'; //next line after error message (styling / readability)
    $stmt = $mysqli->prepare($sql) or back($error.$language['mysqliErrorPrepare'].$stmt->error); //prepare
    foreach($values as $value) { //go through all params
        $stmt->bind_param((is_string($value)?'s':(is_float($value)?'d':(is_int($value)?'i':'b'))), $value) or back($error.$language['mysqliErrorBind'].$stmt->error); //bind param
    }
    $stmt->execute() or back($error.$language['mysqliErrorExecute'].$stmt->error); //execute
    if($returnResult) $result = $stmt->get_result() or back($error.$language['mysqliErrorGetResult'].$mysqli->error); //get result if necessary
    $stmt->close(); //close
    if($returnResult AND $fetch) $result = $result->fetch_assoc()or back($error.$language['mysqliErrorFetch'].$mysqli->error); //fetch if required
    return ($returnResult?$result:true); //return result / true
}

Questions:

Why does it fail?
Can I prepare the where-clause?

Thank you! - Minding

[`mysqli_stmt::bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) is meant to be called 1 time with all the params -> `$stmt->bind_param('isis', $param1,$param2,$param3,$param4);` NOT in a loop 1 at time -> `$stmt->bind_param('i', $param1); $stmt->bind_param('s',$param2);...`. It looks like you are mixing up with [`PDOStatement::bindParam`](http://php.net/manual/en/pdostatement.bindparam.php) which is called for each parameter — Sean, Jun 06 '15 at 22:01
How can I get all my values($values) as single parameters in bind_params? — Minding, Jun 06 '15 at 22:12
look at http://stackoverflow.com/a/5108167/689579 You can use `call_user_func_array()` after creating a new array, with the 1st being the types, and the rest being the values. — Sean, Jun 06 '15 at 22:19
Thank you! It's working, but I don't get those "$$" and "&$$" things, could you explain those to me? And why is: 'array_unshift($paramValues, $paramTypes); call_user_func_array(array($stmt, 'bind_param'), $paramValues);'(my own try) not working? — Minding, Jun 07 '15 at 09:11

score 0 · Answer 1 · answered Jun 06 '15 at 21:45

Isn't it because it's not being called by any active database?

Try $db->query(); as below:

$messages = $db->query('SELECT * FROM messages WHERE (receiverID=? AND receiverType=?) OR (senderID=? AND senderType=?) ORDER BY date DESC', false, '', (int)$_SESSION['ID'], (string)$_SESSION['mode'][0], (int)$_SESSION['ID'], (string)$_SESSION['mode'][0]);

Minding · Accepted Answer · 2018-11-06T16:07:25.443

The reason this didn't work is that I tried to bind the parameter one-by-one, while mysqli::bind_param() expects to receive all parameters at once. Because of this MySQL only has one parameter, but 4 question marks to work with, which obviously results in an error.

The "right" way to bind multiple parameters dynamically would be this:

if(count($paramValues) > 0) {
    $paramTypes = '';
    $paramBindValues = [&$paramTypes];
    foreach(new RecursiveIteratorIterator(new RecursiveArrayIterator($paramValues), RecursiveIteratorIterator::LEAVES_ONLY) as $paramValue) {
        $paramTypes .= (is_string($paramValue)?'s':(is_float($paramValue)?'d':(is_int($paramValue)?'i':'b')));
        $paramBindValues[] = &$paramValue;
        unset($paramValue);
    }
    if(!$stmt->bind_param(...$paramBindValues))
        throw new Error($sql);
}

But please note, that this also defeats the reusablility of prepared statements and therefore propably has sub-optimal performance.

An example:

foreach($users as $user) {
   //This prepares the statement everytime.
   $info = query('SELECT info FROM users WHERE ID=?', true, '', $user['ID']);
}

//This only prepares the statement once.
$stmt = $mysqli->prepare('SELECT info FROM users WHERE ID=?');
foreach($users as $user) {
    $stmt->bind_params('i', $user['ID']);
    $stmt->execute();
    $info = $stmt->get_result()->fetch_assoc();
}
$stmt->close();

Thanks to @Sean for pointing out that mysqli::bind_params() takes in all parameters at once and providing an example.

Hope this helps. -Minding

@Deadooshka Haha, nah, I just stumbled over this question and thought it's only fair to post the answer I came to. — Minding, Nov 06 '18 at 17:00

PHP MySQLi prepare/bind where-clause

2 Answers2