• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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
Posts: 484
Java jQuery Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Martin Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 484
Java jQuery Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic