Welcome to William's ekasi

Here’s a scary stat for web developers:

19.9% of all web hacks are performed using SQL Injection
Source: http://tinyurl.com/WebHackDB

That’s huge! 1 in every 5 hacks is performed using SQL Injection which worries me, I really thought SQL Injection was a solved problem.

How does it work?

SQL Injection works because of the options we have for passing parameters to SQL. The most simple option is where the SQL statement that gets passed to SQL actually includes the text of the parameters. An example of this is shown below:

In this example, the actual SQL statement is being created based on the searchCriteria string parameter. In a normal usage scenario, this would result in a SQL statement being executed by the database that looks something like:

Which is fine for the happy path, but what happens if someone comes along and uses the parameter “Beer’ UNION ALL SELECT * FROM sys.tables;—“? The resulting SQL statement looks drastically different:

This is still perfectly valid SQL and the database engine will execute it exactly as it should (note: this will only work if the columns match up). The database doesn’t know that it’s not supposed to execute the second part of the query. The problem is We haven’t separated the SQL query from the parameter data. We’re treating it all as one. Thankfully, this is an easy fix.

Preventing SQL Injection

The best way to prevent SQL injection is to parameterize your queries. Doing this separates your query from your parameters meaning that SQL will know that regardless of what is passed in as a parameter, it must be treated as a value and not as a potential query. This also has the added benefit of allowing SQL to reuse execution plans (each new parameter in the example above would have to result in a new query plan). Parameterizing your queries is a simple 2-step procedure.

Step 1. Replace the parameter parts of your query with parameter tokens

Our example uses the searchCriteria parameter to build up a string, so instead of changing the string we’re going to replace that section with a token:

Notice how we aren’t passing the parameter in through the string anymore but instead we’re using the @SearchCriteria as part of our string? That’s the token. For each of your parameters, include a unique tokened parameter.

Step 2. Include the parameter’s value in the IDbCommand’s Parameters list

By using the command’s parameter value we have told SQL what the query is as well as what the value of the parameter is and SQL can use that to gain meaning about the query. This means that our query is no longer vulnerable to SQL Injection.

Dynamic SQL

There are times where you need to work with dynamic SQL and this is an often neglected section that isn’t protected from SQL Injection. To protect dynamic SQL from SQL Injection the same solution works: Parameterizing your queries.

Yes, dynamic SQL can also be parameterized – this time, by SQL:

Stored Procedures?

I don’t know where the rumour started about stored procedures being safe from SQL injection, but they aren’t. Stored procedures offer zero protection against SQL Injection!

Here’s an example.

We’re executing a stored procedure here, but we’re still vulnerable to SQL Injection because we’re not separating the parameters from the exec statement. Exactly the same as using normal SQL. Granted, in this case it is more difficult to do SQL injection because of syntax restrictions but this is by no means a solution. The only solution is to again, parameterize it!

In ADO.NET there is a handy property on the IDbCommand object which specifies the query type. If we set the query type to Stored Procedure then ADO.NET forces us to parameterize it. It’s at that point that we get the security we’re looking for.

Notice how here we don’t even have to specify the parameter tokens in the stored procedure name (AddComment)? That’s because ADO.NET tells SQL that we’re executing a particular stored procedure so the parameters have to match up. But remember, the security isn’t coming from the stored procedure – it’s coming from the parameterization!