0 Replies Latest reply on Nov 24, 2014 6:38 AM by Daniel Sailer

    Finding BladeLogic application specific queries that trigger Oracle ORA exceptions

    Daniel Sailer

      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



      1. Amend the trigger SQL command below to log the Oracle exception you encountered.
      2. 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.
      3. Run the commands in BladeLogic that throw Oracle exceptions.
      4. Look at the oraerror table to find the clue on what went wrong:
        SELECT COUNT(bj.job_id) "count"

          FROM job bj, batch_job_job bjj, job sub_job, job_option_value jov

        WHERE bj.job_id = :1  AND

               bj.job_version_id = :2  AND

               bj.job_id = bjj.batch_job_id AND

               bj.job_version_id = bjj.batch_job_version_id AND

               bjj.job_id = sub_job.job_id AND

               bjj.job_version_id = sub_job.job_version_id AND

               sub_job.job_id = jov.job_id AND

               sub_job.job_version_id = jov.job_version_id AND

               jov.option_id = 249 ANDjov.option_value = 1

        (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
      -- 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
          PRAGMA autonomous_transaction;
          id NUMBER;
          sql_text ORA_NAME_LIST_T;
          v_stmt CLOB;
          n NUMBER;
          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);
              end if;
          end loop;