Oracle Security: SQL Injection

From Robs_Wiki
Jump to: navigation, search

What is SQL Injection ?

SQL injection is a technique to maliciously exploit applications that use client-supplied data in SQL statements. Attackers trick the SQL engine into executing unintended commands by supplying specially crafted string input, thereby gaining unauthorized access to a database in order to view or manipulate restricted data.

How does SQL Injection take place?

Incorrectly validated or nonvalidated string literals are concatenated into a dynamic SQL statement, and interpreted as code by the SQL engine. The code passage below is an example of code that is vulnarable for SQL Injection:

create or replace procedure show_col (p_colname varchar2, p_tablename varchar2) AS
type t is varray(200) of varchar2(25);
results t;
stmt CONSTANT VARCHAR2(4000) := 'select '||p_colname|| ' from scott.'||p_tablename;
BEGIN
   dbms_output.put_line('SQL Stmt: '||stmt);
   execute immediate stmt bulk collect into Results;
   for j in 1..Results.Count() loop
      dbms_output.put_line(results(j));
   end loop;
END show_col;
/

Let's first run it for it's intented use, as a harmless user:

set serveroutput on
SQL> execute show_col('ENAME','EMP');
SQL Stmt: select ENAME from scott.EMP
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

Let's now run the same procedure as a malicious user, who wants illegal access to the database:

SQL> execute show_col('ENAME','EMP where 1=2 UNION select username from all_users --');
SQL Stmt: select ENAME from scott.EMP where 1=2 UNION select username from all_users 
ANONYMOUS
APPQOSSYS
AUDSYS
BKUP
DBSNMP
DIP
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
HR
IO_TEST
OJVMSYS
ORACLE_OCM
OUTLN
PIETJE
RECLAIM_USER
REPTST
ROBLA
. . .

Remark: the string is "enhanced" with a UNION to get all the database usernames and the tow dashes disable any where clause, by commenting out all the remaining SQL (if any).

How to protect against SQL Injection using BIND variables

To immunize your code against SQL injection attacks, you must use bind arguments (either automatically with static SQL, or explicitly with dynamic SQL), or validate and sanitize all input concatenated to dynamic SQL.

How to protect against SQL Injection by validating the input using DBMS_ASSERT