Fat Agnus

Recently, I did a code review on web application that a colleague had developed, and to my horror I discovered that is was wide open to this sort of attack. I might be a bit naive, but I thought that every professional developer knew about this sort of attack – I guess not. Luckily for us (who use .net) this is something that can be prevented fairly easy and it should be.

What is SQL Injection

“SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application.” – Wikipedia.

Example

http://www.mysite.com/product.aspx?id=1000

1
2

string id = Request.QueryString(“id”);
string sql = “SELECT * FROM products WHERE id = ‘“ + id + “’”;

If you are doing something like this, then your database can be hacked. If SQL Injection is new to you, then you are probably thinking – How? It is really straight forward.

Example

http://www.mysite.com/product.aspx?id=1000’;DROP Database myDB –

this will be executed against the database like so

1

SELECT * FROM products WHERE id = ‘’;DROP DATABASE myDB –

How to prevent this

Lets rewrite the above example so it will become safer.

Example

1
2
3
4
5
6
7
8
9
10
11
12

string id = Request.QueryString("id");
 
using (SqlConnection connection = new SqlConnection(GetConnectionString()))
{
	string sql = "SELECT * FROM products WHERE id = @id"
	using (SqlCommand command = new SqlCommand(sql, connection))
	{
	    command.Parameters.Add("@id", SqlDbType.VarChar).Value = id; 
 
	    // Do something
	}
}

The great thing about this is that ADO.NET knows how to encode the ID value and thereby prevent someone from sneaking in some evil SQL expression.

Final word

The above example will help you a lot, if you are new to this subject but another thing you want to watch out for is someone trying to insert javascript into your database. There are a lot of websites who are infected with evil javascript because they don’t validate the input from the user, so validate the input, never trust the data.

Here is another great post about the subject.