While implementing recommended cleanup index optimizations, we encountered an issue that certain BladeLogic batch jobs would not run anymore, throwing ORA-01722 Oracle exceptions. The following trigger was implemented in our development system (use at your own risk in production) to prove that the problem was related to the cleanup index optimizations. This trigger will only log the given Oracle exceptions in a separate table 'oraerror' with the oracle user name, error message and the SQL statement that caused the exception. This helped to identify the defect in BladeLogic. The initial trigger and idea came from Alexander Kornbrust of Red-Database-Security GmbH, and we did just amend it to work with BladeLogic and to catch the 1722 exception.
Info Nov 10, 2014 10:38:51 AM Started running the job 'ISS04376377' with priority 'NORMAL' on application server 'XYZ'(2,000,300)
Error Nov 10, 2014 10:38:52 AM An error occurred while attempting to access the database: Message: ORA-01722: invalid number SQLState: 42000 ErrorCode: 1722
Error Nov 10, 2014 10:38:52 AM The job 'ISS04376377' has failed
- Amend the trigger SQL command below to log the Oracle exception you encountered.
- Run this trigger SQL command on your BladeLogic DB as BladeLogic user to create the oraerror table and the trigger that will log the data.
- Run the commands in BladeLogic that throw Oracle exceptions.
- Look at the oraerror table to find the clue on what went wrong:
(as jov.option_value is NVARCHAR and the comparison is run against 1 and not '1', the execution plan with the new index was trying to convert all jov.option_value TO_NUMBER which failed with string values)
-- 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 -- -- Changes made to get it working with BLADELOGIC schema -- Author: Daniel Sailer of AXA Technology Services -- Create a table containing the error messages drop table BLADELOGIC.oraerror; create table BLADELOGIC.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 drop sequence BLADELOGIC.oraerror_seq; create sequence BLADELOGIC.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) drop trigger BLADELOGIC.after_error; create or replace trigger BLADELOGIC.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; n := ora_server_error_depth; for i in 1..n loop -- log only potential SQL Injection attempts if ora_server_error(n) in ( '900','906','907','911','917','920','923','933','970','1031','1476','1719','1722','1742','1756','1789','1790','24247','29257','29540') then -- insert the attempt including the SQL statement into a table INSERT INTO BLADELOGIC.oraerror VALUES (id, sysdate, ora_login_user, ora_client_ip_address, ora_server_error(i), ora_server_error_msg(i), v_stmt); COMMIT; end if; end loop; end; /