PHP PDO error with params

Question

I want that admins can update rights for users, because admins can also add more rights I need to get the row from a $_GET and put it in the query;

        if(isset($_GET['flip'], $_GET['userid'],$_GET['newval'])) {
            switch($_GET['newval']) {
               case 1:
                    $upd = $db->prepare("
                        UPDATE premissions
                        SET ? = '1'
                        WHERE userId = ?
                    ");
                    break;
               case 0:
               default:
                     $upd = $db->prepare("
                        UPDATE premissions
                        SET ? = '0'
                        WHERE userId = ?
                    ");
                    break;                       
            }


            $upd->execute(array($_GET['flip'], $_GET['userid']));
        }

But now I get this error message:

Fatal error: Uncaught exception 'PDOException' with 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 '? = '1' WHERE userId = ?' at line 2'

I understand that something is wrong with the syntax but I don`t know how to fix it.

KIKO Software · Accepted Answer · 2014-09-09T20:16:02.487

You cannot use ? to bind column names (or table names) with PDO. The ? is reseved for binding to variables in the right side of the assignment.

UPDATE premissions SET ? = '0'

Is therefore not possible. You could do something like this:

if(isset($_GET['flip'], $_GET['userid'],$_GET['newval'])) 
{
  switch($_GET['flip']) 
  {
     case 'column1': $column = 'column1';        break;
     case default  : $column = 'default_column'; break;
  }
  $upd = $db->prepare("UPDATE premissions
                       SET {$column} = ?
                       WHERE userId = ?");
  $upd->execute(array($_GET['newval'], 
                      $_GET['userid']));
}

What is important here is that the value of $column can never be something else than what is specified, namely either 'column1' or 'default_column', no matter what the input is. So you're not vulnerable to SQL injection. I would advice to check the other URL parameters as well, if only to prevent unexpected errors. You could for instance add:

is_numeric($_GET['userid'])

as a test before you accept the value of $_GET['userid'].

you should give OP the alternative possiblitly – meda Sep 09 '14 at 14:55 — meda, Sep 09 '14 at 14:55
@meda, you're right, I will, one moment please. – KIKO Software Sep 09 '14 at 15:12 — KIKO Software, Sep 09 '14 at 15:12

score 1 · Answer 2 · edited Sep 09 '14 at 21:46

You can only use the '?' for values not column or table names. What you can do in this case is create an array of columns for that table and then use the in_array() function to validate a column name then enter in the query directly Example:

$columnNames = array('id', 'username', 'name', 'address', 'zipcode');
if (!in_array($_GET['flip'], $columnNames))
{
    // Not a valid column don't bother preparing a statement
}
else
{
    $column = $_GET['flip'];
    $upd = $db->prepare("
                    UPDATE premissions
                    SET {$column} = '1'
                    WHERE userId = ?
}

PHP PDO error with params

2 Answers2