SQL Injection - Getting a Date with Bobby Tables

August 2, 2010

Last Friday I received my copy of the new book “Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions“.  There is an interesting demonstration of SQL injection on pages 17 through 21 of the book, where a clever use of SQL injection permits a significant amount of information to be returned when the only supplied value for a SQL statement is declared as a DATE data type.  If you bought the book, take a close look at the example on those pages (I do not recall this example from the first edition of this book or his “Expert One On One” book).  Certainly, you have heard of Bobby Tables?

I thought that it might be interesting to create a slight twist (an alternate ending) for the example shown in the book.  Let’s create a simple test table with a list of employees, divisions, and series of dates:

CREATE TABLE T1 (
  EMPLOYEE_ID VARCHAR2(30),
  DIVISION VARCHAR2(30),
  SALES_DATE DATE);

INSERT INTO
  T1
SELECT
  DECODE(MOD(ROWNUM,10),
    1,'JIM',
    2,'JACK',
    3,'MIKE',
    4,'BOB',
    5,'JOE',
    6,'SUE',
    7,'MARY',
    8,'JOAN',
    9,'CAROL',
    0,'JAN'),
  DECODE(MOD(ROWNUM,6),
    0,'DIVISION 6',
    'DIVISION '||TO_CHAR(MOD(ROWNUM,6))),
  TRUNC(SYSDATE)-1000+ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

COMMIT;

Now assume that we have a SQL statement like this in a stored procedure:

SELECT
  *
FROM
  T1
WHERE
  EMPLOYEE_ID='JIM'
  AND SALES_DATE>='01-JUL-2010'
ORDER BY
  SALES_DATE;

EMPLOYEE_ID DIVISION    SALES_DAT
----------- ----------- ---------
JIM         DIVISION 5  04-JUL-10
JIM         DIVISION 3  14-JUL-10
JIM         DIVISION 1  24-JUL-10

So, for the employee JIM there were three sales dates, one each in DIVISION 5, DIVISION 3, and DIVISION 1.  The stored procedure might look something like this (adapted from the example in the book):

 CREATE OR REPLACE PROCEDURE T1_RETRIEVE (strEmployeeID IN VARCHAR,dteSalesDate IN DATE) AS
  strSQL VARCHAR(1000);
  rowT1  T1%ROWTYPE;
  curT1  SYS_REFCURSOR;
begin
  strSQL := '
    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID=''' || strEmployeeID ||'''
      AND SALES_DATE>=''' || dteSalesDate ||'''
    ORDER BY 3';

  dbms_output.put_line( strSQL );

  open curT1 for strSQL;

  loop
    fetch curT1 into rowT1;
    exit when curT1%notfound;
    dbms_output.put_line (
      rowT1.EMPLOYEE_ID || ',' || rowT1.DIVISION || ',' || TO_CHAR(rowT1.SALES_DATE,'MM/DD/YYYY'));
  end loop;

  close curT1;
end;
/

We are able to verify that the stored procedure works like this:

SET SERVEROUTPUT ON SIZE UNLIMITED

EXEC T1_RETRIEVE('JIM','01-JUL-2010')

    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID='JIM'
AND SALES_DATE>='01-JUL-2010'
    ORDER BY 3
JIM,DIVISION 5,07/04/2010
JIM,DIVISION 3,07/14/2010
JIM,DIVISION 1,07/24/2010

It appears that the stored procedure worked.  Maybe I was too harsh in the book review of “Oracle SQL Recipes: A Problem – Solution Approach“?  It certainly appears as though we are relying on implicit data type conversion.

Let’s assume that someone is interested in bypassing the date and employee specification for this stored procedure, so that all rows are returned.  Of course it is important to hide what is changed.  Maybe we would want the result of the SQL injection to look something like this:

SELECT
  *
FROM
  T1
WHERE
  EMPLOYEE_ID='JIM'
  AND SALES_DATE>=''
UNION ALL
SELECT
  *
FROM
  T1;

EMPLOYEE_ID DIVISION    SALES_DAT
----------- ----------- ---------
JIM         DIVISION 1  07-NOV-07
JACK        DIVISION 2  08-NOV-07
MIKE        DIVISION 3  09-NOV-07
BOB         DIVISION 4  10-NOV-07
JOE         DIVISION 5  11-NOV-07
SUE         DIVISION 6  12-NOV-07
MARY        DIVISION 1  13-NOV-07
JOAN        DIVISION 2  14-NOV-07
CAROL       DIVISION 3  15-NOV-07
JAN         DIVISION 4  16-NOV-07
JIM         DIVISION 5  17-NOV-07
...

For instance, if we do this:

ALTER SESSION SET NLS_DATE_FORMAT = '"''UNION ALL SELECT * FROM T1 --"';

Now if we execute the stored procedure like before:

EXEC T1_RETRIEVE('JIM','01-JUL-2010')

Output:
-----------------------
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at line 1
-----------------------

An error?  I did provide a warning in the review of the SQL Recipes book.  Let’s do it the correct way this time to avoid the implicit data type conversion:

EXEC T1_RETRIEVE('JIM',TO_DATE('01-JUL-2010','DD-MON-YYYY'))

    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID='JIM'

AND SALES_DATE>=''UNION ALL SELECT * FROM T1 --'
    ORDER BY 3
JIM,DIVISION 1,11/07/2007
JACK,DIVISION 2,11/08/2007
MIKE,DIVISION 3,11/09/2007
BOB,DIVISION 4,11/10/2007
JOE,DIVISION 5,11/11/2007
SUE,DIVISION 6,11/12/2007
MARY,DIVISION 1,11/13/2007
JOAN,DIVISION 2,11/14/2007
CAROL,DIVISION 3,11/15/2007
JAN,DIVISION 4,11/16/2007
JIM,DIVISION 5,11/17/2007
JACK,DIVISION 6,11/18/2007
MIKE,DIVISION 1,11/19/2007
BOB,DIVISION 2,11/20/2007
JOE,DIVISION 3,11/21/2007
SUE,DIVISION 4,11/22/2007
MARY,DIVISION 5,11/23/2007
JOAN,DIVISION 6,11/24/2007
CAROL,DIVISION 1,11/25/2007
JAN,DIVISION 2,11/26/2007
JIM,DIVISION 3,11/27/2007
JACK,DIVISION 4,11/28/2007
MIKE,DIVISION 5,11/29/2007
...
MARY,DIVISION 1,07/30/2010
JOAN,DIVISION 2,07/31/2010
CAROL,DIVISION 3,08/01/2010
JAN,DIVISION 4,08/02/2010

That certainly is a different result than what we received the first time the procedure was executed (what the programmer probably intended).  We could have also tried this:

EXEC T1_RETRIEVE('JIM',SYSDATE)

I suppose that things might have been worse if Bobby Tables had a chance to experiment with this example.  We really should be using bind variables in this example, but what if you still want to use literals rather than bind variables – is there a work-around (one might be wondering if it is not easier to just do things the right way from the start)?  Maybe if we remove the source of the implicit data type conversion in the procedure like this:

CREATE OR REPLACE PROCEDURE T1_RETRIEVE (strEmployeeID IN VARCHAR,dteSalesDate IN DATE) AS
  strSQL VARCHAR(1000);
  rowT1  T1%ROWTYPE;
  curT1  SYS_REFCURSOR;
begin
  strSQL := '
    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID=''' || strEmployeeID ||'''
      AND SALES_DATE>=TO_DATE(''' || dteSalesDate ||''', ''DD-MON-YYYY'')
    ORDER BY 3';

  dbms_output.put_line( strSQL );

  open curT1 for strSQL;

  loop
    fetch curT1 into rowT1;
    exit when curT1%notfound;
    dbms_output.put_line (
      rowT1.EMPLOYEE_ID || ',' || rowT1.DIVISION || ',' || TO_CHAR(rowT1.SALES_DATE,'MM/DD/YYYY'));
  end loop;

  close curT1;
end;
/

Now testing the procedure:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
EXEC T1_RETRIEVE('JIM','01-JUL-2010')

    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID='JIM'

AND SALES_DATE>=TO_DATE('01-JUL-2010', 'DD-MON-YYYY')
    ORDER BY 3
JIM,DIVISION 5,07/04/2010
JIM,DIVISION 3,07/14/2010
JIM,DIVISION 1,07/24/2010

PL/SQL procedure successfully completed.

OK, it works.  Now what happens when the attempted SQL injection happens:

ALTER SESSION SET NLS_DATE_FORMAT = '"''UNION ALL SELECT * FROM T1 --"';
EXEC T1_RETRIEVE('JIM',TO_DATE('01-JUL-2010','DD-MON-YYYY'))

    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID='JIM'

AND SALES_DATE>=TO_DATE(''UNION ALL SELECT * FROM T1 --', 'DD-MON-YYYY')

ORDER BY 3
BEGIN T1_RETRIEVE('JIM',TO_DATE('01-JUL-2010','DD-MON-YYYY')); END;

*
ERROR at line 1:
ORA-00907: missing right parenthesis
ORA-06512: at "TESTUSER.T1_RETRIEVE", line 18
ORA-06512: at line 1

That’s better, but now we created a new problem… time for another change to the procedure?