vary on the number of websites that have been compromised, but even the lowest of the numbers is still in the hundreds of thousands, and at the peak of the infection, they included sites like the United Nations.

Before you go jumping off the SQL Server platform because it's not secure, the truth is all database platforms suffer from this attack vector. Attacks against SQL Server are simply more common because there are more SQL Servers deployed in hosting environments. Developers – who don't know how to protect against these kinds of strikes – are developing the Web pages. Because of the high success rate, this sort of attack is very popular with the malware community, and as a community, if we can remove the hackers' ability to launch these attacks, our sites will be protected and the attackers will move on.

How SQL injection works

In order for Web applications to be susceptible to a SQL injection attack, these things need to be true:

1. Your website uses dynamic SQL. Now this doesn't mean that the application creates SELECT or INSERT statements dynamically. It means any code is created dynamically, including having the application dynamically create a stored procedure command before executing the string.
2. When taking in values from the client application, the values are not validated -- for syntax or for escape characters.

The way it works is that the attacker escapes out of the existing command, either by putting a single quote within a string value or by placing a semicolon at the end of a numeric value and putting a SQL command after the escaped character. When the end result is executed against the database, the command looks something like this:

exec sel_CustomerData @CustomerId=47663; TRUNCATE TABLE Customer

This causes the sel_CustomerData procedure to be executed, after which the TRUNCATE TABLE command is run and the Customer table is truncated. If the table has a foreign key constraint on it, the database will return an error giving the hacker the name of the database table that the constraint is on. A clever hacker uses this technique to find the name of every table in the database. The hacker can then insert data into your tables or select data from your tables (depending on what the database gives the application the right to do). When hackers pull the data from the tables, they could use xp_sendmail or sp_send_dbmail to send the email to themselves. If you've disabled those procedures, a hacker could simply enable them or add in his or her own procedure using the sp_OA procedures.

How to secure SQL Server databases from SQL injection

There are a few ways to protect your database against these kinds of attacks. First we need to lock down the database security using database security best practices. This involves setting up the database security with the lowest set of permissions possible. It also

Tips to secure SQL Server and avoid SQL injection: Lock down SQL Server using Surface Area Configuration Discover and lock down vulnerable SQL Server services SQL injection tools for automated testing

 includes not using any table-level access to the tables. All access to the tables should be done through stored procedures, and those stored procedures should not include any dynamic SQL.

By removing access to the table objects you greatly reduce the surface that can be attacked. However, this is not the only thing that must be done. The stored procedures still present an attack vector that can be exploited. While this attack vector takes more time to exploit, it is possible to exploit the database using your stored procedures -- they're designed to insert, update and delete data from your database. A clever hacker can use your own stored procedures against you.

This is where your application developers need to work with you to ensure the code being executed against the database is secure. Without securing the application layer against SQL injection attacks, all bets are off. The data, as it comes into the database, is basically impossible to validate within the database. It needs to be validated at the application layer.

The easiest way to have an application work with the database is by generating the SQL command dynamically -- within the application. .NET code goes here to populate the v_Input variable from your front-end application:

…
Dim v_Conn As New SqlConnection(p_Connectionstring)
v_Conn.Open()
Dim v_cmd As New SqlCommand
v_cmd.Connection = v_Conn
v_cmd.CommandType = CommandType.Text
v_cmd.CommandText = "exec sel_CustomerData @CustomerName='" & v_Input & "'"
Dim v_DR As SqlDataReader
v_DR = v_cmd.ExecuteReader
v_DR.Close()
v_DR = Nothing
v_cmd.Dispose()
v_cmd = Nothing
v_Conn.Close()
v_Conn = Nothing
v_DR.Close()

If you don't validate the data within the v_Input variable, then you leave yourself open to SQL injection attacks. If you don't validate the input, it allows the attacker to pass in a single quote, and a semicolon, which tells the SQL Server to end the value and the statement moving on to the next statement in the batch. An example value would be "Smith '; truncate table Customer; declare @myV = '". The resulting SQL statement executed against the SQL Server would look like this:

exec sel_CustomerData @CustomerName='Smith'; truncate table Customer; declare @myV = ''

When the calling application runs the code, the procedure is run and the table is then truncated. You should do some basic validation and replace any single quotes within our variable with two single quotes. This will stop SQL Server from processing the truncated statement as it will now be part of the value. By making this simple change, our database call now looks like this:

exec sel_CustomerData @CustomerName='Smith''; truncate table Customer; declare @myV = '''

A better and more secure solution is to paramaterize the stored procedure code. This lets .NET handle the data scrubbing of the variable and makes it so any injection code is not executed.

…

.NET code goes here to populate the v_Input variable from your front end application.

…
Dim v_Conn As New SqlConnection(p_Connectionstring)
v_Conn.Open()
Dim v_cmd As New SqlCommand
Dim v_Parm As New SqlParameter
v_cmd.Connection = v_Conn
v_cmd.CommandType = CommandType.StoredProcedure
v_cmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar, 255)
v_cmd.Parameters.Item("@CustomerName").Direction = ParameterDirection.Input
v_cmd.Parameters.Item("@CustomerName").Value = v_Input
v_cmd.CommandText = "sel_CustomerData"
Dim v_DR As SqlDataReader
v_DR = v_cmd.ExecuteReader
v_DR.Close()
v_DR = Nothing
v_cmd.Dispose()
v_cmd = Nothing
v_Conn.Close()
v_Conn = Nothing
v_DR.Close()

Without properly securing your website's front-end application and back-end database

News: Avoiding SQL injection Microsoft identifies tools to address SQL injection attacks Secure software development a must to combat SQL injection possibility

 fully, you leave your system and data open to SQL injection attacks. These attacks can be as unintrusive as seeing if it's possible and as intrusive as sending all your customer data to the attacker. Destruction could reach levels of all data being deleted or your site and application being used to distribute a virus to unsuspecting customers. In the short term, this would infect your customers' computer; in the long term, your company could be added to an unsafe browsing list.

Note: The .NET code in this tip should be used as a guide. It is not tested or guaranteed to work. I'm a DBA not a .NET developer so use this code to show basic concepts. It is not shown for production use.

ABOUT THE AUTHOR
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.

 Check out his blog: SQL Server with Mr. Denny.