oracle exception: invalid table name

Question

I am using JDBC for connecting to the database (Oracle10) in Servlets.
Following is my query in which I want to set the three parameters dynamically.

Table name
Column name
Value

Query:

query = "select ? from ? where ? = ?";
mypstmt = con.prepareStatement(query);
mypstmt.setString(1, tableName);
mypstmt.setString(2, columnName);
mypstmt.setString(3, columnName2);
mypstmt.setString(4, value);

But above query is giving me error:

java.sql.SQLException: ORA-00903: invalid table name

I checked the table name. it is correct, and if I write the query like:

query = "select "+columnName+" from "+tableName+" where "+columnName2+" = ?";

Then it is executing fine.

So what should I do if I want to set the Table name and Column Names as mypstmt.setString(1,tableName)

Edit1 The reason why I want to parameterize the Table name and Column name is that I am allowing user to Select/Enter Table names and column names, so I want to avoid SQL Injection.

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

5

We can't pass the tablename directly to PreparedStatement as table name cannot be a bind variable . PreparedStatement.

An object that represents a precompiled SQL statement.

A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

You have to construct the sql with string concatenation. Using Stored Procedure , you can pass table name dynamically using Dynamic SQL. Even look at this SO answer to understand why it is restricted.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 11 '13 at 06:16

AllTooSir

48,828
16
130
164

Column names can be passed as parameters using `PreparedStatement`? – Jacob Jul 11 '13 at 06:19
1

@Polppan - no. Only values can be passed as parameters. (Otherwise, the SQL engine would not be able to precompile/plan prepared statements, and you'd have more scope for injection attacks, etc.) – Stephen C Jul 11 '13 at 06:20

score 3 · Answer 2 · answered Jul 11 '13 at 06:19

you can only parameterized ? column value in SQL query. Table/Column parametrizing is not possible, instead use variable to construct such query, for example :

void query(String tableName, String columnName, String queryColumnName String val) {
String query = "select "+columnName+" from "+tableName+" where "+queryColumnName+" = ?";
mypstmt.setString(1, val);
..
}

score 1 · Answer 3 · answered Jul 11 '13 at 06:19

1

In PreparedStatement you can only replace value. You cann't replace tablename, or column name.

answered Jul 11 '13 at 06:19

Ashish Aggarwal

3,018
2
23
46

oracle exception: invalid table name

3 Answers3