mysqli bind_param() expected to be a reference, value given

Question

Can't figure out, whats causing error Parameter 3 to mysqli_stmt::bind_param() expected to be a reference, value given in...

PDO
$query = "INSERT INTO test (id,row1,row2,row3) VALUES (?,?,?,?)";
$params = array(1,"2","3","4");
$param_type = "isss";
$sql_stmt = mysqli_prepare ($mysqli, $query);
call_user_func_array('mysqli_stmt_bind_param', array_merge(array($sql_stmt, $param_type), $params));
mysqli_stmt_execute($sql_stmt);

Also tried OOP

OOP
$insert_stmt = $mysqli->prepare($query);
array_unshift($params, $param_type);
call_user_func_array(array($insert_stmt, 'bind_param'), $params);
$insert_stmt->execute();

But same error, only that now Parameter 2 is causing problem.

So, what's wrong with $params? I need $params to be an array of values.

Solution 1

UPDATE

This answer is outdated. Please use the spread operator in newer PHP versions like answered by Stacky.

From php docu:

Care must be taken when using mysqli_stmt_bind_param() in conjunction with call_user_func_array(). Note that mysqli_stmt_bind_param() requires parameters to be passed by reference, whereas call_user_func_array() can accept as a parameter a list of variables that can represent references or values.

And on the page mysqli-stmt.bind-param you have different solutions:

For example:

call_user_func_array(array($stmt, 'bind_param'), refValues($params));

function refValues($arr){
    if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
    {
        $refs = array();
        foreach($arr as $key => $value)
            $refs[$key] = &$arr[$key];
        return $refs;
    }
    return $arr;
}

Solution 2

Introduced in PHP 5.6, you can use the ... operator ("spread operator") to achieve the same result with less trouble:

//object-oriented
$sql_stmt->bind_param($param_type, ...$params);

//procedural
mysqli_stmt_bind_param($sql_stmt, $param_type, ...$params);

Solution 3

When using mysqli in PHP8.1 or higher, you can pass an array of values directly to execute() which makes for a clean and concise code.

$query = "INSERT INTO test (id,row1,row2,row3) VALUES (?,?,?,?)";
$params = [1, "2", "3", "4"];
$stmt = $mysqli->prepare($query);
$stmt->execute($params);

You can also use PDO, which offers an even more concise syntax.

Comments

[Marcel Korpel]

Why do you use call_user_func_array?

[Your Common Sense]

@MarcelKorpel because mysqli can't be used without

[Marcel Korpel]

@YourCommonSense: Why not? In my code, I used mysqli_stmt->bind_param directly.

[Your Common Sense]

@MarcelKorpel it's just too manual and prevents any abstraction.

[Marcel Korpel]

@YourCommonSense: That still doesn't explain why call_user_func_array is needed.

[Your Common Sense]

@MarcelKorpel to make abstraction possible. To be able to bind whatever array to whatever query, just passing them as arguments to some helper function, instead of writing all these numerous bind_params right in the application code, making it bloated.

[Your Common Sense]

@MarcelKorpel or even without any helpers at all - but just in case of $params array of arbitrary size, which happens pretty often.

[bitWorking]

With execute all params are treated as string. Else you'd need PDO's bindParam or bindValue which doesn't make a big difference. Although I also prefer PDO over mysqli.

[Your Common Sense]

It's not a much problem, you know. Though, if you want strict type-casting and concise code with conventional SQL, you can use SafeMysql with it's brilliant idea of type-hinted placeholders

[bitWorking]

Your "brilliant" class doesn't work with real prepared statements.

[NullPoiиteя]

@redreggae i found that class as good as prepared statements ... and yes idea of Your Common Sense is perfectly fine and safe for mysql but in case of php5.5 it wont be useful just because that doesn't support mysql_* function

[bitWorking]

@NullPointer the class works with mysqli_* so it's useful after php5.5. But the point is with prepared statements you get a speed advantage if you run the same query multiple times. I would not say the class is insecure but to me it feels better to let the dbms do the escaping stuff.

[Your Common Sense]

@redreggae in a typical web-application if you run the same query multiple times, it rather smells of bad design. Not to mention that this speed advantage is a bit too exaggerated. PDO does the same extrapolation by default and nobody actually complained yet.

[bitWorking]

@YourCommonSense yes this is why I set PDO::ATTR_EMULATE_PREPARES to false. So tell me what advantage there is to NOT use prepared statements? You could rewrite your class to work the same way and use prepared statements.

[Your Common Sense]

@redreggae they are cut off with old Occam's razor :)

[bitWorking]

@YourCommonSense To me it seems simpler to let the dbms do the escaping and good to have the speed advantage if I need it. Hanlon's razor, my friend.

[Your Common Sense]

@redreggae it is not actually simpler. You actually need more code to handle. Also, natives won't let you to parse a part of query, which makes conditional query building quite a painful task.

[bitWorking]

@YourCommonSense your class has many good ideas. The downside is the ?a and ?u placeholders handle every param as string and the lack of blob type.

[Your Common Sense]

@redreggae I've never met a case when I would need any of them. Both ?a and ?u works fine for any types. And blobs go fine as strings. What I am missing?

[Asa Carter]

This is no longer working for me since upgrading to php 7.1

[Mindaugas Li]

Asa Carter, did you find a solution for PHP 7? Experience the same problem with PHP 7

[bitWorking]

@MindaugasLi You can try to use it with Reflection: http://php.net/manual/de/mysqli-stmt.bind-param.php#104073

[Eborbob]

Using the spread operator (...) as mentioned in another answer is a much cleaner solution.

[Eborbob]

A much cleaner approach than the ones that use call_user_func_array function.

[Kellen Stuart]

Is it a coincidence I see the same code in the file I was editing? I think not

[Tony]

This is solution for ALL. Simple and work everywhere

[TheRealChx101]

What is param_type? What goes there?

[Stacky]

@TheRealChx101 The variables in this answer are derived from the original question above. So in this case, $param_type is "isss" and $params is array(1,"2","3","4").