The Oracle® Hacker's Handbook: Hacking and Defending Oracle

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