12.1 Overview
In earlier chapters, we discussed that there are two Objects (Statement and PreparedStatement) that are available to interact with tables.
12.2 Statement and PreparedStatement APIs
There are some differences in API of Statement and PreparedStatement which we are discussing in this section.
- Obtain Prepared Statement and Statement – Both PreparedStatement and Statement are obtained from Connection Object.In this chapter we will discuss the differences between Prepared Statement and Statement in terms of performance, security and API
To get PreparedStatement, call prepareStatement(String sql) method on Connection object passing SQL statement where as we can call createStatement()method on Connection object. Note that in PreparedStatement we pass the SQL statement while obtaining the object where as in Statement SQL is passed in methods that interacts with database.
- Dynamic Parameters
Prepared Statement supports dynamic parameter placement which is not supported by Statements. In order to achieve the dynamic query, string concatenation is only option (it has severe consequences which we will discuss in next section).
For example if we want to fetch a record with a given name and name is taken by user as input parameter. Now since name is an input param, we can achieve dynamism as follows-
Statement - Use string concatenation
Scanner scanner = new Scanner(System.in);
String name = scanner.next();
Connection conn= getConnection();
String sql = "select * from user where name='"+name+"'";
Statement stmt= conn.createStatement();
ResultSet rs = stmt.executeQuery(sql) ;
PrepareStatement – Use placeholder (?) for dynamic value and set the value using setXXX methods
Scanner scanner = new Scanner(System.in);
String name = scanner.next();
Connection conn= getConnection();
String sql = "select * from user where name=?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, name);
ResultSet rs = stmt.executeQuery();
Advantage of dynamic param is that we need not to take care of data types. WE just need to call correct setXX based on data type where as in Statement we need to add quotes (‘) for string values.
c) Batch Processing – PreparedStatement supports batch processing whereas Statement does not. When we want to execute statements in a batch, we should use Prepared Statement. There is an API addBatch() and executeBatch() available.
12.3 SQL Injection (Security)
SQL Injection is basically a technique which can be used to hack the data using SQLs. Using a String concatenation to make the String dynamic in Statement is not secure and vulnerable to SQL injection attacks.
Let’s understand it with the help of example and then we will see it practically. Assume there is a table User which stores users data and we can retrieved the user data based on user’s username (assume username is unique).
To get this, we will write below Query
String username= name grabbed from user or dynamic value
String sql = select * from user where username=’+username+”’’;
Now since we are taking username from user and user can enter anything and assume in case user enters Any name' or '1'='1 query becomes
Select * from username where username='any name' or '1'='1'
Are you able to see the issue here? Now the user has intentionally added one more condition with OR operator which will always be true and this query will return complete data of the table.
Now you must be thinking how we can prevent this. So we must escape the inputs which is grabbed from user before using it in SQL. We avoid using manual escape and suggest to use Prepared Statements because it does the escaping internally.
Let’s see how escaping can prevent SQL injection.
Escaping Any name' or '1'='1 will become Any name\' or \'1'=\'1 and then SQL becomes
Select * from username where username='any name\' or \'1\'=\'1' which means user input query will try to match Any name' or '1'='1 with username and there is no chance there will be an additional condition will get added.
Let’s validate above understanding with the help of example on User table.
a. Add data in User table so that table’s state will become like below
b. Write a program to fetch record based on name.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class SQLInjectionExample { public static void main(String args[]) { try { Scanner scanner = new Scanner(System.in); System.out.println("Enter name:"); String name = scanner.nextLine(); Connection conn= getConnection(); String sql = "select * from user where name='"+name+"'"; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while(rs.next()) { String userName=rs.getString("name"); String age=rs.getString("age"); String email= rs.getString("email"); System.out.println("Name= " + userName + " age = " + age + " email= " + email ); } stmt.close(); conn.close(); } catch(Exception e) { e.printStackTrace(); } } private static Connection getConnection() { Connection con = null; String dbDriver = "com.mysql.jdbc.Driver"; String dbUsername = "root"; String dbPassword="password"; String dbHostname="localhost"; String dbPort="3306"; String schema="JdbcTutorial"; try { String url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema; Class.forName(dbDriver); con = DriverManager.getConnection(url, dbUsername, dbPassword); } catch (ClassNotFoundException ex1) { System.out.println("Failed to find driver class " + ex1.getMessage()); System.exit(1); } catch (SQLException ex2) { System.out.println("Connection failed " + ex2.getMessage()); System.exit(2); } return con; } }
On running above program, user need to enter the name and now user can input
Any name' or '1'='1 and see we will get all three records
c. Now change the program to escape the input before using it in SQL
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class SQLInjectionExample { public static void main(String args[]) { try { Scanner scanner = new Scanner(System.in); System.out.println("Enter name:"); String name = scanner.nextLine(); Connection conn= getConnection(); String escapedName = name.replaceAll("'","\\\\'"); String sql = "select * from user where name='"+escapedName+"'"; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while(rs.next()) { String userName=rs.getString("name"); String age=rs.getString("age"); String email= rs.getString("email"); System.out.println("Name= " + userName + " age = " + age + " email= " + email ); } stmt.close(); conn.close(); } catch(Exception e) { e.printStackTrace(); } } private static Connection getConnection() { Connection con = null; String dbDriver = "com.mysql.jdbc.Driver"; String dbUsername = "root"; String dbPassword="password"; String dbHostname="localhost"; String dbPort="3306"; String schema="JdbcTutorial"; try { String url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema; Class.forName(dbDriver); con = DriverManager.getConnection(url, dbUsername, dbPassword); } catch (ClassNotFoundException ex1) { System.out.println("Failed to find driver class " + ex1.getMessage()); System.exit(1); } catch (SQLException ex2) { System.out.println("Connection failed " + ex2.getMessage()); System.exit(2); } return con; } }
As we have escaped input, now code is not vulnerable to SQL injections.