Wednesday, April 13, 2011

What is SQL Injection ?

SQL Injection  :  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. Let's have a look

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> conn scott/tiger@noida
Connected.
SQL> create table aa (username varchar2(20),password varchar2(20));
 Table created.
 SQL> insert into aa values ('SCOTT','TIGER');
 1 row created.
 SQL> insert into aa values ('SCOTT1','TIGER1');
 1 row created.
 SQL> insert into user_mas values ('SCOTT2','TIGER2');
 1 row created.

 SQL> COMMIT;
 Commit complete.

SQL> select * from aa ;
 USERNAME                PASSWORD
--------------------    --------------------
SCOTT TIGER
SCOTT1 TIGER1
SCOTT2 TIGER2

SQL> select username from aa where username ='SCOTT' and password='TIGER' ; 

USERNAME
--------------------
SCOTT

SELECT USERNAME FROM AA  WHERE USERNAME='SCOTT' AND PASSWORD='TIGER' OR 'A'='A'
SQL> /
 USERNAME
--------------------
SCOTT
SCOTT1
SCOTT2

SELECT USERNAME FROM AA  WHERE USERNAME='SCOTT' AND PASSWORD='TIGER'
SQL> /
 USERNAME
--------------------
SCOTT

SELECT USERNAME FROM AA WHERE USERNAME='HALIM' AND PASSWORD='TEST'
 UNION SELECT USERNAME FROM AA WHERE USERNAME LIKE '%'
 USERNAME
--------------------
SCOTT2
SCOTT1
SCOTT

 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.
 

A.) 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.

No comments: