PHP PDO selecting columns with single quotes in their name

Question

In phpmyadmin, I can run this through SQL:

SELECT * FROM mytable WHERE `Bob's Stuff` > 1

But, when I run the exact same query a php script:

$stmt->prepare("SELECT * FROM mytable WHERE `Bob's Stuff` > :int");
$stmt->bindValue(':int', $int);
$stmt->execute();

I get this error:

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 ':int AND Bob's Stuff >= '1')' at line 10' in script.php:208 Stack trace: #0 script.php(208): PDOStatement->execute() #1 {main} thrown in script.php on line 208

If I change the query column to any other column name, the query works fine.

Is it possible to have column names with apostrophes?

IMHO, having a column or a table named with an apostrophe in it is just wrong... — Kemal Fadillah

John Woo John Woo · Accepted Answer · 2012-09-08T09:57:22

Make that column name also as parameter,

$tableName = "`Bob\'s Stuff`";
$stmt->prepare("SELECT * FROM mytable WHERE :tableName > :int");
$stmt->bindValue(':tableName', $tableName);
$stmt->bindValue(':int', $int);
$stmt->execute();

By the way, one suggestion is next time don't create columns like Bob's Stuff. Make it single word like this BobStuff.

PHP PDO selecting columns with single quotes in their name

1 Answers