SQL works from PMA but not Java

Question

I have a prepared statement like so:

CREATE TABLE IF NOT EXISTS ? (uuid VARCHAR(128), item VARCHAR(48), value FLOAT, UNIQUE (uuid))

If I execute this directly in PMA, but replacing the ? with any text text, it works perfectly and it creates the table correctly. However, if I run it from Java it doesn't work.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Murder_PlayerData' (uuid VARCHAR(128), item VARCHAR(48), value FLOAT, UNIQUE (u' at line 1

Here's the Java code

    String table = "Murder_PlayerData";
    String execute = "CREATE TABLE IF NOT EXISTS ? (uuid VARCHAR(128), item VARCHAR(48), value FLOAT(11), UNIQUE (uuid))"; 

    PreparedStatement statement = sql.getConnection().prepareStatement(execute);
    statement.setString(1, table);
    statement.execute();

Why does it work in PMA but not when I do it from Java?

If you're trying to use this as a prepared statement, it's not working because prepared statement values *are always literals*... that means the generated SQL is `CREATE TABLE IF NOT EXISTS 'Murder_PlayerData'` not `CREATE TABLE IF NOT EXISTS Murder_PlayerData` (note the quotes). — user253751, Feb 17 '15 at 02:59
Please post the relevant java code, at the very least the statement(s) you're using to (try to) execute this sql instruction — Barranka, Feb 17 '15 at 03:01
@immibis I edited in the Java part now! The string I use `statement.setString(int, String)` doesn't have quotation marks — Vapid, Feb 17 '15 at 03:38
you can't use the table name as parameter, see http://stackoverflow.com/questions/1208442/using-prepared-statements-to-set-table-name — Astra Bear, Feb 17 '15 at 03:49
@VapidLinus yes, your string doesn't have quote marks - but do you understand the purpose of parameterized statements? If you had to add quote marks to the parameter yourself, it would be pretty useless. — user253751, Feb 17 '15 at 04:48

score 1 · Accepted Answer · answered Feb 17 '15 at 15:49

Prepared statements can't be used to define table names, but to define values related to columns (insert values, values for where or having conditions, etcetera). One way to understand it is: Prepared Statements are for DML operations, not for DDL operations.

If you want to build a table on runtime, you need to build the SQL statement "by hand":

String table = "Murder_PlayerData"
String strSQL = "create table if not exists " + table + "("
                 // Add your column definitions
              + ")"
Statement stmt = conn.createStatement();
stmt.execute(strSQL);

Notice that, if the variable table can be filled with data provided by the user, your code will be vulnerable to SQL Injection Attacks. I recommend you don't create tables if their names must be provided by users, but rather create the tables without any user interaction and then insert the values, using some kind of key to identify which records belong to each user.

I didn't realize prepared statements didn't work for table names! Thank you, this should fix it. The table name is provided in my own code, so I shouldn't have to worry about SQL Injection. Thank you! — Vapid, Feb 18 '15 at 01:30

SQL works from PMA but not Java

1 Answers1