Using Prepared Statements to set Table Name

Question

I'm trying to use prepared statements to set a table name to select data from, but I keep getting an error when I execute the query.

The error and sample code is displayed below.

[Microsoft][ODBC Microsoft Access Driver] Parameter 'Pa_RaM000' specified where a table name is required.



private String query1 = "SELECT plantID, edrman, plant, vaxnode FROM [?]"; //?=date
public Execute(String reportDate){
    try {

        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection conn = DriverManager.getConnection(Display.DB_MERC);
        PreparedStatement st = conn.prepareStatement(query1);
        st.setString(1, reportDate);
        ResultSet rs = st.executeQuery();

Any thoughts on what might be causing this?

Answer 1

A table name can't be used as a parameter. It must be hard coded. So you can do something like:

private String query1 = "SELECT plantID, edrman, plant, vaxnode FROM [" + reportDate + "?]";

Answer 2

If you need a solution which is not vulnerable to SQL injection, you have to duplicate the query for all tables you need:

final static String QUERIES = {
    "SELECT x FROM Table1 x WHERE a=:a AND b=:b AND ...",
    "SELECT x FROM Table2 x WHERE a=:a AND b=:b AND ...",
    "SELECT x FROM Table3 x WHERE a=:a AND b=:b AND ...",
    ...
};

And yes: the queries are duplicates and only the table name differs.

Now you simply select the query that fits your table, e.g. like

...
PreparedStatement st = conn.prepareStatement(QUERIES[index]);
...

You can use this approach wich JPA, Hibernate, whatever...

If you want a more verbose approach consider using an enum like

enum AQuery {
    Table1("SELECT x FROM Table1 x WHERE a=:a AND b=:b AND ..."),
    Table2("SELECT x FROM Table2 x WHERE a=:a AND b=:b AND ..."),
    Table3("SELECT x FROM Table3 x WHERE a=:a AND b=:b AND ..."),
    ...

    private final String query;
    AQuery(final String query) {
        this.query = query;
    }

    public String getQuery() {
        return query;
    }
}

Now use the either an index

String sql = AQuery.values()[index].getQuery();
PreparedStatement st = conn.prepareStatement(sql);
...

Or use a table name

String sql = AQuery.valueOf("Table1").getQuery();
PreparedStatement st = conn.prepareStatement(sql);
...

Answer 3

This is technically possible with a workaround, but very bad practice.

String sql = "IF ? = 99\n";
sql += "SELECT * FROM first_table\n";
sql += "ELSE\n";
sql += "SELECT * FROM second_table";
PreparedStatement ps = con.prepareStatement(sql);

And then when you want to select from first_table you set the parameter with

ps.setInt(1, 99);

Or if not, you set it to something else.

Answer 4

As a number of people have said, you can't use a statement parameter for a table name, only for variables as part of the condition.

Based on the fact you have a variable table name with (at least) two table names, perhaps it would be best to create a method which takes the entity you are storing and returns a prepared statement.

PreparedStatement p = createStatement(table);

Answer 5

You can't set table name in prepared statement

As said before, it is not possible to set the table name in a prepared statement with preparedStatement.setString(1, tableName). And it is also not possible to add parts of the SQL query to a prepared statement (eg preparedStatement.addSql(" or xyz is null")).

How to do it right without risking SQL injections?

The table name must be inserted into the SQL (or JQL) query you want to execute with string operations like "select * from " + tableName or String.format("select * from %s", tableName)

But how to avoid SQL injections?

If the table name does not come from user input, you are probably safe. For example, if you make a decision like here

String tableName;
if(condition) {
    tableName = "animal";
} else {
    tableName = "plant";
}
final String sqlQuery = "delete from " + tableName;
...

If the table name depends on the users input, you need to check the input manually. For example, with a white-list containing all valid table names:

if(!tableNamesWhitelist.contains(tableName)) {
    throw new IllegalArgumentException(tableName + " is not a valid table name");
}
String sqlQuery = "delete from " + tableName;

or with an enum:

public enum Table {
    ANIMAL("animal"),
    PLANT("plant");

    private sqlTableName;
    private TableName(String sqlTableName) {
        this.sqlTableName= sqlTableName;
    }
    public getSqlTableName() {
        return sqlTableName;
    }
}

and then convert the user-input string like ANIMAL into Table.ANIMAL. An exception is thrown, if no fitting enumeration value does exist.

eg

@DeleteMapping("/{table}")
public String deleteByEnum(@PathVariable("table") Table table) {
    final String sqlQuery = "delete from " + table.getSqlTableName();
    ...
}

Of course these examples work with select, update, ... too and a lot of other implementations to check the user input are possible.

Answer 6

This might help:

public ResultSet getSomething(String tableName) {

PreparedStatement ps = conn.prepareStatement("select * from \`"+tableName+"\`");
ResultSet rs = ps.executeQuery();
}

Answer 7

I'm not sure you can use a PreparedStatement to specify the name of the table, just the value of some fields. Anyway, you could try the same query but, without the brackets:

"SELECT plantID, edrman, plant, vaxnode FROM ?"

Answer 8

String table="pass"; 

String st="select * from " + table + " ";

PreparedStatement ps=con.prepareStatement(st);

ResultSet rs = ps.executeQuery();