SQL Injection in PLSQL

What is SQL Injection?

SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).

 
Avoiding SQL Injection in PL/SQL

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. This section describes SQL injection vulnerabilities in PL/SQL and explains how to guard against them.

To demonstrate the examples in this topic, connect to the HR schema and execute the statement in the below example.

CREATE TABLE secret_records (

user_name VARCHAR2(9),

service_type VARCHAR2(12),

value VARCHAR2(30),

date_created DATE);

INSERT INTO secret_records

VALUES ('Andy', 'Waiter', 'Serve dinner at Cafe Pete', SYSDATE);

INSERT INTO secret_records

VALUES ('Chuck', 'Merger', 'Buy company XYZ', SYSDATE);

SQL Injection Techniques

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. This topic classifies SQL injection attacks as follows:

■ Statement Modification

■ Statement Injection

■ Data Type Conversion

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.

The below example creates a procedure that is vulnerable to statement modification and then invokes that procedure with and without statement modification. With statement modification, the procedure returns a supposedly secret record.

Create vulnerable procedure

CREATE OR REPLACE PROCEDURE get_record(user_name    IN VARCHAR2,

                                       service_type IN VARCHAR2,

                                       record       OUT VARCHAR2) IS

  query VARCHAR2(4000);

BEGIN

  -- Following SELECT statement is vulnerable to modification

  -- because it uses concatenation to build WHERE clause.

  query := 'SELECT value FROM secret_records WHERE user_name=''' ||

           user_name || ''' AND service_type=''' || service_type || '''';

  DBMS_OUTPUT.PUT_LINE('Query: ' || query);

  EXECUTE IMMEDIATE query

    INTO record;

  DBMS_OUTPUT.PUT_LINE('Record: ' || record);

END;

/

Procedure created.

Demonstrate procedure without SQL injection

DECLARE

record_value VARCHAR2(4000);

BEGIN

get_record('Andy', 'Waiter', record_value);

END;

/

Query: SELECT value FROM secret_records WHERE user_name='Andy' AND service_type='Waiter'

Record: Serve dinner at Cafe Pete

PL/SQL procedure successfully completed.

Example of statement modification

DECLARE

  record_value VARCHAR2(4000);

BEGIN

  get_record('Anybody '' OR service_type=''Merger''--',

             'Anything',

             record_value);

END;

Query: SELECT value FROM secret_records WHERE user_name='Anybody ' OR service_type='Merger'--' AND service_type='Anything'

Record: Buy company XYZ

PL/SQL procedure successfully completed.

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.

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.

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

·         Using Validation Checks to Guard Against SQL Injection

·         Using Explicit Format Models to Guard Against SQL Injection

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.

The procedure in below example is invulnerable to SQL injection because it builds the dynamic SQL statement with bind arguments.

CREATE OR REPLACE PROCEDURE get_record_2(user_name    IN VARCHAR2,

                                         service_type IN VARCHAR2,

                                         record       OUT VARCHAR2) IS

  query VARCHAR2(4000);

BEGIN

  query := 'SELECT value FROM secret_records

WHERE user_name=:a

 AND service_type=:b';

  DBMS_OUTPUT.PUT_LINE('Query: ' || query);

  EXECUTE IMMEDIATE query

    INTO record

    USING user_name, service_type;

  DBMS_OUTPUT.PUT_LINE('Record: ' || record);

END;

/

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.

Using Validation Checks to Guard Against SQL Injection

CREATE OR REPLACE PROCEDURE raise_emp_salary(column_value NUMBER,

                                             emp_column   VARCHAR2,

                                             amount       NUMBER) IS

  v_column VARCHAR2(30);

  sql_stmt VARCHAR2(200);

BEGIN

  -- Check validity of column name that was given as input:

  SELECT COLUMN_NAME

    INTO v_column

    FROM USER_TAB_COLS

   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);

  END IF;

  -- If column name is not valid:

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    DBMS_OUTPUT.PUT_LINE('Invalid Column: ' || emp_column);

END raise_emp_salary;

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.

The procedure in below example is invulnerable to SQL injection because it converts the datetime parameter value, SYSDATE - 30, to a VARCHAR2 value explicitly, using the TO_CHAR function and a locale-independent format model.

Create invulnerable procedure

CREATE OR REPLACE PROCEDURE get_recent_record(user_name    IN VARCHAR2,

                                              service_type IN VARCHAR2,

                                              record       OUT VARCHAR2) IS

  query VARCHAR2(4000);

BEGIN

  -- Following SELECT statement is vulnerable to modification

  -- because it uses concatenation to build WHERE clause.

  query := 'SELECT value FROM secret_records WHERE user_name=''' ||

           user_name || ''' AND service_type=''' || service_type ||

           ''' AND date_created> DATE ''' ||

           TO_CHAR(SYSDATE - 30, 'YYYY-MM-DD') || '''';

  DBMS_OUTPUT.PUT_LINE('Query: ' || query);

  EXECUTE IMMEDIATE query

    INTO record;

  DBMS_OUTPUT.PUT_LINE('Record: ' || record);

END;