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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

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, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 11, 2013 at 6:16

AllTooSir

49.5k17 gold badges132 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jacob Over a year ago

Column names can be passed as parameters using PreparedStatement?

Stephen C Over a year ago

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

harsh · Accepted Answer · 2013-07-11 06:19:13Z

3

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

answered Jul 11, 2013 at 6:19

harsh

7,7023 gold badges33 silver badges33 bronze badges

Comments

Ashish Aggarwal · Accepted Answer · 2013-07-11 06:19:37Z

1

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

answered Jul 11, 2013 at 6:19

Ashish Aggarwal

3,0262 gold badges25 silver badges47 bronze badges

Collectives™ on Stack Overflow

oracle exception: invalid table name

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related