Prepared statements, also known as parameterized statements or parameterized queries, are queries that are built up by a template query and then have data inserted afterward. Separating query command from data can prevent SQL injection vulnerabilities.

Whoa, you’re getting ahead of yourself. What’s an SQL injection vulnerability?

An SQL injection vulnerability is when a malicious user is able to take control of an SQL query to make it do something other than what is intended by the original developer. This can mean different attacks in different scenarios, but an SQL injection attack may result in an attacker retrieving more information than intended, retrieving different information than intended, circumventing authentication or authorization, manipulating data in tables, or simply deleting some, if not all information within the database.

An example SQL query in Java would be:

Now say that you only wanted to get information about a certain user. For authentication, you may be tempted to write code such as:

However, if the user can control either user or password, they can inject other conditions into the SQL query that would enable them to be authenticated as any user, without the correct password.

Let’s say for example that the user could enter the user admin and password foo” or 1=1 --

If this was accepted, then query would now effectively become:

Here ‘--’ is used as a comment, meaning that the trailing double quote would be ignored, and although the password may not be “foo”, 1=1 always evaluates to true, and therefore the query would be returned for the admin user, in this case likely logging in the malicious user as the administrator.

Ok, I understand SQL Injection vulnerabilities now. So how do prepared statements help?

Prepared statements allow a distinction between the functionality of the query and the data.

Using a prepared statement, you would write the above code as follows:

And now whatever a malicious user inserts in as the username or password, the SQL query will not change.

But why? Isn’t it just inserting it into the string as before, like a call to sprintf?

Although this looks a bit like an overly long format string, this isn’t the case. With prepared statements, the query is first parsed and compiled prior to the data being inserted. So, when it executes the query, only at that point is the data bound into the parameters, so the database sees the literal values that are passed in, instead of values that are able to change the meaning of the query.

There are also performance improvements from using prepared statements when you need to use the same query over and over but with differing user input. This is because the query can be sent to the database once and compiled, and then only the different bound values need to be transferred to the database for each subsequent use.

Phew! So as long as we use prepared statements, we’re safe, right?

Not quite so fast! It is still possible to introduce SQL injection vulnerabilities if the original query was created dynamically using user-controlled data. The following example is still vulnerable to an SQL injection attack, despite using prepared statements:

It is critical that if you must use user-provided input in an SQL query, that the user-controlled data is inserted into a bound parameter, however, if there is no user input, it’s still safe to use regular SQL queries the majority of the time.  

Can’t I just make sure that the user input doesn’t contain a double quote?

Checking for, or removing certain characters, also known as blacklisting, can be extremely difficult and is often error prone, and is therefore usually suggested against. Depending upon the query involved, the database (and version) in use, or whether you are using a statically or dynamically typed language may all have a large impact on the different characters and components that you may need to account for, and it is generally always safer to use prepared statements when you want to insert user-controlled data into an SQL query.

What if I did the opposite and made sure the user input only had certain characters?

The opposite of blacklisting is called whitelisting. This is also a suitable technique, but it may differ depending upon the types of data you need to insert, and you may not want to worry about it.

For example, if you need names, you may need characters such as single quotes or hyphens, which may result in SQL injection vulnerabilities under certain circumstances.

It’s also worth noting that although you may start off development of a piece of software with a simple whitelist, software is continuously evolving, and simple whitelists often become very complicated whitelists that are difficult to maintain and difficult to verify if they are secure.

What about stored procedures? I’ve heard some say these protect against SQL injection too

Stored procedures are a type of SQL query that are stored within the database. These are another safe option to use, as long as, similar to prepared statements, they were not created dynamically. The primary differences between stored procedures and prepared statements are:

• Stored procedures are procedures stored in the database, prepared statements are queries stored in the codebase and sent to the database at runtime.
• Stored procedures are written in the language associated with the database (such as T-SQL or PL/SQL), and may be therefore less transportable. Prepared statements are usually standard SQL and are therefore more transportable across different databases.

Both have different uses, and different performance in different scenarios, though they can both protect against SQL injection if used correctly.