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

4 Responses to “SQl Injection in PL/SQL Program”

  1. Joel Goodman Says:

    Thanks for posting this Laszlo.

    • lczinkoc Says:

      Hi Joaquín,

      to tell the truth I believed the documentation.
      In the PL/SQL Supplied Packages the DBMS_ASSERT was mentioned as new package.
      Maybe this package was unofficial object in the previous version.
      Thanks
      Laszlo

  2. Joaquín González Says:

    DBMS_ASSERT is also present in 10.2.0.3 at least.

  3. Mike Says:

    Just passing by.Btw, you website have great content!

    _________________________________
    Making Money $150 An Hour

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: