Robert Robbins

I've seen very little blogger chatter about the massive SQL injection attack that is making the news even though it is us web developers who are being blamed for it. Even Jeff Atwood has neglected to blog about it and he loves to rant about shoddy coding practices in the industry.

I suspect the silence is due to the very technical nature of SQL injection attack prevention. I've done some research on this topic and it is not as easy to fix as you might think. The best resource I've seen can be found at http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/ That web page of attack vectors is huge and makes it quite clear that you cannot prevent an exploit just by parsing out a few SQL keywords.

I have inherited a classic ASP web application that is probably quite vulnerable. The original developer did not use any stored procedures and there is nothing in there to sanitize the user input. Converting all of its dynamic SQL statements into stored procedures is going to be a huge chore. First I will need to locate all the SQL statements in hundreds of ASP web pages. I do have some scripts to recursively search a directory and read target files to create such a report with line numbers. I may be able to load the web site in Visual Studio and use its search features. Then I need to create stored procedures for every SQL statement. That will probably be a few hundred. After that I'll have to write a lot of verbose VBScript code to deal with every input parameter. Here is some old sample code I have showing how much code is required for a very simple query.  

<%
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_my_query"
cmd.Parameters.Append cmd.CreateParameter("Title", adVarChar, adParamInput, 255, "Slaughterhouse Five")
cmd.Parameters.Append cmd.CreateParameter("Author", adVarChar, adParamInput, 255, "Kurt Vonnegut")
cmd.Parameters.Append cmd.CreateParameter("ID", adInteger, adParamReturnValue)
cmd.Execute
Response.Write "ID number of record inserted is: " & CStr(cmd.Parameters("ID").Value)
%>

Of course, there are going to be many bugs introduced by these changes and I'll probably have to struggle with some data types (i.e. figure out which DataTypeEnum constant to use). Last night I backed up the database for this classic ASP web application and I did not find any script blocks in its text fields but other hackers will begin to customize this exploit (which uses table cursors and system objects to discover all the table names and columns).

I have another classic ASP site and an ASP.NET site that are probably vulnerable but these are mostly non-public administration sites. I can probably delay any problems by making sure Google does not index them.