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?
- 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.
- 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.
