An Example to Demonstrate The Vulnerability of SQL Injection and Its Prevention in Oracle
Tags: oracle, plsql, sql, writing
Published: 2019-12-31 14:31:10

In this post, I am giving an example to demonstrate how SQL Injection could be vulnerable to an application and how you can prevent it.

The demonstration is based on the SCOTT schema’s EMP table. To download the SCOTT schema script click the following link [Download Scott Schema Script](https://www.foxinfotech.in/2018/06/download-oracle-scott-schema-script.html).

##An Example to Perform SQL Injection

In this section, I am giving an example of a PL/SQL stored procedure which will accept a parameter employee number as (p_empno) to display the salary for that employee. In the code, I am using the concatenation of that parameter (p_empno) value in the SQL statement string for REF CURSOR, which is not recommended and will be the cause of successful SQL Injection. Below is the procedure:

```sql
CREATE OR REPLACE PROCEDURE PRC_GET_EMP_SAL (p_empno VARCHAR2)
IS
   --Declare a ref cursor and local variables--
   TYPE C IS REF CURSOR;

   CUR_EMP   C;
   L_ENAME   VARCHAR2 (100);
   L_SAL     NUMBER;
   L_STMT    VARCHAR2 (4000);
BEGIN
   --Open the ref cursor for a Dynamic SELECT statement--
   L_STMT := 'SELECT ename, sal 
            FROM emp 
            WHERE empno = ''' || p_empno || '''';

   OPEN CUR_EMP FOR L_STMT;

   LOOP
      --Fetch the result set and print the result set--
      FETCH CUR_EMP
      INTO L_ENAME, L_SAL;

      EXIT WHEN CUR_EMP%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (L_ENAME || ' -- ' || L_SAL);
   END LOOP;

   CLOSE CUR_EMP;
END;
/
```

###Test

```sql
SET SERVEROUTPUT ON;

BEGIN
   prc_get_emp_sal ('7566');
END;
/
```

###Output

```text
JONES -- 27706.89
PL/SQL procedure successfully completed.
```

Till now everything is fine. Because we correctly called the procedure. Now we will see how we can hack the above procedure by using the SQL Injection trick to fetch the salary of all employees. Maybe sometimes you also want to do this. Joking!

###Test Using SQL Injection

```sql
SET SERVEROUTPUT ON;

BEGIN
   prc_get_emp_sal ('X'' OR ''1''= ''1');
END;
/
```

###Successful SQL Injection Output

```text
WARD -- 11641.56
JONES -- 27706.89
MARTIN -- 11641.56
BLAKE -- 26542.7
CLARK -- 22817.41
SCOTT -- 83819.06
KING -- 46566.18
TURNER -- 13969.85
ADAMS -- 10244.6
JAMES -- 8847.64
FORD -- 27939.74
MILLER -- 12107.2
PL/SQL procedure successfully completed.
```

Wow, now you can see every employee’s salary using this SQL Injection trick. Just imagine, that you have a text field in an application whether it is browser-based or desktop and you are passing the value straightway to the procedure, and if you use the above trick, then surely this will happen.

Check more tutorials on Oracle PL/SQL at [Foxinfotech.in](https://www.foxinfotech.in/)

##An Example to Prevent SQL Injection

Now we will modify the above procedure to use bind variable instead of concatenating the parameter value and this way no SQL Injection trick can work.

```sql
CREATE OR REPLACE PROCEDURE PRC_GET_EMP_SAL_2 (p_empno VARCHAR2)
IS
   --Declare a ref cursor and local variables--
   TYPE C IS REF CURSOR;

   CUR_EMP   C;
   L_ENAME   VARCHAR2 (100);
   L_SAL     NUMBER;
   L_STMT    VARCHAR2 (4000);
BEGIN
   --Open the ref cursor for a Dynamic SELECT statement--
   L_STMT := 'SELECT ename, sal 
            FROM emp 
            WHERE empno = :p_bind_empno';

   OPEN CUR_EMP FOR L_STMT USING p_EMPNO;

   LOOP
      --Fetch the result set and print the result set--
      FETCH CUR_EMP
      INTO L_ENAME, L_SAL;

      EXIT WHEN CUR_EMP%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (L_ENAME || ' -- ' || L_SAL);
   END LOOP;

   CLOSE CUR_EMP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Can not fetch any records for: ' || p_empno);
END;
/
```

###Test the above procedure normally

```sql
SET SERVEROUTPUT ON;

BEGIN
   prc_get_emp_sal_2 ('7566');
END;
/
```

###Output

```text
JONES -- 27706.89
PL/SQL procedure successfully completed.
```

###Test the above procedure using SQL Injection

```sql
SET SERVEROUTPUT ON;

BEGIN
   prc_get_emp_sal_2 ('1'' OR ''1''= ''1');
END;
/
```

###Failed SQL Injection Output

```text
Can not fetch any records for: 1' OR '1'= '1
PL/SQL procedure successfully completed.
```

So make a note of it, if you are creating PL/SQL programs using dynamic SQL, use the binding methods.