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:xampphtdocssupershoppublicapimodelsCategory.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?
2
Answers
You can’t paramatise table names with PDO.
You will need to sanitize the table name and insert into the SQL string.
You can see the quotes its placing around
'category'
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.
This way no unsanitized data will go directly into the query.
Courtesy: