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.
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.
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.
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.
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.
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.
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.
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.
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.
The CREATE_COLLECTION_FROM_QUERY_B and CREATE_COLLECTION_FROM_QUERYB2 functions accept bind variables which are escaped, preventing SQL injection.
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.