Avoiding SQL Injection in PL/SQL (Oracle10g)

SQL injection in a technique by which users attain unauthorized access to a database for the purpose of querying or do updates on secured data for the wrong intentions.

In order to avoid this, an application developer should gain knowledge of SQL injection vulnerabilities and guard against them. These techniques differ, but a single vulnerability which is in general exploited, is the invalidation of string input and its concatenation into a dynamic SQL statement.

SQL injection attacks can be classified mainly as Statement Modification or Statement Injection. In the statement modification technique, a dynamic SQL statement can be deliberately altered so that it executes in a way that is different from its original intent. In general the user gets access to unauthorized data by altering the WHERE clause of a query or through insertion of a UNION ALL clause.

Another classic example is skipping password authentication by changing a WHERE clause to be always TRUE. For example supposing a procedure is created that authenticates password by validating the user name and password inputs against data in a stored table and this is done in the form of a dynamically constructed statement, a malicious user can easily exploit the concatenation vulnerability as shown below.

If the SQL statement after being built in the form

SELECT COUNT(*) FROM USER_PWD_DETL
WHERE USER_NAME =’BKS’
AND PASSWORD = ‘CTX123’

then the password statement can be entered in the form as

X’ OR ‘X’ = ‘X
such that the OR condition in the WHERE clause evaluates always to TRUE.

Statement Injection technique is whereby a user appends one or more extra SQL statements to a statement that is generated dynamically. This occurs usually in anonymous PL/SQL blocks. An example for this is the case when a Web form gets user prompted user name and password and on clicking Submit a PL/SQL procedure is invoked which concatenates the user name and password into an anonymous block and executes the block for authenticating the user.

For example if the final query built is of form

SELECT COUNT(*) FROM USER_PWD_DETL
WHERE USER_NAME =’BKS’
AND PASSWORD = ‘CTX123’

Then instead of entering CTX123 as password the user can enter password as
CTX123’);DELETE FROM HR_DTLS WHERE ‘X’ = ‘X

To guard against SQL injection while using dynamic SQL in PL/SQL application the input text has to be thoroughly validated. This can be done by the usage of techniques such as using Bind variables or validation checks against SQL Injection.

Usage of Bind variables in dynamic SQL not only causes improvement in performance but also renders the code immune to SQL injection attacks. Further, concatenation in SQL statements can be avoided and users can be prevented from altering the code. This is because Oracle database uses the value of the bind variable totally and does not infer from its contents.

Similarly a program should always validate user’s input in a thorough manner. For example if the user is entering a employee number for a DELETE statement then the validity of the employee number must be checked by query before the DELETE statement is executed.

A less familiar SQL injection technique is the usage of NLS session parameters to change or inject SQL statements. When a datetime or numeric value is concatenated into a text belonging to a dynamic SQL statement it must be first converted into a VARCHAR2 type. This data type conversion is dependent on the setting of NLS for the database session where the statement is executed.

The conversion of datetime values makes use of format models given in the parameters related to NLS. One of the datetime format models consists of “text”. This text is copied into the result of conversion and may hence be exploited. If SYSDATE depends on the value of NLS_DATE_FORMAT and is used in a SELECT statement that uses concatenation to build WHERE clause, then the statement is vulnerable to modification.

For example, in a session, NLS_DATE_FORMAT is ‘DD-MON-YYYY’;
And the query that is dynamically built is of form

SELECT EMP_NAME FROM EMP_DETLS
WHERE EMP_NAME =’ANDY’
AND JOIN_DATE > ’20-MAY-2009’
Here, there is no statement modification.

But the session can be altered by below statement
ALTER SESSION SET NLS_DATE_FORMAT = '"'' OR EMP_NAME =''MANDY"'

This results in query being built as

SELECT EMP_NAME FROM EMP_DETLS
WHERE EMP_NAME =’ANDY’
AND JOIN_DATE >’ ’ OR EMP_NAME = ‘MANDY’

For guarding against this injection technique, explicit format models should be used. If Datetime and numeric values are concatenated into a SQL or PL/SQL statement’s text and they cannot be passed as bind variables they have to be converted into text using unambiguous format models that do not depend on the values of NLS parameters of the executing session.

It is thus mandatory that an application developer should have knowledge pertaining to SQL injection techniques that exploit the vulnerability of a string input not being correctly validated and used in concatenation in a dynamic SQL statement. Also it is necessary to gain knowledge and deploy the necessary guarding techniques to help develop a secure application.

| An Introduction to Oracle’s JD Edwards EnterpriseOne Tools | Avoiding-sql-injection-in-pl-sql-oracle1og | Developing PL/SQL Web Applications in Oracle10g: An Overview | Identifying Rows by Address in Oracle 10g | Introducing Oracle OLAP option to Oracle Database 11g | Some Exciting New Features of Oracle11g | The Benefits of Partition for Improved Performance in Oracle Database 11g | Understanding Automatic SQL Tuning in Oracle10g | Using XMLTextReader Class for Better Access to XML |