2

Please read fully first

In this answer: How to prevent SQL injection with dynamic tablenames?

Pekka points out why this code:

$clas=$_POST['clas'];
$query="SELECT * FROM $clas ";

Cannot be repaired by using a PDO or mysql-real_escape_string().

Can anyone please provide sample code how to fix this so a newbie can paste that code
(after/adjusting it to his needs) and be safe from SQL-injection.

Please don't explain SQL-injection, I know all about injection and PDO, I just need sample code

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319

2 Answers2

5

You could use a whitelist to ensure that the value is indeed one of the tables you wish to be accessed in that way.

Example:

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

Note that constructing SQL queries directly from GET or POST parameters is usually a bad idea anyways, but a whitelist can make it safe.

Lotus Notes
  • 6,302
  • 7
  • 32
  • 47
  • 4
    I think Pekka, the accepted answerer in the other thread the OP links, actually suggests this (or rather suggests restricting to a list such as a SHOW TABLE query). So in that respect, this is stricter and virtually Pekka approved(tm) – horatio May 18 '11 at 21:29
  • I'd recommend adding backticks ` around the `$clas` just in case a user want to use a reserved word as table name. – Johan May 19 '11 at 19:08
-1

You can use the for escape :P 

$clas = str_replace('`','\\`',$_POST['clas']);
$query = "SELECT * FROM \`{$clas}\`";

So, is a ver very bad idea.

Do it different.

Exos
  • 3,958
  • 2
  • 22
  • 30
  • is safe. check: http://dev.mysql.com/doc/refman/5.0/en/identifiers.html, more explames?: http://pastebin.com/MEUU82nM .. please check before vote!!! – Exos May 19 '11 at 17:13