Recent Posts

Tuesday, 13 November 2018

SQL Injection Attack


     In the case of Simple Statement every time the query will send to the database with user provided input values. Every time the query will be compiled and executed. Some times end user may provide special characters as the part user input,which may change behaviour of sql query.This is nothing but SQL Injection Attack,which causes security problems.
     But in the case of PreparedStatement query will be compiled at the beginning only without considering end user's input. User provided data will be considered at the time of execution only. Hence as the part of user input,if he provides any special characters as the part of input,query behaviour wont be changed. Hence there is no chance of SQL Injection Attack in PreparedStatement.

E.g:
select count(*) from users where uname='"+uname+"' and password='"+password+"'"
     If the end user provides username as ashok.mariyala and password as Kumar@12345 then the query will become
select count(*) from users where uname='ashok.mariyala' and password='Kumar@12345'.

     The query is meaningful and it is validating both username and password. If the end user provides username as ashok.mariyala'-- and password as Kumar@12345 then the query will become
select count(*) from users where uname='ashok'--' and password='Kumar@12345'

     It is not meaningful query because it is validating only username but not password. i.e., with end user's provided input the query behavior is changing, which is nothing but SQL injection attack.

Note
-- Single Line SQL Comment
/* Multi Line SQL Comment */

Eg 2:
select * from users where user_id  =  enduserprovidedinput
select * from users where user_id  = 101;
     returns record information where user_id = 101.

select * from users where user_id = 101 OR 1=1;
     Here 1=1 is always true and hence it returns complete table information like username, password, user_id etc. which may create security problems.

SQL Injection Attack with Statement
SQL Script
create table users(uname varchar2(20),upwd varchar2(20));

insert into users values('ashok.mariyala','Kumar@12345');
insert into users values('vinod.mariyala','Vinod@321');

SQLInjectionDemo
import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class SQLInjectionDemo { 
   public static void main(String[] args) throws Exception {
      String sqlQuery = "select count(*) from users where uname = '"+uname+"' and password = '"+password+"'""; 
      Class.forName("oracle.jdbc.OracleDriver"); 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      Statement st = con.createStatement();
      Scanner sc = new Scanner(System.in); 
      System.out.println("Enter Username : "); 
      String uname = sc.next(); 
      System.out.println("Enter Password : "); 
      String password = sc.next();
      ResultSet rs =st.executeQuery(sqlQuery);
      int c=0; 
      if(rs.next()) { 
         c = rs.getInt(1); 
      } 
      if(c==0) 
         System.out.println("Invalid Credentials"); 
      else 
         System.out.println("Valid Credentials");
      con.close();
   }
}
Output
java SQLInjectionDemo
Enter Username : ashok.mariyala
Enter Password : Kumar@12345
Valid Credentials

java SQLInjectionDemo
Enter Username : ashok.mariyala'--
Enter Password : Ashok@12345
Valid Credentials

SQLInjectionDemo using PreparedStatement
import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class SQLInjectionDemo { 
   public static void main(String[] args) throws Exception {
      String sqlQuery = "select count(*) from users where uname = ? and password =?"; 
      Class.forName("oracle.jdbc.OracleDriver"); 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      PreparedStatement pst = con.prepareStatement(sqlQuery);
      Scanner sc = new Scanner(System.in); 
      System.out.println("Enter Username : "); 
      String uname = sc.next(); 
      System.out.println("Enter Password : "); 
      String password = sc.next();
      pst.setString(1,uname);
      pst.setString(2,password);
      ResultSet rs =st.executeQuery(sqlQuery);
      int c=0; 
      if(rs.next()) { 
         c = rs.getInt(1); 
      } 
      if(c==0) 
         System.out.println("Invalid Credentials"); 
      else 
         System.out.println("Valid Credentials");
      con.close();
   }
}
Output
java SQLInjectionDemo
Enter Username : ashok.mariyala
Enter Password : Kumar@12345
Valid Credentials

java SQLInjectionDemo
Enter Username : ashok.mariyala'--
Enter Password : Ashok@12345
InValid Credentials


Next Tutorial : Stored Procedures and CallableStatement

Previous Tutorial : Advantages and Disadvantages of PreparedStatement

No comments:

Post a Comment