wood burning stoves 2.0*
The moose likes JDBC and the fly likes Is it possible to use a variable or param as the name of a table in a select statement? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Is it possible to use a variable or param as the name of a table in a select statement?" Watch "Is it possible to use a variable or param as the name of a table in a select statement?" New topic
Author

Is it possible to use a variable or param as the name of a table in a select statement?

Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

I am just wondering if something like this is possible - I know the following example does not work because I tried it, but is there a way to achieve something similar?


True wisdom is in knowing you know nothing - Socrates
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

The construct you're looking for is called "dynamic sql". You can use the execute immediate statement to execute an SQL which is a result of a VARCHAR2/CLOB expression; you can even use bind variables with it (look up that statement in Oracle's docs). More complicated things can be handled with DBMS_SQL package.

In your case the line would be
Be careful, though. By stuffing literal values into the SQL commands you can easily expose yourself to SQL injection even within PL/SQL.
Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

Martin

Yeah, SQL Injection is exactly what I am working on. I am removing all occurrences of what you mention. But for some parts of the code, say we have two tables:

us_table
canada_table

And the execute immediate is something like 'select id from '||region||'_table where 1=1'

I have just used if statements to deal with this (luckily there are only two possibilities where this occurs in our code), but I was just wanting to be sure I was using best practice.

Thanks
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

For similar things, you could use the DBMS_ASSERT package, that can help to validate a string is a valid identifier, for example. Or when you construct the table name, you could verify that it is an actual table name against USER_TABLES or ALL_TABLES dictionary views.
 
Consider Paul's rocket mass heater.
 
subject: Is it possible to use a variable or param as the name of a table in a select statement?
 
Similar Threads
Hibenate Mapping
What is the use of using EXECUTE IMMEDIATE in Oraccle PL/SQL
How to get a Table Schema from oracle database to Java Programs...?
JBoss and J2EE Security
problem in extracting values from ResultSet