Safe way to use table name as parameter in JDBC qu

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?

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.

Answer 2

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.

Answer 3

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.