You : “Deadly Injection ? Do we have such one ?”
“Yeah..Here I am.. And at your Service” says “SQL Injection”
So What is… SQL Injection?
SQL injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements, thereby gaining unauthorized access to a database in order to view or manipulate restricted data
SQL injection techniques differ, but they all exploit a single vulnerability: string input is not correctly validated and is concatenated into a dynamic SQL statement.
SQL injection attacks are classified as below:
3.Data Type Conversion
What can you do in Statement Modification?
Statement modification means deliberately altering a dynamic SQL statement so that it executes in a way unintended by the application developer. Typically, the user retrieves unauthorized data by changing the WHERE clause of a SELECT statement or by inserting a UNION ALL clause. The classic example of this technique is bypassing password authentication by making a WHERE clause always TRUE.
What can you do in Statement Injection
Statement injection means that a user appends one or more new SQL statements to a dynamic SQL statement. Anonymous PL/SQL blocks are vulnerable to this technique.
What can you do with Data Type Conversion
A less known SQL injection technique uses NLS session parameters to modify or inject SQL statements.
A datetime or numeric value that is concatenated into the text of a dynamic SQL statement must be converted to the VARCHAR2 data type. The conversion can be either implicit (when the value is an operand of the concatentation operator) or explicit (when the value is the argument of the TO_CHAR function). This data type conversion depends on the NLS settings of the database session that executes the dynamic SQL statement. The conversion of datetime values uses format models specified in the parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, or NLS_TIMESTAMP_TZ_FORMAT, depending on the particular datetime data type. The conversion of numeric values applies decimal and group separators specified in the parameter NLS_NUMERIC_CHARACTERS.
One datetime format model is “text”. The text is copied into the conversion result. For example, if the value of NLS_DATE_FORMAT is ‘”Month:” Month’, then in June, TO_CHAR(SYSDATE) returns ‘Month: June’
Enough of Injecting ! Let’s see the cure
Guarding Against SQL Injection
If you use dynamic SQL in your PL/SQL applications, you must check the input text to ensure that it is exactly what you expected. You can use the following techniques:
Using Bind Arguments to Guard Against SQL Injection
The most effective way to make your PL/SQL code invulnerable to SQL injection attacks is to use bind arguments. The database uses the values of bind arguments exclusively and does not interpret their contents in any way. (Bind arguments also improve performance.)
Using Validation Checks to Guard Against SQL Injection
Always have your program validate user input to ensure that it is what is intended. For example, if the user is passing a department number for a DELETE statement, check the validity of this department number by selecting from the departments table. Similarly, if a user enters the name of a table to be deleted, check that this table exists by selecting from the static data dictionary view ALL_TABLES
DBMS_ASSERT – Sanitize User Input to Help Prevent SQL Injection
The package contains a number of functions that can be used to sanitize user input and help to guard against SQL injection in applications that don’t use bind variables
CREATE OR REPLACE PROCEDURE raise_emp_salary (
amount NUMBER )
-- Check validity of column name that was given as input:
SELECT COLUMN_NAME INTO v_column
WHERE TABLE_NAME = 'EMPLOYEES'
AND COLUMN_NAME = emp_column;
sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE '
|| DBMS_ASSERT.ENQUOTE_NAME(v_column,FALSE) || ' = :2';
EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
-- If column name is valid:
IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('Salaries were updated for: '
|| emp_column || ' = ' || column_value);
-- If column name is not valid:
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
-- Invoke raise_emp_salary from a dynamic PL/SQL block:
'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';
EXECUTE IMMEDIATE plsql_block
USING 110, 'DEPARTMENT_ID', 10;
-- Invoke raise_emp_salary from a dynamic SQL statement:
EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'
USING 112, 'EMPLOYEE_ID', 10;
Using Explicit Format Models to Guard Against SQL Injection
If you use datetime and numeric values that are concatenated into the text of a SQL or PL/SQL statement, and you cannot pass them as bind variables, convert them to text using explicit format models that are independent from the values of the NLS parameters of the executing session. Ensure that the converted values have the format of SQL datetime or numeric literals. Using explicit locale-independent format models to construct SQL is recommended not only from a security perspective, but also to ensure that the dynamic SQL statement runs correctly in any globalization environment
Know More : http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_assert.htm