Update PDO bind key and values

Question

so I recently switched to use pdo instead of mysqli. now I have a question about binding key values with mysqli. I looped through it escaped the key's and values and used them in my queries now I want to do the same thing in pdo but this isn't working and I don't know why this is my code:

foreach($userdata as $key => $value){
    $sql = $this->db->prepare("UPDATE `users` SET :key = :value WHERE `id` = :userid");
    $sql->execute(
        array(
            'key' => $key,
            'value' => $value,
            'userid' => $userid
        )
    );
}

ofcourse there's more code to see if it needs update and other type of inputs that need more validation but this is the main query i used but without binding. is this possible with pdo and binding parameter's and values? this is the error i'm getting:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'username' = 'sjerdus' WHERE `id` = '2''

Are you getting any errors? It doesn't look like you're doing any [error checking](http://php.net/manual/en/pdo.error-handling.php). — Jay Blanchard, Mar 16 '15 at 13:29
try using ':key' => $key etc... note the " : " not sure if it makes a difference but i do it always that way — Shaeldon, Mar 16 '15 at 13:31
Yes @JayBlanchard this si the error message SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''username' = 'sjerdus' WHERE `id` = '2'' — Sjoerd de Wit, Mar 16 '15 at 13:49
@Shaeldon The ":" is optional. You can put it or not, it's the same result. — Blackus, Mar 16 '15 at 13:54
You cannot alias columns (`:key`), you must state them just as you did with `id`. — Jay Blanchard, Mar 16 '15 at 13:56

score 4 · Accepted Answer · answered Mar 16 '15 at 14:00

4

You have this error because you tried to update a field named dynamically. The SET :key = ... can't work because when the parameter will be replaced by its value, it will be escaped (and quoted) by PDO.

If you want to put a variable field name that will be updated, you have to manually concatenate the field name, but you'll have to check for the security yourself.

Try something like this :

//Security checks for $field variable...

$sql = $this->db->prepare("UPDATE `users` SET " . $field . " = :value WHERE `id` = :userid");
$sql->execute(
    array(
        'value' => $value,
        'userid' => $userid
    )
);

answered Mar 16 '15 at 14:00

Blackus

6,883
5
40
51

so it is impossible to bind field names with pdo? that's a pity – Sjoerd de Wit Mar 16 '15 at 14:06
1

Nope because prepared statements are not only here to substitute parameters. Query is prepared server side to be executed more than once with different parameters, but the "core" of the query (tables and fields that are queried) doesn't change. – Blackus Mar 16 '15 at 14:12
isn't it a design flaw? seems more then likely to me that fields in a query could change especially if you don't wan't to write 100 if else statements – Sjoerd de Wit Mar 16 '15 at 14:17
Truth is, RDBMS do a lots of things to optimize queries. You should read [this answer](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter/15990488#15990488) by @IMSoP that is much more complete on why you can't have field or table name as bound parameters. – Blackus Mar 16 '15 at 14:28
Hey so how do i escape a string without mysql or mysqli since quote does the same sanitizing as bind it gives the same error. – Sjoerd de Wit Mar 16 '15 at 15:42
As your variable will contain a field name, you can easily compare it against a white list or something like this. – Blackus Mar 16 '15 at 16:04

Raheel · Answer 2 · 2015-03-16T13:37:49.677

Here is you could do. I assume that the $userid you have provided is an integer. Where as when you use params in execute() directly. They are considered as string.

foreach($userdata as $key => $value){
    $sql = $this->db->prepare("UPDATE `users` SET :key = :value WHERE `id` = :userid");
    $sql->bindParam(':key', $key);
    $sql->bindParam(':value', $value);
    $sql->bindParam(':userid', $userid);
    $sql->execute()
    );
}

http://php.net/manual/en/pdostatement.bindparam.php

Update PDO bind key and values

2 Answers2