Can't bind string containing @ char with mysqli_stmt_bind_param

Question

I have a problem with my database class. I have a method that takes one prepared statement and any number of parameters, binds them to the statement, executes the statement and formats the result into a multidimentional array. Everthing works fine until I try to include an email adress in one of the parameters. The email contains an @ character and that one seems to break everything. When I supply with parameters:

$types = "ss" and $parameters = array("[email protected]", "testtest")

I get the error:

Warning: Parameter 3 to mysqli_stmt_bind_param() expected to be a reference, value given in ...db/Database.class.php on line 63

Here is the method:

private function bindAndExecutePreparedStatement(&$statement, $parameters, $types) {
    if(!empty($parameters)) {
        call_user_func_array('mysqli_stmt_bind_param', array_merge(array($statement, $types), &$parameters));
        /*foreach($parameters as $key => $value) {
            mysqli_stmt_bind_param($statement, 's', $value);
        }*/
    }

    $result = array();

    $statement->execute() or debugLog("Database error: ".$statement->error);

    $rows = array();

    if($this->stmt_bind_assoc($statement, $row)) {
        while($statement->fetch()) {
            $copied_row = array();
            foreach($row as $key => $value) {
                if($value !== null && mb_substr($value, 0, 1, "UTF-8") == NESTED) { // If value has a nested result inside
                    $value = mb_substr($value, 1, mb_strlen($value, "UTF-8") - 1, "UTF-8");
                    $value = $this->parse_nested_result_value($value);
                }
                $copied_row[$ke<y] = $value;
            }
            $rows[] = $copied_row;
        }
    }

    // Generate result
    $result['rows'] = $rows;
    $result['insert_id'] = $statement->insert_id;
    $result['affected_rows'] = $statement->affected_rows;
    $result['error'] = $statement->error;

    return $result;
}

I have gotten one suggestion that:

the array_merge is casting parameter to string in the merge change it to &$parameters so it remains a reference

So I tried that (3rd line of the method), but it did not do any difference.

How should I do? Is there a better way to do this without call_user_func_array?

---

update: I found a less pretty way of solving this. What I did was to create a new reference array with a loop and use that one instead.

private function bindAndExecutePreparedStatement($statement, $parameters, $types)
{
    if(!empty($parameters)) {
        $parameters_references = array();
        foreach($parameters as $key => $parameter) {
            $parameters_references[] = &$parameters[$key]; 
        }
        call_user_func_array('mysqli_stmt_bind_param', 
            array_merge(array($statement, $types), $parameters_references)); 
    } 
    .... 

I'm still interested in solving this with PDO, it seems l like it has a lot of other functionality as well. I just haven't learned to use it yet.

Answer 1

I wrote the parameter-binding code in Zend Framework's MySQLi adapter. I find the parameter-binding API for MySQLi to be hard to use. It's not the array that needs to be a reference, it's each element of the array.

You can see the code I wrote in the _execute() method in this class: http://framework.zend.com/svn/framework/standard/trunk/library/Zend/Db/Statement/Mysqli.php

I recommend that you check out PDO. It's far more easy to use. You can bind parameters, but it's even easier to just pass the array of parameter values as an array to the PDOStatement's execute() method.