62

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?

Hardik Mishra
  • 14,779
  • 9
  • 61
  • 96
ForestSDMC
  • 775
  • 2
  • 7
  • 12

4 Answers4

88

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.

fmucar
  • 14,361
  • 2
  • 45
  • 50
  • 2
    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. :-) – ForestSDMC Jul 03 '12 at 15:14
  • 36
    Does this mean SQL injection protection is impossible with dynamic table names? – Richard Tingle Sep 04 '15 at 12:47
  • 1
    @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. – JulienD Nov 07 '16 at 16:45
  • 1
    @RichardTingle Not impossible, just more difficult. In MySQL, for example, you could enclose the table name in backticks, and escape backticks with double backticks: https://dev.mysql.com/doc/refman/5.7/en/identifiers.html – shmosel Feb 26 '18 at 19:40
  • 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. – fly2matrix Jun 12 '20 at 05:16
  • 2
    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. – ka3ak Aug 03 '20 at 07:50
  • I arrived here knowing the answer. I am currently looking for a reference I can link to so that secops folks may more easily accept this answer. – Meadowlark Bradsher Jan 08 '23 at 20:53
7

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);
manishpes
  • 329
  • 2
  • 10
  • 89
    Vulnerable to SQL injection. DO NOT USE THIS ANSWER! – SigmaX Apr 27 '16 at 15:58
  • 14
    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? – Toadfish May 10 '16 at 07:40
  • 16
    @SigmaX so what is your solution that is not vulnerable to SQL injection? – Mahdi Jun 28 '16 at 15:59
  • 7
    @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. – SigmaX Jun 28 '16 at 16:09
  • 4
    It completely depends where myTablename comes from. See Jonathan Warner's comment above – davidfrancis Oct 04 '16 at 11:33
  • @SigmaX I know its an SQL injection.Please give an answer if you have – Arun Nov 06 '17 at 16:54
  • 1
    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. – Alejandro Teixeira Muñoz Aug 23 '19 at 10:46
  • 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. – manikanta nvsr Jul 17 '20 at 18:12
  • It does not counter the purpose of preparedStatement. Notice that tablename needs to be replaced using string manipulation. But all the values can still be passed in via setParameter(), and this may happen in a loop so the PreparedStatement gets used more than once. – Queeg Apr 18 '23 at 13:12
1

One alternative could be String.format:

e.g.

String sql = String.format("INSERT INTO $1%s (col1, col2, col3, (etc)",  myTablename);
davidfrancis
  • 3,734
  • 2
  • 24
  • 21
  • 23
    This is the usual "oh I know what SQL injection is" paranoia - it completely depends where myTablename comes from. – davidfrancis Oct 04 '16 at 11:32
  • 9
    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. – SigmaX Oct 05 '16 at 16:41
  • 3
    True, a point which you could have made in your original comment rather than the capital letters 8=} – davidfrancis Oct 07 '16 at 13:24
-2

If your table name is coming from your own code ONLY...

...you would need to add it to the original string:

String tableName = "some_table_name";
// some other code
String strQuery = "INSERT INTO " + tableName + " (col1, col2, col3, col4, col5) VALUES (?,?,?,?,?,?);";

If the table name is coming any other unreliable source (user input, a parameter that other code is passing in), do not do this and see the other answers!

matt freake
  • 4,877
  • 4
  • 27
  • 56
  • 13
    Vulnerable to SQL injection. DO NOT USE THIS ANSWER, KIDS! – SigmaX Apr 27 '16 at 15:55
  • 9
    ...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. – matt freake Apr 27 '16 at 16:17
  • 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." – SigmaX Apr 27 '16 at 21:48