How to prevent SQL injection with dynamic table names?

Question

I had this discussion with a high reputation PHP guy:

PDO is useless here. as well as mysql_real_escape_string. extremely low quality.

This is of course cool, but I honestly don't know what is wrong with the suggestion to use mysql_real_escape_string or PDO to fix this code:

 <script type="text/javascript"> var layer; window.location.href = "example3.php?layer="+ layer; <?php //Make a MySQL connection $query = "SELECT Category, COUNT(BUSNAME) FROM ".$_GET['layer']." GROUP BY Category"; $result = mysql_query($query) or die(mysql_error()); 

In that

 $layer = mysql_real_escape_string($_GET['layer']); $query = "SELECT Category, COUNT(BUSNAME) FROM `".$layer."` GROUP BY Category"; 

given that the JavaScript code gets the client side.

Answer 1

Your advice is really wrong.

mysql_real_escape_string() will not work for dynamic table names; it is intended to exclude string data limited only by quotation marks. He will not escape the inverse symbol. This is a small but important difference.

So I could insert an SQL injection into this, I just had to use a closing backtick.

PDO does not provide sanitation for dynamic table names, either .

This is why it is good not to use dynamic table names or, if necessary, compare them with a list of valid values, for example, a list of tables from the SHOW TABLES command.

I also did not know about this and was probably to blame for repeating the same bad advice until it was pointed out to me here on SO, also by Colonel Shrapnel.

Answer 2

For the record, here is an example code for fixing this hole.

 $allowed_tables = array('table1', 'table2'); $clas = $_POST['clas']; if (in_array($clas, $allowed_tables)) { $query = "SELECT * FROM `$clas`"; } 

Answer 3

To answer how to really fix the code:

 '...FROM `' . str_replace('`', '``', $tableName) . '`...' 

This duplicates all the backlinks in the table name (this is done in MySQL).

One thing I'm not sure about is whether it is "encoding safe" (what is the correct name for this?). Usually it is recommended that mysql_real_escape_string be used instead of addslashes for mysql_real_escape_string , because the former takes into account the encoding of the MySQL connection. Perhaps this problem also applies here.