Sunday, November 10, 2013

APEX Security Holes - Page Items to Submit Property (Part 1)

I've discovered some interesting things about APEX checksum and item session state. I'll show you some tricks (security holes) and how to avoid them.

Suppose you are authorized to see only departments 10 and 30. Your query should look like this:

  SELECT deptno, dname 
    FROM dept 
   WHERE deptno in (10,30)

and the report page:

where link "View Employees" points to page where you can see all employees in selected department. SQL on that page should look like this:

  SELECT ename    as "Name"
       , job      as "Job"
       , hiredate as "Hire Date"
       , sal      as "Salary"
       , deptno
   FROM emp
  WHERE deptno = :P2_DEPTNO

On this page checksum is turned on so you can't manipulate with URL and change department number:

The link that points to second page should look like this:


But if you accidently put item that is used for filtering employees (in my case P2_DEPTNO) to "Page Items To Submit" property of report region there is an easy way to change it and to see employees that you are not supposed to see.

If you clicked on Accounting department on first page you should see 3 employees:

After that, you just have to open your favorite web debug tool, in my case FireBug, and run following code (where R12587121803287243 is ID of your report region) that sets value of item P2_DEPTNO to 20 and refreshes report region:

Voilà! Now you should see employees in department 20:

The refresh of region can be done in other ways. For example, by clicking on pagination.
To see if report region have some items to submit in report properties you can execute this line in FireBug console:


When you change session state protection property of item P2_DEPTNO to "Checksum Required - ..." refresh of report will not work but neither the pagination on that report.

So watch out with property "Page Items to Submit"!!

In next post I'll show you how can you easily change item session state on some other ways.

Tested on APEX

Monday, September 16, 2013

Presentation on Croatian Oracle User Group conference (HROUG 2013)

This year I'll have presentation on HROUG (Croation Oracle User Group) conference that will take place in Rovinj from October 15th - 19th 2013. There will be some great presentations held by Tomas Kyte, Deneš Kubiček, Jože Senegačnik, Melanie Cafrrey and many more...

My presentation is about hybrid mobile applications in APEX.

I'm looking forward to it!

Monday, July 8, 2013

APEX static files bug

I noticed a bug regarding uploading static files in APEX. When you try to upload file with same name as existing one you'll get error message that static file name already exists. But if you query apex_application_files view you can see that this file was uploaded (like workspace file). Funny fact is that when you go to static files page in APEX builder you won't see that "hidden" file.

This can lead to problems with static files in runtime because APEX sometimes uses some of the "hidden" files and not the ones you see in APEX builder. To clean up this mess you should delete files manually.

One way to do it is to use APEX SQL Workshop, but it's also easy to do it from your favorite PL/SQL IDE. The only thing you have to do is to set workspace id (security_group_id).

    v_workspace_id apex_workspaces.workspace_id%type;
  select workspace_id
    into v_workspace_id
    from apex_workspaces w
   where workspace = upper(&p_workspace_name);

After that you can manage your static files in current session of IDE by using view apex_applicaton_files (or wwv_flow_files).

This was tested in APEX, but I know that same problem existed in earlier versions of APEX.

Tuesday, June 11, 2013

Show processing image on every submit action

There's an easy way to show processing image on each submit action in APEX.

Just create DA on global page (page 0) that fires on event "Before Page Submit" and in Action section choose "Execute JavaScript Code". The code that you should add is:


Tested on APEX version

Wednesday, June 5, 2013

Saving Values of Disabled Items to Session State

As you may know disabled items are not submitted in HTML forms (see It's same for APEX items, at least for ones disabled with dynamic actions or JavaScript/jQuery.

There's a simple workaround for this. You can create dynamic action on event Before Page Submit that executes JavaScript code that will remove HTML attribute disabled from all items on page:


If you want this to work on all pages, put this dynamic action on global page (page 0).

As I said, this will not work for text field items that have property Disabled set to Yes and Save Session State to No. To save this kind of text field items set property Save Session State to Yes. In this case input item will have HTML attribute readonly, instead of disabled.

Wednesday, May 8, 2013

Handling iframe call inside JS

There's an easy way to figure if your page is called inside iframe (e.g. using SkillBuilders Modal Page Plugin).
You can use simple JS snippet and put it inside Function and Global Variable Declaration of page properties:

After that you can use iFrameCall JS variable inside your page JS code, for example in dynamic actions for hiding some page elements (e.g. footer and header elements).

So, create dynamic action that fires on event Page Load and use your JS variable for condition:

The code of Execute JavaScript Code for hiding footer and header can look like this (if you have header and footer HTML tags in your template):

$('header').css('display', 'none');
$('footer').css('display', 'none');

Monday, February 18, 2013

APEX debug levels

From time to time I run into some new stuff about APEX. So few days ago I was investigating something about debug messages and figured it out that there are different levels of debug messages in APEX.

You probably know that as a sixth argument in APEX URL you can pass either YES or NO and that will turn on/off page debugging (if it's enabled in application properties). So the thing I discovered is that you can also pass LEVELn where n is between 1 and 9 and stands for level of debug detail to display. The value of YES is equal to LEVEL4.

If you take a look at apex_debug package specification you can see meaning of each level (although missing levels 3 and 7):

-- critical error
c_log_level_error        constant t_log_level := 1; 

-- less critical error
c_log_level_warn         constant t_log_level := 2; 

-- default level if debugging is enabled (e.g. used by apex_application.debug)
c_log_level_info         constant t_log_level := 4; 

-- application: messages when procedures/functions are entered
c_log_level_app_enter    constant t_log_level := 5; 

-- application: other messages within procedures/functions
c_log_level_app_trace    constant t_log_level := 6; 

-- apex engine: messages when procedures/functions are entered
c_log_level_engine_enter constant t_log_level := 8; 

-- apex engine: other messages within procedures/functions
c_log_level_engine_trace constant t_log_level := 9; 

You can set this level when adding debug messages manually using apex_debug.message procedure.

Monday, February 11, 2013

Closing Modal Page on button click

If you are using SkillBuilders Modal Page Plugin (version 2.0.0 that uses ColorBox) and want to close it dynamically on button click there is an easy way of doing this using the page button template (instead of creating DA on each modal page ).

Create a copy of your button template and add to it a CSS class, for example closeModal:

After that add JS that closes modal page (to the page template or JS file):

The final step is to change your button template to the newly created one. Also, check that your button Action property is defined as "Defined by Dynamic Action".

I've also created a plugin for this, so if somebody needs it, send me an email. But I think that using the above described approach is more convenient.