How to use a tablename variable for a java prepared statement insert

Question

I am using a java PreparedStatment object to construct a series of batched INSERT queries. The query statement is of the format...

String strQuery = "INSERT INTO ? (col1, col2, col3, col4, col5) VALUES (?,?,?,?,?,?);";

...so both field values and the tablename are variables (ie. I have multiple tables with the same column format of which each insert will be directed to a different one). I can get the executes to work if I remove the "?" tablename variable and hard code but each prepared statement will be inserted into a different table so needs to remain a variable I populate immediately prior to executing the batch query using...

stmt.setString(1, "tableName1");

How can I let this be a dynamic variable please?

Solution 1

You can't. You need to contruct the sql with string concatenation/placeholder with String.format. prepared statement is for the column values not for table name.

Solution 2

You can use placeholder in place of table name and then replacing that with your tablename.

String strQuery = "INSERT INTO $tableName (col1, col2, col3, col4, col5)
                   VALUES (?,?,?,?,?,?);";

and replace when u come to know the tablename

String query =strQuery.replace("$tableName",tableName);
stmt =conn.prepareStatement(query);

Solution 3

One alternative could be String.format:

e.g.

String sql = String.format("INSERT INTO $1%s (col1, col2, col3, (etc)",  myTablename);

Comments

[ForestSDMC]

Thanks folks...it seems as I do not know the tablename I want to insert into until the point of swapping in the variables for each row, the best thing is to construct the insert within a DB stored procedure. Then pass all the parameters for each row into the stored proc then let the DB handle the tablename manipulation. Thanks anyway folks for the responses though. :-)

[Richard Tingle]

Does this mean SQL injection protection is impossible with dynamic table names?

[SigmaX]

Vulnerable to SQL injection. DO NOT USE THIS ANSWER, KIDS!

[SigmaX]

Vulnerable to SQL injection. DO NOT USE THIS ANSWER, KIDS!

[matt freake]

...well that depends where your tableName is coming from. If it's coming from a user or from an unreliable client, then I'd agree with you. But if it's contained within your own method, then the immutability of String means it's safe surely.

[SigmaX]

Technically true. But a good answer still needs a big fat disclaimer: "as a rule, avoid using dynamic SQL queries, period—it's widely considered unsafe practice. If you have to do this, make sure you know exactly what you're doing."

[Toadfish]

only if $tablename is taken from user input though, right? But it'd be fine if something like a radio button selection was returning an enumerator value, or any other method that restricts the possible values of $tablename to a defined set?

[Mahdi]

@SigmaX so what is your solution that is not vulnerable to SQL injection?

[SigmaX]

@Mahdi The first goal should be to avoid using dynamic SQL queries at all. But if I had to, I'd retrieve the list of existing tables from the server and use it as a whitelist to check that tableName is the name of a valid existing table.

[davidfrancis]

This is the usual "oh I know what SQL injection is" paranoia - it completely depends where myTablename comes from.

[davidfrancis]

It completely depends where myTablename comes from. See Jonathan Warner's comment above

[SigmaX]

Agreed. But, like with most security issues, a large fraction of the visitors to Q&A sites won't understand that they need to be attentive to where myTablename comes from. So the answer needs to be qualified.

[davidfrancis]

True, a point which you could have made in your original comment rather than the capital letters 8=}

[JulienD]

@Richard I came to the same conclusion and it sounds silly, but at least it is rather easy to check the table name against the list of available tables in the database.

[Arun]

@SigmaX I know its an SQL injection.Please give an answer if you have

[shmosel]

@RichardTingle Not impossible, just more difficult. In MySQL, for example, you could enclose the table name in backticks, and escape backticks with double backticks: dev.mysql.com/doc/refman/5.7/en/identifiers.html

[Alejandro Teixeira Muñoz]

This solution is "SQL Injection" per se...... so ... As it's clear that for example, in my case, the table name will be calculated internally on compiled code.... no sql injection from the user interface can be produced. Just a developer with compiling rights is able to do it.

[fly2matrix]

Use a hashmap with dynamic tablename as key and a dynamic sql-string to prepare - Prepared statement for the first time and then store it. Next time just retrieve that prepared statement based on table name from the hashmap. Idea of prepared statement is to reduce sql-compilation each time and bind data at runtime.

[manikanta nvsr]

The purpose of preparedStatement is "compile once and execute for all". Since this answer is creating preparedStatment for every tableName, it counters the purpose of preparedStatement.

[ka3ak]

Everyone speaks about SQL injection. But I can hardly imagine that users might be prompted to enter a table name. If you run the same query on multiple tables the table names as parameters are only created and passed inside the application code.