A trap in PDOStatement::bindParam

First, let's check out below codes:

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', "test");
 
$query = <<prepare($query);
 
$bind_params = array(':username' => "laruence", ':password' => "weibo");
foreach( $bind_params as $key => $value ){
    $statement->bindParam($key, $value);
}
$statement->execute();

What is the SQL executed finally? Is there any problem with above codes?

Many people may think the query executed is :

INSERT INTO `user` (`username`, `password`) VALUES ("laruence", "weibo");

But the query actually gets executed is :

INSERT INTO `user` (`username`, `password`) VALUES ("weibo", "weibo");

Is it a big trap? This issue is drafted in a bug report : #63281.

The reason comes from the difference between bindParam and bindValue, the second parameter in bindParam should be a reference.

Let's take a look at the foreach statement above:

<?php
foreach( $bind_params as $key => $value ){
    $statement->bindParam($key, $value);
}

It's the same as:

<?php
//The first iteration
$value = $bind_params[":username"];
$statement->bindParam(":username", &$value); //Now username is the reference to the $value

//The second iteration
$value = $bind_params[":password"]; //oops! $value is overrided with value of password
$statement->bindParam(":password", &$value);

So we should pay attention to this trap when using bindParam together with foreach. What's the right way to achieve what we expected?

1. Don't use foreach, manually assign values instead

<?php
$statement->bindParam(":username", $bind_params[":username"]); 
$statement->bindParam(":password", $bind_params[":password"]);

2. Replace bindParam with bindValue, or directly pass the parameters in the execute method

3. Use foreach and reference(Don't recommend this method)

<?php
foreach( $bind_params as $key => &$value ) { //Note here
    $statement->bindParam($key, $value);
}

At last, you should pay attention to the use of foreach when need to pass reference to a function call.

Source : http://www.laruence.com/2012/10/16/2831.html