Look! There is a trap with PDOStatement::bindParam!

Recommended: "PHP Video Tutorial"

Without further ado, let’s look at the code directly:

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

Excuse me, what is the final SQL statement executed above? Is there any problem with the code?

Okey, I think most students will think that the final SQL executed is:

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

But, unfortunately, you are wrong, the final SQL executed is :

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

Is it a big trap?

------If you want to find the reason yourself, then don’t continue reading------ ---

This problem comes from a bug report today: #63281

The reason is that the difference between bindParam and bindValue is that bindParam requires the second parameter to be a reference Variable (reference).

Let us disassemble the foreach of the above code, that is, this foreach:

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

is equivalent to:

<?php
//第一次循环
$value = $bind_params[":username"];
$statement->bindParam(":username", &$value); //此时, :username是对$value变量的引用
//第二次循环
$value = $bind_params[":password"]; //oops! $value被覆盖成了:password的值
$statement->bindParam(":password", &$value);

So, when using bindParam When using it, you should pay special attention to this trap when used in conjunction with foreach. So what is the correct approach?

1. Do not use foreach, but assign values ​​manually

<?php
$statement->bindParam(":username", $bind_params[":username"]); //$value是引用变量了
$statement->bindParam(":password", $bind_params[":password"]);

2. Use bindValue instead of bindParam, or Pass the entire parameter array directly in execute.

3. Use foreach and reference (not recommended, see: Weibo for the reason)

<?php
foreach( $bind_params as $key => &$value ) { //注意这里
    $statement->bindParam($key, $value);
}

Finally, expand on the requirement that parameters be references, And there are functions with lag processing, so be careful when using foreach!

Original address: https://www.laruence.com/2012/10/16/2831.html

The above is the detailed content of Look! There is a trap with PDOStatement::bindParam!. For more information, please follow other related articles on the PHP Chinese website!