Field Notes Inside an Integrated Communications Agency

Any time we publish a web form with an input field, we are presenting the user community with an opportunity to trick our server into running a piece of malicious code. An example of this, called the SQL Injection, is logging in to a web site by submitting a username of

' or 1=1 --

The effect of this is often to allow an unauthorised user to log in. Here's how it works. If the site developer was not careful, they may have used a SQL statement like "Select * from user where username = '<name from form>' and password = '<password from form>'." Because the first single quote in hacker's username terminates the "username = '" part of that statement and the "--" terminates the SQL statement, all the database engine sees is "Select * from user where username = '' or 1=1."  That statement would lead the application to believe that a user/password match had been found.  Viola, the hacker is logged in as whoever was the first registered user (often an admin).

It can be worse as well.  If you have a dynamic search query built from a Web form, the hacker might try:

 Union Select * from sysobjects

So now you have a page that has, with the authority of your application, requested a list of database objects, which it might present as search results, depending on you the results page has been written.  With a list of database objects and a site that is vulnerable to SQL Injection attacks, a hacker can do a lot of damage.

Some people think that you can get around this by cleaning up inputs through string manipulation or introducing line breaks.  Usually, they are wrong.

Even if your site won't give up critical information or permit unauthorized access, you can still be embarassed by attacks that cause pages to crash. The thing to do is to just not allow any SQL Injection at all.  Fortunately, this is easy to do.

Calling stored procedures with parameters instead of just passing ad-hoc SQL is proof against SQL Injection attacks. So is building your SQL ad-hoc but wrapping all inputs in parameters:

SQLCommand comm = new SQLCommand("Select * from User where username = @Username");

comm.add(new SQLParameter("@Username", txtUserName.text);