6

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?

Martin
  • 149
  • 1
  • 2
  • 7
  • 2
    The best approach would be to fix your design so you don't have to put table names dynamically. – Kayaman Oct 06 '16 at 09:34
  • 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? – Mario Santini Oct 06 '16 at 09:36
  • 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? – Thilo Oct 06 '16 at 09:36
  • 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). – Thilo Oct 06 '16 at 09:38
  • How about the rest of the query? Column names and such? Don't you need those to be very dynamic as well? – Thilo Oct 06 '16 at 09:39
  • what is the target db? – Nicolas Filotto Oct 06 '16 at 09:50
  • 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 .... – Martin Oct 06 '16 at 11:22

3 Answers3

5

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Nicolas Filotto
  • 43,537
  • 11
  • 94
  • 122
  • 1
    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... – Martin Oct 14 '16 at 08:15
  • 1
    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 – Nicolas Filotto Oct 14 '16 at 08:30
  • 1
    @Bakus123 good question, I don't know much `SQLite` but as far as I can see from the [doc](https://www.sqlite.org/lang_keywords.html) 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) – Nicolas Filotto Jan 18 '17 at 17:41
  • @NicolasFilotto, thx but what about square brackets? In docs they write that it can be used quote identifiers also. – Bakus123 Jan 18 '17 at 18:10
  • @Bakus123 indeed it should work too but if I were you, I would use double-quotes as it seems to be more standard in SQLite – Nicolas Filotto Jan 18 '17 at 18:13
  • @NicolasFilotto, so it will be ok - `String.format("SELECT foo from \"%s\"", tableName.replace("\"", "\"\""));` ? – Bakus123 Jan 18 '17 at 18:23
2

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.

Rubasace
  • 939
  • 8
  • 18
  • 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. – Martin Oct 14 '16 at 08:25
1

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.

Serge Ballesta
  • 143,923
  • 11
  • 122
  • 252