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 - I

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. Overview

    The article describes the SQL injection attacks on applications. It demonstrates the code exploitation, ways of rectification and code designing recommendations.

    2. Introduction

    SQL injection refers to the attack on the applications which have Oracle as back end database. The activity to inject SQL and PL/SQL code through an application is SQL injection. It is a serious vulnerability and may lead to fatal consequences. An unauthorized attacker may get full database access by inputting bad input through application which may lead to leakage of important data.

    3. A basic demonstration of SQL injection

    The SQL query below gets the password of an input user from the USERS table.

    Code (SQL):
    SELECT USERNAME, PASSWORD
    FROM USERS
    WHERE USERNAME = 'CLUB'
    Developers are ignorant that their above query can be misused to an extent that it can list login names and passwords, which is relevant and crucial information for an organization. An invader can give input as
    Code (Text):
    ''OR 1=1
    which can retrieve all login and password.

    Code (SQL):
    SELECT USERNAME, PASSWORD
    FROM USERS
    WHERE USERNAME = ''
    OR 1=1
    In the above SQL, WHERE clause has two filter conditions. First condition yields FALSE while the second condition gives TRUE output. Since both are combined using OR logical operator, the combination gives TRUE and query retrieves all the login names and their passwords.

    Like the above technique, SQL code can be exploited in multiple ways. Dynamic SQL and User/Definer right preserved subprograms are most prone to SQL injections.

    4. Impact of SQL Injection
    [​IMG]

    5. Categories of SQL Injection

    Depending on the impact of SQL Injection, it can be divided into two major categories.

    [TABLE]First Order Attack Second Order Attack
    Bad string Input (using quote and concatenation operator) Performing an alternate activity to an ongoing system activity[/TABLE]

    6. SQL Injection: Example

    A procedure P_GET_SAL was created to get the salary of input Employee Id.

    Code (SQL):
    CREATE OR REPLACE PROCEDURE P_GET_SAL  (P_ENAME VARCHAR2 DEFAULT NULL)
    AS
    CUR SYS_REFCURSOR;
    V_ENAME VARCHAR2(100);
    V_SAL NUMBER;
    BEGIN
      V_STMT := 'SELECT ENAME, SALARY FROM EMPLOYEE  WHERE ENAME = '''|| P_ENAME || '''';
      DBMS_OUTPUT.PUT_LINE(V_STMT);  
      OPEN CUR FOR V_STMT;
      LOOP
        FETCH CUR INTO V_ENAME, V_SAL;
        EXIT WHEN CUR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee : '||V_ENAME||' draws '||TO_CHAR(V_SAL));
      END LOOP;
      CLOSE CUR;
    END;
    A malicious input can be given in below ways to inject the SQL. Illustration is as below.

    Code (SQL):
    SQL> EXEC P_GET_SAL(‘KING’);
    Employee KING draws 4500

    PL/SQL PROCEDURE successfully completed.
    Code (SQL):
    SQL> EXEC P_GET_SAL('KING'' UNION SELECT ENAME, SALARY FROM EMPLOYEE WHERE 1=1');
    Employee KING draws 4500
    Employee ALLEN draws 1200
    Employee MIKE draws 3400
    Employee KATE draws 2300
    Employee PAUL draws 6400
    Employee TOMY draws 2700
    Employee JENNY draws 6200
    Employee JAKES draws 4600

    PL/SQL PROCEDURE successfully completed.
    7. Strategies to Resolve SQL Injection

    Several strategies can be adopted to safeguard the SQL code and eradicate the impacts of SQL injection in applications. Some of them are listed below.

    1. Use of Static SQL
    2. Using Invoker’s rights
    3. Use of Dynamic SQL with bind arguments
    4. Validate and sanitize input using DBMS_ASSERT
     

    Attached Files:

  2. Dinesh Chowdhary

    Dinesh Chowdhary Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    Bangalore
    Thanks for the info...:)
     
  3. partha

    partha Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Good information. Thanks for sharing.:cool:
     
  4. salmankhalid

    salmankhalid Forum Advisor

    Messages:
    116
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Lahore, Pakistan
    very nice and comprehensive information