Developing Secure ApplicationsBy 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.
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.
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:
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:
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.
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.
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 .
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.
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.)
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 .
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 .
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.
David Peake (email@example.com) is a principal product manager in Oracle’s Server Technologies division. He has been with Oracle since 1993.