Safe way to use table name as parameter in JDBC query

Question

What is the safe way how to put table name as parameter into SQL query? You cannot put table name as parameter using PreparedStatement. Concatenating string to execute query with dynamic table name using Statement is possible, however it is not recommended because of risk of SQL injection. What is the best approach to do this?

Pavilion

The best approach would be to fix your design so you don't have to put table names dynamically. – Nonsuch

The reason because is not safe is that you lost the control of what you're going to execute on your database. Even you perform strong checks you have a weak design and this will cost you in term of performance, stability and security. So why you want to do it? – Armoury

If there is only a set of tables acceptable, a lookup table would work (input has to be one of the options contained there). If any table is acceptable, I have to ask why? Are you developing something like mySQLAdmin? – Godbey

I suppose if you limit the input to only have letters, numbers, underscore, you should be safe (as far as injection goes, still weird that someone should have access to any table). – Godbey

How about the rest of the query? Column names and such? Don't you need those to be very dynamic as well? – Godbey

what is the target db? – Krystin

Target DB is SQL Server. My database contains tables that need to be exported. New tables may be created in future. So I am reading list of tables from config file and processing them one by one. When new table is created by admin, it must by added on the list in config file. Columns are not problem, because i read them using SELECT * FROM .... – Pavilion

Answer 1

I would try to solve the design problem, so you don't have to set the table name dynamically. If this is not possible, I would go for a design where you manage a list of available tables and users pick one from there, BY ID, so you can retrieve the real table name from the chosen id and replace the table name placeholder with it, avoiding any chance of sql injection in the table name replacement.

Mudlark

I agree that this is the design problem. It is a backgroung running application. Values are not picked by normal users. They are configured by administrator. I like the idea to configure ID and TABLENAME value pairs. And to check if the real table exists before execution. – Pavilion

Answer 2

The best way would be:

1. To put your table name between the characters used to delimit the name of the table which change from one database to another
2. And escape the provided table name accordingly such that SQL injection won't be possible anymore.

So for example in case of MySQL, the table name's delimiter is the backquote character and we escape it by simply doubling it.

If your query is SELECT foo from bar, you could rewrite your query as next:

 String query = String.format("SELECT foo from `%s`", tableName.replace("`", "``"));

This way you inject the name of your table without taking the risk of seeing some malicious code being injected.

Krystin

What if tableName value would be: "table'; GRANT ......; PRINT '......" For this to work, tablename variable should also be checked if it contains other chars than alphanumeric... – Pavilion

No this cannot work, you will then have SELECT foo from "table'; GRANT ......; PRINT '......", it will use what you have between back quotes as table name – Krystin

@Bakus123 good question, I don't know much SQLite but as far as I can see from the doc you can either use double quotes or grave accents (for compatibility with MySQL) to quote identifiers so it should work with both using the same idea (doubling it to escape it) – Krystin

@NicolasFilotto, thx but what about square brackets? In docs they write that it can be used quote identifiers also. – Projection

@Projection indeed it should work too but if I were you, I would use double-quotes as it seems to be more standard in SQLite – Krystin

@NicolasFilotto, so it will be ok - String.format("SELECT foo from \"%s\"", tableName.replace("\"", "\"\"")); ? – Projection

Can you tell me what delimiter to be used if we're using SQL server? – Larena

@Bakus123..Can you tell me what to do for SQL server? – Larena

Try to replace simple quotes with 2 simple quotes https://mcmap.net/q/45330/-how-do-i-escape-a-single-quote-in-sql-server – Krystin

Answer 3

There is a rationale behind allowing only actual parameters in dynamic JDBC queries: the parameters can come from the outside and could take any value, whereas the table and column names are static.

There can be use cases for parameterizing a table or a column name, mainly when different tables have almost same structure and due to the DRY principle you do not want to repeat several times the same query only changing the table (or column) name. But in that use case, the programmer has full control on the names that will substituted, and should carefully test that there is no typo in any of them => there is no possibility of SQL injection here, and it is safe to replace the table name in the query string.

That is quite different for a web application exposed on internet where a query will use what has been entered in a form field, because here anything could occur, including a semicolumn to terminate the original harmless query and forge a new harmfull one => SQL injection if you just concatenate strings instead of correctly building a parameterized query.

I cannot imagine a use case where the table name or a column name could be a string typed in a form field by a user, which would be the only reason to allow to parameterize them.

Northington