PHP PDO bindParam was falling in a foreach

Question 1

I had a loop like that :

foreach($Fields as $Name => $Value){
    $Query->bindParam(':'.$Name, $Value, PDO::PARAM_STR);
}

Nothing complicated. However, each value was set to the last one in the array ($Fields).

How can I fix that ?

Question 2

However, thanks to this guys. I found out that you need to pass the value by reference with a & before like that :

foreach($Fields as $Name => &$Value){
    $Query->bindParam(':'.$Name, $Value, PDO::PARAM_STR);
}

This was driving me nuts.

Actual quote from PHP.net :

Vili 28-May-2010 12:01

This works ($val by reference):

<?php
foreach ($params as $key => &$val){
    $sth->bindParam($key, $val);
}
?>

This will fail ($val by value, because bindParam needs &$variable):

<?php
foreach ($params as $key => $val) {
    $sth->bindParam($key, $val);
}
?>

Question 3

PDO does not escape the variables. The variables and the SQL command are transferred independently over the MySQL connection. And the SQL tokenizer (parser) never looks at the values. Values are just copied verbatim into the database storage without the possibility of ever causing any harm. That's why there is no need to marshall the data with prepared statements.

Note that this is mostly a speed advantage. With mysql_real_escape_string() you first marshall your variables in PHP, then send an inefficient SQL command to the server, which has to costly segregate the actual SQL command from the values again. That's why it's often said that the security advantage is only implicit, not the primary reason for using PDO.

If you concat the SQL command and don't actually use prepared statments (not good!), then yes, there still is an escape function for PDO: $pdo->quote($string)

Question 4

Trying to bindParam to an array element like $array['key'] causes a few issues because its bound as reference, but its not. Its, just not done that way.

So three ways:

$stmt = $dbh->prepare($sql);
// bind to variables that can be a reference
$stmt->bindParam(":GROUP_ID", $id, PDO::PARAM_INT);
$stmt->bindParam(":INSTALLED_VERSION_NUM_1", $pt1, PDO::PARAM_INT);
$stmt->bindParam(":INSTALLED_VERSION_NUM_2", $pt2, PDO::PARAM_INT);
foreach ($installed_groups as $installed_group){
        $installed_version_parts = explode('.', $installed_group['version']);
        // assign the referenced vars their new value before execute
        $id = $installed_group['group_id'];
        $pt1 = $installed_version_parts[1];
        $pt2 = $installed_version_parts[2];
        $stmt->execute();
}

Or: (less efficient)

$stmt = $dbh->prepare($sql);
foreach ($installed_groups as $installed_group){
        $installed_version_parts = explode('.', $installed_group['version']);

        // use bindValue (not bindParam) INSIDE the loop
        // bindValue doesn't set them by reference, so any value expression works
        $stmt->bindValue(":GROUP_ID", $installed_group['group_id'], PDO::PARAM_INT);
        $stmt->bindValue(":INSTALLED_VERSION_NUM_1", $installed_version_parts[1], PDO::PARAM_INT);
        $stmt->bindValue(":INSTALLED_VERSION_NUM_2", $installed_version_parts[2], PDO::PARAM_INT);
        $stmt->execute();
}

Or:

$stmt = $dbh->prepare($sql);
foreach ($installed_groups as $installed_group){
        $installed_version_parts = explode('.', $installed_group['version']);

        // pass them on execute directly
        $stmt->execute(array(':GROUP_ID'=>$installed_group['group_id'],
                             ':INSTALLED_VERSION_NUM_1'=>$installed_version_parts[1],
                             ':INSTALLED_VERSION_NUM_2'=>$installed_version_parts[2]));
}

Question 5

Well, at second glance your question looks more complex to be answered with just one link

How does php pdo's prepared statements prevent sql injection?

How can prepared statements protect from SQL injection attacks?

What are other pros/cons of using PDO?

Most interesting question.
A greatest PDO disadvantage is: it is peddled and propagated a silver bullet, another idol to worship.
While without understanding it will do no good at all, like any other tool.
PDO has some key features like

Database abstraction. It's a myth, as it doesn't alter the SQL syntax itself. And you simply can't use mysql autoincremented ids with Postgre. Not to mention the fact that switching database drivers is not among frequent developer's decisions.
Placeholders support, implementing native prepared statements or emulating them. Good approach but very limited one. There are lack of necessary placeholder types, like identifier or SET placeholder.
a helper method to get all the records into array without writing a loop. Only one. When you need at least 4 to make your work sensible and less boring.

Does using PDO reduce efficiency?

Again, it is not PDO, but prepared statements that reduces efficiency. It depends on the network latency between the db server and your application but you may count it negligible for the most real world cases.

Question 6

use

$pdo->bindValue(':html', $html, PDO::PARAM_STR);

instead of

$pdo->bindParam(:html, $html);

PHP PDO bindParam was falling in a foreach

Answers

php,pdo