How to correctly insert the name of the table from a variable in the SQL query? PHP / PDO

Let's start with the fallacies.

• mysql_real_escape_string. many generations of php coders believed and believe that this function serves to protect against certain injections. From this point of view, its use, of course, is justified. But if one day to discover the real purpose of this function, it becomes clear that it is needed here as an android hipster. You can even see a working example of SQL injection , which this, like any other function of string searching, cannot interfere in the least.
• PDO :: quote (). Already much better. Unlike the previous version, which will pass unnoticed, and then skip the injection, quote () will cause an error immediately, because they have not yet learned how to select data from the database row - they need the name of the table.
  Some time ago, in the comments to this function, there was a comment in the manual, and with a bunch of positive ratings, it was suggested that the quotes added to it should be torn off from the obtained value. I advised, instead, to tear off the hands of the proposer, but agreed that only his comment would be torn off. In the end, I wrote my own, with explanations of how to do it right.

So, how to add the name of a table or field to a query?

Only through the white list .

Those. after filtering through the list pre-written in the code

There may be many implementation options, one of which was suggested by the author in the comments - read all table names from the database and remember them. This, in principle, can work. Some frameworks even cache this information (for example, Yii). In the case of advanced ORMs, the field-table names are taken from the properties of the corresponding class, again, manually entered.

But if we get bogged down in the old-fashioned way, fulfilling requests directly through the PDO, then the easiest way is to register the available options right before use, especially since there should not be many such cases - otherwise we have obvious design problems.

Plus you need to remember to designate the name of the field or table in accordance with the syntax of the database. For example, for mysql this will be the conclusion of the value in the backtick.

As a result, we get something like this:

 if (!in_array($table, ["user","product","catalog"])); { throw new \Exception("Invalid table name!"); } $sql = "SELECT * FROM `$table` WHERE foo = ?" // далее как обычно 

Much more frequent is the situation when we need to execute an UPDATE query dynamically if the field names come, say, from $ _POST. The principle remains the same:

 / список допустимых значений $allowed = ["name","surname","email"]; // инициализация массива для значений $params = []; // инициализация строки с парами `fieldname` = :placeholder $setStr = ""; // цикл по разрешенным полям foreach ($allowed as $key) { if (isset($_POST[$key]) && $key != "id") { $setStr .= " `$key` = ?,"; $params[] = $_POST[$key]; } } $setStr = rtrim($setStr, ","); $params[] = $_POST['id']; $pdo->prepare("UPDATE users SET $setStr WHERE id = ?")->execute($params);