Securing Vulnerability Exploits with Apex – Part 3

For my final security blog I will be looking at SQL injection and cross site scripting, both of which should always be considered when developing a web based application. Apex has in-built functionality to prevent these attacks and the following best practices will allow you to fully utilise these solutions.

Part 3 – SQL Injection and Cross Site Scripting

SQL injection enables user input from an application to change an application’s underlying SQL. Cross site scripting (also referred to as XSS) is an attack that takes advantage of pages with dynamically generated HTML and Javascript. Rendered content in an application can be altered to steal data and session credentials.

This blog will highlight the main areas of an application that are at risk of SQL injection or cross site scripting and will provide the Apex based solution for each problem.

SQL Injection – Substitution Variables

Problem

The variable substitution syntax in Apex (e.g. &P1_NAME) can be used to access the values of page items. This is useful when dynamically displaying HTML output but can be dangerous when used in SQL or PL/SQL code.

Solution

Within SQL and PL/SQL code, bind variables should be used instead (e.g. :P1_NAME). Bind variables will always treat data as ‘flat’ data and never mistake it for code. This prevents malicious users from altering item values to affect any underlying SQL queries.

SQL Injection – PL/SQL Function returning SQL query

Problem

Sometimes it is necessary to dynamically build a SQL query for items or reports in Apex. If these dynamic queries contain bind variables that are appended on to the SQL then any SQL passed as a bind variable can alter the query, causing undesired results.

e.g. A query implementing a concatenated bind variable:
RETURN(‘SELECT * FROM EMPLOYEES WHERE EMPLOYEE_NAME =’’’||:P1_NAME||’’’’);

If a bind variable of ‘ OR 1=1 – is entered, then the query becomes:
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_NAME = ‘ ‘ OR 1=1 –‘

Returning all the details from the employees table.

Solution

Bind variables should always be included within the dynamic SQL string rather than concatenated on before execution. This ensures that the security of the bind variable is enforced and the query cannot be altered.

SQL Injection – Cursor Variables

Problem

In the same way as the above function returning a dynamic SQL query, a cursor which uses bind variables to construct a query before execution can be altered using SQL injection.

Solution

All cursors using bind variables should also incorporate the USING clause to specify a list of bind arguments to be used at runtime. This causes the input to be escaped and SQL injection attacks prevented.

SQL Injection – Apex Collections

Problem

One popular method of creating Apex collections is to use the CREATE_COLLECTION_FROM_QUERY function which accepts a dynamic SQL query. Again, if bind variables are used to construct the SQL string before execution then it could be altered using SQL injection.

Solution

The CREATE_COLLECTION_FROM_QUERY_B and CREATE_COLLECTION_FROM_QUERYB2 functions accept bind variables which are escaped, preventing SQL injection.

XSS – Report Columns

Problem

Apex Classic and Interactive Reports use a Standard Report Column type to allow HTML and Javascript to be rendered by the browser from a report query. If the data in these report columns comes from a form that users can alter, then malicious users can enter HTML and Javascript into the form to have it run when the report is displayed.

Solution

HTF.ESCAPE_SC and APEX_JAVASCRIPT.ESCAPE functions should be used to escape any data that can be entered by users that is used in a Standard Report Column.

XSS – HTP Output

Problem

Some applications use HTP.P to dynamically generate HTML within a page or create custom forms using APEX_ITEM. If data displayed in this way is editable by users within the application then a malicious user could enter HTML or Javascript into the database to exploit the HTP.P function.

Solution

The HTP.PS function should be used to escape any data from the database. This will automatically escape the <,>,”,& characters.

Author: Craig Sykes 

Job Title: Senior Oracle Development Consultant

Bio: Craig is a Senior Development Consultant at DSP-Explorer. Craig has an MSc in Computing Science and is an experienced software engineer, utilising development tools such as PL/SQL and APEX to provide bespoke ERP software to both UK and international businesses. Craig has experience developing solutions to connect Oracle systems to a wide range of existing external applications within business environments.