PDO prepare with named parameters generates a wrong query

Question

Possible Duplicate:
Can PHP PDO Statements accept the table name as parameter?

Im writing a script that allows users to register and login to my site. Im using PDO prepare and execute to prevent SQL injections.

Building the query manually like this DOES work:

$a_query = "SELECT COUNT(*) FROM ". $login_table . " 
WHERE `username` = ". $my_username . "  
AND `password = " . $my_hash ;
$result_1 = $db->prepare($a_query);
$result_1->execute();

But when I try to use prepare correctly like this, it does NOT:

$a_query = "SELECT COUNT(*) FROM :table 
WHERE `username` = :name
AND `password = :pass ;"
$result_1 = $db->prepare($a_query);
$result_1->bindParam(":table", $login_table);
$result_1->bindParam(":name", $my_username);
$result_1->bindParam(":pass", $my_hash);
$result_1->execute();

The error message I get from $result_1->errorInfo[2] reads:

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 ''customerlogin' WHERE `username` = 'guest' AND `password`
= 'qwerty' at line 1

As you can see, prepare() mysteriously slices off the first part of the query before sending it to mysql.

Can anyone explain to me why and how I can fix this?

Bound parameters can only represent values, not identifiers in SQL. And `:table` cannot be a string value. — mario, Jan 31 '13 at 12:37

score 0 · Accepted Answer · edited Feb 03 '13 at 03:48

As you can see, prepare() mysteriously slices off the first part of the query before sending it to mysql.

It does not. It's just the error message handler which actually does that, trying to display only the relevant part of the query. Usually such an error message means "look at the query right before this part". So - it points out at the :table in your query.

Your query does not work because prepared statements do not support identifiers.

PDO, as any other raw API, is insufficient for any real-life query. A developer should use database abstraction library methods in their application code, not raw API methods.

It will make life much easier and code shorter. For example, your whole code can be made into only 2 lines:

$sql  = "SELECT COUNT(*) FROM ?n WHERE `username` = ?s AND `password = ?s";
$data = $db->getOne($sql, $login_table, $my_username, $my_hash);

And it would be safer than your whole screen-long code using raw PDO.

PDO prepare with named parameters generates a wrong query

1 Answers1