SQL injection attacks are probably the most common way for hackers to strike Internet-facing SQL Server databases. No matter how secure your network is or how many firewalls you have in place, any application that uses dynamic SQL and allows for unchecked user input to be passed to the database is at risk for a SQL injection assault. Recent reports on Web hack attacks show SQL injection attacks are on the rise and lead not only to data theft and data loss, but in the most recent string of automated injection attacks, databases were compromised to serve malicious Java script code to customers. The infiltration causes Web servers to infect the client computer with another virus. Reports 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:
- 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.
- When taking in values from the client application, the values are not validated -- for syntax or for escape characters.
The way ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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 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 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:
[IMAGE]Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's more than 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 uses these skills in his role as a senior database administrator and architect at Awareness Technologies. Denny is a longtime member of PASS and Quest Software's Association of SQL Server Experts and has written numerous technical articles on SQL Server management.
Check out his blog:
SQL Server with Mr. Denny
