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
then the password statement can be entered in the form as
X = X
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
of entering CTX123 as password the user can enter password as
Similarly a program should always validate users 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.
in a session, NLS_DATE_FORMAT is DD-MON-YYYY;
But the session
can be altered by below statement
This results in query being built as
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 statements 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 Oracles 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 |
Visit XML Training Material Guide Homepage
Amazon and the Amazon logo are trademarks of Amazon.com, Inc. or its affiliates.Copyright - © 2004 - 2020 - All Rights Reserved.