PHP's PDO: can I prepare a query which will keep unchanged the column if corresponding variable is unset?

Question

Let's say I have a prepared query:

$update_rq = $DB->prepare("
  UPDATE `table`
  SET
    `A` = :a,
    `B` = :b,
    `C` = :c
  WHERE `id` = :id
  LIMIT 1
");

If I will execute this query with the following code:

$update_rq->execute(['id' => $id,'a' => 1,'b' => 2]);

I think that this query will set C column with NULL value (if this column can have NULL value or it will throw an error if it can not).

Can I modify the prepare or execute statements to change this behavior to keep C column unchanged (SET C = C) if the corresponding variable is unset (equals to NULL)?

Another approach: Probably I could fetch row prior to update, change needed columns and than update with new values?

Answer 1

What you're doing won't work, you need to bind the same number of parameters as you have placeholders. PHP won't implicitly substitute a null, it'll simply fail. You can do something simple like:

$data         = array('a' => 1, 'b' => 2, 'c' => null);
$data         = array_filter($data);
$placeholders = array_map(function ($col) { return "`$col` = :$col"; }, array_keys($data));
$query        = sprintf('UPDATE `table` SET %s WHERE id = :id', join(', ', $placeholders));
$stmt         = $DB->prepare($query);

$stmt->execute($data + array('id' => $id));

This executes the statement with exactly as many placeholders and values as there are; any null values are entirely ignored. Do take care to whitelist your column names; meaning you should not accept arbitrary column names as user input, since the "`$col` = :$col" step opens you up to SQL injection. At least do something like:

$allowedCols = array('a', 'b', 'c');
if (array_diff_key($_POST, array_flip($allowedCols))) {
    throw new UnexpectedValueException('Received invalid data');
}