SQL Injections with Prepared Statements

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.

Is SQL injection possible even on a prepared statement

This query : String query = "SELECT * FROM Users WHERE username=? and password=?"; is safe, because whatever the parameters can be, it will still be executed as a simple select. At most, it will end browsing a whole table.

But prepared statement is just a tool and (bad) programmers may still misuse it.

Let's look at the following query

String query = "SELECT id, " + paramName + " FROM Users WHERE username=? and password=?";

where paramName would be a parameter name. It is only as safe as paramName is, because you use directly a variable to build the string that will be parsed by the database engine. Here PreparedStatement cannot help because JDBC does not allow to parameterize a column name.

So the rule here will be :

• avoid such a construct if you can !
• if you really need it, double check (regexes, list of allowed strings, etc.) that paramName cannot be anything other than what you expect because that control is the only prevention against SQL injection

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!

Avoiding SQL injections with prepare and execute

my $sth = $dbh->prepare("SELECT * FROM Users WHERE user='?'");

This won't work because it's searching for a literal '?' character — not a parameter. If you try to send a value for the parameter, MySQL will be like, "what do you want me to do with this?" because the query has no parameter placeholder.

If you want to use a parameter, you must NOT put the parameter placeholder inside string delimiters in the SQL query, even if the parameter will take string or datetime value:

my $sth = $dbh->prepare("SELECT * FROM Users WHERE user=?");

The next example:

$sql_query = "SELECT * FROM Users WHERE user='" . $user . "'";
$dbh->prepare($sql_query);
$dbh->execute();

That will run the query, but it's NOT safe. You can prepare any query even if it has no parameters.

Using prepare() is not what makes queries safe from SQL injection. What makes it safer is using parameters to combine dynamic values instead of doing string-concatenation like you're doing in this example.

But using parameters does require the use of prepare().

PS: You don't need to put ; at the end of your SQL queries when you run them one at a time programmatically. The separator is only needed if you run multiple queries, like in an SQL script, or in a stored procedure. In your examples, the ; is harmless but MySQL doesn't require it, and it will just ignore it.

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

Can this prepared statement prevent SQL injection?

Yes it will prevent SQL injection because

Prepared statements uses bound parameters.

Prepared Statements do not combine variables with SQL strings, so it is not possible for an attacker to modify the SQL statement.

Prepared Statements combine the variable with the compiled SQL statement, this means that the SQL and the variables are sent separately and the variables are just interpreted as strings, not part of the SQL statement.

---

Related Topics