As Published In
Oracle Magazine
July/August 2009

DEVELOPER: Browser-Based


Developing Secure Applications

By David Peake

Defend your Oracle Application Express applications against unauthorized use.

Security must be designed into applications from the outset, starting with database design; continuing through application design, development, and testing; and finishing with implementation and training. This column outlines the measures you should incorporate to develop secure applications with Oracle Application Express.

The column’s examples all use the sample application on the hosted instance of Oracle Application Express at apex.oracle.com. (You must request a free workspace to use the hosted instance.) After getting a brief overview of authentication and authorization in Oracle Application Express, you’ll learn how to set session timeouts and manage session state, protect your applications from SQL injection, and defend against cross-site scripting.

Authentication and Authorization

Authentication and authorization are the basic mechanisms for securing an Oracle Application Express application. Authentication establishes that a user is allowed to log in to the application; authorization implements various access levels within the application.

Oracle Application Express provides several preconfigured authentication schemes, including LDAP credentials, Oracle Application Server Single Sign-On, and Oracle Application Express credentials. You can also define custom authentication by using the built-in page sentry or custom sentry functions. Authenticated users’ session details are stored in the database and verified with each page request and submission.

Authorization schemes, once defined, can be used to control access to pages, buttons, tabs, items, and so on. It is important to add authorization to all restricted pages, not just to the navigational controls used to access those pages.

Managing Session Timeouts

One essential way to harden your application is to ensure that users are automatically timed out of the current session after a period of time or if they are idle for a certain period of time. Timeouts limit the ability of unauthorized people to access the open application from a valid user’s unattended system.

Follow these steps to define, in the Sample Application, a public Session Timeout page to which timed-out users are automatically sent:

1. Select the Sample Application from Application Builder. Click Create Page . Select Blank Page , and click Next twice.
2. Enter Session Timeout for Name , click Next twice, and then click Finish .
3. Note the number of the new page, which you’ll need for a later step. Click Edit Page .
4. Under Page, click Session Timeout for Page Name . Click Security , select Page is Public for Authentication , and click Apply Changes .
5. In the Regions area, click the Create icon. Select HTML , and click Next . Select HTML again, and click Next .
6. Enter Idle Timeout for Title , and click Next . In the Enter HTML Text Region Source box, enter dle time has been exceeded and your session has been expired . Click Next .
7. Select Request = Expression 1 for Condition Type , enter IDLE in the Expression 1 box, and click Create Region .
8. Repeat Step 5.
9. Enter Maximum Session Timeout for Title , and click Next . In the Enter HTML Text Region Source box, enter Total session time has been exceeded and your session has been expired . Click Next .
10. Select Request = Expression 1 for Condition Type , enter MAXIMUM in the Expression 1 box, and click Create Region .
11. Click Shared Components . Under Security , click Edit Security Attributes .
12. Click Session Timeout , and select 1 Minute for Maximum Session Length in Seconds . Using the application ID in place of &APP_ID and the actual page number from Step 3 in place of xx , enter f?p= &APP_ID:xx:0:MAXIMUM for Session Timeout URL , select 10 Seconds for Maximum Session Idle Time in Seconds , enter f?p= &APP_ID:xx:0:IDLE for Idle Timeout URL , and click Apply Changes .

Now you can test the timeout. Run the application, but do nothing in the application’s user interface for at least 15 seconds. The Session Timeout page should appear, with the idle message shown in Figure 1.

 

figure 1
Figure 1: Idle timeout


Log back in to the application, by clicking the Logout link at the top of the screen. Navigate around the application continuously for more than one minute. The Session Timeout page should appear, with the maximum session message.

Go back to Application Builder, and follow Steps 11 and 12 above, this time resetting the session timeout variables to appropriate values.

Managing Session State

Oracle Application Express persists session state in database tables, because it’s more secure to store it on the server than on the client. Developers can query the session state stored by Oracle Application Express applications by using Application Builder and built-in monitoring pages. Developers and administrators can access session state for any application in the workspaces for which they are authenticated. Here are a few best practices for developers:

 

  • As a standard part of implementing program control flow, clear the session state of unneeded values, using clear-cache page processes or clear-cache directives in the URLs used to navigate to pages. 
  • Use password-page item types that do not save state. This prevents the entered passwords from being saved in session state tables in the database. 
  • When sensitive data must persist in a session, save it in Oracle Application Express session state tables in encrypted form. To use this feature, set the page item’s Store value encrypted in session state attribute to Yes . Encrypted stored values are automatically decrypted when read.


Session state protection (SSP). Drill-down reports often include the detail record’s identifier in the URL to call the detail page. If you don’t protect the URL, users can easily change that identifier and potentially access detail records they do not have rights to access. Savvy users can also try to change the page being called, by altering the URL. Built-in SSP functionality in Oracle Application Express prevents hackers from modifying the URLs within your application. When enabled, SSP uses checksums positioned in the URLs to prevent URL tampering and unauthorized access to and alteration of session state.

Avoiding SQL Injection

SQL injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements. Attackers trick the SQL engine into executing unintended commands, by supplying specially crafted string input, thereby gaining unauthorized access to a database to view or manipulate restricted data. String literals that are incorrectly validated or not validated are concatenated into a dynamic SQL statement and interpreted as code by the SQL engine. To immunize your code against SQL injection attacks, you must use bind arguments (either automatically with static SQL or explicitly with dynamic SQL) or validate and sanitize all input concatenated into dynamic SQL.

Strategies that can help you safeguard against—or at least mitigate the impact of—SQL injection attacks include the following:

 

  • Ensure that all excess database privileges are revoked and that only routines that are intended for end-user access are exposed. This does not entirely eliminate SQL injection vulnerabilities, but it does help mitigate the impact of any attacks. 
  • Dynamic SQL built with concatenated input values presents the easiest entry point for SQL injection, so either use static SQL, when all the identifiers are known at code compilation time, or use bind variables. 
  • Take advantage of the Oracle-supplied DBMS_ASSERT package, which contains functions you can use to sanitize user input in order to guard against SQL injection in applications that use dynamic SQL built with concatenated input values that can’t use bind variables.


Using bind variables. The following example demonstrates how dynamic SQL statements in the WHERE condition can be attacked and then shows how to remove the threat by using bind variables. First, follow these steps to simulate a SQL injection attack:

1. In Application Builder, click Create Page , select Blank Page as the page type, and click Next twice.
2. Enter SQL Injection for Name , click Next twice, and click Finish . Note the page number for use in later steps. These examples use page 21; be sure to substitute the correct page number for your application instance.
3. Click Edit Page .
4. In the Regions area, click the Create icon, select HTML , click Next , select HTML again, and click Next .
5. For Title enter Search Employee Number , click Next , and click Create Region .
6. In the Items area, click the Create icon, select Text as the Item Type , click Next twice, enter P21_EMPNO for Item Name , and for Region select Search Employee Number . Click Next twice, and click Create Item .
7. In the Buttons area, click the Create icon, select Search Employee Number , and click Next . Select Create a button in a region position , and click Next . Enter Search for Button Name , and click Next . Click Next twice, enter 21 for Branch to page (when button pressed) , and click Create Button .
8. In the Regions area, click the Create icon, select PL/SQL Dynamic Content , click Next , enter Employee Details for Title , and click Next .
9. Enter the following for PL/SQL Source :

 

FOR c1 IN (SELECT ename FROM emp 
WHERE empno = &P21_EMPNO.) LOOP
   htp.p('<br>Employee Name:'||c1.ename);
END LOOP;


10. Click Next , select Value of Item in Expression 1 Is NOT NULL for Condition Type , enter P21_EMPNO for Expression 1 , and click Create Region .

Run the SQL Injection page. Enter 7521 for Empno , and click Search . The employee name Ward is displayed. Now enter 0 or 1=1 for Empno and click Search . Because of the SQL injection, all the employee names are listed, as shown in Figure 2.

 

figure 2
Figure 2: SQL injection


Now change the PL/SQL to use a bind variable:

1. Navigate to Application Builder for page 21, and in the Regions area, click Employee Details .
2. For Region Source , change

 

 
WHERE empno = &P21_EMPNO. 
to 
WHERE empno = :P21_EMPNO. 


3. Make sure the Do not validate PL/SQL code (parse PL/SQL code at runtime only) box is checked. Click Apply Changes .

Run the SQL Injection page. Enter 7521 for Empno , and click Search . The employee name Ward is displayed. Enter 0 or 1=1 for Empno , and click Search . This time you should see an error message, because the PL/SQL uses a bind variable. 

Next Steps


 READ more Browser-Based

LEARN more about
Oracle Application Express
Oracle Application Express Application Builder User's Guide

 DOWNLOAD Oracle Application Express

 VISIT the Oracle Application Express Forum

Using the DBMS_ASSERT function. The following example demonstrates how dynamic SQL statements in a FROM clause can be attacked and then shows how to remove the threat when you can’t use bind variables. Follow these steps to simulate a SQL injection attack:

1. In Application Builder, navigate to your SQL Injection page. (This example continues to use page 21.)
2. In the Items area, click the Create icon, select Text , and click Next twice. Enter P21_TABLE_NAME for Item Name , select Search Employee Number for Region , click Next twice, and click Create Item .
3. In the Regions area, click Employee Details . For Region Source , change FROM emp to FROM &P21_TABLE_NAME . Make sure the Do not validate PL/SQL code (parse PL/SQL code at runtime only) box is checked. Click Apply Changes .

Run page 21 of the application. Enter 7521 for Empno , enter EMP for Table Name , and click Search . The employee name Ward is displayed. Now enter EMP where 1=1 union all select ename from emp for Table Name and click Search . The SQL injection causes all the employee names to be listed.

In order to prevent SQL injection, change the PL/SQL to use the DBMS_ASSERT function:

1. In Application Builder, navigate to the SQL Injection page. In the Items area, click the Create icon, select Hidden , and click Next . Select Hidden for Hidden Item Type , and click Next . Enter P21_VALID_TABLE for Item Name , select Search Employee Number for Region , click Next twice, and click Create Item .
2. In the Validations area, click the Create icon, select Item level validation , and click Next . Select Search Employee Number : 20. P21_TABLE_NAME (Table Name) , and click Next . Select PL/SQL , and click Next . Select Function Returning Boolean , and click Next . Enter Assert Table Name for Validation Name , and click Next .
3. Enter the following in the Validation box:

 

BEGIN
   IF dbms_assert.simple_sql_name(:P21_TABLE_NAME) = :P21_TABLE_NAME THEN
   :P21_VALID_TABLE := :P21_TABLE_NAME;
    RETURN true;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    :P21_VALID_TABLE := NULL;
    RETURN false;
END; 


4. Enter Invalid Table Name . in the Error Message box, and click Next . Select SEARCH for When Button Pressed , and click Create .
5. In the Regions area, click Employee Details . In the Region Source box, change FROM &P21_TABLE_NAME to FROM &P21_VALID_TABLE , making sure that the Do not validate PL/SQL code (parse PL/SQL code at runtime only) box is checked. Click Apply Changes .

Run the SQL Injection page. Enter 7521 for Empno , enter EMP for Table Name , and click Search . The employee name Ward is displayed. Now enter EMP where 1=1 union all select ename from emp for Table Name and click Search . Because you used the DBMS_ASSERT function within the validation, a validation error message is displayed and the query returns the Oracle Database error message shown in Figure 3.

 

figure 3
Figure 3: Validation error message


 


David Peake (david.peake@oracle.com) is a principal product manager in Oracle’s Server Technologies division. He has been with Oracle since 1993.

Send us your comments