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.
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:
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
SET SERVEROUTPUT ON;
BEGIN
prc_get_emp_sal ('7566');
END;
/
Output
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
SET SERVEROUTPUT ON;
BEGIN
prc_get_emp_sal ('X'' OR ''1''= ''1');
END;
/
Successful SQL Injection Output
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
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.
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
SET SERVEROUTPUT ON;
BEGIN
prc_get_emp_sal_2 ('7566');
END;
/
Output
JONES -- 27706.89
PL/SQL procedure successfully completed.
Test the above procedure using SQL Injection
SET SERVEROUTPUT ON;
BEGIN
prc_get_emp_sal_2 ('1'' OR ''1''= ''1');
END;
/
Failed SQL Injection Output
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.
Top comments (0)