CODEWITHSUNDEEP
phpmysql
Andre
Andre

Reputation: 788

Safest way to use input for table name?

I was working on my website, and currently modifying some of my SQL code. I noticed that I was putting a direct user input into a code that finds a table.

For example:

$query = 'SELECT NAME, GENDER FROM `' . $last_name . '` ORDER BY NAME';

I know I shouldn't be using input from a user as the table name, but in my scenario I have no other option. Since I don't have another option, I would like to try and make it as secure as possible.

I'm aware that prepared statements wont work with table names, and they only work for column values, so I'm a little at loss. Should I just use mysql_real_escape_string to overcome this? I appreciate all answers!

tldr: What is the safest way to use user input as a table name?

Upvotes: 1

Views: 1507

Answers (3)

Kevin Delaney
Kevin Delaney

Reputation: 53

I don't like giving web programs access to my database schema. I would say the "safest" way to give users access to a subset of tables is to create a table that contains the public table names

CREATE TABLE Public_Tables (table_id Int, table_name Varchar2);

You could populate Public_Tables from the schema.

If you give each table a unique integer, then you would use the UIDs and reduce a chance of a SQL Injection script.

Of course, before taking this step, I would examine and normalize my table structure. I agree with answer 2. It seems to me that LastName should be a column in a table.

If I truly had to create a table for each user I would feel better creating tables with a UID than with a last name. The tables might be 'User_1', 'User_2' instead of "Smith", "O'Brien," "Delete *' etc.

Upvotes: 1

Fredrik
Fredrik

Reputation: 764

An interesting case. I would select all table names from the given database:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbName'

(from Get all table names of a particular database by SQL query?)

And use the results to create a whitelist of table names that your application accepts. Simple and secure.

Upvotes: 1

Andrew M
Andrew M

Reputation: 9

At first glance, I have to ask whether it is necessary to have a separate table for each LAST_NAME? It would seem more useful to have the LastName as a column, which would solve the problem since you could then use prepared statements.

Also, using a quoted table name usually makes it case-sensitive, so the user would also be having to get the Case correct or won't find the table.

However, assuming that redesigning the app to use LastName as a column is impossible - you could use a prepared statement to look for the table in the database metadata before actually running the query e.g. in Oracle (this is one way):

Select count(*) from USER_TABLES where TABLE_NAME = '?';

Then you can be certain that the table actually exists in advance, which makes you reasonably sure that it meets the database syntactic requirements, which in turn means it is unlikely to allow injection. But still not guaranteed, I think...

Upvotes: 0

Related Questions