Smita B Kumar 👀Tech Hunt: JDBC Statement vs PreparedStatement

JDBC Statement vs PreparedStatement – SQL Injection Example

While working with JDBC API for database connectivity, we can use Statement or PreparedStatement to execute queries. These queries can be CRUD operation queries or even DDL queries to create or drop tables.

JDBC Statement has some major issues and should be avoided in all cases, let’s see this with a simple example.

I have Users table in my local MySQL database with following data.

Below script will create the table and insert the data for test use.

CREATE TABLE `Users` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL DEFAULT '',

`email` varchar(20) NOT NULL DEFAULT '',

`country` varchar(20) DEFAULT 'USA',

`password` varchar(20) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `Users` (`id`, `name`, `email`, `country`, `password`)

VALUES

(1, 'Smita', 'Smita@apple.com', 'India', 'Smita123'),

(4, 'David', 'david@gmail.com', 'USA', 'david123'),

(5, 'Raman', 'raman@google.com', 'UK', 'raman123');

A utility class for creating JDBC Connection to our mysql database.

DBConnection.java

package com.sbk.jdbc.statements;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBConnection {

public final static String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";

public final static String DB_URL = "jdbc:mysql://localhost:3306/UserDB";

public final static String DB_USERNAME = "Smita";

public final static String DB_PASSWORD = "Smita123";

public static Connection getConnection() throws ClassNotFoundException, SQLException {

Connection con = null;

// load the Driver Class

Class.forName(DB_DRIVER_CLASS);

// create the connection now

con = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

System.out.println("DB Connection created successfully");

return con;

}

Now let’s say we have following class that asks user to enter the email id and password and if it matches, then prints the user details. I am using JDBC Statement for executing the query.

GetUserDetails.java

package com.sbk.jdbc.statements;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Scanner;

public class GetUserDetails {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

//read user entered data

Scanner scanner = new Scanner(System.in);

System.out.println("Please enter email id:");

String id = scanner.nextLine();

System.out.println("User id="+id);

System.out.println("Please enter password to get details:");

String pwd = scanner.nextLine();

System.out.println("User password="+pwd);

printUserData(id,pwd);

}

private static void printUserData(String id, String pwd) throws ClassNotFoundException, SQLException {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try{

con = DBConnection.getConnection();

stmt = con.createStatement();

String query = "select name, country, password from Users where email = '"+id+"' and password='"+pwd+"'";

System.out.println(query);

rs = stmt.executeQuery(query);

while(rs.next()){

System.out.println("Name="+rs.getString("name")+",country="+rs.getString("country")+",password="+rs.getString("password"));

}

}finally{

if(rs != null) rs.close();

stmt.close();

con.close();

}

Let’s see what happens when we pass different kinds of input to above program.

Valid User
1 2 3 4 5 6 7 8 9	Please enter email id: david@gmail.com User id=david@gmail.com Please enter password to get details: david123 User password=david123 DB Connection created successfully select name, country, password from Users where email = 'david@gmail.com' and password='david123' Name=David,country=USA,password=david123

So our program works fine and a valid user can enter their credentials and get his details. Now let’s see how a hacker can get unauthorized access to a user because we are using Statement for executing queries.

SQL Injection
1 2 3 4 5 6 7 8 9	Please enter email id: david@gmail.com' or '1'='1 User id=david@gmail.com' or '1'='1 Please enter password to get details: User password= DB Connection created successfully select name, country, password from Users where email = 'david@gmail.com' or '1'='1' and password='' Name=David,country=USA,password=david123

As you can see that we are able to get the user details even without having password. The key point to note here is that query is created through String concatenation and if we provide proper input, we can hack the system, like here we did by passing user id as david@gmail.com’ or ‘1’=’1. This is an example of “SQL Injection” where poor programming is responsible for making our application vulnerable for unauthorized database access.

One solution is to read the user input and then escape all the special characters that are used by MySQL but that would be clumsy and error prone. That’s why JDBC API came up with PreparedStatement interface that extends Statement that automatically escape the special characters before executing the query. Let’s rewrite above class using PreparedStatement and try to hack the system.

GetUserDetailsUsingPS.java

package com.sbk.jdbc.statements;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner;

public class GetUserDetailsUsingPS {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

// read user entered data

Scanner scanner = new Scanner(System.in);

System.out.println("Please enter email id:");

String id = scanner.nextLine();

System.out.println("User id=" + id);

System.out.println("Please enter password to get details:");

String pwd = scanner.nextLine();

System.out.println("User password=" + pwd);

printUserData(id, pwd);

}

private static void printUserData(String id, String pwd) throws ClassNotFoundException,

SQLException {

Connection con = null;

PreparedStatement ps = null;

ResultSet rs = null;

String query = "select name, country, password from Users where email = ? and password = ?";

try {

con = DBConnection.getConnection();

ps = con.prepareStatement(query);

//set the parameter

ps.setString(1, id);

ps.setString(2, pwd);

rs = ps.executeQuery();

while (rs.next()) {

System.out.println("Name=" + rs.getString("name") + ",country="

+ rs.getString("country") + ",password="

+ rs.getString("password"));

}

} finally {

if (rs != null)

rs.close();

ps.close();

con.close();

}

Now if we will try to hack the system, let’s see what happens.

SQL Injection
1 2 3 4 5 6 7	Please enter email id: david@gmail.com' or '1'='1 User id=david@gmail.com' or '1'='1 Please enter password to get details: User password= DB Connection created successfully

So we are not able to hack the database, it happened because the actual query that is getting executed is:

select name, country, password from Users where email = 'david@gmail.com\' or \'1\'=\'1\' and password=''

When we fire a query to be executed for a relational database, it goes through following steps.

1. Parsing of SQL query

2. Compilation of SQL Query

3. Planning and optimization of data acquisition path

4. Executing the optimized query and return the resulted data

When we use Statement, it goes through all the four steps but with PreparedStatement first three steps are executed when we create the prepared statement. So execution of query takes less time and more quick that Statement.

Another benefit of using PreparedStatement is that we can use Batch Processing through addBatch() andexecuteBatch() methods. We can create a single prepared statement and use it to execute multiple queries.

Some points to remember about JDBC PreparedStatement are:

· PreparedStatement helps us in preventing SQL injection attacks because it automatically escapes the special characters.

· PreparedStatement allows us to execute dynamic queries with parameter inputs.

· PreparedStatement provides different types of setter methods to set the input parameters for the query.

· PreparedStatement is faster than Statement. It becomes more visible when we reuse the PreparedStatement or use it’s batch processing methods for executing multiple queries.

· PreparedStatement helps us in writing object Oriented code with setter methods whereas with Statement we have to use String Concatenation to create the query. If there are multiple parameters to set, writing Query using String concatenation looks very ugly and error prone.

· PreparedStatement returns FORWARD_ONLY ResultSet, so we can only move in forward direction.

· Unlike Java Arrays or List, the indexing of PreparedStatement variables starts with 1.

· One of the limitation of PreparedStatement is that we can’t use it for SQL queries with IN clause because PreparedStatement doesn’t allow us to bind multiple values for single placeholder (?). However there are few alternative approaches to use PreparedStatement for IN clause.

That’s all for the comparison of JDBC Statement and PreparedStatement, you should always use PreparedStatement because it’s fast, object oriented, dynamic and more reliable.

Smita B Kumar 👀Tech Hunt

Tuesday, July 5, 2016

JDBC Statement vs PreparedStatement

No comments:

Post a Comment

About Me