Cant create table with preparedStatement

Question

I can't create a table in the database (mySQL), using preparedStatement and try to enter name of future table with preparedStatement.setInteger():

static String queryCreateTable = "CREATE TABLE ?" +
                                 "(ID INTEGER not NULL ," +
                                 "BRAND VARCHAR(40)," +
                                 "MODEL VARCHAR(40)," +
                                 "YEAR INTEGER not NULL," +
                                 "NOVELTY BINARY," +
                                 "PRIMARY KEY ( ID ))";

And then I try to construct and call the statement after inputing name of table by user:

newNameOfTable = JOptionPane.showInputDialog("Connected for saving data. " +
                                "Input name of new table:");

                        pStatement = connection.prepareStatement(queryCreateTable);
                        pStatement.setString(1, newNameOfTable);
                        pStatement.executeUpdate();

It works well if I try to execute it without entering name (like a constant string: "CREATE TABLE newtable (...)" but I need to enter name..

SQL doesn't allow for the names of tables or columns to be enterred as parameters. — Gordon Linoff, Nov 09 '15 at 17:13
why dont you get the name of the table first and then create your query string? — AbtPst, Nov 09 '15 at 17:14
see http://stackoverflow.com/questions/1208442/using-prepared-statements-to-set-table-name — PaulF, Nov 09 '15 at 17:15
Also see answer here: http://stackoverflow.com/questions/12910891/can-i-create-a-mysql-table-using-a-pdo-parameterized-statement — JJF, Nov 09 '15 at 17:16

score 3 · Accepted Answer · answered Nov 09 '15 at 17:32

You will have to format the string after reading the table name, something like:

static String queryCreateTable = "CREATE TABLE {0}" +
                                 "(ID INTEGER not NULL ," +
                                 "BRAND VARCHAR(40)," +
                                 "MODEL VARCHAR(40)," +
                                 "YEAR INTEGER not NULL," +
                                 "NOVELTY BINARY," +
                                 "PRIMARY KEY ( ID ))";

then create like:

newNameOfTable = JOptionPane.showInputDialog("Connected for saving data. " +
                            "Input name of new table:");

statement = connection.createStatement();
statement.execute(MessageFormat.format(queryCreateTable, newNameOfTable));

score -2 · Answer 2 · answered Nov 10 '15 at 01:25

newNameOfTable = JOptionPane.showInputDialog("Connected for saving data. " +
                                "Input name of new table:");

static String queryCreateTable = "CREATE TABLE " + newNameOfTable +
                                 "(ID INTEGER not NULL ," +
                                 "BRAND VARCHAR(40)," +
                                 "MODEL VARCHAR(40)," +
                                 "YEAR INTEGER not NULL," +
                                 "NOVELTY BINARY," +
                                 "PRIMARY KEY ( ID ))";


pStatement = connection.prepareStatement(queryCreateTable);
pStatement.executeUpdate();

PreparedStatement example: http://tutorials.jenkov.com/jdbc/preparedstatement.html

This does not fix the problem OP is having. He is wanting to use a PreparedStatement to prevent a MySQL injection attack. By simply appending the name of the table as text to the command, an attacker could input something in a way that allows him to run a command he should not be allowed to run. E.G., querying for other users' data or God forbid dropping a table or deleting an etire database. — Whirvis, Jan 27 '20 at 17:28

Cant create table with preparedStatement

2 Answers2

Linked

Related