Blog @ andreaprovaglio.com

SQL injection vulnerabilities are still very common in web applications (OWASP rates injection flaws as the second most important security issue in Top Ten 2007). Input validation and parameterized queries (also called prepared statements) are the most popular safeguard options to prevent SQL injections. However, even using parameterized queries, an application can still be vulnerable to SQL injections. This is not a new topic (early researches on exploiting parameterized queries appeared more than 3 years ago) but there is still a lot of confusion among security-unaware software developers and architects.

As usual, problems arise when we can't see the forest for the trees, in other words when there is a lack of holistic approach to security. Parameterized queries are not the silver bullet against SQL injections, they must be used properly and in the proper context to be effective. If a strongly typed parameterized statement is used to execute a static query involving no stored procedures, we can be reasonably sure that it cannot be exploited by injection attacks (at the time of writing, there are no known vulnerabilities to this scenario). But what if the query to be executed is dynamically generated? What if a stored procedure is called instead?

At the end, we have two different situations revolving around the same issue, namely string concatenation, which is the root of the vulnerability. SQL injections consist in a malicious user tricking a SQL interpreter into executing arbitrary code passed as a parameter to an application: this is possible if somewhere in the application a user input is concatenated to a SQL statement and passed to the SQL interpreter for execution. The SQL interpreter has no way to discriminate what comes from the developer and what from the user, hence the exploit. Parameterized queries essentially allow the developer to explicitly separate the SQL statement from the parameters, preventing the SQL interpreter from executing user injected code.

Now, string concatenation can occur anywhere in an application, so parameterized queries must be consistently used, everywhere across the application to be effective. Let's have a look to the stored procedure scenario. Many applications use stored procedures to retrieve and modify data; some applications use stored procedures as the sole way to access the data. This may be the result of a careful design or the random outcome of an internal fight between the development team and the database administrators. The former could lead to catastrophic consequences: just imagine the developers thinking "We use parameterized queries, so we are secure," while the db guys think "Stored procedures can only be called by internal applications so the input is trusted." Trust no one, Mr. Mulder.

The dynamic query scenario is probably more common. In some situations a SQL query cannot be determined at design time so the actual SQL statement must be programmatically created at runtime (i.e., as a result of a web form where the user can choose one or more search options). We call this technique Dynamic SQL (where the "dynamic" portion of the query should be limited to query clauses, i.e., parameter values can vary at runtime even with a static prepared statement). Most newbie programmers duly use parameterized queries when they have to execute a static query but don't realize (or are too lazy...) they can and, actually, should use the same approach with dynamic queries. So, they revert to string concatenation exposing their applications to the hordes of evil attackers out there.

In the end, here's just a couple rules of thumb to create safer code:

1. Never (I mean it!) concatenate user input to SQL instructions to create queries. Always use parameterized queries to separate code from parameters (you can create parameterized queries in stored procedures as well)
2. Always use parameterized queries, even when you have to create dynamic queries (it's a little bit harder but absolutely worthwhile!)

I'll give some examples of safe dynamic SQL in future posts.