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 */
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