How To Fix SQL Injection: Oracle

How to Fix SQL Injection using Oracle Database Code


A stored procedure is a logical set of SQL statements, performing a specific task; it is compiled once and stored on a database server for all clients to execute; they are used very commonly for the many benefits that they provide. Often times, stored procedures are blindly considered secure; however, it is not so always. SQL Injection is a concern when dynamic SQL is handled incorrectly in a stored procedure.

In Oracle, dynamic SQL can be used in 1. EXECUTE IMMEDIATE statements, 2. DBMS_SQL package and 3. Cursors. This article illustrates how dynamic SQL can be built securely to defend against SQL injection attacks.

Execute Immediate Statement

Secure Usage
--Execute Immediate - named parameter
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = :email';
EXECUTE IMMEDIATE sqlStmt USING email;

--Execute Immediate - positional parameter
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = :1 and emp_name = :2';
EXECUTE IMMEDIATE sqlStmt USING email, name;

Here, bind variables are used to set data in the query, hence sql injection proof.

Vulnerable Usage
sqlStmt:= 'SELECT emp_id FROM employees WHERE emp_email = ''' || email || '''';
EXECUTE IMMEDIATE sqlStmt INTO empId;

Here, the input variable "email" is used directly in the query using concatenation; opening up the possibility to manipulate the "where" clause.

DBMS_SQL Package

Secure Usage
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = :email';
empcur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(empcur, sqlStmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(empcur, ':email', email);
DBMS_SQL.EXECUTE(empcur);

Here, bind variable is used to set data to query, hence sql injection proof.

Vulnerable Usage
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = ''' || email || '''';
empcur:= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(empcur, sqlStmt, DBMS_SQL.NATIVE);
DBMS_SQL.EXECUTE(empcur);

Here, the input variable "email" is used directly in the query using concatenation; opening up the possibility to manipulate the "where" clause.

Cursor with dynamic query

Secure Usage
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = :email';
OPEN empcur FOR sqlStmt USING email;

Here, bind variable is used to set data to the query, hence sql injection proof.

Vulnerable Usage
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = ''' || email || '''';
OPEN empcur FOR sqlStmt;

Here, the input variable "email" is used directly in the query using concatenation; opening up the possibility to manipulate the "where" clause.

Authors

Priya Gnanasundar

How To Fix SQL Injection: