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.

I can't get the dynamic SQL to execute. The above example is what I would ideally like to have as a proof of concept (or something similar with the proper syntax). My problem isn't so much the union, but that I need to be able to split up strings like `UTL_HTTP` or `UTL_FILE` into something like `'UTL_' || /*testing*/ 'HTTP'`. — Gray, Jan 05 '17 at 16:09
It still doesn't really make sense. `execute immediate` is a PL/SQL construct, and you're trying to use it in plain SQL. Even if the entire unioned statement is ultimately run dynamically, that happens in a SQL context, so it's still invalid. — Alex Poole, Jan 05 '17 at 16:11
@AlexPoole That could explain a lot. I am obviously out of my wheelhouse here, and was not aware of a distinction between PL/SQL context vs a "plain" SQL context. If that's the problem, then I am certainly willing to accept that as an answer. Is there anything comparable in plain SQL that would allow me to split up function names like that to dynamically execute SQL? — Gray, Jan 05 '17 at 16:13
It might be interesting to see if you if you could pass a PL/SQL block that does something inside which your filter doesn't catch, but you wouldn't see any results from it - without actually doing something destructive, you couldn't tell if it was just ignored, since you aren't getting errors back. I'm not really sure what you mean about splitting up function names. Maybe showing a valid statement and one you'd like to end up being run would help. — Alex Poole, Jan 05 '17 at 16:18
regarding splitting up function names, I gave an example in my first comment in this thread. I basically have the statement `SELECT from where =` that I can use. So, just as a proof of concept, I did `SELECT 1 FROM DUAL WHERE 1=1 UNION SELECT 2 FROM DUAL`. The entire union part is being injected in the last input block. I'd like to be able to use more *interesting* functions, such as UTL_FILE, but that word is blocked. If I could split it up using string concatenation (as seen in top comment), then the WAF would not detect it, and I could continue exploitation. — Gray, Jan 05 '17 at 16:25
I see. But yes, to split it up and concatenate it you would have to be executing it dynamically, and you wouldn't be able to do that within that query construct. (Unless it lets you run more than one statement, which I imagine is the first thing you try). You also can't call a procedure from plain SQL, and some functions aren't allowed either. — Alex Poole, Jan 05 '17 at 16:32
@Gray It is possible to execute dynamic SQL inside SQL, but it's very rare and it can't be done out of the box. It requires custom PL/SQL objects, like in my open source project [Method4](https://github.com/method5/method4). So while it's possible, someone would have to install something unusual and make it available to the application. — Jon Heller, Jan 05 '17 at 18:07

score 2 · Accepted Answer · answered Jan 05 '17 at 16:27

2

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.

answered Jan 05 '17 at 16:27

Alex Poole

183,384
11
179
318

This is extremely helpful to see. Thank you. I am open to options such as creating functions (made up of dynamic SQL). I just want to minimize the footprint/cleanup. If there are any alternatives to EXECUTE IMMEDIATE within plain SQL. Something like an "eval" command... that would be extremely helpful. Otherwise, is there a way to create an alias for a command dynamically? I know my question is a bit all over the place, but the truth is that I don't really know what to ask for. I need to be able to type words *like* `UTL_FILE` without sending it to the server altogether like that. – Gray Jan 05 '17 at 16:33
1

[12c lets you declare functions in a query](https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF55665), though I haven't tried that yet; but the CTE is defined before the `select`, so I don't think that helps you either. If all you can do is replace your `` values then I'm struggling to see any systematic way to do anything nasty. I'm not sure a union would even be that helpful, even if it wasn't somehow blocked, before 12c. (In 11g you can't use a CTE in a union branch anyway, not sure in 12c). – Alex Poole Jan 05 '17 at 16:43
Basically you can't do anything dynamically from SQL. I'm not sure `UTL_FILE` is being blocked as such, or it you're just trying to call a procedure from a query, or a function that isn't allowed - if you don't see the error it might be more basic than you think. Or, the user you're connected as has had its privileges stripped back so it can't even try to execute the packages you're trying. – Alex Poole Jan 05 '17 at 16:53
The actual words `UTL_FILE` and `UTL_HTTP` are being blocked because I can make a request to the root of the server with a fake parameter like `?waf=UTL_HTTP` and I get the WAF error. Same as if I do `OR 1=1;--` or other common SQLi strings. It could be that the functions themselves are blocked as well, but it's getting dropped at the WAF level, so I can't tell for sure. It's too bad you can't split function names with comments like UTL_/*TEST*/HTTP or something like that. Regardless, you've certainly gone above and beyond for the answer, so thank you. Let me know if any light bulbs go off! :) – Gray Jan 05 '17 at 17:19

UNION on dynamic SQL statements

1 Answers1