Today we will look into JDBC Statement vs PreparedStatement and some SQL Injection Example. While working with JDBC 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.

Statement vs PreparedStatement

Before comparing Statement vs PreparedStatement, let’s see why we should avoid JDBC Statement. 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.

MySQL-Users-Table-450x140

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

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

DBConnection.java

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

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

Valid User:

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:

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 and automatically escape the special characters before executing the query.

Let’s rewrite above class using PreparedStatement and try to hack the system.

GetUserDetailsUsingPS.java

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

SQL Injection:

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() and executeBatch() methods. We can create a single prepared statement and use it to execute multiple queries.

Some points to remember about JDBC PreparedStatement are:

  1. PreparedStatement helps us in preventing SQL injection attacks because it automatically escapes the special characters.
  2. PreparedStatement allows us to execute dynamic queries with parameter inputs.
  3. PreparedStatement provides different types of setter methods to set the input parameters for the query.
  4. 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.
  5. 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.
  6. PreparedStatement returns FORWARD_ONLY ResultSet, so we can only move in forward direction.
  7. Unlike Java Arrays or List, the indexing of PreparedStatement variables starts with 1.
  8. 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, read more at JDBC PreparedStatement IN clause.

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

By admin

Leave a Reply

%d bloggers like this: