Robert Robbins

I've been following the rise of SQL Injection attacks because I'm busy protecting a vulnerable web application. I'm very critical of the lack of attention this is getting in the developer community. In my opinion, if web developers were really communicating and collaborating with each other there would be a lot of chatter about this problem. I'm sure there are many programmers being kept busy dealing with the aftermath of these attacks but they are probably all implementing their own quirky solutions in isolation without the benefit of community feedback. And it is the lack of community which created the problem. There is no peer pressure to think about security or exchange of ideas on how best to protect a site against SQL Injection attacks (a topic that can generate a lot of debate). And without any discussion about the SQL Injection attack many developers are going to remain ignorant about it. There is however some ridicule about a lame attempt to protect against SQL Injection to be found on The Daily WTF. It is worthwhile to read through the comments.

I've heard that the Redmond Magazine web site got hit by a SQL Injection attack.  Although the press enjoys the irony in that, I only bring it up because they identified the source of the attack and mention copycat attacks "...continue to remain vulnerable to copycats introducing new malicious domains within the vulnerable sites."

I received an issue of eWeek today, May 19, 2008 Vol. 25 No. 16 with an article entitled "Botnet installs SQL injection tool". I can't find the article online but here is a link to their security section: http://www.eweek.com/c/s/Security/ The article describes how a botnet is distributing a SQL injection attack tool that searches Google for .asp pages that contain certain terms. You should be very worried if you have a classic ASP web application that is indexed by Google. I remember that the old version of Storefront, Storefront 5.0, a classic ASP e-commerce shopping cart, only protects against SQL Injection attacks by scrubbing input without using parameterised stored procedures. I'm not sure if it would be safe from this.

Yesterday I tried to check out a Microsoft web site with some information on how to protect ASP.NET web sites from SQL Injection attacks but I did not get very far. The HelloSecureWorld web site is designed like a cheesy computer game. To use the Virtual Laboratory you need to install an ActiveX control which managed to kill my browser. I disapprove of the way Microsoft handled this. They played the blame game and treated it like a public relations issue. The only resources they provided are some vague white papers on "best practices" which don't contain any concrete implementation details. This is not how I envision a true developer community would handle this situation. There should be plenty of concerned articles, blog posts, and helpful tools developed to elevate the general level of security on the Internet.

It certainly isn't fun to revisit a crusty old ASP web application to protect it against SQL Injection attacks. I have to write a lot of stored procedures and then test them. Then I have to write the VBScript code to send the parameters to the stored procedures. This is great way to introduce errors into a working web site! I do have one technical tip to share. If you want to return the ID number of the identity field for a recently inserted record, then place the SET @ID = SCOPE_IDENTITY() outside of the BEGIN...END block that contains the INSERT statement. That tripped me up. In a classic ASP web application, you will frequently find that another SELECT query will be made to get the ID number for a recently inserted record. You can eliminate this query by making the INSERT statement return its generated ID number as an output parameter.

I've been very busy so instead of blogging about one specific thing I have a lot of minor stuff to report.

I've now read several books on ASP.NET 2.0 so I've abandoned ASP.NET 1.1 except for Storefront 6.0 customizations. I converted one large ASP.NET 1.1 project to ASP.NET 2.0 and I'm converting a classic ASP site to ASP.NET 2.0 (it is going very slow). This will help to keep me focused on ASP.NET 2.0. I can't get into ASP.NET 3.5 because we have not moved to Visual Studio 2008 at work although I do have Visual Web Developer 2008 Express. I'm also securing a classic ASP site by converting its data access to use stored procedures (also going very slow).

A number of small side projects would probably be of more interest. I noticed that none of my WordPress blog posts were being picked up by Google Blog Search but they have a Google Blog Search Pinging Service API so I decided to write a REST client for that in ASP.NET 2.0. This turned out very well and I managed to get my blog posts listed in that search engine. This should have been unnecessary because I added the Google Blog Search to my WordPress Update Services to send the ping but apparently that isn't effective. Maybe my hosting company blocks outgoing PHP web requests???  I cannot get blog trackbacks to work either.

I'm getting started on using the Seesmic API. Seesmic is an interesting web site for conducting conversations via video responses although it is now also being used to leave video comments on text blogs. Today I added a page to their Google Group on ASP.NET 2.0 - Seesmic API Authentication. This took me several days to write because you need to create a MD5 hash and there was a JSON response to deal with. I experimented with the totally undocumented JavaScriptSerializer.DeserializeObject method. According to MSDN, this converts the specified JSON string to an object graph. However, there is no sample code and I have no clue as to how to use it. Based on my experimentation it seems to create nested dictionary objects and KeyValuePair objects.

I read about SubSonic in the book The ASP.NET 2.0 Anthology. I like how it creates the CRUD part of a site for you because that has always been a tedious chore. Database table management is usually a back end feature relegated to the admin directory so it doesn't have to be anything fancy. However SubSonic scaffolding was giving me some strange captions based on my table names. SubSonic is open source software so I was able to add a property to allow me to set the Scaffold Caption. While poking around the source code I found a treasure trove of useful functions in the Sugar directory. You should definitely get yourself some sugar.

Another major side project of mine was YouComment, an experiment in improving the experience of reading comments left on YouTube videos. This continues my focus on the social networking aspects of the site. There were two major technical challenges in this project. First I had to reorganize the XML because the nodes where not nested in such a way as to map comment replies to nested unordered lists. I also needed a hash table to reduce the number of web requests made to retrieve user account information. Unfortunately, this page cannot handle videos with hundreds of comments. But then I have a lot of problems viewing comments on YouTube because it kills my browser to load hundreds of comments. I think I need to create a new version that just displays your comment and its replies. The YouTube community has been complaining for a long time that it would be easier if the comment email notification actually included the comment so you don't need to visit the site to read it but YouTube has refused to make that improvement.

I'm sorry to say that AIR applications appear to be getting some real traction now. I'm already using three AIR applications that are very useful; Twirl, AlertThingy, and Klok. Klok is a personal time tracking application. It is great for timing your activity on a project. I think Twirl was bought by Seesmic and bloggers are crazy about AlertThingy. I think I should be learning how to create AIR applications.

I've found a serious shortcoming in one of the security methods I've been using. I've inherited two projects in which social security numbers were stored in a database in an unencrypted format. For the web application I don't think the SQL Server 2005 built-in encryption methods are an option because the web hosting company is still using SQL Server 2000. Instead, I used the .NET Framework's built-in cryptography classes found in the System.Security.Cryptography namespace. I used the Rijndael (aka Advanced Encryption Standard (AES)) cipher in a custom assembly which I uploaded to the web server's bin directory without the source code. This encryption method relies on a 128 bit key and an initialization vector (IV) which are basically just byte arrays of 16 numbers.

The shortcoming with this security solution is that the 128 key and initialization vector (IV) can be obtained from the DLL by using Lutz Roeder's .NET Reflector. I was able to reverse engineer my DLL and easily found the keys you would need to decrypt the social security numbers. I then tried using Dotfuscator Community Edition 3.0 to see if it could obscure the keys but I was still able to find them although the variables were renamed a.a and a.b. I was still able to determine that RijndaelManaged was the encryption method and the byte arrays make it obvious that these are the keys (which are identical to the non-obfuscated version). I doubt that any obfuscator tool is going to touch a byte array so you really cannot have your keys in a DLL that can be reversed engineered.

The MSDN article on the RijndaelManaged.CreateEncryptor Method suggests that when you create a new instance of the RijndaelManaged class, it generates a new key and initialization vector (IV). However, this is not very clear and the sample code I've seen used byte arrays to specify the key and initialization vector.

Of course, if someone has a copy of your database and your application files then you are probably screwed anyway.

Today I discovered that you cannot use the LoginAdapter from the CSS Friendly Adapters with the Sample Access Providers. I am using an Access database for my Membership Provider because I only needed a login form for my administration directory and did not want to go to the trouble of adding the Security, Membership, and Role Management schema to my SQL Server database.

I am also using a fancy theme that styles tables but I did not want this style applied to the login control which renders a table. So I tried to use the LoginAdapter from the CSS Friendly Adapters. This broke my login form and I wasted considerable time trying to figure out what was going wrong.

Eventually I found this forum thread which provided a clue: http://forums.asp.net/t/1043974.aspx. Apparently using a custom MembershipProvider will break some of the CSS Friendly Adapters and the Sample Access Provider qualifies as a custom MembershipProvider. Removing the LoginAdapter fixed my login problem so I created a new theme without the table style sheet and applied this theme to the login page.