What is SQL injection ? How is Oracle application subject to SQL injection attacks ?

What is SQL injection ? How is Oracle application subject to SQL injection attacks ?
==============================================================================

[This is just for creating awareness about SQL Injection attacks]

SQL injection is a basic attack used to either gain unauthorized access to a database or to retrieve
information directly from the database.SQL injection attacks are simple in nature – an attacker passes
string input to an application in hopes manipulating the SQL statement to his or her advantage.

Oracle may provide stronger and more inherent protections against SQL injection attacks than other
database, however applications without proper defenses against these types of attacks can be vulnerable.
Despite these advantages many web applications are vulnerable to SQL injection attacks.

Oracle has generally faired well against SQL injection attacks as there is no multiple SQL statement
support (SQL Server and PostgreSQL), no EXECUTE statement (SQL Server), and no INTO OUTFILE
function (MySQL).
Also, use of bind variables in Oracle environments for performance reasons provides
strong protection against SQL injection attacks.

Generally Four Types of SQL Injection Attack. these are :-

1. SQL Manipulation
2. Code Injection
3. Function Call Injection
4. Buffer Overflows

All of these types of SQL injection are valid for databases including SQL Server, DB2, MySQL, PostgreSQL and Oracle.

Among these SQL Manipulation is more important. and vulnerable. i just want to describe it Here.

1. SQL Manipulation:
======================
SQL Injection attack is SQL manipulation. The attacker attempts to modify the existing SQL statement
by adding elements to the WHERE clause or extending the SQL statement with set operators
like UNION, INTERSECT, or MINUS.

In this example in first query I add in where clause just "OR 'A'='A' " then what happens.

In second query i add union clause just
" UNION
SELECT USERNAME FROM USER_MAS
WHERE USERNAME LIKE '%' "


Example:-

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 16 15:09:49 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn halim/halim@test107
Connected.
SQL>
SQL>
SQL> create table user_mas (username varchar2(20),password varchar2(20));

Table created.

SQL> insert into user_mas values ('HALIM','TEST');

1 row created.

SQL> insert into user_mas values ('SCOTT','TEST1');

1 row created.

SQL> insert into user_mas values ('ADMIN','TEST2');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM USER_MAS;

USERNAME PASSWORD
-------------------- --------------------
HALIM TEST
SCOTT TEST1
ADMIN TEST2

SQL>
SQL>
SQL> SELECT USERNAME FROM USER_MAS
2 WHERE USERNAME='HALIM' AND PASSWORD='TEST';

USERNAME
--------------------
HALIM

SQL>
SQL> ED
Wrote file afiedt.buf

1 SELECT USERNAME FROM USER_MAS
2* WHERE USERNAME='HALIM' AND PASSWORD='TEST' OR 'A'='A'
SQL> /

USERNAME
--------------------
HALIM
SCOTT
ADMIN

SQL>
SQL>
SQL> ED
Wrote file afiedt.buf

1 SELECT USERNAME FROM USER_MAS
2* WHERE USERNAME='HALIM' AND PASSWORD='TEST'
SQL> /

USERNAME
--------------------
HALIM

SQL>
SQL> ED
Wrote file afiedt.buf

1 SELECT USERNAME FROM USER_MAS
2 WHERE USERNAME='HALIM' AND PASSWORD='TEST'
3 UNION
4 SELECT USERNAME FROM USER_MAS
5* WHERE USERNAME LIKE '%'
6 /

USERNAME
--------------------
ADMIN
HALIM
SCOTT

SQL>
SQL>
SQL>
SQL>



PLSQL Codes subject to SQL injection attacks
===================================

SQL statements can be executed four different ways in PL/SQL –
a) embedded SQL,
b) cursors,
c) execute immediate statements,
d) the DBMS_SQL package.

Embedded SQL statements and static cursors only allow bind variables, this is not subject to SQL injection attacks.
But following are subject to SQL injection attacks.

B) dynamic cursors.(ref cursor) [To prevent SQL INJECTION attack bind variables should always be used]

CREATE OR REPLACE PROCEDURE pro_sample(P_empno IN VARCHAR2)
AS
sql_s VARCHAR2;
BEGIN
sql_s := 'SELECT * FROM emp WHERE empno = ''' || p_empno || '''';
OPEN cursor_states FOR sql_s;
LOOP FETCH cursor_states
INTO rec_state
EXIT WHEN cursor_states%NOTFOUND;
END LOOP;
CLOSE cursor_status;
END;

This can subject to attack by Sql injection

C) EXECUTE IMMEDIATE [To prevent SQL INJECTION attack bind variables should always be used]

D) DBMS_SQL package (for dynamic SQL statements)
[To prevent SQL INJECTION attack bind variables should always be used]



To prevent SQL injection and to improve application performance, bind variables should always be used.