Harden Your Application Against SQL Injection Attacks

So far, I've covered methods for protecting your application from script exploits and unauthorized user access. However, if your application is using a database — and it most probably does! — those methods still leave a gaping hole in your application security.

A common hack against your database comes in the form of SQL injection attacks. To put it simply, SQL injection attacks consist of giving your application a string value that concatenates to your original SQL string call, producing something other the intended query.

For example, let's say you have username and password form fields to authenticate the user against the database for gaining access to your application. Your SQL call for checking the user's credentials would be similar to the one below:

Select * From Users 
  Where username = '" + userName.Text + "'
  and Password = '" + Password.Text  + "'"

Now, if a valid user enters his credentials, say "Tom" as the username and "tom1234" as the password, the query looks like this to the compiler:

Select * From Users Where username = 'Tom' And Password = ' tom1234'

Tom is authenticated and redirected to the applications main page.

Because the SQL query is using form field values as parameters that will compose the SQL call, a hacker can come along and use a malformed SQL injection attack to crash the query, thus gaining access to your application. A common string to use in such a malformed attack is the string ' Or 1=1 -- which results in this query:

Select * From Users 
  Where username = ' ' Or 1=1 --   ' And Password = ' '

Two hyphens together denote a comment in SQL syntax, so the rest of the query (after the second 1) is ignored, leaving us with the SQL call:

Select * From Users Where username = ' ' Or 1=1

Now, because we only need one record returned to validate the user (the user's record based on his credentials) the above statement will now return all records, regardless of matching usernames or passwords. That's because 1 will always equal 1; and, because the statement is using an OR condition, it becomes true. The application routes the user (hacker) to the application's main page, because the SQL statement found at least one record from the query.

You can see from that simple example how most SQL injection attacks can be effective in gleaning important information to changing and removing data from your database. The hacker only needs a basic understanding of SQL syntax to accomplish this.

To protect against SQL injection attacks, limit the use of dynamic SQL strings that use form and URL string values, as in the example above. Instead, use stored procedures where and when possible. If you must use dynamic SQL, then retrieve the string parameters using the Request.Form method. It automatically encodes your string values. An SQL injection attack using hyphens, single, or double quotes, or any other odd characters will no longer work if your values are encoded. Doing these two things harden your application against these types of attack and most importantly protect your data.

Other things you can do to secure your application include making sure that you close all your database connections, including record sets, when not using them. Also, make sure your SQL web user does not have system or administrative privileges. It's also a good idea to change your SQL server process from running as a system process (default) by creating a different account, and having SQL server use that account when running the server. Doing these things can keep hackers from gaining control of other processes and services that use these permissions.

While most attacks are script exploits against your application, other attacks can and do occur, such as compromising your web server. By gaining control of your web server, the attacker no doubt has control of your applications and the permissions that protect it.

To prevent this, make sure any unecessary services are disabled, such as indexing service. Also make sure that the Scripts virtual directory that comes with the web server does not have anonymous access. Another important yet simple way for further protection is to move your applications out of the Inetpub\wwwserver directory to another location on the server. Attackers look at this directory first and often with automated scripts that scan this directory for weak permissions.