Hit By SQL Injection Attack

I hate to admit it, but we were hit by this SQL Injection Attack last week. The gist of a SQL Injection Attack is that the attacker hijacks your database query and inserts his own query that displays your data on the screen, wipes out your database, or other action. This particular attack wipes out every row and column in EVERY table in your database and replaces its content with a URL and script tags in the hope that your site will then be filled with links to the rogue web site. The best defense against this type of attack is to use parameterized queries/stored procedures rather than a “dynamic” SQL statement. This keeps the “bad” query from actually executing. Instead, the bad info is passed in as a parameter and the procedure then just fails or returns no data.

If I know how to prevent an attack, how did this attack work on our site? The short answer is that I was dumb. The longer answer is that most explanations of this type of attack demonstrate with a username and password-type page. You enter your username and password on a page. The insecure way to code this is to dynamically have code like this:

Dim sqlStatement As String = String.Format("Select * From Users Where username = '{0}' AND password = '{1}'", usernameField.Text, passwordField.Text)

The attacker then puts in a big set of SQL code in the username field that comments out your own statement and does what he wants with the statement. I went through every page and application on the site that got hit and couldn’t find anywhere where we were building dynamic SQL like this. But then a great support member from our hosting company, DiscountASP.NET, looked through the logs and found the problem. It was with a particular design that we originally used with our VBTrain.NET controls. It passed the productId in as part of the query string. For example:  http://www.vbtrain.net/productDisplay.aspx?id=9. There are no active links to this page, but they were still on the site and working. The attack came when the rogue site sent the bogus SQL Statement (encoded) in place of the 9 above. So the URL looked like this:

productDisplay.aspx id=6%29+declare+%40s+varchar%284000%29+set+%40s%3Dcast%280x73657420616e73695f7761726e696e6773206f6666204445434c41524… rest removed for security reasons.

If I had used stored procedures for the query to get the data, we still would not have been vulnerable. Unfortunately, when I first set these sites up, I was reading data from various tables and decided to have a common method where I passed in the table, column, default value, etc. Here was the original method:

Protected Function RetrieveDataReader(ByVal tableName As String, ByVal columnName As String, ByVal columnValue As String, Optional ByVal sortColumn As String = "notAColumn") As SqlDataReader
Dim conId As SqlConnection = Me.VBTrainConnectionId
Dim dbString As String = String.Format("Select * from {0} where ({1} = {2})", tableName, columnName, columnValue)
If sortColumn <> "notAColumn" Then
dbString &= String.Concat(" order by ", sortColumn)
End If
Dim dbCommand As New SqlCommand(dbString, conId)
Dim dbReader As SqlDataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return dbReader
End Function

We needed to use dynamic SQL in this case since it is hard if not impossible to write a stored procedure where the name of the table and the columns vary. We then called the function like this:

Dim dbReader As SqlDataReader
dbReader = Me.RetrieveDataReader("productInfo", "productID", idVal)

idVal was what we grabbed off the query string (everything after the ?). Those of you who have made it this far can probably see how this was a huge security hole. The other pages using this function had hard-coded values, which were safe. But passing in idVal straight from the URL allowed the hacker to blow away our database. So what to do?

  1. The best thing would be to change the design so that it calls a stored procedure instead. We will do this on the next redesign.
  2. For now, we can eliminate the vulnerability by checking the parameter before passing to the RetrieveDataReader function. We now make sure the parameter is a number AND that it is a very short length before allowing it through. Otherwise, we use a known value such as the 9 above.

Hopefully someone will read this and use it to protect your sites better than we did. I created the pages in question back in 2002. So another lesson is to go back and look at your old sites and applications and check them for security vulnerabilities.

On the positive side, we have the master copies of the databases in question saved locally and were able to recover quickly by using SQL Data Compare from Red Gate Software to update the database that had gotten hacked.

About Jeff Rhodes
Jeff Rhodes is the Branch Chief, Program Office Support at the U.S. Office of Personnel Management (OPM). Prior to that, he was the Academic Chief Technical Officer at the United States Air Force Academy and previously a Senior IT Specialist in charge of SharePoint and other key systems at the Academy. Jeff was the founder and Chief Technical Officer of Platte Canyon Multimedia Software Corporation, a leader in developing commercial e-learning software. He graduated at the top of his class at the Air Force Academy, where he earned a Bachelor of Science in Electrical Engineering. Jeff received a Master’s degree in Economics from the London School of Economics, which he attended under a British Marshall Scholarship. He is the author of Creating Business Applications with Office 365: Techniques in SharePoint, PowerApps, Power BI, and More, Programming for e-Learning Developers: ToolBook, Flash, JavaScript, and Silverlight, VBTrain.Net: Creating Computer and Web Based Training with Visual Basic .NET and The ToolBook Companion. He lives in Colorado Springs with his wife Sue and is the proud father of his sons Derek and Michael.

One Response to Hit By SQL Injection Attack

  1. Peter Hoyt says:

    Wow, thanks for sharing that Jeff… I’m betting I’ve got a database or two just as vulnerable to an injection attack!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: