Vladimir MartonThe 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.
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:
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!
