CODEWITHSUNDEEP
javasqlprepared-statement
Brandon
Brandon

Reputation: 601

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?

Upvotes: 35

Views: 37119

Answers (7)

Datz
Datz

Reputation: 3841

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.

Upvotes: 5

Tanvir Singh
Tanvir Singh

Reputation: 21

This might help:

public ResultSet getSomething(String tableName) {

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

Upvotes: -1

mypetlion
mypetlion

Reputation: 2524

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.

Upvotes: 1

camickr
camickr

Reputation: 324128

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

Upvotes: 39

Damien Allison
Damien Allison

Reputation: 109

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

Upvotes: 0

Anoop
Anoop

Reputation: 1

String table="pass"; 

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

PreparedStatement ps=con.prepareStatement(st);

ResultSet rs = ps.executeQuery();

Upvotes: -3

Pierre
Pierre

Reputation: 35246

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 ?"

Upvotes: -3

Related Questions