Fix SQL Injection using Oracle Database Code

gistfile1.txt

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;

//---------------------------------------------------
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);

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);

//---------------------------------------------------
Cursor with dynamic query
Secure Usage
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = :email';
OPEN empcur FOR sqlStmt USING email;

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

reference: https://software-security.sans.org/developer-how-to/fix-sql-injection-in-oracle-database-code