Introduction

For many years now, SQL Injection attacks on large corporate websites have been highly

publicized. Several articles around the Web have described what an injection attack is,

how it works, and the basics of how to defend against it. A couple of very good articles

here at SQL Server Central also delve into this topic

(SQL Injection

by Christoffer Hedgate and SQL Injection - Part 1

by Randy Dyess).

So why did I feel the need to write another article on SQL Injection? For three reasons:

1. The good work by Mr. Dyess and Mr. Hedgate offer code samples and examples for

   ASP. I felt that a sample pertaining to ASP.NET, for those without the ASP background, was in order.

2. These two authors focus on using parameterized queries; and in the case of Mr.

   Hedgate, validating user input. Excellent advice all around, but I feel there are other lines of

   defense which should be addressed as well.

3. Finally, no matter how many SQL Injection articles are posted around the Web, DBA's and

   developers continue to post highly exploitable code samples to newsgroups and discussion boards.

In this article, I hope to build upon the good work of Mr. Hedgate and Mr. Dyess, and provide

updated samples as well as a more complete defensive strategy for dealing with SQL Injection.

What Is SQL Injection

SQL Injection is a method of exploiting databases via the user interface. The method takes advantage

of the fact that SQL allows multiple commands on a single line, separated by semicolons. SQL Injection

also takes advantage of the way SQL handles single quotes.

We'll create a sample using a fictitious Database Admin. We'll call him Ima D. Ba. Ima has to create a login

table for Northwind that contains the usernames and passwords of all employees. He creates the following table, called

Test_Logins, in the Northwind Database:

Ima then made a SQL Login for the local ASPNET user. He made ASPNET the database owner (dbo) for the Northwind Database and also made ASPNET a SQL system administrator.

One of the company developers then wrote a simple ASP.NET login webform that asks for username and password, which it then validates against Ima's Test_Logins table.

Private Sub LoginButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoginButton.Click
        Dim success As Boolean = False
        Dim sqlcon As New System.Data.SqlClient.SqlConnection("SERVER=(local);" & _
      "INITIAL CATALOG=Northwind;" & _
      "INTEGRATED SECURITY=SSPI;")
        sqlcon.Open()
        Dim sqlstr As String = "SELECT * " & _
                                "FROM Test_Logins " & _
                                "WHERE UserName = '" & Username.Text & "' " & _
                                "AND Password = '" & Password.Text & "'"
        Dim sqlcmd As New System.Data.SqlClient.SqlCommand(sqlstr, sqlcon)
        Dim sqldr As System.Data.SqlClient.SqlDataReader = sqlcmd.ExecuteReader()
        If sqldr.HasRows Then
            success = True
        End If
        sqldr.Close()
        sqlcmd.Dispose()
        sqlcon.Dispose()
        If success Then
            Server.Transfer("Success.aspx")
        End If
    End Sub

The login code basically performs five steps:

1. User enters username and password
2. Web form opens a connection to the Northwind Database
3. Web form dynamically builds a SQL query: SELECT * FROM Test_Logins WHERE UserName = 'username' AND Password = 'password'
4. Web form performs the SQL query over the open connection
5. If there are any rows in our resultset, we know that the user has entered a good username and password.

Enter The Hacker

Our hacker Yuri picks up one of Andrew Fuller's business cards at a tech convention. The

business card has Andrew's title ("Vice President of Sales") in big 12 point type. So Yuri decides that Andrew probably has access to a lot of confidential company information. Andrew is now targeted.

This is a very simplified example, but it demonstrates several avenues of attack. The first is to trick the server into believing we are actually logging in as someone else. We can enter the following as a username:

The resulting SQL query is:

SELECT * FROM Test_Logins WHERE UserName = 'AndrewF'; -- AND Password = ''

Everything after the -- is a comment, so the password check is completely bypassed, allowing us to successfully log

in as AndrewF without a password!

Alternatively Yuri could append DROP statements, DELETE statements or any other valid T-SQL commands to our username to produce intrusive or destructive results, as in these examples. We could even potentially access stored procedures and extended stored procedures (like xp_cmdshell) to wreak havoc all over the corporate network!

How To Avoid SQL Injection

So now that we know what SQL Injection is, how can we avoid it? Let's look at the mistakes Ima and his developer made:

The Developer Did Not Validate User Input

Your first line of defense against SQL Injection is always input validation. Any time an application accepts input from a user and uses it in a query against a database, it absolutely has to be validated.

This validation can be done server-side, client-side, or even both. In our example, we know that certain special

characters in input strings can open us up to SQL injection: double-dash (--), single-quote(') and semi-colon (;) are the primary trouble-makers. We can check for these and other special characters on the client or server side and eliminate

them before we dynamically build queries based on user input.

A good rule of thumb is if you don't allow certain characters

in the input string, eliminate them completely (or throw an exception) during the validation phase. For instance, all our

usernames consist of only alphabetic characters, so we could limit input in that form field to letters 'A' - 'Z' and 'a' -

'z'. Anything the user inputs, including drop-down box form inputs, should be validated before you try to query with it.

The Developer Built A Query Dynamically

Our next line of defense is to avoid building queries dynamically when possible. Always look to parameterized queries (sp_executesql in T-SQL) first.

But if you do need to build a query dynamically, validate your input well and be

certain to replace single quotes (') with escaped single quotes ('') in string constants. This can be done with the T-SQL Replace() function, or the .NET String.Replace() method can be used for this purpose.

For this particular application, however, it makes much more sense for Ima and the Developer to use a Parameterized Query to validate our user. Parameterized queries pass the input values to the server separately from the actual SQL Query. The server then provides a level of safety by using sp_executesql with parameters instead of dynamically building the query string.

Build queries dynamically only when you have to. And when you do have to, be sure to validate all input to the generated query string before running it against the server. Also when building dynamic SQL Queries, you generally should not need to dynamically insert table names, column names, etc. (unless you're building some sort of SQL DDL/Admin tools). If you find that you are generating a lot of dynamic queries with dynamic table and column names, it might be a good idea to take a closer look at your database implementation.

Ideally, Ima will build a stored procedure that accepts the username and password as parameters. This procedure will validate the user and return a success or failure code and any other pertinent information. The stored procedure model will add a layer of abstraction that will keep the hacker a little further away from our base tables.

The DBA Gave Away The Farm

You might notice in the example attacks that the hacker was able to run such commands as DELETE FROM Test_Logins and DROP TABLE Test_Logins. This is because Ima made the ASPNET user the Northwind dbo. Also, Ima gave the ASPNET user SysAdmin rights. Our DBA needs to scale back the rights for the ASPNET user to something more reasonable. Most likely this user will have read-only rights to most information; and Ima's ASPNET user should never, ever have rights to run DDL commands such as CREATE TABLE, DROP TABLE, etc. Ima also needs to lock down the master database to ensure the ASPNET user doesn't grab all available information about the server configuration.

The Developer Is Sending Too Much Information Across The Wire

In our sample, our Developer is sending much more information across the wire (between the IIS and SQL Server) than is necessary. All we need to know is if our username and password matched. So all we really need to return from SQL Server is a single number, not a complete table row! We could change the SELECT statement to return the EmployeeID of the logged in user with SELECT EmployeeID FROM .... The less information we have to pass along, the better our security.

The DBA Is Storing Plain Text In Critical Columns

Closely related to the previous problem, Ima is storing passwords in plain text format in the Test_Logins table. This is a big no-no. Sensitive information such as passwords should be stored in encrypted or hashed format. That way, even if our hacker happens to get past all the other lines of defense and is able to view the data in our tables, it will be unusable to him. One method of protecting passwords is to hash the passwords using a hash algorithm (like MD5) before storing them in your table. Then when you go to compare passwords at login time, hash the password in your ASP.NET code or your Windows Forms code before sending it to the SQL Server. Any time we can prevent sending sensitive information across the wire in plain text format, we should take advantage of it.

Conclusions

SQL Injection is a tactic used by hackers to gain unauthorized access to confidential data stored in SQL Server. Proper planning and development practices can help secure your system against SQL Injection and other types of attacks.

Resources

• MSDN Entry: sp_executesql Microsoft
• MSDN Entry: EXECUTE
• SQL Injection by Christoffer Hedgate
• SQL Injection Part 1 by Randy Dyess