Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?

user_3086 0
Viewed 109163

I'm using this code and I'm beyond frustration:

try {
    $dbh = new PDO('mysql:dbname=' . DB . ';host=' . HOST, USER, PASS);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
}
catch(PDOException $e)
{
    ...
}
$stmt = $dbh->prepare('INSERT INTO table(v1, v2, ...) VALUES(:v1, :v2, ...)');
$stmt->bindParam(':v1', PDO::PARAM_NULL); // --> Here's the problem

PDO::PARAM_NULL, null, '', all of them fail and throw this error:

Fatal error: Cannot pass parameter 2 by reference in /opt/...

sql-insert pdo null mysql php
10 Answers

Several answers have given examples of what you should do. But they haven't really explained why you should do one of those things.

The bindParam method is meant to be used with something like a loop (or just repeated statements). It binds a variable reference. So something like

$stmt = $dbh->prepare('INSERT INTO t1 (v1) VALUES(:v1)');
$stmt->bindParam(':v1', $i, PDO::PARAM_INT);
for ($i = 0; $i < 10; $i++) {
    $stmt->execute();
}

Would insert values 0 through 9 in a table.

That's obviously a very simple example that could be implemented in other, more efficient ways. You could have more complex logic here. But the basic idea is that you bind a reference to a variable and then you can change the value of the variable.

You can get around the need for a reference by creating a variable before calling bindParam. But in your case, you don't particularly want to bind to a variable reference. You just want to bind a value. So go ahead and do exactly that with bindValue.

You can mostly just use bindValue. But to show why both methods exist, let's rewrite the previous example to use bindValue instead of bindParam:

$stmt = $dbh->prepare('INSERT INTO t1 (v1) VALUES(:v1)');
for ($i = 0; $i < 10; $i++) {
    $stmt->bindValue(':v1', $i, PDO::PARAM_INT);
    $stmt->execute();
}

This will work, but you have to call bindValue on every iteration of the loop whereas you only needed to call bindParam once. But you aren't doing anything like that, so you can just

$stmt->bindValue(':v1', null, PDO::PARAM_INT);

And everything will work, as stated in the accepted answer. Because you want to bind a value, not a variable reference.

user_66606780

Based on the other answers but with a little more clarity on how to actually use this solution.

If for example you have an empty string for a time value but you want to save it as a null:

  if($endtime == ""){
    $db->bind(":endtime",$endtime=NULL,PDO::PARAM_STR);
  }else{
    $db->bind("endtime",$endtime);
  }

Notice that for time values you would use PARAM_STR, as times are stored as strings.

user_13804790

In my case I am using:

  • SQLite,

  • prepared statements with placeholders to handle unknown number of fields,

  • AJAX request sent by user where everything is a string and there is no such thing like NULL value and

  • I desperately need to insert NULLs as that does not violates foreign key constrains (acceptable value).

Suppose, now user sends with post: $_POST[field1] with value value1 which can be the empty string "" or "null" or "NULL".

First I make the statement:

$stmt = $this->dbh->prepare("INSERT INTO $table ({$sColumns}) VALUES ({$sValues})");

where {$sColumns} is sth like field1, field2, ... and {$sValues} are my placeholders ?, ?, ....

Then, I collect my $_POST data related with the column names in an array $values and replace with NULLs:

  for($i = 0; $i < \count($values); $i++)
     if((\strtolower($values[$i]) == 'null') || ($values[$i] == ''))
        $values[$i] = null;

Now, I can execute:

$stmt->execute($values);

and among other bypass foreign key constrains.

If on the other hand, an empty string does makes more sense then you have to check if that field is part of a foreign key or not (more complicated).

user_16097910
Related