Session State Protection in Detail

Session State Protection (SSP) can be confusing for new APEX developers and also difficult for senior APEX developers trying to explain it. This article will cover SSP in detail.

What is SSP? 

SSP is used to prevent URL tampering. URL tampering is when a malicious user modifies URL parameters to access data that they may not have access to. For example if you have a Master Detail setup for the EMP table (i.e a report with edit links to a form) each link will look something like this: http://fcapex42.clarifit.com:8894/apex/f?p=211:11:14916146169058::::P11_EMPNO:7698. If you restricted the report to employees in your current department you can easily change the EMPNO id and change it to another employee in another department. Even though the intention of the application was to “restrict” users to only edit people within their own department a malicious or curious user could start editing all the employees.

SSP prevents URL tampering by applying a checksum to the end of the URL. The new URL looks something like: http://fcapex42.clarifit.com:8894/apex/f?p=211:11:14916146169058::::P11_EMPNO:7698&cs=3358AAB32787C2A2B294CE934D50FD0C3. If a user now tries to modify the EMPNO id they will get an error in APEX as the URL won't have the correct checksum.

Applying SSP

To add SSP at the page level (technically called Page Access Protection at the page level) edit the page and scroll down to the Security section. Select Arguments Must Have Checksum for the Page Access Protection option.

Once that is added you can modify the page items you want SSP to be applied to. Edit a page item and scroll down to the Security section. Select an option for Session State Protection (note: click on the help link to find the differences between the various options; I usually use Checksum Required - Session Level).

To modify a page and all its page items at the same time go to Shared Components > Session State Protection > Page Item and click on a page link.

Two Layered Approach: Page vs Page Items

The easiest way to think of SSP in APEX is that it’s a two layered approach. First you must define which pages (not page items) require a checksum applied to it when passing in URL parameters. The second part is to define which page items require a checksum when set from the URL. You can’t do the later with out the former (i.e. if you just define a set of page items to have SSP it won’t work) but you can have pages with SSP without any page items with SSP.

The two layered approach is where most people get confused. If you apply SSP at the page level, shouldn’t all items be protected? At first glance the answer is “yes”. But the actual answer is no. The following example highlights why both “layers” are required.

Suppose we only apply Page Access Protection for the EMP form page (let’s say P11). If I clicked on a link to edit an employee it would add the checksum. Initially it looks as if everything is ok. Now suppose we have another page, P20, that doesn’t have Page Access Protection enabled. We could actually set P11_EMPNO from P20. The URL would look like this: http://fcapex42.clarifit.com:8894/apex/f?p=211:20:14916146169058::::P11_EMPNO:7839 Most people forget that you can set page items from any page, i.e. you’re not restricted to only setting items for the current page that you’re accessing.

You could also set any of the page items via an AJAX call (since none of them have SSP applied to them). Either way, just applying page access protection isn't enough.

When Not to Apply SSP

If you haven’t implemented SSP in your application you should really look at doing so. Before you apply it to all items it’s important to note that any items that can be set via an AJAX call can not have SSP enabled for them. The most usual case of this is cascading LOVs (select a department, then a list of employees gets refreshed with all the employees that belong to the selected department).

The reason why you can’t have SSP item items that are set via AJAX is that AJAX uses JavaScript to build the URL. Since JavaScript code is downloaded and runs on the end user’s machine it is not deemed to be secure. So if you had your checksum code in a JavaScript file a malicious user to easily reverse engineer it and apply checksums for any item they wanted to.

Conclusion

SSP is a great feature to quickly help secure your application. It’s important to remember that SSP only prevents URL tampering. Nothing more, nothing less. It’s a common mistake by developers, and managers alike, that just applying SSP means that they’ve locked down and secured an application. It’s just one of many steps to help protect your application.

APEXposed Down Under

In a few weeks David Peake (Oracle APEX Product Manager and native Australian) and I will be heading to New Zealand and Australia for ODTUG APEXposed.

The conference will offer talks that will be relevant for all levels of APEX developers ranging from beginners (and those interested in learning about the product) all the way to advanced APEX development. Of course if there isn’t something covered you can always bring your questions and ask me or David during the Q&A panel. You can see the entire agenda here.

This will be a two day event in both Auckland (Nov 5-6) and then we’re going to do it all over again in Melbourne on (Nov 8-9). If you haven't already registered you can still take advantage of the early bird discount until Oct 24th. 
 
I look forward to seeing everyone there.

Martin

PL/SQL Exceptions Propagation during Variable Declaration

It's always good to know how any language handles and propagates exceptions, Oracle PL/SQL being no different. They're plenty of examples online about raising and handling exceptions on the web, but one thing you may not have realized is how PL/SQL propagates exceptions that occur in the variable declaration section of a procedure.

In the first example I created a procedure that has a variable, l_var, which can handle one character. As expected, when I assign more then one character an exception is raised and is propagated to the EXCEPTION block of the procedure.

SQL> CREATE OR REPLACE PROCEDURE sp_test(p_var in varchar2)
  2  AS
  3    l_var VARCHAR2(1);
  4  BEGIN
  5    dbms_output.put_line('***START***');
  6    l_var := 'abc';
  7  exception
  8    WHEN others THEN
  9      dbms_output.put_line('***Exception***');
 10      raise;
 11  END sp_test;
 12  /

Procedure created.

SQL> exec sp_test(p_var => 'abc');
***START***
***Exception***
BEGIN sp_test(p_var => 'abc'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "ODTUG.SP_TEST", line 10
ORA-06512: at line 1

In the next example, instead of assigning the value in the main block of code I assigned the value in the declaration section. You'll notice that the procedure doesn't even get to the "START" line nor is the exception handled in the procedure's exception block. Instead the exception is propagated to the calling process right away.

SQL> CREATE OR REPLACE PROCEDURE sp_test(p_var in varchar2)
  2  AS
  3    l_var VARCHAR2(1) := p_var;
  4  BEGIN
  5    dbms_output.put_line('***START***');
  6  exception
  7    WHEN others THEN
  8      dbms_output.put_line('***Exception***');
  9      raise;
 10  END sp_test;
 11  /

Procedure created.

SQL> exec sp_test(p_var => 'abc');
BEGIN sp_test(p_var => 'abc'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "ODTUG.SP_TEST", line 3
ORA-06512: at line 1

Before you go and change any of your existing code based on this article, I'm not saying that you should avoid defining variables in the declaration section of a procedure. Instead, just be aware of how the exception is propagated. This can be useful to know if your local variable is assigned to an input parameter. In that case you may want to assign the local variable in the main block of code rather then in the variable declaration section.

For documentation of how PL/SQL propagates exceptions raised in declarations go here. If you haven't already done so, I'd recommend reading the entire PL/SQL Error Handling documentation.

APEX_ADMINISTRATOR_ROLE

The APEX Dictionary is a set of views that describe all the different objects of an APEX application. They are extremely useful when trying to compare objects or using the metadata in your application. One example, which I recently wrote about, is to use a view from the dictionary to leverage the APEX build options in your PL/SQL code.

By default the views will only allow you to see information for applications, and their objects, that are linked to your current schema (i.e. the application's parsing schema must be the same as your schema). For older versions of APEX the only way to view all the applications in the entire database was to either log in as SYSTEM or SYS.

In newer versions of APEX (I think it was released in APEX 4.1) there's a new database role called APEX_ADMINISTRATOR_ROLE. This role allows for non SYSTEM/SYS users to view all the APEX applications in your database. It's a very useful thing to have if you want to run your own scripts to check for things like standards, security audits, performance, etc.

One example where this role can be very useful is to monitor for slow running pages in all your applications across the entire database (rather than just ones in a particular schema). The following query, executed by a user that has the APEX_ADMINISTRATOR_ROLE, will show all the slow pages in the past two days:

SELECT *
FROM apex_workspace_activity_log
WHERE trunc(view_date) >= trunc(SYSDATE) - 1 -- Just look at the past 2 days
  AND elapsed_time > 1; -- 1 = 1 second

This is just one of many examples where the APEX_ADMINISTRATOR_ROLE can be
useful for system wide level analysis.

The APEX_ADMINISTRATOR_ROLE also allows you to run procedures in the APEX_INSTANCE_ADMIN package.

How to Send/Upload a CLOB from the Browser to APEX via AJAX

Today Alistair Lang asked on Twitter "how do I pass in a CLOB to an on-demand process using AJAX in APEX?". I had this same questions a few months ago when I was working on uploading files using AJAX into APEX.

It turns out you can't use the standard addParam APEX JavaScript method (hopefully this will change in 4.2). Instead you need call a different function which will store the CLOB into a special APEX collection then process the CLOB from that collection. Here's a breakdown of what needs to happen:

- Send the CLOB from the browser to APEX. It will be stored in the CLOB001 column in the collection "CLOB_CONTENT".
- Once the CLOB is sent to APEX call your On Demand process (i.e. AJAX request) to run some PL/SQL code. This PL/SQL code will need to retrieve the CLOB value from the collection

Here's an example

On Demand Process: On your page create an On Demand process called "MY_PROCESS". In the code enter the following:

DECLARE
  l_clob CLOB;
BEGIN
  SELECT clob001 
  INTO l_clob
  FROM apex_collections 
  WHERE collection_name = 'CLOB_CONTENT';
  
  -- Now you can process the CLOB using l_clob
END;

JavaScript Code: This can be stored either in a Dynamic Action or custom JS code:

/**
 * Code to run once the upload is done
 * 
 * Clob is now accessible in the apex_collections view:
 * SELECT collection_name, seq_id, clob001 FROM apex_collections 
 * WHERE collection_name = 'CLOB_CONTENT';
 *  - Note: The collection name "CLOB_CONTENT" is not modifiable
 * 
 * Use this function to make an AJAX request to trigger 
 * an On Demand Process (i.e. run some PL/SQL code)
 */
function clubUploadDone(){
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=MY_PROCESS',$v('pFlowStepId'));
  //Optional: pass some additional values get.addParam('x01','some data');
  gReturn = get.get();
}

/**
 * Send clob to APEX (will be stored in the apex_collection "CLOB_CONTENT"
 */
var clobObj = new apex.ajax.clob(
  //Callback funciton. only process CLOB once it's finished uploading to APEX
  function(p){
    if (p.readyState == 4){
      clubUploadDone();
    }
  });
  
clobObj._set('Put clob content here'); //Sends the data to Oracle/APEX collection

It's important to note that there is only one area where the CLOB data is stored so each time you send a new CLOB it will erase the older value in the collection. If you're sending multiple CLOBS sequentially you need to handle it accordingly. A good example of this is if you're uploading multiple files via a drag & drop interface.