Avoiding SQL Injection Attacks

Take a simple SQL Query.

Select account_number from users where username='USER' and password='PASSWORD' This might be the kind of query you'd use to see if a user entered his username and password correctly.  If you get an account_id then the user is logged in.

So, you put a couple of text boxes on a login page and try to pass the values from the text boxes to the query, like this.

string USER=tbUser.text;
string PASSWORD=tbPassword.text;
string SQL="Select account_number from users where username='" + USER + "' and password='" + PASSWORD + "'" That way if the user types in "Bob" and "MyPassword" the query executed looks like this.

Select account_number from users where username='Bob' and password='MyPassword'  Perfect.  This works great, and was a pretty standard way of doing this kind of query for a very long time, dating back to before the world wide web.  But what if a SQL-savvy hacker wants to see what kind of mischief he can cause, and tries playing with your SQL's head.  What if he entered something like this...

Username: ' or ''='
Password: ' or ''='

What does that do to our query?

Select account_number from users where username='' or ''='' and password='' or ''='' There is a really good chance that this query will bring back all the records in your database, and then think that this user is correctly logged into the very first one.  Note that login queries are not the only kind that can be hacked this way, but they are the easiest targets.  It could just as easily be your help ticket system, or anything else exposed to the web.

This is called a SQL injection attack. It's very common, but luckily it's very easy to thwart.  But there are some programmers who go about it the wrong way.

How not to protect yourself.

1. Don't think your site is too small to get noticed and attacked.
2. Don't  rely on Javascript, as it is easily disabled.
3. Don't rely on Flash, as there are still people who hate it and will not load it on their browsers.
4. Don't rely on HTML settings like maximum character lengths. They can be dispatched.

How to protect yourself.
An easy way to render SQL Injection attacks ineffective is to use sql parameters.  The not so easy part of that - if you have a massive web site - is that you have to edit ALL of your SQL that is exposed to the web.  in SQL Server, you do that like this:
string SQL="Select account_number from users where username=@USER and password=@PASSWORD"; Then, in your query you use parameters to fill in the values, like this:
myCommand.Parameters.AddWithValue("@USER", username.text);
myCommand.Parameters.AddWithValue("@PASSWORD", password.text); Now (at least in SQL Server), no matter what they enter, it will be treated as query values.  if there is no user who's name and password are ' or ''=', then they will not be logged in.  I know it's a hassle to recode all your website queries.  But nowhere near as big a hassle as having a massive data breach to deal with.


...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.