PLSQL and Oracle Application Server

PL/SQL and Oracle Application Server

PL/SQL procedures can be executed over the Web via Oracle Application Server. In fact, it's one of the more common application environments used for Oracle-based web applications. When using a PL/SQL-based web application, essentially the web server is working simply as a proxy server. It receives requests from clients and passes these to the backend database server for execution. The results are passed back to the web server, which then passes it on to the client.

For example, assume there's a bookstore that uses PL/SQL for its e-Commerce site. The store might create several packages, one for browsing for books and another for purchasing. Assume the package that allows book browsing is called BROWSE and it exports a number of procedures such as SEARCH_BY_AUTHOR, SEARCH_BY_TITLE, and so on. To search for books by a given author, users of the web application would request in their web browser the following URL:

 http://www.books.example.com/pls/bookstore/browse.search_by_author?p_author=Dickens 

Let's break this down:

www.books.example.com is the web site. The /pls indicates that this is a request for a PL/SQL application. A handler is defined for this in the apache configuration files. /bookstore is the DAD or Database Access Descriptor. This DAD points to a location of a configuration file that contains details of how the web server is to connect to the database server. This information includes things like the username and password with which the web server will authenticate. /browse is the name of the package and search_by_author is the name of the procedure. Note that if the web user happened to know the name of the schema in which the browse package resides, let's say SCOTT, he or she could request /pls/bookstore/SCOTT.BROWSE.SEARCH_BY_AUTHOR.

When the client requests this, the web server sends this request to the database server. The database server executes the SEARCH_BY_AUTHOR procedure passing Dickens as an argument. This procedure queries a table of books and sends the results back to the web server. The web server duly responds to the client.

Oracle provides a PL/SQL Toolkit for use with web applications. This Toolkit contains packages such as HTP, which contains procedures for producing HTML text, and HTF, which contains functions for creating HTML text. There is also a group of packages that begin with OWA, such as OWA_COOKIE and OWA_UTIL. OWA_UTIL contains a number of interesting procedures such as CELLSPRINT. This takes as an argument a SQL select query and the results are returned to the client.

In older versions of Oracle Application Server it was possible to execute this procedure:

 http://www.books.example.com/pls/bookstore/SYS.OWA_UTIL.CELLSPRINT?P_THEQUERY=select+1+from+dual 

Here begins an interesting tale. Needless to say, allowing people to run queries over the Web against your backend database server is not a good thing, so Oracle fixed this. It did so by introducing a PlsqlExclusionList. If a request came in for anything in the list it would be rejected. Here are a number of things that were in the list by default ”anything in SYS schema, any package starting with DBMS*, and anything starting with OWA*. Oracle didn't add schemas like MDSYS or CTXSYS, but more on that later. The point is that the fix could be trivially bypassed by breaking the pattern matching. By inserting a %20, %08, or a %0A in front of the schema, one could still gain access to the SYS schema:

 http://www.books.example.com/pls/bookstore/%0ASYS.OWA_UTIL.CELLSPRINT?P_THEQUERY=select+1+from+dual 

I reported this and they fixed it. A while later, I went back and took a look at this exclusion list protection and, out of curiosity , I tested its robustness. This time I went from %00 to %FF replacing the Y of SYS and checked the web server for a 200 response ”that is, I could gain access to OWA_UTIL again. I found that %FF was translated by the web server to the hex byte 0xFF (obviously) and this was sent over to the database server. Interestingly, though, the database server translated the 0xFF to 0x59 ”a Y! This allowed me to gain access to OWA_UTIL again and allowed me to run arbitrary queries.

 http://www.books.example.com/pls/bookstore/S%FFS.OWA_UTIL.CELLSPRINT?P_THEQUERY=select+1+from+dual 

This is related to the character sets in use by the application server and the database server. For this to work both must be using the WE8ISO8859P1 character set ”a common situation. Digging deeper I also found that if the web server uses the AMERICAN_AMERICA.WE8ISO8859P1 character set and the database server uses the ENGLISH_UNITEDKINGDOM.WE8MSWIN1252 character set, then %9F is also converted to a Y.

 http://www.books.example.com/pls/bookstore/S%9FS.OWA_UTIL.CELLSPRINT?P_THEQUERY=select+1+from+dual 

There may be other such interesting combinations. Anyway, I duly reported this to Oracle and they fixed it in August of 2004. In September I reported an issue with a PL/SQL procedure that had a security impact if one could get to it via an application server, but Oracle refused to fix it on the grounds that because of their new "fix" for the exclusion lists it wasn't possible to gain access to the procedure. This somewhat annoyed me. I argued with them saying that I'd found two bugs in the past in the exclusion list, and could they be absolutely sure there weren't any more. Better to fix the bug in the procedure. In fact I was so irritated it caused me to have a flash of inspiration: you can enclose identifiers, such as SYS, in double quotes ”for example:

 EXEC "SYS".DBMS_OUTPUT.PUT_LINE('Hello!'); 

Why not use double quotes when calling it via an application server. By rights this should break the pattern matching. Sure enough it did. Lo and behold we have another obvious way of bypassing the exclusion list (incidentally, the 10g Application Server is not vulnerable to this; 10gAS takes the user input and turns all uppercase characters to lowercase so "SYS" becomes "sys". So while the double quotes still get through, the database server can find the "sys" schema. When quoting identifiers they need to be in uppercase). So Oracle is now fixing this and, thankfully, the bug in the procedure.

Anyway, back to PL/SQL and Oracle Application Server. Earlier we discussed the DRILOAD package in the CTXSYS schema. 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 it doesn't seem like it's working. The reason is because when you call the VALIDATE_STMT procedure, if you're not doing a select, the procedure returns

 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. Although the error indicates that no statement is parsed, the query is still executed. For example, requesting

 http://www.books.example.com/pls/bookstore/ctxsys.driload.validate_stmt? sqlstmt=CREATE+OR+REPLACE+PROCEDURE+WEBTEST+AS+BEGIN+HTP.PRINT('hello');+END; 

returns a 404.

Requesting

 http://www.books.example.com/pls/bookstore/ctxsys.driload.validate_stmt? sqlstmt=GRANT+EXECUTE+ON+WEBTEST+TO+PUBLIC 

also returns a 404. However, now requesting

 http://www.books.example.com/pls/bookstore/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 request 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 can be.

It should be noted here that 99% of the issues discussed in this section on PL/SQL can be performed over the Web via an Oracle Application Server.



Database Hacker's Handbook. Defending Database Servers
The Database Hackers Handbook: Defending Database Servers
ISBN: 0764578014
EAN: 2147483647
Year: 2003
Pages: 156

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net