Oracle APEX Security Tutorial

Learn how to harden your Oracle APEX applications! Prevent cross site scripting, SQL injection and more.

Oracle Application Express (APEX) has many security related features that help developers create applications that are guarded from today’s web based threats. But if developers are unaware of these features, how they work and what they guard against, then it is likely they will create applications with at least a few security holes. In this tutorial you will see demos of certain exploits, including Cross Site Scripting and SQL injection, and subsequently learn how to protect against them using the correct feature(s) in APEX or Oracle in general.

Want more? April 10 INTERMEDIATE APEX Training with Expert Tyson Jouglet!

This free training is segmented into several separate lessons:

  1. Overview (1:21)
  2. Is APEX Secure (1:13)
  3. Controlling Access (0:57)
  4. Authentication Schemes (3:08)
  5. Conditions vs Authorization (12:45)
  6. Protect the Ends (12:45)
  7. Propagate the WHERE (3:47) (click on video below)
  8. Session State Protection (8:44)
  9. Other Session State Protection (3:24)
  10. SQL Injection (5:16)
  11. Cross Site Scripting (9:05)

Date: Sep 27, 2012

Want more? April 10 INTERMEDIATE APEX Training with Expert Tyson Jouglet!

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.


Propagate the WHERE

7. Securing Oracle APEX – Propagate the WHERE


>> Dan:  The first is to propagate your WHERE clause. A WHERE clause that I added to that report page was not enough. As you saw, all that was doing was creating links and links are means to the end, not the end itself. 




What we’re actually going to is a page to view that particular order and work with it. What we would need to do is propagate that same WHERE clause over that page or everywhere else in the apps for that matter. One of the ways we can do that in APEX is using a runtime WHERE clause. This is actually really tedious but it’s far safer than not using it. 


Another way is to use VPD. VPD unfortunately requires the Enterprise edition of the database. So if you have that edition there’s some more work involved in getting this set up but it’s definitely a step in the right direction. 




Let’s take a look at the runtime WHERE clause. Unfortunately, we don’t have enough time to demo the Enterprise edition feature of the VPD but we’ll take a look at a runtime WHERE clause. 




I’m going to go back to orders. 




We’ll drill in here. We’ll take a look at that predicate that I added here. This one. I limited the rows which you’ll be able to see. 


Now what you have to do is bring that same predicate everywhere else you need this level of protection. When we drill down on an order, we go over to page 29, we’ll edit this. And here’s the fetch row process. I’m going to go in and focus on where it says WHERE clause and we paste it in here. You don’t actually put the WHERE in it. It’s already here. 


So app user =. And just like we had before, we’ll apply that change and now equally, if not more important, we need to do the same thing to the actual processes that do the processing. 




That’s one, two, three different places we needed to propagate this WHERE clause. Not only now it is in four places, it must be maintained in four places. You can see what I mean by tedious, right? 


Also, there’s a chance you may forget to do this, which of course would leave you vulnerable. 




Let’s give this a shot now. We start with 7. 




This one brought back no data. 




The user should be able to see order 7. 




We have to modify this a little bit to pick up on a username appropriately. I’d say I couldn’t implement this here unfortunately. Let me try just removing the O. 




Okay. There we go. Alright. Sorry about that. 


Now we’re able to view 7. But watch what happens if we come and manipulate the URL this time. If I try to say view 2 again. 




It doesn’t work. No data found. That’s propagating the WHERE clause. I’m choosing that runtime WHERE clause and that’s why that exists.


Copyright 2017

Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!