1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Injection in Oracle - II

Discussion in 'SQL PL/SQL' started by SBH, Jan 3, 2011.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Introduction

    In the last article, we simulated the attack of simulation of SQL Injection in Oracle. In the current article, we shall see the remedies of SQL Injection, code recommendations, and guidelines to avoid malicious attack on applications.

    2. Using invoker’s rights to reduce SQL Injection

    A subprogram can be set to be executed by its Invoker’s rights. By default, it executes by its definer’s access rights. If a subprogram is marked as AUTHID CURRENT_USER, Oracle server validates the rights of the current user to execute the subprogram or not.

    The below procedure P_ACTION is created to change the password of an input username. Note that it is a system related activity, so its access must be for limited users.

    Code (SQL):
    CREATE OR REPLACE PROCEDURE P_ACTION(P_USER VARCHAR2 DEFAULT NULL,
      P_NEWPASS VARCHAR2 DEFAULT NULL)
    AUTHID CURRENT_USER
    IS
      V_STMT VARCHAR2(500);
    BEGIN
      V_STMT := 'ALTER USER '||P_USER ||' IDENTIFIED BY '||P_NEWPASS;
      EXECUTE IMMEDIATE V_STMT;
    END P_ACTION;
    The procedure P_ACTION is owned by SYS. Now EXECUTE privilege is granted only to the users U1 and U2.

    Code (SQL):
    SQL> GRANT EXECUTE ON P_ACTION TO U1, U2
    GRANT succeeded.
    Any other user who tries to execute P_ACTION ends up getting an error.

    Code (SQL):
    CONNECT U3/U3

    SQL> EXECUTE P_ACTION('ORCL', 'abcxxx123')

    ERROR at line 1:
    ORA-01031: Insufficient privileges
    ORA-06512: at "SYS.P_ACTION", at line 1
    ORA-06512: at line 1
    3. Using Static SQL

    Static SQL reduces the chances of SQL injection as dynamic statements are more prone to malicious inputs and code exploitations.
    We already saw the attack on P_GET_SAL in earlier illustrations. The dynamic SQL used in the procedure can be converted into static as below to ignore the bad inputs.

    Code (SQL):
    CREATE OR REPLACE PROCEDURE P_GET_SAL   (P_ENAME VARCHAR2 DEFAULT NULL)
    AS
    BEGIN
      FOR I IN
        (SELECT ENAME, SALARY
         FROM EMPLOYEE
         WHERE ENAME= P_ENAME)
      LOOP
          DBMS_OUTPUT.PUT_LINE('Employee : '||I.ENAME||' draws '||TO_CHAR(I.SALARY));
      END LOOP;
    END;
    /

    SQL> EXECUTE P_GET_SAL('KING');
    Employee KING draws 4500

    PL/SQL PROCEDURE successfully completed.

    SQL> EXEC P_GET_SAL('KING'' UNION SELECT ENAME, SALARY FROM EMPLOYEE WHERE 1=1');

    PL/SQL PROCEDURE successfully completed.
    4. Using Dynamic SQL with bind arguments

    Irrespective of benefits of static SQL, there are situations where use of dynamic SQL cannot be ignored. The scenarios where SQL statement has to be prepared at runtime or DDL execution at execution time is required, dynamic SQL is the only way of achievement.

    If PL/SQL code uses Dynamic SQL statements, then instead of passing inputs in concatenated form, it must use bind arguments. This makes a dynamic statement less prone to malicious attacks. For example, in the below PL/SQL block, V_STMT is the dynamically created statement which retrieves all the objects owned by an Input user. Note that it can be attacked by sending bad inputs (using concatenation and quote operator).

    Code (SQL):
    BEGIN

    V_STMT := 'SELECT OBJECT_NAME FROM ALL_OBJECTS '||'  WHERE OWNER = '''|| P_USER ||'''';
    EXECUTE IMMEDIATE V_STMT;

    END;
    Above situation can be bypassed by the use of bin variables. In the Dynamic SQL statement, place holder variable :1 is given. It would be replaced by the bind argument input P_USER in EXECUTE IMMEDIATE statement.

    Code (SQL):
    BEGIN

    V_STMT := 'SELECT OBJECT_NAME FROM ALL_OBJECTS '||'  WHERE OWNER = :1';
    EXECUTE IMMEDIATE V_STMT USING P_USER;

    END;
    5. Validating Inputs using DBMS_ASSERT

    Input values can be sanitized before their use to reduce the injections attacks. This can be done using DBMS_ASSERT package. It is an oracle supplied package which contains subprograms to validate the inputs, especially Oracle Identifiers.
    Note that DBMS_ASSERT package must be used with SYS schema. Below are the details about the subprograms contained by the DBMS_ASSERT.

    [TABLE]Function Description
    NOOP No Operation.
    ENQUOTE_LITERAL Encloses string literal in single quotes
    ENQUOTE_NAME Encloses string literal in double quotes
    SIMPLE_SQL_NAME Verifies that the string is a simple SQL name
    QUALIFIED_SQL_NAME Verifies that the string is a qualified SQL name
    SCHEMA_NAME Verifies that the string is an existing schema name
    SQL_OBJECT_NAME Verifies that the string is a qualified identifier of an existing SQL object[/TABLE]

    Illustration

    1. The below PL/SQL block verifies the current schema is CLUB or not. If yes, then it returns the current schema, else control moves to EXCEPTION block.

    Code (SQL):
    DECLARE
      L_SCHEMA := 'CLUB';
    BEGIN
      L_SCHEMA := sys.dbms_assert.SCHEMA_NAME(L_SCHEMA);
    EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
    2. The below SQL verifies the existence of an object.

    Code (SQL):
    SQL> SELECT DBMS_ASSERT.sql_object_name('P_GET_SAL') FROM dual;

    DBMS_ASSERT.SQL_OBJECT_NAME('P_GET_SAL')
    ----------------------------------------------------------------------------------------------------
    P_GET_SAL

    1 ROW selected.
    3. The below SQL quotes an input string

    Code (SQL):
    SQL> SELECT DBMS_ASSERT.enquote_literal('Club Oracle') FROM dual;

    DBMS_ASSERT.ENQUOTE_LITERAL('CLUBORACLE')
    ----------------------------------------------------------------------------------------------------
    'Club Oracle'

    1 ROW selected.
    Limitations
    • It can be used in buffer overflow attacks.
    • It does not ensure that given SQL name can be parsed or not.
    • Cross-site scripting attacks cannot be handled using DBMS_ASSERT.