Examples PreparedStatement Query

Prepared statements are the preferred way to construct SQL statements with parameters. They are used to reduce the risk of SQL injection attacks

The Problem

If a website allows a user to search for books by author, the backend will need to query the database using input passed in by a web page. The first stab might be:

var statement = connection.createStatement("SELECT * FROM books WHERE author ='" + request.query.author + "'");
statement.query()
.then((rows) => {
    // format the rows for the web response
})
.catch((error) => {
    // handle the error
});

This is dangerous. If request.query.author is "John Grishom'; DROP TABLE books; SELECT 'Gotcha", then the query becomes:

SELECT * FROM books WHERE author ='John Grishom'; DROP TABLE books; SELECT 'Gotcha'

And as soon as the query was performed the entire books table would be gone from the database.

The programmer can try to do some string substitutions to protect the query, but that work is already done. Use a PreparedStatement.

The Solution - PreparedStatement

Let's clean up the code using a PreparedStatement.

var statement = connection.prepareStatement("SELECT * FROM books WHERE author = ?");
statement.query(request.query.author)
.then((rows) => {
    // format the rows for the web response
})
.catch((error) => {
    // handle the error
});

Not only is the string properly quoted, but the statement is easier to read. Also, the statement can be created once and reused multiple times with different arguments.

When to Use a Regular Statement

Generally, you should use a PreparedStatement for everything. However, a regular statement is faster to create. It's not any faster to query, but if you control the SQL, if you never make mistakes, and if the speed at which you create statements is crucial, then a regular statement might be your solution. Usually though, a prepared statement will work if you refactor your code.