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