SQL Injection in PL/SQL - myth or fact?

Question

I am trying to hack my PL/SQL code. We create the PL/SQL procedure that opens and fetch the cursor. By our standard we did create a dynamic SQL statement, but we are unable to inject the OR 1=1 condition.

I did prepare a http://sqlfiddle.com/#!4/a62a3/5 demo, where you can try to inject the code.

CREATE FUNCTION get_documents (p_document_id IN DOCUMENTS.DOCUMENT_ID%TYPE)
    RETURN SYS_REFCURSOR
AS
    p_rs SYS_REFCURSOR;
BEGIN
    DBMS_OUTPUT.PUT_LINE('------ INPUT VALUES ------');
    DBMS_OUTPUT.PUT_LINE('p_document_id: ' || p_document_id);

    OPEN p_rs FOR 
        SELECT DOCUMENT_ID, '(' || MY_FIELD || ')' FROM DOCUMENTS WHERE DOCUMENT_ID = '' ||  p_document_id  || '';
    RETURN p_rs;
END;

We tried to inject the code in p_document_id parameter. We set it to:

 document_refcur_local:=get_documents('10'' OR 1=1; -- ');

but we were unable to select all records. Could you please let me know what am I doing it wrong?

`'' || p_document_id || ''` doesn't really do anything. It just returns `p_document_id` (with two empty strings concatenated to it). — melpomene, Aug 29 '18 at 12:00
I am not sure but looked to me as if you are only sending a parameter instead of concatenating an SQL statement in the front end. (You are dynamically changing the parameter but the SQL statement itself is not a dynamic string) — Cetin Basoz, Aug 29 '18 at 12:04

Milney · Accepted Answer · 2018-08-29T12:03:19.583

5

This is not in fact a dynamic statement and so is not vulnerable to an injection.

If you built this string from a front-end with string concatenation of the p_document_id outside of the SQL query - then sent it to SQL, it would be vulnerable, but you cannot do the injection in the SQL query itself (Unless building a string then running it, i.e. a dynamic query, which yours is not doing)

A dynamic query vulnerable to injection would look more like;

EXECUTE IMMEDIATE 'SELECT * FROM DOCUMENTS WHERE DOCUMENT_ID = ''' + someUserInput + ''''

And you could inject by passing as someUserInput something like

' OR 1=1; --

edited Aug 29 '18 at 12:03

answered Aug 29 '18 at 11:58

Milney

6,253
2
19
33

Thanks for the answer. Could you please explain why is not a dynamic statement? We did use a concatenation? What does defines the dynamic statement? – Gico Aug 29 '18 at 12:01
1

@Gico - A dynamic statement builds the statement itself as a string, then executes that string. Your statement only dynamically changes A PARAMETER, not the query itself. Looks at my edit for an example... – Milney Aug 29 '18 at 12:05
1

@Gico The point is that you're not concatenating strings in order to create a string containing SQL code; you're merely concatenating strings inside your (static) SQL. – melpomene Aug 29 '18 at 12:06
1

Still wouldn't work. Oracle won't allow a `;` inside a string used for EXECUTE IMMEDIATE – Aug 29 '18 at 12:10

score 4 · Answer 2 · answered Aug 29 '18 at 12:07

If you want to succeed at SQL injection your SELECT statement needs to be specified as a character string to which you concatenate your document ID string, which contains the condition to be added. Here's your code, rewritten:

CREATE OR REPLACE FUNCTION GET_DOCUMENTS (p_document_id IN VARCHAR2)
  RETURN SYS_REFCURSOR
IS
  p_rs SYS_REFCURSOR;
BEGIN
  DBMS_OUTPUT.PUT_LINE('------ INPUT VALUES ------');
  DBMS_OUTPUT.PUT_LINE('p_document_id: ' || p_document_id);

  OPEN p_rs FOR 
    'SELECT DOCUMENT_ID, ''('' || MY_FIELD || '')'' FROM DOCUMENTS WHERE DOCUMENT_ID = ''' ||  p_document_id  || '''';

  RETURN p_rs;
END GET_DOCUMENTS;

score 2 · Answer 3 · answered Aug 29 '18 at 12:35

An example:

create table documents (id, value) as 
(
    select 1, 'x' from dual union all
    select 2, 'x' from dual union all
    select 3, 'y' from dual
)

A vulnerable function:

create or replace function countDocs(pValue IN varchar2) return number is
    vRetVal number;
begin
    execute immediate 'select count(*) from documents where value = ''' || pValue || ''''
    into vRetVal;
    return vRetVal;
end;

What you can do:

SQL> select countDocs('y') from dual;

COUNTDOCS('Y')
--------------
             1

SQL> select countDocs('y'' or ''a''=''a') from dual;

COUNTDOCS('Y''OR''A''=''A')
---------------------------
                          3

A safe way could be with bind variables:

create or replace function countDocsSafe(pValue IN varchar2) return number is
    vRetVal number;
begin
    execute immediate 'select count(*) from documents where value = :bindVar'
    into vRetVal
    using pValue ;
    return vRetVal;
end;

Which gives:

SQL> select countDocsSafe('y') from dual;

COUNTDOCSSAFE('Y')
------------------
                 1

SQL> select countDocsSafe('y'' or ''a''=''a') from dual;

COUNTDOCSSAFE('Y''OR''A''=''A')
-------------------------------
                              0

SQL Injection in PL/SQL - myth or fact?

3 Answers3