Design & Develop Secured Oracle Applications

Oded Raz, Oracle ACE Director Brillix LTD

2

Agenda

• Security Risks

• SQL Injection – Overview

• SQL Injection - Demo

• DBMS_ASSERT

• Error Handling

• Secured By Design

3

Vulnerabilities By Industry – 2010

4

Top Web Site Vulnerabilities - 2010

What is SQL Injection

• SQL injection happens when an application fails

to filter SQL syntax from user-controllable input.

• The user input is used in the construction of

dynamic SQL statements

• The user input then affects the execution of the

dynamically generated SQL statement

SQL Injection – Demo

‘ or ‘k’=‘k

11

Impact of SQL Injection

Bypassing authentication mechanisms

select id from users where name=‘admin’ and password=‘’ or ‘1’=‘1’

Information disclosure

select phone from users where name=‘’ UNION select credit_num from users --’

Information tampering

select id from clients where name=‘’; update clients set debt=0; --

12

Impact of SQL Injection Database corrupting

select usr_id from clients where name=‘’; drop table clients;--

Command execution

select picture from animals where name=‘‘;EXEC master.dbo.xp_cmdshell 'format /y c:’

SQL Injection - Sources

Web Pages :● JSP● ASP● PHP

PL / SQL Java .Net

What to Look for :

Dynamic SQL● EXECUTE IMMEDIATE (PL/SQL)● DBMS_SQL package (PL/SQL)● PreparedStatement (Java)

Input not being sanitized Unhandled Errors

EXECUTE IMMEDIATE

CREATE OR REPLACE PROCEDURE odtug (name IN VARCHAR2) IS

sql VARCHAR2; code VARCHAR2;

BEGIN ... sql := 'SELECT salary FROM emp WHERE name = ''' || name || ''''; EXECUTE IMMEDIATE sql INTO code; ... END;

sql := 'SELECT salary FROM emp WHERE name = :name';

EXECUTE IMMEDIATE sql USING name INTO code;

Use Bind Variables

DBMS_SQL

CREATE OR REPLACE PROCEDURE kscope(name IN VARCHAR2) ISdyn_cursor INTEGER; rows_processed INTEGER; sql VARCHAR2(150); code VARCHAR2(2);

BEGIN sql := 'SELECT salary FROM emp WHERE name = ''' || name || ''''; dyn_cursor := dbms_sql.open_cursor; DBMS_SQL.PARSE(dyn_cursor , sql, DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN(dyn_cursor , 1, code, 10); rows_processed := DBMS_SQL.EXECUTE(dyn_cursor); DBMS_SQL.CLOSE_CURSOR(dyn_cursor);

END;

sql := 'SELECT postal-code FROM states WHERE state-name = :name'; dyn_cursor := dbms_sql.open_cursor; DBMS_SQL.PARSE(dyn_cursor, sql, DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMNdyn_cursor, 1, code, 10); DBMS_SQL.BIND_VARIABLE(dyn_cursor, ':name', name); rows_processed := DBMS_SQL.EXECUTE(dyn_cursor); DBMS_SQL.CLOSE_CURSOR(dyn_cursor);

Use Bind Variables

Dynamic Cursors

CREATE OR REPLACE PROCEDURE kscop(address IN VARCHAR2) ISsql VARCHAR2;

BEGIN

sql := 'SELECT * FROM emp WHERE address = ''' || address || ''''; OPEN crs_emp FOR sql; LOOP

FETCH crs_emp INTO rec_state EXIT WHEN crs_emp %NOTFOUND;

END LOOP; CLOSE crs_emp; END;

Avoid using Dynamic Cursorsuse

EXECUTE IMMEDIATE / DBMS_SQLwith bind variables

Instead

JDBC - PreparedStatement

String name = request.getParameter("name");

PreparedStatement pstmt = conn.prepareStatement("insert into EMP (ENAME) values ('" + name + "')");

pstmt.execute(); pstmt.close();

PreparedStatement pstmt = conn.prepareStatement ("insert into EMP (ENAME) values (?)");

String name = request.getParameter("name"); pstmt.setString (1, name); pstmt.execute(); pstmt.close();

19

DBMS_ASSERT

• ENQUOTE_LITERAL - Encloses the string literal within

single quotation marks.

sql_stmt constant varchar2(32000) := ' SELECT count(*) FROM emp where dept_name='''|| dept_parm ||'''';

literal varchar2(1024):= '''|| dept_parm ||''';

sql_stmt constant varchar2(32000) := ' SELECT count(*) FROM emp where dept_name= ‘|| SYS.DBMS_ASSERT.ENQUOTE_LITERAL(literal);

20

DBMS_ASSERT

• SIMPLE_SQL_NAME - Verifies that the string is a simple

SQL name.

sql_stmt constant varchar2(32000) := ‘SELECT ‘||p_col ||’ FROM ‘|| p_tab;sql_stmt constant varchar2(32000) := ‘SELECT ‘ || SYS.DBMS_ASSERT.SIMAPLE_SQL_NAME(p_col)||’ FROM ‘|| SYS.DBMS_ASSERT.SIMAPLE_SQL_NAME(p_tab);

21

Error Handling

Do not enclose valuable information, it can be used to

orchestrate as attack.

EXCEPTION WHEN OTHERS THEN dbms_output.put_line('No Data Found');

22

Defense = Security By Design

• Use Bind Arguments

• Error Handling – Do not enclose valuable information

• Input Validation

• Use DBMS_ASSERT

• Always validate HTML Fields / Parameters

• Carefully inspect dynamic SQL and filter parameters

• Use fully qualified name when calling packages.

Q & A

Exploits of a mom – XKCD – Randall Munroe

Design & Develop Secured Oracle ApplicationsPlease Fill Out Your Evaluations

Oded Raz, Oracle ACE Director Brillix LTD