--
-- detect.sql
-- Pete Finnigan 
-- 16-Jun-2003
--
-- Example code for an article for www.securityfocus.com called "detecting
-- SQL injection in Oracle" by Pete Finnigan (pete@petefinnigan.com)
-- website http://www.petefinnigan.com
--
set serveroutput on size 1000000

spool detect.lis

--
-- use a default user such as dbsnmp, of course any other user can be used, this user
-- is used here for simplicity to avoid creating a user. In a production system
-- first of all do not run tests like this one and do not use DBSNMP.
--
-- UN-COMMENT THIS IF YOU CHOOSE THIS USER
--connect dbsnmp/dbsnmp

--
-- create a sample table to use in the examples
-- 

drop table customers;
create table customers
(
	customer_forname	varchar2(30),
	customer_surname	varchar2(30),
	customer_phone		varchar2(30),
	customer_fax		varchar2(30),
	customer_type		number(10)
)
tablespace users;

--
-- insert three records to test with
--

insert into customers
(
	customer_forname,
	customer_surname,
	customer_phone,
	customer_fax,
	customer_type
)
values
(
	'Fred',
	'Clark',
	'999444888',
	'999444889',
	3
)
/

insert into customers
(
	customer_forname,
	customer_surname,
	customer_phone,
	customer_fax,
	customer_type
)
values
(
	'Bill',
	'Jones',
	'999555888',
	'999555889',
	2
)
/

insert into customers
(
	customer_forname,
	customer_surname,
	customer_phone,
	customer_fax,
	customer_type
)
values
(
	'Jim',
	'Clark',
	'999777888',
	'999777889',
	1
)
/

--
-- create a sample PL/SQL procedure to SQL Inject
--

create or replace procedure get_cust (pv_surname in varchar2)
is
	type cv_typ is ref cursor;
	cv cv_typ;
	lv_phone	customers.customer_phone%type;
	lv_stmt		varchar2(32767):='select customer_phone'||
			' from customers '||
			'where customer_surname='''||
			pv_surname||'''';
begin
	dbms_output.put_line('debug:'||lv_stmt);
	open cv for lv_stmt;
	loop
		fetch cv into lv_phone;
		exit when cv%notfound;
		dbms_output.put_line('::'||lv_phone);
	end loop;
	close cv;
exception
	when others then
		dbms_output.put_line(sqlcode||sqlerrm);
end get_cust;
/
sho err

--
-- This is the call used for each test throughout the paper.
--

exec get_cust('x'' union select username from all_users where ''x''=''x');


--
-- check before using log miner
--

-- check ARCHIVELOGMODE is on
select log_mode from v$database
/

-- check the log destination and start flag
select name,value from v$parameter
where name in('log_archive_start','log_archive_dest')
/

-- check if transaction_auditing is on, this is needed so that usernames 
-- are recorded in the redo
select name,value from v$parameter
where name = 'transaction_auditing'
/

-- now do the logminer test
connect sys as sysdba

alter system archive log current
/

connect dbsnmp/dbsnmp@emil

set serveroutput on size 100000

exec get_cust('x'' union select username from all_users where ''x''=''x')
/

connect sys as sysdba

alter system archive log current
/

-- create the logminer dictionary
set serveroutput on size 1000000
exec dbms_logmnr_d.build('logmnr.dat','/tmp')
/

-- find the correct archive log
select name
from v$archived_log
where completion_time=(select max(completion_time) from v$archived_log)
/

-- load the file into logminer and start it (note readers need to use the file
-- found above not mine!!)
exec dbms_logmnr.add_logfile('/export/home/u01/app/oracle/admin/emil/archive/1_7.dbf',sys.dbms_logmnr.NEW)
/

exec dbms_logmnr.start_logmnr(dictFileName => '/tmp/logmnr.dat')
/

-- search the results
select scn,username,timestamp,sql_redo
from v$logmnr_contents
/

--
-- get the OS PID for sql*net trace
--

select p.spid,s.username
from v$session s,v$process p
where s.paddr=p.addr
/

--
-- test internal trace files :
--

alter session set sql_trace=true
/
exec get_cust('x'' union select username from all_users where ''x''=''x')
/

-- run tkprof
-- add  the relevant path to the trace file.
host tkprof emil_ora_616.trc output.trc sys=yes

-- find the ID (change the ID to the one from your trace file)
select username,user_id
from dba_users
where user_id=17
/

--
-- reading SQL from the 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 '%UNION%')
order by 1,2,3
/

--
-- using the audit trail to monitor abuse
--

-- turn on audit
audit select on sys.all_users by access
/
audit select on dbsnmp.customers by access
/

-- run the exploit
exec get_cust('x'' union select username from all_users where ''x''=''x')
/
-- check the audit trail
col username for a8
col timestamp for a20
col action_name for a15
col obj_name for a10
col owner for a8
col sessionid for 999
col returncode for 999
select  username,
        to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') timestamp,
        action_name,
        obj_name,
        owner,
        sessionid,
        returncode
from    dba_audit_object
order by timestamp
/

--
-- explore fine grained audit
--

-- analyse the sample table
analyze table dbsnmp.customers compute statistics
/

-- check if CBO is used
select name,value from v$parameter
where name='optimizer_mode'
/

-- create an audit policy
begin
dbms_fga.add_policy(object_schema=>'DBSNMP',
           object_name=>'CUSTOMERS',
           policy_name=>'SQL_INJECT',
           audit_condition=>'1=1',
           audit_column=>null,
           handler_schema=>null,
           handler_module=>null,
           enable=>true);
end;
/

-- run the exploit
set serveroutput on size 100000
exec get_cust('x'' union select username from all_users where ''x''=''x')
/

-- check the audit trail
col db_user for a15
col object_name for a20
col sql_text for a30 word_wrapped
select db_user,timestamp,object_name,sql_text
from dba_fga_audit_trail
order by timestamp
/

-- SQL to generate standard audit policies
select 'exec dbms_fga.add_policy(object_schema=>'''||owner||''',object_name=>'''
||table_name||''',policy_name=>'''||table_name||''',audit_condition=>''1=1'',aud
it_column=>null,handler_schema=>null,handler_module=>null,enable=>true);'
from dba_tables
where owner not in ('SYS','SYSTEM')
/





spool off