SQl Injection in PL/SQL Program

 

SQL injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements.

Let’s  consider the following example.

Developer wanted to allow for the End Users to select data  

from an arbitrary (employees’s like) table for ‘IT_PROG’ job category.

CREATE OR REPLACE PROCEDURE  SQL_INJ(tname VARCHAR2) IS
   type emp_cur_type IS REF CURSOR;
   c1 emp_cur_type; j number:=0;
   type c_type is table of  employees%rowtype;
   emp c_type;
stmt varchar2(200);
BEGIN
  stmt:='SELECT * FROM '||tname||Q'# WHERE JOB_ID='IT_PROG'#';
  OPEN c1 FOR stmt;
    FETCH c1 BULK COLLECT INTO emp;
    FOR I IN 1..emp.COUNT LOOP
    dbms_output.put_line
    (emp(I).LAST_NAME||' salary :'||EMP(I).SALARY);
    END LOOP;
CLOSE c1;
END SQL_INJ;
/
With the following SQL*Plus command 
we will get the suitable result:
EXECUTE SQL_INJ('EMPLOYEES')

However, if we issue the next command:
EXEC SQL_INJ(Q'#EMPLOYEES WHERE JOB_ID='AD_VP' --#')
In this case we could rewrite the intended SQL statement
and selected the AD_VP's data!

Using the DBMS_ASSERT package (introduced in Oracle 11g)
we can avoid the vulnerability of our program.
For example, using the  SQL_OBJECT_NAME function
we can achieve the desired behavior:

CREATE OR REPLACE PROCEDURE  SQL_INJ2(tname VARCHAR2) IS
   type emp_cur_type IS REF CURSOR;
   c1 emp_cur_type; j number:=0;
   type c_type is table of  employees%rowtype;
   emp c_type;
stmt varchar2(200);
BEGIN
  stmt:='SELECT * FROM '||DBMS_ASSERT.SQL_OBJECT_NAME(tname)||
        Q'# WHERE JOB_ID='IT_PROG'#';
  OPEN c1 FOR stmt;
      FETCH c1 BULK COLLECT INTO emp;
      FOR I IN 1..emp.COUNT LOOP
      dbms_output.put_line
      (emp(I).LAST_NAME||' salary :'||EMP(I).SALARY);
      END LOOP;
CLOSE c1;
END SQL_INJ2;
/

Let's issue again this command:
EXEC SQL_INJ2(Q'#EMPLOYEES WHERE JOB_ID='AD_VP' --#')

ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 293
ORA-06512: at "HR.SQL_INJ2", line 8
ORA-06512: at line 1

Using the following command we get the proper result:
EXEC SQL_INJ2(Q'#EMPLOYEES#')

About these ads