Pdo update with variables inside the query

Question

Ok so i store the column i want to update in side a variable so i need to put the variable in side the main query i try and do it like so

$sqlltyryrt = "UPDATE user_items SET :fggdgdf = :fggdgdf +1 WHERE username=?";
    $qqqqq = $db->prepare($sqlltyryrt);
$qqqqq->execute(array('fggdgdf'=>$fggdgdf),$_SESSION['username']);

I have searched for an answer and have found a thread here on the site doing the same:

Using a passed variable in a query using PDO (Oct 2011; by Don S)

What is your question actually? Not that the query looks correct (AFAIK you can use the same placeholder name only once in a query and also it makes no sense to assign to a bound parameter), just asking about which part exactly you're puzzled as this is currently missing in your question. — hakre, Sep 01 '13 at 17:37
Well im puzzled with the SET I want to do SET $fggdgdf = $fggdgdf +1 but of course that would not work... — user2734610, Sep 01 '13 at 17:41
okay, that are not bound parameters, you might need to reference the column by it's name, I wonder if that is possible with bound parameters *and* the help of a function but you are probably easier by inserting the column-name as string in PHP so that you pass it along verbatim. That bears a bit the risk of injection (as any string concatenation), but if you're careful this should just work. — hakre, Sep 01 '13 at 17:44
andrewsi code works pretty good i just escape the string and make sure its safe be for i use it. — user2734610, Sep 01 '13 at 17:45
Just also found this one if you want to dig a bit into the materia: http://stackoverflow.com/questions/985842/mysql-concat-to-create-column-names — hakre, Sep 01 '13 at 17:47

score 1 · Accepted Answer · edited Sep 01 '13 at 17:45

$sqlltyryrt = "UPDATE user_items SET :fggdgdf = :fggdgdf +1 WHERE username=?";
$qqqqq = $db->prepare($sqlltyryrt);
$qqqqq->execute(array('fggdgdf'=>$fggdgdf),$_SESSION['username']);

You can't bind the names of columns; so that isn't going to work. There's no way to use a bound variable for a column or table name, so the only way to do this is to actually interpolate the variables into the string:

$sqlltyryrt = "UPDATE user_items SET $fggdgdf = $fggdgdf +1 WHERE username=?";
$qqqqq = $db->prepare($sqlltyryrt);
$qqqqq->execute(array($_SESSION['username']));

But you need to be very sure that you've sanitized the variables, else you're open to SQL injection. You can use whitelisting for this, as you should be able to generate an array of possible column names and can check that the variables are present in that array.

But the fact that you're trying to bind the names of comments implies that your database design could do with looking at.

Thank you your code works. I make sure the variable is safe before i used it.Thanks again. — user2734610, Sep 01 '13 at 17:43

Pdo update with variables inside the query

1 Answers1