UNION on dynamic SQL statements

Question

What I am trying to accomplish is essentially this:

SELECT 1 FROM DUAL
UNION 
EXECUTE IMMEDIATE 'SELECT 2 FROM dual';

I am actually a penetration tester trying to bypass a web application firewall, so I am sure this looks silly/strange from the other side. Basically, I need to be able to do a UNION with dynamic queries in order to bypass a filter. So, in this example, you are passing a string from Java into an Oracle DB using this function.

I don't have any feedback from the database on what is wrong with my query, and could not find any documentation for someone doing something similar. I need a simple example where I UNION a normal query with a simple dynamic SQL string.

Answer 1

The execute immediate statement is only valid inside a PL/SQL block. You can't mix it with plain SQL. You can run (only) PL/SQL dynamically too, but again not mixing the two in one statement like you've tried.

If you run what you showed in a normal client you'd see it complains:

Error starting at line : 1 in command -
SELECT 1 FROM DUAL
UNION 
EXECUTE IMMEDIATE 'SELECT 2 FROM dual'
Error at Command Line : 3 Column : 1
Error report -
SQL Error: ORA-00928: missing SELECT keyword
00928. 00000 -  "missing SELECT keyword"
*Cause:    
*Action:

Even if the statement you pass is itself executed dynamically, you'd see the same error:

BEGIN
  EXECUTE IMMEDIATE q'[SELECT 1 FROM DUAL
UNION 
EXECUTE IMMEDIATE 'SELECT 2 FROM dual']';
END;
/

Error report -
ORA-00928: missing SELECT keyword
ORA-06512: at line 2
00928. 00000 -  "missing SELECT keyword"

A further consideration, though it's a bit moot here, is that a dynamic query isn't actually executed if you aren't consuming the results by selecting into a variable (see the note here.