How do Prepared Statements prevent SQL injection better than Statements?

Question

Background: I have started a project using JDBC and MYSQL to simulate a bookstore, all local. To connect to the database, I started out using a Statement but I began to read that when using a query multiple times that just changes its parameters, it can be more efficient to use a PreparedStatement for those queries. However, the thing advantage I read the most about was how PreparedStatements could prevent SQL injection much better.

Sources: Answers on this thread here
Google
Professors

My Question: How do PreparedStatements prevent SQL injection better, or even different for that matter, than Statements when dealing with parametrized queries? I am confused because, if I understand correctly, the values still get passed into the SQL statement that gets executed, it's just up to the the programmer to sanitize the inputs.

Answer 1

You're right that you could do all the sanitation yourself, and thus be safe from injection. But this is more error-prone, and thus less safe. In other words, doing it yourself introduces more chances for bugs that could lead to injection vulnerabilities.

One problem is that escaping rules could vary from DB to DB. For instance, standard SQL only allows string literals in single quotes ('foo'), so your sanitation might only escape those; but MySQL allows string literals in double quotes ("foo"), and if you don't sanitize those as well, you'll have an injection attack if you use MySQL.

If you use PreparedStatement, the implementation for that interface is provided by the appropriate JDBC Driver, and that implementation is responsible for escaping your input. This means that the sanitization code is written by the people who wrote the JDBC driver as a whole, and those people presumably know the ins and outs of the DB's specific escaping rules. They've also most likely tested those escaping rules more thoroughly than you'd test your hand-rolled escaping function.

So, if you write preparedStatement.setString(1, name), the implementation for that method (again, written by the JDBC driver folks for the DB you're using) could be roughly like:

public void setString(int idx, String value) {
    String sanitized = ourPrivateSanitizeMethod(value);
    internalSetString(idx, value);
}

(Keep in mind that the above code is an extremely rough sketch; a lot of JDBC drivers actually handle it quite differently, but the principle is basically the same.)

Another problem is that it could be non-obvious whether myUserInputVar has been sanitized or not. Take the following snippet:

private void updateUser(int name, String id) throws SQLException {
    myStat.executeUpdate("UPDATE user SET name=" + name + " WHERE id=" + id);
}

Is that safe? You don't know, because there's nothing in the code to indicate whether name is sanitized or not. And you can't just re-sanitize "to be on the safe side", because that would change the input (e.g., hello ' world would become hello '' world). On the other hand, a prepared statement of UPDATE user SET name=? WHERE id=? is always safe, because the PreparedStatement's implementation escapes the inputs before it plugs values into the ?.

Answer 2

When using a PreparedStatement the way it is meant to be used - with a fixed query text with parameter placeholders, no concatenation of external values -, then you are protected against SQL Injection.

There are roughly two ways this protection works:

1. The JDBC driver properly escapes the values and inserts them in the query at the placeholder positions, and sends the finished query to the server (AFAIK only MySQL Connector/J does this, and only with useServerPrepStmts=false which is the default).

2. The JDBC driver sends the query text (with placeholders) to the server, the server prepares the query and sends back a description of the parameters (eg type and length). The JDBC driver then collects the parameter values and sends these as a block of parameter values to the server. The server then executes the prepared query using those parameter values.

Given the way a query is prepared and executed by the server, SQL injection cannot occur at this point (unless of course you execute a stored procedure, and that stored procedure creates a query dynamically by concatenation).