aspose file tools*
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
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: 3611
    
  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: 3611
    
  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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Is it possible to use a variable or param as the name of a table in a select statement?