Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Updated SQL Injection

By Michael Coles, (first published: 2005/09/21)

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

Total article views: 83753 | Views in the last 30 days: 3
 
Related Articles
ARTICLE

SQL Injection!

Do your developers really understand how to prevent injection attacks? Or scarier still, how many kn...

SCRIPT

Login Password Policy

This script lists all of the SQL logins with their password policy

FORUM

Reset Password for linked server login

Reset Password for linked server login

FORUM

how to overcome sql injection

avoid sql injection in sql server2005

ARTICLE

SQL Injection Everywhere

Steve Jones talks about the possibility of SQL Injection, or other security issues from malformed in...

Tags
security    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones