I was reading this article recently on InfoWorld, and even with all of the best practices and options available to avoid it, SQL injection attacks still make up 20% of the world's hacks and are the single largest attack vector in use.  I thought of all the times I was mocking up a project and passed values straight through, only to have to go back later and cleanse the inputs.  How many people mean well, but forget to go back and address their //TODOs?  There are a few ways to mitigate this:

• Only allow database access through stored procedures
• Only allow database access through a DAL that strips out injection attacks
• Cleanse all text inputs of common injection code before passing them through

I have a client whose site is was filled with myriad opportunities for an attack of this nature to proceed.  The original site architect never designed a DAL, had business logic mixed throughout the code-behind pages, and constructed most SQL commands as:

"SELECT * FROM customers WHERE customers_username = '" + txtUsername.Text + "' AND customers_password = '" + txtPassword.Text + "'";

Since rewriting the entire site using stored procedures was outside of the scope of the project, I created the following function to strip out possible attacks.

public class Common {
 public static void CleanSQLInputs(ref string sToClean) {
  string[] blackList = {"/*","*/","--",";-",";","@@","cursor ","declare ","delete ","drop ","execute ","insert ","select ","sysobjects","syscolumns","xp_"};
  for (int i = 0; i < blackList.Length; i++) {
   sToClean = sToClean.Replace(blackList[i], "");
  }
  sToClean = sToClean.Replace("'", "''");
  sToClean = sToClean.Replace("\"", "''");
 }
}

Now, anywhere I need to clean an input, I just call

Common.CleanSQLInputs(sUsername);

before passing sUsername into the SQL command.  I've seen other solutions that implement this as a function returning a string, but I prefer to do it this way at the beginning of each method for any strings that are being passed in from the UI to the database so I have to keep track of what's been cleansed.

Luckily, the site in question had never been exploited.  But past precedent is no substitute for real security, and the site owner was relieved to hear that this was in place.

Have you ever been hit with a SQL injection attack?  What steps have you taken to shore up your code from attacks?

I found a very useful guide for installing .Net 3.5 into a Vituozzo container.  I am still not impressed by Virtuozzo, but for the purposes of the site being hosted on it, it works.  Luckily, .Net frameworks can be installed on a per-container basis, so moving to 3.5 (LINQ!) was pretty easy.  There was 3 minutes of downtime scattered throughout the installation-not too shabby, although for those 3 minutes that the web site wasn't serving up pages, a fair number of fingernails were bitten.

As .Net has matured, did Microsoft ever tell us what's been up with the numbering?  We went from 1.0 to 1.1 to 2.0.50727 to 3.0 to 3.5 to 4.0.  Why was version 2.0's revision code included everywhere it's used?  Were they planning on releasing a 2.0.50728 at some point?  I like the cleanliness that 4.0 will bring back to web.config.  Visual Studio 2010β2 crashed today while saving web.config to a website, which knocked it down for a few minutes…  It seems to have escaped notice, but that's what happens when you get lazy and decide that not every site needs a staging server.

While working on a client's site, I used an http handler to call a very well thought-out image resizing class I found via CodeProject.  I removed the ability to use named keys (lines 42, 51-62, & 276-336), opting instead for controlling width and/or height explicitly.  Since it's a .NET 2.0 app, the Linq code in GetImageDimensions would have had to go anyway.  I didn't realize how much I've come to love LINQ until I went back to write some new SQL code for this project…  Also, I removed the dimension check that was preventing the image from being upscaled-I needed it to work both ways (lines 239-246).

With the code in place, the image resizing worked great.  The images resized properly, but after attempting to use a management app located in a subdirectory, I realized that the custom http handlers were being inherited by all of the children, causing all sorts of trouble.  Since one of the apps is a virtual directory controlled by the webhost, editing the individual web.config files wasn't possible.  I guess the ASP.NET team had run across this before, because they created the location tag and its inheritInChildApplications attribute.

My first attempt to wrap only the httphandlers section didn't work, but wrapping the entire system.web section did the trick (turns out, this is a bug in .Net 2.0 which Microsoft has said will not be fixed).  Now, all of the sub apps are working well where they're at, and the images are resizing perfectly.