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.

Advertisement

Good Articles About Pricing Software

I was having lunch recently with a fellow owner of a small software business. We were discussing pricing strategy and I followed up with the list of my favorite articles related to pricing software. I have been interested in pricing theory ever since my graduate studies at the London School of Economics and even taught Microeconomics for a time the Colorado Springs campus of Regis University. When it comes to pricing your own software, it can literally be a make or break decision for your company.

The first three links are from Eric Sink. I first read his “Closing the Gap” articles a number of years ago in The Best Software Writing 1 edited by Joel Spolsky. It very closely mirrored by own thoughts on how to run a business and how to sell software without the hype and the pressure that is so often used. Eric articulates his concepts and vision brilliantly. I bought the book mentioned above after reading Joel on Software for years before that. Joel has many great opinions on running a software company and other topics. His Camels and Rubber Duckies is a great article. I frequently tell our propective customers that we proudly post our prices on our site and never use the “How Much Money Have You Got” pricing that Joel picks apart in his article.

Product Pricing Primer: http://www.ericsink.com/bos/Product_Pricing.html

Closing the Gap, Part 1:  http://www.ericsink.com/bos/Closing_the_Gap_Part_1.html

Closing the Gap, Part 2: http://www.ericsink.com/bos/Closing_the_Gap_Part_2.html

Camels and Rubber Duckies: http://www.joelonsoftware.com/articles/CamelsandRubberDuckies.html

Joining the 21st Century

Although I have written three books, presented at numerous conferences, and publish the quarterly EnterPage newsletter, this is my first blog post. My focus will be primarily technical, with (hopefully) interesting insights into .NET, ToolBook, HTML, JavaScript, and Flex/Flash. I will also add some content on topics related to running your own small software company as well as whatever random subjects come up. I hope you join me for the ride.