Guide Area

Database Tutorial – Statement vs PreparedStatement

The first article of the series Best Database Practices will explain what Statements and PreparedStatements are, what is the difference between the two, performance comparison and simple code examples.

In the world of programming languages, database connectors are used to connect to any database. These connectors are usually included in the languages themselves, but you can also obtain 3rd party libraries if you want some more advanced framework. No matter what library you use, you will be using Statements to execute SQL queries.

All code examples in this article will be written in Java.

What is a Statement

Statement is a simple object which executes the SQL query. It is created using the connection object. A query is passed to the statement in string format and should be populated with actual values. There is no way to substitute values after creating the statement. See the example below:

Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM mytable");

Statements are not cached. After the statement is executed, the object is null-ed and connection is closed. If you want to execute the same query again, the database will try to compile it all over again, which results in higher response delay.

What is a PreparedStatement

PreparedStatement is similar to normal Statement in a way that they are both created from a connection object. PreparedStatement also executes the SQL query, just as Statement. But at the same time, PreparedStatements are much more secure and also faster. Why?

PreparedStatement does not execute the query right away – it prepares it. What this means is that the query is loaded into the PreparedStatement object and waits for further processing. This is where you normally do variable substitution to prevent SQL injection. See, in a regular statement, you’d have to prevent the injection yourself. For example, you’d have to check if the query was not manipulated with. If yes, you need to remove special characters that could mess up your query and format variables to their proper formats. And even then, you cannot be sure the injection won’t happen.

-- Let's say we give user a chance to delete his profile but we ask him to insert his userid.
userid = context.getRequestParam("userid");

-- Instead, he inserts string "12 OR 1=1"
userid = "12 OR 1=1"
sql = "DELETE * FROM users WHERE userid=" + userid;

-- And the SQL query will result in:
"DELETE * FROM users WHERE userid=12 OR 1=1"
-- This query will delete all users from the table

A general rule in programming says – never trust user input. PreparedStatement has it’s own way of preventing situations like the one above. If you used PreparedStatement to substitute variable userid and then execute the query above, it would handle the string “12 OR 1=1” as a content of the parameter userid, and not as a part of the final query.

// Let's say user tries to enter his email as 
// "me@gmail.com\" OR email LIKE \"%@%\""
// (the %@% will match any email containing symbol @)

String email = context.getRequestParam("email");
String sql = "DELETE * FROM users WHERE email = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, email);


// Query without PreparedStatement: 
// DELETE * FROM users WHERE email = "me@gmail.com" OR email LIKE "%@%";

// Query with PreparedStatement: 
// DELETE * FROM users WHERE email = "me@gmail.com OR email LIKE \"%@%\"";

I think you can see the security advantage of using PreparedStatement. Of course, if user inputs that into the query, he will never receive any result, but that is not something that hackers are looking for – they simply want to cause troubles.

Another advantage of it is that it can be cached by the database server. I will quote one of the Stackoverflow answers here:

Without caching, you will get a new PreparedStatement each time you request one from the Connection. With caching, you will frequently get the exact same Java object of type PreparedStatement if you provide the same SQL string. If you provide the same SQL to a PreparedStatement, even with different parameters, often the database can reuse information like the execution plan, but only if you continue to use the same PreparedStatement. Caching makes that easier by not requiring your app to hold on to that PreparedStatement reference itself.John Watts

Conclusion

My humble opinion is – if you can, use PreparedStatements. In most cases, it will be advantageous to you. It can become tricky if you plan to use connection pooling – if you wish to read more, check out my next article from this series!

Rate this post

Vladimir Marton

Software Developer focused on Java, Python, PHP and SQL. Guidearea is my oldest project where I write articles about programming, marketing, SEO and others.

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.