0

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?

Vlada Katlinskaya
  • 991
  • 1
  • 10
  • 26
  • 4
    1. The query would not execute at all and fail with a "number of placeholders doesn't match number of parameters" error. 2. You should simply build your query dynamically, only adding as many `A = :a` parts as you're actually going to execute. – deceze Nov 11 '14 at 18:56
  • @deceze I didn't get your idea clearly: you saying I should make separate prepared query for setting each column separately? Or I should prepare a (new) query each time I know which column shell be changed? – Vlada Katlinskaya Nov 11 '14 at 19:07
  • If you don't want to update C, just leave it out of the query? – Curtis Mattoon Nov 11 '14 at 19:12
  • @deceze I could fetch row, change needed values and only than - update? Is it any good approach? – Vlada Katlinskaya Nov 11 '14 at 19:26

1 Answers1

3

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');
}
deceze
  • 510,633
  • 85
  • 743
  • 889
  • This will definitely work. Is this approach is any better than doing a `query` each call? As I can understand in this case there is no advantage of using prepared statement. Moreover: what do you think about **Another approach** I stated in the end of my answer? There will be prepared statement advantage... – Vlada Katlinskaya Nov 11 '14 at 20:41
  • There are many ways to slice this bread. You could probably also do some shenanigans with `SET a = IFNULL(:a, a)` or some such. I'd prefer a perfectly tailored query though. And there's still a tremendous advantage to prepared statements here for passing in the values with the correct syntax and preventing SQL injection. – deceze Nov 11 '14 at 20:46