Oracle APEX Exploitation – Part 4

It’s been a while since I wrote the first few posts on APEX exploits, so I’m going to briefly discuss an attack that is very widespread and potentially dangerous.

SQL Injection

In very brief terms, SQL injection involves an attacker causing the server to execute SQL code that it was not intending to, by passing that SQL into a vulnerable statement. Dynamic SQL is very vulnerable to SQL injection attacks, with the most obvious examples coming from developers using execute immediate. APEX does make it quite difficult for you to write injectable code, in that it provides items as bind variables and you very rarely have to use anything like execute immediate. The chances of an attack due to this are therefore quite low. It does however also make it very easy for a developer to create a vulnerability, by the use of substitution strings (&ITEM_NAME. format). It is this that I am going to look at.

As always, this is provided to enable you to protect your own applications only.

Mechanism of Attack

So… suppose I, as a new developer who isn’t quite up to speed with APEX development yet, want to create an application where a user can enter a value into a field on the screen and I update a record with that data (I’ve seen production applications as simple as this). I have an on-submit page process with the following code:

 begin   update my_table      set val='&P1_VALUE.'    where code='ROW_TO_UPDATE'; end; 

I pre-populated a basic table using:

 create table my_table as   select 'Test String' val, 'ROW_TO_UPDATE' code from dual; 

Testing my application, it works perfectly.

I have however introduced a vulnerability in my update; because :p1_value is not a bind variable (bind variables cannot be SQL injected) – it is a substitution string. This means that APEX substitutes the value of P1_VALUE into the code before executing it. Not only is this bad for performance (queries are hard-parsed for each distinct value of P1_VALUE), but as an attacker I can also manipulate the value to perform things the application didn’t intend.
I can pass the following string into the text box:

 X'; delete my_table; update my_table set val='Y 

Now suddenly my table is empty! Why? Because the string I entered above was substituted into the original statement to give us this:

 begin   update my_table      set val='X'; delete my_table; update my_table set val='Y'    where code='ROW_TO_UPDATE'; end; 

The Implications

The implications of this are that I effectively have complete access to your schema. I can execute almost any artitary piece of code I wish (within the realms of your parsing schema privileges). Is that a big deal? Well – I could certainly do a lot of damage if I so wanted.

 X'; begin for i in (select table_name from user_tables) loop execute immediate 'drop table ' || i.table_name; end loop; end; update my_table set val='Y 

That would at least take the application offline for awhile. Generally that’s the least of your worries though – such activities are generally the realm of your average script kiddie and any good DBA would likely be able to restore that relatively quickly from a backup and redo logs. What is generally of greater concern is data theft. Let’s see what we have access to.

 X'; begin for i in (select table_name from user_tables) loop apex_error.add_error(p_message=>i.table_name,p_display_location=>apex_error.c_inline_in_notification); end loop; end; update my_table set val='Y 

Now I have a list of all the tables in the schema (one would probably use all_tables in reality – I just wanted a short list for the demo). There are some there that I might find of interest if I was a malicious hacker… 🙂

There are different ways of getting data out of the database like this – you can select into a field, use an error message like I did, render out using htp.p and various other ways – which you choose will depend on a number of things such as the particulars of the exploit, the volume of data you need to extract and so on. Now we know the tables, let’s grab that data. I’d do another query to determine the column names (using user_tab_cols) however I don’t feel I need to explain that here. Next I’m going to dump that data into the existing field on the form.

 X'; begin :p1_value :=''; for i in (select * from bank_accounts) loop :p1_value := :p1_value || chr(10) || i.person_name || ',' || i.sort_code || ',' || i.account_no; end loop; end; update my_table set val='Y 

That’s a bit hard to read – but no problem – because we just use the Developer options in the browser to change the field into a textarea. To be honest, I didn’t even have to that – I could have just ripped it straight out of the source code.

But there I have a comma separated list of bank account details. I can dump any data that is accessible to the schema, I can update it, I can encrypt it and extort a fee from the owner to decrypt it.. All because a developer used &P1_VALUE. instead of :P1_VALUE.

To be honest, the list of things you can do here is pretty endless – I saw an example (I believe originally presented at KScope 2013 by Recx) where they used wwv_flow_utilities.export_application_to_clob to download the application definition offline, modify the application to include a backdoor vulnerability that emailed a user’s username and password to the attacker when they log in, and then reuploaded that application through the SQLi vulnerability! You’d imagine that at the same time an attacker would probably also create several backdoors in various places to counter cleanup attempts.

One thing I didn’t mention at the beginning was how to find these vulnerabilities. There are automated tools out there that will do this for you, however the absolute simplest way has to be to simply put a quote in the text field! If there is a vulnerability then the expanded query will be invalid (three quotes):

 begin   update my_table      set val='''    where code='ROW_TO_UPDATE'; end; 

and you’ll likely get an ORA-00933 error.

If you get that then you know the application isn’t escaping user input properly and that there is a high possibility of an attack.

How To Defend Against It

This is pretty simple.

1. Don’t use dynamic SQL
2. Use bind variables

Anywhere you are using dynamic SQL or substitution variables instead of binds, you are running a high risk of introducing a SQL injection vulnerability into your application.

Summary

It is very easy to avoid SQL injection attacks, but also (unfortunately) very easy to introduce them too. They are extremely simple, but the outcome can be catastrophic. This can range from data corruption/deletion and data leaks, through siphoning of your users account details through backdoors, to your system being used as a spam bot net! If you ever find yourself typing the words “execute immediate” then be very very wary. And never use substitution strings in place of bind variables.

I might do a few more examples of different types of SQLi attacks (i.e. in report queries/conditions rather than post submit processing) over the coming months, so keep tuned.