Sasha Sydoruk

SQL injection is a very dangerous thing. An experienced hacker can break your site open in the matter of minutes, if the SQL injection opportunity exists. To demonstrate how quickly things can happen watch this video:

[youtube]MJNJjh4jORY[/youtube]

As you can see, once the hole has been found, you application is fully open to data theft and vandalism.

What is the best way to prevent SQL injection? Never and I repeat never, concatenate together your SQL queries. One common mistake that I see quite often – “We are safe because we are using stored procedures”, but when you look inside that stored procedure you see the same string concatenation; usually this concatenation happens with ordering and sorting clauses. It is very important to realize that you are still vulnerable even if you concatenate your strings in the stored procedures.

What is the right way to do it? Use SqlParameters or any other implementation of IDataParameter that works with your database. So, instead of this:

string sqlBadQuery = “SELECT * FROM User WHERE FirstName = ‘” + tbxFirstName.Text + “‘”;

use this:

SqlParameter spFirstName = new SqlParameter(“@FirstName”, SqlDbType.VarChar, 50);
spFirstName.Value = tbxFirstName.Text;
string sqlQuery = “SELECT * FROM User WHERE FirstName = @FirstName”;

In this example we created SqlParameter spFirstName. In the constructor we are providing the name, data type and length of the parameter. Notice that because we specified the data type as VarChar, we didn’t have to use single quotes in our query. SQL Server will do it for us.

A couple of times I was told that even when you use parameters, you are still vulnerable to SQL injection. I am not sure how correct this statement is. I looked for supporting information and I could not find anything. When you execute an inline, parameterized query in SQL Server with profiler running you will notice that this call is handled by sp_executesql. sp_executesql provides the same benefits as stored procedure does – security and execution plan caching. So, even if you pass some bad input like this – “’ or 1 = 1 –”, it will be properly enquoted and 0 rows will be found. To find out more about sp_executesql - go here.

Now, if you have stored procedures available, by all means use them. But sometimes you just have to revert to dynamic SQL, and when you do, make sure you use it correctly. Here is a really good article by Erland Sommarskog - The Curse and Blessings of Dynamic SQL. This article describes in a great detail why you would use dynamic SQL and how to do it in a safe manner.

Wikipedia has a really good description of what a SQL Injection is. You can find it here. Also here is a general list of links that discuss the issue and the remedies to SQL Injection:

• http://www.unixwiz.net/techtips/sql-injection.html
• http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
• http://www.ngssoftware.com/papers/advanced_sql_injection.pdf