Statement vs PreparedStatement in java

Statement vs PreparedStatement

PreparedStatement is a subclass of Statement.

Similar to Statements, we can use a PreparedStatement to run SQL statements against database.

Here are the differences between them :

Efficiency

When a statement is executed, the JDBC driver compiles the SQL statement before sending it to database.

If we have multiple similar statements, the same process of compiling each statement before sending to database is repeated.

Statement stmt = conn.createStatement();

stmt.executeUpdate("insert into employee(age,name) values(23,'James')");

stmt.executeUpdate("insert into employee(age,name) values(25,'Raj')");

stmt.executeUpdate("insert into employee(age,name) values(23,'Charlie')");

Compiling each SQL statement can result in poor performance if a large number of statements are executed.

A PreparedStatement compiles the statement once and uses substitution variables to modify the final statement to be executed.

String insertSql = "insert into employee(age,name) values(?,?)";

PreparedStatement ps1 = conn.prepareStatement(insertSql);

ps1.setInt(1, 23);

ps1.setString(2, "James");

ps1.executeUpdate();

ps1.setInt(1, 25);

ps1.setString(2, "Raj");

ps1.executeUpdate();

...

So, PreparedStatement is efficient since the statement is compiled only once even though it is executed several times.

SQL Injection attack

SQL injection in a technique where an attacker could decide to place malicious code inside of the string that is being used to query some data.

For example, here is code using Statement to query User table for an userid.

//userId is retrieved from a textbox control in UI

stmt.executeQuery("Select * from User where id = " + userId);

An attacker can provide the userid as :

12345 or 1 = 1

Now, the final query become :

Select * from User where id = 12345 or 1 = 1

This statement will always evaluate to true because of the “or” condition.

PreparedStatement helps prevent SQL Injection attack as it uses substitution variables instead of concatenating strings.

String selectSql = "Select * from User where userid= ?";

PreparedStatement ps3 = conn.prepareStatement(selectSql);

ps3.setString(1, "Jeromy");

The SQL used in a PreparedStatement is precompiled, which means that a valid SQL string is formed prior to the SQL being sent to the DBMS.

From that point on, the parameters are sent to the driver as literal values and not executable portions of SQL; thus no SQL can be injected using a parameter.

Forming valid SQL statements with non-standard data

PreparedStatement partially insulates the application from the details of creating a valid SQL statement.

For example :

String text = "Steve O'Connor";

stmt.executeUpdate("Update employee set name = " + text + "where id = 1");

This won’t work, since the final sql query will be :

Update employee set name = ‘Steve O’Connor’ where id = 1

When you use a PreparedStatement with substitution parameter, the JDBC driver assumes responsibility of creating valid SQL statements.

For example, we can store a string value in PreparedStatement with or without quote in following manner :

String sql = "Update employee set name=? where id=1";

PreparedStatement ps3 = conn.prepareStatement(sql);

ps3.setString(1, "Steve O'Connor");

ps3.executeUpdate();

Similarly, PreparedStatement is helpful in creating valid SQL statements with nonstandard objects like Date, Time, TimeStamp,BigDecimal etc.

Top Java Tutorial

Statement vs PreparedStatement in java