Using Prepared Statements to set Table Name

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?

Solution 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 + "?]";

Solution 2:

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.

Solution 3:

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);
...