bindParam and bindValue loops in PHP

PHP has a great function for binding parameters to values in a PDO statement, to help you avoid injection attacks.

public PDOStatement::bindParam(
    string|int $param,
    mixed &$var,
    int $type = PDO::PARAM_STR,
    int $maxLength = 0,
    mixed $driverOptions = null
): bool

This function allows you to bind a parameter to a value on a PDO object, so you can execute a SQL statement without worrying about fruity variables being added to your statements. From the PHP docs:

$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam('calories', $calories, PDO::PARAM_INT);
/* Names can be prefixed with colons ":" too (optional) */
$sth->bindParam(':colour', $colour, PDO::PARAM_STR);
$sth->execute();

This code should be self-explanatory but lets break it down. Set our parameter variables:

$calories = 150;
$colour = 'red';

Next up, create our SQL statement:

$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');

Notice here that the :calories and :colour placeholders will be substituted for the variables we will shortly bind to them, when we execute the statement. The colons here indicate that the value is a placeholder.

Now we run the bindParam function and finally execute the statement:

$sth->bindParam('calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR);
$sth->execute();

Notice we omit the colon from the parameter name. The PDO::PARAM_INT and PDO::PARAM_STR set the variable's datatypes in the database. Here, as an integer (PDO::PARAM_INT) and a string (PDO::PARAM_STR).

Loops

You might be thinking of using a loop to bind your statement values to parameters. You can do that, but there is a caveat due to the way that bindParam() works.

In bindParam() The value of the parameter is not immediately assigned to the placeholder in the prepared statement. Instead, a reference to the variable is stored in memory and the value of the variable only replaces the placeholder when the statement is executed. Since the bound variable is a reference and since we used a forloop $value for the values - once the foreach loop finishes running, the final variable of the $value will be the final iteration on the foreach loop.

This has some uses — for example if the variable which you have bound to the parameter changes before the statement is executed, then the bound value will reflect whatever the variable has become.

This code wont work to bind all variables to placeholders:

//No good!
foreach ($statementValues as $key => $value) {
    $statement->bindParam($key, $value);
}

This code binds the last value in the $statementValues array to every value in your statement.

The way around this is to use the bindValue() function instead, it works in exactly the same way, but the values are assigned to the placeholders in the prepared statement.

//It's good!
foreach ($statementValues as $key => $value) {
    $statement->bindValue($key, $value);
}

Thomas - 11-07-2023