Asked  2 Years ago    Answers:  5   Viewed   131 times

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?

 Answers

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?

Friday, October 7, 2022
 
4

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database? They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.
mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.
PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.


* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

Tuesday, November 1, 2022
 
kennyc
 
2

No, you cannot bind identifiers, only values.

Identifiers (table names, field names, etc.) are not supposed to be user inputs in the first place. It is a very bad idea to handle them in such a way.

Saturday, November 26, 2022
 
avywam
 
1

I would try to solve the design problem, so you don't have to set the table name dynamically. If this is not possible, I would go for a design where you manage a list of available tables and users pick one from there, BY ID, so you can retrieve the real table name from the chosen id and replace the table name placeholder with it, avoiding any chance of sql injection in the table name replacement.

Tuesday, October 11, 2022
 
5

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Monday, October 10, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 

Browse Other Code Languages