Safely escaping table names / column names

Question

I use PDO in php and therefore cannot escape table names or column names using prepared statements. Will this be an error-free way to implement it yourself:

$tn = str_replace('`', '', $_REQUEST['tn']); $column = str_replace('`', '', $_REQUEST['column']); $sql = "SELECT * FROM `tn ` WHERE `column` = 23"; print_r( $pdo->query($sql)->fetchAll() ); 

Or is there some other way that this can be attacked?

Answer 1

You can use the dynamic whitelist by querying the database which columns are valid for a given database table. This is an additional sql query, but security is good.

 select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = :databaseName and TABLE_NAME = :tableName 

Extract the results, and then just make sure all the dynamic column names are in the result set.

I believe that views are included in INFORMATION_SCHEMA.COLUMNS , so everything should work simply.

Then just use backlinks around the approved column names when building dynamic sql (I assume you only use ascii column names, otherwise you have additional considerations).