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.

@YourCommonSense: Why not? In my code, I used mysqli_stmt->bind_param directly. — Marcel Korpel, Apr 20 '13 at 13:25
@MarcelKorpel it's just too manual and prevents any abstraction. — Your Common Sense, Apr 20 '13 at 13:27
@YourCommonSense: That still doesn't explain why `call_user_func_array` is needed. — Marcel Korpel, Apr 20 '13 at 13:29
@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, Apr 20 '13 at 13:31
@MarcelKorpel or even without any helpers at all - but just in case of `$params` array of *arbitrary* size, which happens pretty often. — Your Common Sense, Apr 23 '13 at 07:47

bitWorking · Accepted Answer · 2017-11-16T14:23:31.247

55

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

edited Nov 16 '17 at 14:23

answered Apr 20 '13 at 13:30

bitWorking

12,485
1
32
38

2

This is no longer working for me since upgrading to php 7.1 – Asa Carter Feb 24 '17 at 23:15
Asa Carter, did you find a solution for PHP 7? Experience the same problem with PHP 7 – Mindaugas Li Aug 30 '17 at 12:17
@MindaugasLi You can try to use it with Reflection: [http://php.net/manual/de/mysqli-stmt.bind-param.php#104073](http://php.net/manual/de/mysqli-stmt.bind-param.php#104073) – bitWorking Sep 13 '17 at 10:19
1

Using the spread operator (...) as mentioned in another answer is a much cleaner solution. – Eborbob Nov 13 '17 at 16:50
Is it a coincidence I see the same code in the file I was editing? I think not – Kellen Stuart Dec 13 '17 at 02:42

Stacky · Answer 2 · 2018-05-11T07:59:50.000

37

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

edited May 11 '18 at 07:59

answered Oct 13 '17 at 07:34

Stacky

875
9
24

2

A much cleaner approach than the ones that use `call_user_func_array` function. – Eborbob Nov 13 '17 at 16:50
This is solution for ALL. Simple and work everywhere – Tony Nov 30 '20 at 11:22
What is ```param_type```? What goes there? – TheRealChx101 Dec 15 '21 at 22:53
@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")`. – Stacky Jan 28 '22 at 09:43

score 2 · Answer 3 · edited Apr 03 '22 at 08:19

2

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.

edited Apr 03 '22 at 08:19

mickmackusa

43,625
12
83
136

answered Apr 20 '13 at 13:25

Your Common Sense

156,878
40
214
345

1

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. – bitWorking Apr 20 '13 at 13:36
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](https://github.com/colshrapnel/safemysql) with it's brilliant idea of type-hinted placeholders – Your Common Sense Apr 20 '13 at 13:46
3

Your "brilliant" class doesn't work with real prepared statements. – bitWorking Apr 20 '13 at 14:08
@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 – NullPoiиteя Apr 20 '13 at 16:52
@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. – bitWorking Apr 20 '13 at 20:08
@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. – Your Common Sense Apr 20 '13 at 20:27
@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. – bitWorking Apr 20 '13 at 20:43
@redreggae they are cut off with old Occam's razor :) – Your Common Sense Apr 20 '13 at 20:49
1

@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. – bitWorking Apr 20 '13 at 21:04
@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. – Your Common Sense Apr 20 '13 at 21:15
@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. – bitWorking Apr 20 '13 at 21:22
@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? – Your Common Sense Apr 21 '13 at 07:02

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

3 Answers3

Linked

Related