PDO with bindParam() returns syntax error

Question

I'm doing a PDO UPDATE query to MariaDB database.

$query = "UPDATE :table 
                        SET `name`=:name, 
                            `title`=:title,
                            `description`=:description 
                        WHERE id=:id";

            $stmt = $this->conn->prepare($query);

            $stmt->bindParam(':table',$this->table, PDO::PARAM_STR);
            $stmt->bindParam(':id',$this->id, PDO::PARAM_INT);
            $stmt->bindParam(':name',$this->name, PDO::PARAM_STR);
            $stmt->bindParam(':title',$this->title, PDO::PARAM_STR);
            $stmt->bindParam(':description',$this->description, PDO::PARAM_STR);

            $stmt->execute();

It seems to me that everything is looking good, but I do get an error:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''category' SET `name`='Snowboards', ' at line 1 in C:\xampp\htdocs\supershop\public\api\models\Category.php on line 109

If I do straightforward SQL query in phpMyAdmin, everything is fine:

UPDATE
    `category`
SET
    `name` = 'Snowboards',
    `title` = 'Title',
    `description` = 'Description'
WHERE
    `id` = 9

What am I doing wrong with those bindings?

Possible duplicate of [Can PHP PDO Statements accept the table or column name as parameter?](https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) — Will B., Jul 04 '19 at 08:23

score 3 · Accepted Answer · answered Jul 04 '19 at 08:18

You can't paramatise table names with PDO.

You will need to sanitize the table name and insert into the SQL string.

"UPDATE ".$this->table."
SET `name`=:name, 
`title`=:title,
`description`=:description 
WHERE id=:id";

You can see the quotes its placing around 'category'

score 0 · Answer 2 · answered Jul 04 '19 at 08:33

The error is triggered since table name cannot be used as a parameter. If you want to sanitise/filter table-name, you can do it manually.

One way for it is to maintain a whitelist of table-names as an array. i.e, Mapping acceptable table names to an array with keys that correspond to the potential user input.

e.g.

array('u'=>users', 't'=>'table', 'c'=>comments')

This way no unsanitized data will go directly into the query.

Courtesy:

PDO with bindParam() returns syntax error

2 Answers2