SQL Book

Defending against SQL Injection attacks

Introduction

In the first part of this series we saw how SQL Injection attacks work, how they can be used by a hacker to hack all major database brands, and the potential consequences of leaving yourself open to them. In this article we will look at the methods you can secure your database and front end applications to protect yourself from SQL Injection hacks. We will discover that multiple techniques can be layered upon one another to provide stronger defenses against potential attackers.

The defense techniques we will look at in this article include:

  • Validating user input
  • Avoiding building dynamic SQL queries
  • Connection string settings and database object permissions
  • Error handling - preventing revealing error messages
  • Not storing sensitive information in human readable form

Not every user of your application is friendly

All applications available over the web are open to hacking by a large number of potential hackers. A medium sized company is likely to have at least one employee who is familiar with basic hacking techniques whether as an IT professional or just as an interest or hobby. This means applications restricted to the internal network are also potentially at risk.

Because SQL injection requires that special character sequences are entered by the hacker we can try and validate user input to catch these special characters. The characters we validate for are:

  • a single quote which we should replace with 2 single quotes.
  • two hyphens -- which comments out any of the query which is appended after these two hyphens
  • System table names such as SQL Servers
  • sysobjects table.

How you handle these errors is up to you, your validation script should search for these characters and either replace them, remove them or raise an error. You should also notify the system administrator with the form values so that they are aware the potential attack has taken place.

Another method of restricting what SQL a hacker can inject is to set the maximum characters allowed in a textbox, i.e. a first name box should not need to be longer than 20 characters. Limiting the number of characters prevents the hacker entering their own SQL statements. For instance the statement 'TRUNCATE TABLE Customers' is over 20 characters long and so would not be able to be injected through the Firstname textbox that was limited to 20 characters in length.

If it is a web applicaion the length of the submitted textbox values should be validated server side as well as in the web browser in case the hacker managed to get around the client side validation.

Although the above mentioned methods of validating user input are a good start point, it is likely that at some point you will forget to validate every point in your application and so will be exposed somewhere. Because of this more defences are needed...

Avoid building dynamic SQL queries

The SQL Injection attacks depend on the hackers input being joined to the start of a SQL statement that is defined in the script. If you can use SQL that uses parameters instead of concatenating strings together then the ability to inject SQL is greatly reduced. The best solution is to use Stored Procedures. I am not aware of any SQL Injection attack that has worked when using a stored procedure rather than dynamic SQL.

Some databases such as Access do not support Stored Procedures so on occasion you may have to use SQL that is built 'on the fly'. If you must use this type of SQL building then make sure you use parameterised queries. The example below shows an example of creating a paramaterised SQL query and executing it from VB.NET.

-- assumes a connection object called conn

Dim SQL As String
SQL = "SELECT UserName FROM Users " & _
WHERE UserName = @username AND Password= @password"

Dim cmd As SqlCommand = New SqlCommand(SQL, conn)

cmd.CommandType = CommandType.Text

Dim par As SqlParameter
par = New SqlParameter("@Username",SqlDbType.VarChar,20)
par.Direction=ParameterDirection.Input
par.Value = txtUser.Text
par.Parameters.Add(par)

par = New SqlParameter("@Password",SqlDbType.VarChar,15)
par.Direction=ParameterDirection.Input
par.Value = txtPass.Text
par.Parameters.Add(par)

Dim DR As SqlDataReader = cmd.ExecuteReader
If DR.Read Then
 ' the username and password matched so log the user in...
End If

Limit the priveliges of the hacker

When the hacker attempts a SQL injection attack the query will be being run with the permissions of the user account specified in the applications connection string. You should never run your applications under an account with administrators privileges ( e.g. the sa account in SQL server) as this will let the hacker perform any SQL operation they want to. It is best practice to create a new account for the application to access data and restrict the permissions of this account to the minimum necessary for performing SELECT, INSERT, UPDATE and DELETE statements.

To take this a step further the account should only be given rights to execute the stored procedures necessary for running the application. Permissions from the underlying database tables and views should then be removed for the application account so that the application user (including any hackers) cannot view or modify data in a way that the application wasn't designed to do.

Error Handling

Where possible trap your errors, notify the system administrator and then display a 'user friendly' error message. Many applications will display the error messages that help programmers to debug an application. If the hacker can view these messages then they may find out important information about your database name, table and column names. For example:

SQL Server error: Cannot find column 'SalesTotal' in
table 'Customers' in 'MyDatabase'.

This example gives the hacker a huge hand in attempting a SQL injection attack. It would be better to trap the error and if necessary give a less specific but still useful error message:

Database error. Unrecognized column name.

If possible, do not store sensitive information in human readable format

Although strictly not a defense against SQL injection, it makes sense to not store sensitive information such as passwords in a human readable form. This may mean encrypting or hashing your passwords before storing them in the database. The advantage of this is that if the hacker finds a weakness and the database is compromised, they will not be able to view the passwords of your user accounts etc.

Conclusion

In this article we have demonstrated some techniques for helping defend against SQL Injection attacks. We can see that we can implement security measures both in the database itself (such as implementing Stored Procedures and restricting table permissions) as well as in the front end application (such as user validation and error handling). If your database supports Stored Procedures then this is the best approach to take.

Join our newsletter

Subscribe to our free weekly newsletter and get exclusive SQL tips and articles straight to your inbox. 

 Your email address is only used to send you our newsletter.