A Pitfall of PDOStatement::bindParam中文EN

No more chit-chat, let's look at the code directly:

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

So, what is the SQL statement that ultimately gets executed, and is there any problem with the code above?
Okey, I think most of you would assume the final SQL is:

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

But unfortunately, you're wrong. The SQL that ultimately gets executed is:

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

A pretty nasty pitfall, isn't it?
------ If you want to find the reason yourself, then don't keep reading ---------
This problem comes from a bug report today: #63281
The root cause is the difference between bindParam and bindValue — bindParam requires its second argument to be a reference variable.
Let's unpack the foreach in the code above. That foreach:

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

is equivalent to:

<?php
// first iteration
$value = $bind_params[":username"];
$statement->bindParam(":username", &$value); // at this point, :username is a reference to the $value variable
// second iteration
$value = $bind_params[":password"]; // oops! $value has been overwritten with :password's value
$statement->bindParam(":password", &$value);

So, when using bindParam, be especially careful about this trap when combining it with foreach. Then what's the correct way?
1. Don't use foreach — assign manually

<?php
$statement->bindParam(":username", $bind_params[":username"]); // the argument is a reference now
$statement->bindParam(":password", $bind_params[":password"]);

2. Use bindValue instead of bindParam, or pass the whole parameter array directly to execute.
3. Use foreach with a reference (not recommended, see this Weibo for the reason)

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

Finally, to put it in broader terms: for any function that requires reference parameters and processes them lazily, be cautious when using it together with foreach!