Apex Application Builder Timeout

Ever come across something you always mean to check out but never get around to it?

I had that recently using Apex. Every time I ran to lunch, did other things, watched the grass grow... I came back to find my Application Builder had timed out.

It's frustrating to have to log in again, and doubly frustrating to find you're back at Apex Home, not where ever you were.

It took all of 30 seconds to find the correct setting, but I thought I'd share anyway for those who may be newer to the tool.

First log in to your INTERNAL workspace, or visit
http://localhost:7777/pls/apex/apex_admin
in lieu of your usual
http://localhost:7777/pls/apex/f?p=...

Under Home -> Manage Service -> Security, there is a section on Session Timeout.

I've modified my maximum session idle time to be the same as my maximum session length. When working from home, this is more than suitable. In a work environment (hmm, my home should be included in this category), you ought to negotiate with the powers that be.

There are similar settings at Application level through Shared Components -> Security. And as with most settings, it may be modified programatically at runtime using Apex_Util.

If you are having a non-mathematical day, you can use the following SQL query to work out how many seconds you want - 86400 is the number of seconds in a day. This query represents midnight plus however many seconds.

SAGE@sw10g> select trunc(sysdate) + 3600/86400 from dual;

TRUNC(SYSDATE)+3600
-------------------
27-01-2010 01:00:00

1 row selected.

Injecting SQL into the New Year

No Oracle blog would be complete without a comment regarding SQL Injection.

This is a way to exploit a potential security vulnerability in the database layer of your application. Depending on the programming language used, and the way your SQL is written - this vulnerability could extend from viewing rows you're not supposed to; to viewing data from other columns/tables; to dropping/manipulating other database objects.

There are some security gurus out there, and I'd suggest you read some work by either Pete Finnigan or Peter Lorenzen.

For now I'll give a brief demo on an example that could be demonstrated in Oracle Application Express.

Let's say you have a simple report region on the EMP table, with a field allowing you to search by employee name:

You may wish to define this report region as SQL Query (PL/SQL function body returning SQL query), which allows you to dynamically create the SQL  - a common requirement in many applications.

The correct way to define this SQL would be to use bind variables. The following image demonstrates a simple example of this (using the Oracle 10g q-quote mechanism)

Now if this SQL was modified slightly, we introduce the possibility of SQL injection.

 

For those not familiar with the q-quote syntax, it may read:


v_sql := v_sql||'WHERE UPPER(ename) LIKE '''||:P3_ENAME||'%''';


You see, if we performed the following search, we could illicitly obtain all the rows from the query:

This happens because our SQL actually forms the following:


SELECT empno, ename, job, hiredate from emp WHERE UPPER(ename) LIKE 'S' or 1=1--%'


Where the OR 1=1 ensures all rows are returned. So not only are you taking a performance hit, you are creating vulnerabilities in your application. Lorenzen's paper describes some other potential threats within Apex.

If the region used bind variables, the same search criteria would just return no rows.

A simple example completed.

I hope everyone enjoyed any time off they may have had over the new year, I certainly did!