safely escaping table names/column names

Question

I'm using PDO in php and as such can't escape table names or column names using prepared statements. Would the following be a foolproof way to implement it myself:

$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 still some avenue that this can be attacked?

@Petah I need to be able to use this class after I add more tables without having to constantly be updating a whitelist — qwertymk
– qwertymk, Commented Oct 15, 2012 at 0:08
Ask the DB for a list of valid column names and cache them if two queries per request is an issue. If you are allowing users to input column names, you might need to change your database design anyway. — Lusitanian
– Lusitanian, Commented Oct 15, 2012 at 1:45

goat · Accepted Answer · 2012-10-15 01:10:15Z

4

You can use a dynamic white list by asking the database what columns are valid for a given database table. It's an additional sql query, but safety is good.

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

Fetch the results of that and then just make sure all the dynamic column names are in the result set.

I believe views are included in INFORMATION_SCHEMA.COLUMNS, so it should all just plain work.

Then just use backticks around the validated column names when assembling the dynamic sql(I assume you use purely ascii column names, otherwise you potentially have additional considerations).

answered Oct 15, 2012 at 1:10

goat

31.9k7 gold badges76 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

qwertymk Over a year ago

This will cause there to be two db calls for every call. I'm looking for something that will sanitize the table and column name rather than a whitelist. I really want to know the limits of what I can let through. eg I know I can left through a-zA-Z0-9_. What about other chars?

goat Over a year ago

You're already making mistakes(period isnt allowed, see dev.mysql.com/doc/refman/5.0/en/identifiers.html) unless you really understand what you're doing, including a good understanding character sets and unicode, just use a whitelist and stop worrying about performance issues you don't have. Or, restrict table and col names to a length limited a-zA-Z0-9_ to make it easy to validate. Avoid unicode and non ascii characters.

qwertymk Over a year ago

Everyone keeps saying that but why? What could happen if I have something like this (waitforitwaitforit): "``" . preg_replace('#``|--#', '', $input) . "``" (two `` means one)

goat Over a year ago

Everyone keeps saying that because they know that some things too complicated to bother with. If you want, you can google multibyte character set sql injection to get an idea of how its accomplished, but it's not likely to make much sense unless you understand multibyte encodings. A lot of the exploits are only possible under certain specific conditions.

derekdreery Over a year ago

If you're fetching a list of columns, you probably want to use a key-value cache to speed up access to it (it won't change).

Collectives™ on Stack Overflow

safely escaping table names/column names

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related