How Can Prepared Statements Protect from SQL Injection Attacks

How can prepared statements protect from SQL injection attacks?

The idea is very simple - the query and the data are sent to the database server separately.

That's all.

The root of the SQL injection problem is in the mixing of the code and the data.

In fact, our SQL query is a legitimate program.
And we are creating such a program dynamically, adding some data on the fly. Thus, the data may interfere with the program code and even alter it, as every SQL injection example shows it (all examples in PHP/Mysql):

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";

will produce a regular query

SELECT * FROM users where id=1

while this code

$spoiled_data = "1; DROP TABLE users;"
$query        = "SELECT * FROM users where id=$spoiled_data";

will produce a malicious sequence

SELECT * FROM users where id=1; DROP TABLE users;

It works because we are adding the data directly to the program body and it becomes a part of the program, so the data may alter the program, and depending on the data passed, we will either have a regular output or a table users deleted.

While in case of prepared statements we don't alter our program, it remains intact

That's the point.

We are sending a program to the server first

$db->prepare("SELECT * FROM users where id=?");

where the data is substituted by some variable called a parameter or a placeholder.

Note that exactly the same query is sent to the server, without any data in it! And then we're sending the data with the second request, essentially separated from the query itself:

$db->execute($data);

so it can't alter our program and do any harm.

Quite simple - isn't it?

The only thing I have to add that always omitted in the every manual:

Prepared statements can protect only data literals, but cannot be used with any other query part.

So, once we have to add, say, a dynamical identifier - a field name, for example - prepared statements can't help us. I've explained the matter recently, so I won't repeat myself.

How does a PreparedStatement avoid or prevent SQL injection?

The problem with SQL injection is, that a user input is used as part of the SQL statement. By using prepared statements you can force the user input to be handled as the content of a parameter (and not as a part of the SQL command).

But if you don't use the user input as a parameter for your prepared statement but instead build your SQL command by joining strings together, you are still vulnerable to SQL injections even when using prepared statements.

How do Prepared Statements prevent SQL injection better than Statements?

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 ?.

Will prepared statements prevent sql injection attacks?

According to this, yes: http://en.wikipedia.org/wiki/SQL_injection

In that case the statement is already compiled and injected code would not be interpreted (and thus not be executed) again.

SQL Injection with Prepared Statements

No, it will not be vulnerable. Using prepared statements is the suggested way to protect against SQLi.

If you are sure department_type parameter is not affected from any user input, then it is safe to place it directly into the query.

A variable's being affected from user input is a bit complicated though. This effect can be indirect as well. For example if department_type is retrieved from database, which was saved on another page being affected by user then it is indirectly affected from user input and should be considered as "not trusted".

How secure are PreparedStatements?

The value in prepared statements is that you separate your variable data from the query string itself. This provides two main advantages:

1. in the case of a repeated query which differs only in the data sent, the query itself does not need to be re-parsed; instead, only the new data needs to be sent.

2. You are protected from SQL injection attacks that might cause improperly escaped data included directly in your query string, because your data is separate from the query string itself and is sent in a safe manner.

Prepared statements do not (and are not intended to) protect you from man-in-the-middle attacks between your client and your database that might alter the query (or data) you intended to send. For that, you need to use an SSL transport, and otherwise ensure that attackers can't interpose themselves in your communication with your database.

prepared statement and SQL injection

A prepared statement query will be something like this:

String sql = "insert into offers (name, email, text) values(?, ?, ?);

You the create a PreparedStatement with this. Then set the 1-based indexed values and execute the query. 100% safe to SQL-injections!

With Spring, you will probably use a JdbcTemplate. Note there is a NamedParameterJdbcTemplate implementation which does not use indexes, but named parameters. Query will be:

String sql = "insert into offers (name, email, text) values(:name, :email, :text);

Then set the name-based values and execute the query. Again 100% safe to SQL-injections!

Are numeric parameters subject to SQL injection attacks?

I can think of two things that might go wrong even if id is an int and can never be anything else:

1. Someone in the future might change the id type to a String.
2. Someone might copy-paste your code to another part of the codebase, and then modify the SQL so that it's concatenated with a String, making that part vulnerable.

How to build query in Java to prevent SQL injection using prepared statement

Your first fragment of code is unsafe and vulnerable to SQL injection. You should not use that form.

To make your first fragment safe, you would need to manually escape the value to prevent SQL injection. That is hard to do correctly, and choosing the wrong way of handling values could potentially reduce performance depending on the underlying database (eg some database systems will not use an index if you supply a string literal for an integer column).

The second fragment is the standard way. It protects you against SQL injection. Use this form.

Using a prepared statement with parameter placeholders is far simpler, and it also allows you to reuse the compiled statement with different sets of values. In addition, depending on the database, this can have additional performance advantages for reusing query plans across connections.

---

Related Topics