Escape quotes in pdo->prepare

Question

I'm having trouble with pdo->prepare in mysql, heres the query:

$stmt = $this->pdo->prepare("SELECT * FROM :tabletype where name like :name");

Its parsing as

SELECT * FROM 'type1' where name like 'souza'

its ok with the name string but the tabletype its not working with quotes, is there a way to make it work without the quotes ?

Thanks

Parameterization is meant for values only, not for identifiers (table/column names). — mario, Aug 21 '14 at 18:06
In most databases you can't use placeholders for table names. You need to interpolate them and use identifier quoting. PDO, helpfully, doesn't *provide* a function for identifier quoting. — hobbs, Aug 21 '14 at 18:07
I honestly don't know why answers were given for this question; it should've been closed and marked as a duplicate. I've lost count on how many times I've seen these in the past 2 weeks. — Funk Forty Niner, Aug 21 '14 at 18:21

score 2 · Accepted Answer · answered Aug 21 '14 at 18:07

You can only use placeholders where expressions are allowed in the query. Since the tablename is not an expression you can't parametrize it. You have to concatenate the variable explicitly. So it should be:

$this->pdo->prepare("SELECT * FROM `$tabletype` WHERE name LIKE :name");

score 1 · Answer 2 · answered Aug 21 '14 at 18:09

Placeholders in prepared statements can only represent VALUES. They cannot be used to replace SQL keywords or table/field names. If you want to use a dynamic table name in your query, you'll have to insert it manually:

$tbl = 'foo';
$dbh->prepare("SELECT * FROM $tbl WHERE field = :placeholder");

And yes, this opens you up to SQL injection attacks. Placeholders are very handy, but they're not universally useful.

And note that if you're doing a LIKE comparison and want to use wildcards, you'll have more work ahead of you:

$dbh->prepare("SELECT * FROM tbl WHERE field LIKE CONCAT('%', :placeholder, '%')");

Even more so, if the pattern contains one of the two wildcards too: `%` and `_` ... — VMai, Aug 21 '14 at 18:39

score 0 · Answer 3 · answered Aug 21 '14 at 18:06

0

you can not bind table name and column name. use back tick(`) for identifiers like table name, column name etc.

try like this:

$stmt = $this->pdo->prepare("SELECT * FROM `type1` where `name` like :name");

answered Aug 21 '14 at 18:06

Awlad Liton

9,366
2
27
53

This won't allow him to vary the table name in the query, which is presumably why he tried to bind it. – Barmar Aug 21 '14 at 18:09
ooh. i did not thought that he wanted to vary table name. – Awlad Liton Aug 21 '14 at 18:11

Escape quotes in pdo->prepare

3 Answers3