Prepared Statement - SQL Injection Proof

Prepared statement: is special type of statement derived from Statement Interface. Prepared statement is used more often then Statement as it is faster in execution.

the prepared statement is cached and then the execution path is pre-determined on the database server allowing it to be executed multiple times in an efficient manner. This mean, normal SQL Statement is complied each time when it is executed while prepared statement are compiled once and then executed many time.

Prepared Statement are little different in Syntax. We will proceed with our Employee DB for better comparison with Statements,
Case: Fetch all records by using name and salary

************************************************************************

import java.sql.*;
class JDBCConnection {
public static void main (String args[]) throws SQLException
{
Connection conn = null;
PreparedStatement ps= null;
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
String conStr = "jdbc:oracle:thin:@localhost:1521:xe";
conn = DriverManager.getConnection(conStr, "scott","tiger");
String queryStr = "SELECT * FROM tblEmployee where empName = ? AND empSalary = ?";
ps = conn.prepareStatement(queryStr);
ps.setString(1,"Rahul");
ps.setInt(2, 25000);
ResultSet rset = ps.executeQuery();
while (rset.next())
{
System.out.println (rset.getString(1)); // empName
System.out.println (rset.getInt(2)); // empCode
System.out.println (rset.getInt(3)); // empSalary
System.out.println (rset.getString(4)); // empAddress
}
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
conn.close();
ps.close();
}
}
}
*************************************************************

I belived you have gone through Statements, if not then have a quick look over it here.

You can see the main difference in Statement and Prepared statement are depicted in Green Colored text. We substitute the filter parameter with place holder and calls prepareStatement() method. This returns a Prepared Statement object. Now we got compiled prepared statement so we can use this compiled statement to execute query so we set filter parameter to prepared statement using setString() and setInt() method. This value replaces the placeholder in query string. Then you need to call executeQuery() method just like you do in case of statement. After that there is not difference in processing the result set returned by your query. From this point, you can resume same concept those are associated with simple jdbc Statement.

Sample code snippet for update table are given blow,

PreparedStatement updateRecord = conn.prepareStatement("UPDATE Employee
SET Salary = ? WHERE empCode = ?");
updateRecord.setInt(1, 25000);
updateRecord.setInt(2, 1002);

Same concept is followed for deleting and inserting the records so we are not going in detail of that, just replace your green codes with appropriate query and parameter and execute them in same way.

SQL Injection:

So in heading we said SQL injection proof. lets discuss this. SQL injection is a kind of hacking or attack on a system by sending some malicious sql which can delete or corrupt data in database.

The sql injection usually happened by directly using the value coming from UI form.

Suppose there is a text field in UI which takes empCode to search details. if some one passes '1000; DELETE FROM EMPLOYEE' in text box and you just passes this value to your statement without validating the input, guess what will happen. DELETE statement will also execute with search statement and all the data in Employee table will be deleted...Horrible.

As you have seen, poorly written statements can be breached by sql injection but Prepared statements prevent this by ensuring the type of input used in query. Like for providing int value, we use setInt() method which accept only integer value. similarly other method used for inserting filter input checks it's type and prevent any such incidence.
In case of any clarification, feel free to email me at mohit.amour@gmail.com


Thanks,
The Interpreter