How to sanitise MySQL table names

Question

Would this simple table name sanitisation be enough to prevent SQL injections?

$table = str_replace('`', '', $table);
$table = '`'.$table.'`';

Note: I use PDO.

Curious: why would you want to do that, why not just do it straight in your query? — Funk Forty Niner, Apr 28 '15 at 19:47
@Fred-ii- I don't understand, what do you mean in your query? — Emanuil Rusev, Apr 28 '15 at 19:48
@JohnConde Thanks. I'd appreciate it if you elaborated on that. — Emanuil Rusev, Apr 28 '15 at 19:48
since this is mysql-related. `SELECT col from $table` and wrapping your $table in ticks `\`` - or you could *probably* use a constant but am not 100% sure about that. — Funk Forty Niner, Apr 28 '15 at 19:50
If you are allowing the user to select which table they query from, the only way to "sanitize" and verify no hacking would be to have a whitelist of allowed tables. Your method would fail to allow for other database schemas also. — Jonathan Kuhn, Apr 28 '15 at 19:51
@JonathanKuhn Could you give an example of an input that would pass the above sanitisation and still be harmful? — Emanuil Rusev, Apr 28 '15 at 19:54
it would be easier to define an accepted set of characters for the table names... — Karoly Horvath, Apr 28 '15 at 19:54
Because then you don't have to worry about how mysql interprets the special characters... — Karoly Horvath, Apr 28 '15 at 19:55
@KarolyHorvath Inside backticks, can't you use any characters except backticks? — Barmar, Apr 28 '15 at 19:57
I didn't realize you're asking about table name. sry about that. — Pedro Lobito, Apr 28 '15 at 19:57
@EmanuilRusev: it won't be safer than a safe solution. but again, you don't have to worry... — Karoly Horvath, Apr 28 '15 at 19:58
@KarolyHorvath, What if I restrict table names to alphanumeric characters (+ underscores)? Wouldn't this be perfectly safe? — Emanuil Rusev, Apr 28 '15 at 20:00
that's what I'm suggesting. it can be more restrictive than necessary, but creating exotic table names is just generally a bad idea, asking for trouble.. — Karoly Horvath, Apr 28 '15 at 20:00
I can confirm that using a constant works. Tested with both `mysqli_` and PDO. — Funk Forty Niner, Apr 28 '15 at 20:09
If you wish to have another answer submitted regarding my solution, let me know. You can accept Jonathan's but mine could also serve for someone else, maybe even you ;-) Otherwise, I will remain silent. *"A constant is an identifier (name) for a simple value. As the name suggests, that value cannot change during the execution of the script"*. — Funk Forty Niner, Apr 28 '15 at 20:16
to add to all the solutions above, you can add a prefix to the table name and this way the use can't just choose any table for the DB, he will be able to choose just tables from the tables you let him choose. for example : `SELECT * FROM table_prefix_name_".$user_table.";` — ron, May 05 '19 at 11:36

score 2 · Accepted Answer · answered Apr 28 '15 at 20:13

If you are allowing the user to select which table they query from, the only way to "sanitize" and verify no hacking would be to have a whitelist of allowed tables. Your method would fail to allow for other database schemas also. This could also be as simple as querying for a list of tables from a specific database.

I don't have a mysql instance to play with to try to find bad table names. I can say though that while I can't think (or really test and verify) a way to bypass this, I can say that I can think of several ways that the query would straight up fail. Trying to access other databases otherDB.tableName or selecting from multiple tables with joins. While this would likely cause an error, you still don't want to take the chance of someone finding something that will go through.

Hm... how would restricting the character set not provide adequate sanitation? alphanumeric plus _ and - would be just fine. — Peter Kionga-Kamau, Feb 25 '23 at 01:42
@PeterKionga-Kamau In reality, any user input should be considered bad and untrusted. It would most likely depend on the situation, however, one example might just be that you don't want the user to query for any table like a users table or information_schema. It all comes down to what you are accepting as user input and what you are allowing the user to submit. — Jonathan Kuhn, Mar 06 '23 at 04:39

score 1 · Answer 2 · answered Apr 28 '15 at 20:13

1

If you want to be super safe, and avoid errors you could query the INFORMATION_SCHEMA to check if the table exists. Then you will know the tables exists, and can catch an invalid table earlier. You can also limit to a particular schema.

split the input into DB and Table (if necessary)
Select table_name, table_schema from information_schema.tables where table_schema = 'db' and table_name = 'tableName' (note: you can use parametrized query here to prevent injection)
If you get a return, might as well use the returned values (table_schema.table_name), else not a valid table
Then perform your query, knowing that the table exists, and is valid

This extra query of checking information_schema, could cause some overhead, but maybe you could optimize for your specific need.

answered Apr 28 '15 at 20:13

Dan

10,614
5
24
35

*"note: you can use parametrized query here to prevent injection"* - Can you elaborate on that? I hope you don't mean something like [`SELECT * FROM :table`](http://stackoverflow.com/q/182287/) by this. – Funk Forty Niner Apr 28 '15 at 20:23
@Fred-ii- Since the user input values are in the WHERE clause you can use parametrized query. I'm assuming the catalyst for this question is that you can't use parameters in the FROM clause, so I was just pointing out that this way, the only time you use user input in a query, it is possible to use prepared statements to make it safe. – Dan Apr 28 '15 at 20:26
Not really, as the query that checks the existence of the table is still a query and vulnerable to injection... – Peter Kionga-Kamau Feb 25 '23 at 01:43
@PeterKionga-Kamau I said to use a parameterized query for that very reason. – Dan Mar 08 '23 at 19:18
@Dan But your code doesn't use parameters - it sounds like you are suggesting that they are optional ("you can use..."), it's risky and worth noting that it's risky. – Peter Kionga-Kamau Mar 09 '23 at 06:07

How to sanitise MySQL table names

2 Answers2