How to create a prepared statement for UPDATE query

1. Comments (1)

Sometimes we need to update not all fields but only certain ones. Say, we have an array with values we want to add to the query.

The task is not very complex, but one have to keep in mind the SQL injection against which prepared statements won't help. To protect from that, all field names should be checked against a pre-compiled list of allowed values. The rest is simple. So the algorithm would be:

1. Define an array with allowed values
2. Loop over the source array and create a SET statement for SQL dynamically, based on the allowed fields list
3. Respective values should be added to the designated array to be used in the execute()

So, imagine we have $_POST array with several fields. Here is the code to insert them into database safely:

// the list of allowed field names
$allowed = ["name","surname","email"];

// initialize an array with values:
$params = [];

// initialize a string with `fieldname` = :placeholder pairs
$setStr = "";

// loop over source data array
foreach ($allowed as $key)
{
    if (isset($_POST[$key]) && $key != "id")
    {
        $setStr .= "`$key` = :$key,";
        $params[$key] = $_POST[$key];
    }
}
$setStr = rtrim($setStr, ",");

$params['id'] = $_POST['id'];
$pdo->prepare("UPDATE users SET $setStr WHERE id = :id")->execute($params);

}