Table name as parameter using PDO/MySQL prepared statement

Question

Is this possible? e.g.

SELECT * FROM :database WHERE id = :id

If not, should I just do this:

SELECT * FROM ' . $database . ' WHERE id = :id

Or is there some other trick I need to learn?

Answer 1

Table and Column names cannot be replaced by parameters in PDO. see Can PHP PDO Statements accept the table or column name as parameter?

Answer 2

It is quite dangerous to pass dynamically built table names in a query. But if it is so much needed by your application, you have to sanitize the data. Since PDO cannot help with this, you have to call mysql_real_escape_string on the table name yourself. Also you will have to enclose the table name with backticks as `table_name`. So prepare the query as:

'SELECT * FROM `' . mysql_real_escape_string($database) . '` WHERE id = :id

One note: mysql_real_escape_string needs an already established connection to the DB.

EDIT: But when I think about it, probably is best to match the $database variable against your existing tables.