SQL injections are ways of attacking applications’ underlying SQL statements. Instead of expected value, intruder puts the string that expands or changes the SQL statement. Only dynamic SQL statements are susceptible to SQL injections.

SQL statement can be executed in Oracle from PL/SQL stored procedures, using dbms_sql, execute immediate or via application interface.

SQL Manipulation

Consider this SELECT statement:
SELECT * from data_table where account=v1

The expected value for variable v1 can be something like ‘123’. If, instead, the following value is supplied for v1 variable:

v1=123’ or ‘a’=’a

SQL statement changes to:

· SELECT * from data_table where account=’123’ or ‘a’=’a’;

Example of SQL injection within EXECUTE IMMEDIATE

CREATE PROCEDURE Empl(name IN VARCHAR2) as
sqls VARCHAR2(200);
cc integer;
BEGIN
sqls := 'SELECT count(*) FROM emp
WHERE name = ''' || name || '''';
EXECUTE IMMEDIATE sqls INTO cc;
END;

SQL can be injected into Empl procedure with:

begin
Empl('Smith'' or ''1''=''1');
end;

This will turn the SELECT statement from Empl procedure:
SELECT count(*) FROM emp WHERE name = 'Smith';
to:
SELECT count(*) FROM emp WHERE name = 'Smith' or ‘1’=’1’;

To avoid this problem, use bind variables instead of string concatenation and strip the single quotes from the input string.

Other ways to inject SQL

UNION and sub-select can be also used.

Examples:

v1=123’ union select username||password
from dba_users where ‘a’=‘a

v1=123’ or exists (select ‘x’ from dual ) and ‘a’=‘a;

An intruder can use some of the known bugs for SQL injection.

Products affected: Oracle up to 9.0.1.3.

Assumption is that connected user has only CONNECT role.

SQL> connect scott/tiger

Connected.

SQL> select a.username, a.password

from sys.dba_users a left outer join sys.dba_users b on

b.username = a.username ;

USERNAME PASSWORD
------------ -----------------------------
SYS D4C5016086B2DC6A

SYSTEM D4DF7931AB130E37

RMAN E7B5D92911C831E1

CSUSER 1A00922D8C0F146

The vendor has issued a bulletin and made patches available:

http://otn.oracle.com/deploy/security/pdf/sql_joins_alert.pdf

SQL in Java

JDBC is a connectivity used by JSP, EJB and Java servlets. SQL statements are dynamic and they are executed using CallableStatement or PreparedStatement subinterfaces.

Example of the vulnerable code:

String dept = request.getParameter(“Dept");
String sql = "begin ? := Empl('" + dept + "'); end;";
CallableStatement cs = conn.prepareCall(sql);

With the injection, the code becomes:

begin ? := Empl(''); delete emp; commit; Empl(Smith''); end;

The correct way of coding is using bind variables:

CallableStatement cs= conn.prepareCall ("begin ? := Empl(?); end;");

Defense against SQL Injections

• Using bind variables
• Checking the input string for quotes and stripping them
• Checking for values that don’t make sense
• String bound the user input
• Expect use of bugs