-- Purpose: Oracle Database Error Trigger to detect SQL Injection Attacks
-- Version: v 0.91   
-- Works against: Oracle 9i, 10g and 11g
-- Author: Alexander Kornbrust of Red-Database-Security GmbH
-- must run as user SYS
-- latest version: http://www.red-database-security.com/scripts/oracle_error_trigger.html
--

-- Create a table containing the error messages
create table system.oraerror (
id NUMBER,
log_date DATE,
log_usr VARCHAR2(30),
terminal VARCHAR2(50),
err_nr NUMBER(10),
err_msg VARCHAR2(4000),
stmt CLOB
);

-- Create a sequence with unique numbers
create sequence system.oraerror_seq
start with 1
increment by 1
minvalue 1
nomaxvalue
nocache
nocycle;


-- log the following error messages
ORA-00900: invalid SQL statement
ORA-00906: missing left parenthesis
ORA-00907: missing right parenthesis
ORA-00911: invalid character  (e.g. PHP MAGIC_QUOTES_GPC enabled)
ORA-00917: missing comma
ORA-00920: invalid relational operator
ORA-00923: FROM keyword not found where expected
ORA-00933: SQL command not properly terminated
ORA-00970: missing WITH keyword
ORA-01031: insufficient privileges (attempt of privilege escalation)
ORA-01476: divisor is equal to zero (attempt blind sql injection with 1/0)
ORA-01719: outer join operator not allowed in operand of OR or IN
ORA-01722: invalid number (enumeration via rownum and current rownum does not exist)
ORA-01742: comment not properly terminated (inline comment, e.g. optimizer hint, not properly terminated)
ORA-01756: quoted not properly terminated (single quote not properly terminated)
ORA-01789: query block has incorrect number of result columns (attempt to use UNION SELECT)
ORA-01790: expression must have same datatype as corresponding (attempt to use UNION SELECT)
ORA-24247: network access denied by access control list (ACL)
ORA-29257: Host %S unknown (attempt to use utl_inaddr)
ORA-29540: Class does not exist (attempt to utl_inaddr but java not installed) 


CREATE OR REPLACE TRIGGER after_error
 AFTER SERVERERROR ON DATABASE
 DECLARE
 pragma autonomous_transaction;
 id NUMBER;
 sql_text ORA_NAME_LIST_T;
 v_stmt CLOB;
 n NUMBER;
BEGIN
 SELECT oraerror_seq.nextval INTO id FROM dual;
  --
 n := ora_sql_txt(sql_text);
 --
 IF n >= 1
 THEN
 FOR i IN 1..n LOOP
 v_stmt := v_stmt || sql_text(i);
 END LOOP;
 END IF;
 --
 FOR n IN 1..ora_server_error_depth LOOP
 --
 -- log only potential SQL Injection attempts
 -- alternatively it's possible to log everything
IF ora_server_error(n) in (  '900','906','907','911','917','920','923','933','970','1031','1719','1722','1742','1756','1789','1790','24247','29257','29540') 
 OR ( (ora_server_error(n) = '1476' ) and (instr(v_stmt,'/* OracleOEM') =0) ) -- execption bug in Oracle OEM
 THEN
   -- insert the attempt including the SQL statement into a table   
   INSERT INTO system.oraerror VALUES (id, sysdate, ora_login_user, ora_client_ip_address, ora_server_error(n), ora_server_error_msg(n), v_stmt);
   -- send the information via email to the DBA
   --  <<Insert your PLSQL code for sending emails >>
   COMMIT;
 END IF;
END LOOP;
 --
END after_error;
/