SQL Injection Attacks

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


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?


Consuming and Parsing Yelp API Requests Using jQuery

I've been hard at work designing a business directory for our neighborhood association, Newbold Neighbors, and I was enamored with the idea of not having to reinvent the wheel in terms of creating a review system.  Why do the hard work when Yelp has already done it?

I just bought another hosting plan with PowerDNN so I can begin my (slow) migration over to DotNetNuke 5.  I haven't even gotten as far as moving my own site over, but I knew that I wanted to use some of the new features for the neighborhood site (or at least I wanted new people using it to gain the advantage of never knowing some of the hang-ups/eccentricities of DNN4…)  Moving the skins and containers over was a cinch-just update the file references in the headers.  So far, so good.

I had purchased a copy of DNNDev's XMod a while back, and never really found a use for it.  I checked around SnowCovered and saw that for previous owners, the upgrade to XMod 5.5 was just $20!  What a deal-especially since it's the first version to support DNN5.  After reviewing XMod's functionality, I decided that it was definitely the right tool for the job.  Since I heart clean urls, I also went with the XMod Details View (free) which lets me show a detail view using the record id.  The only downside (actually, my biggest complaint with DNN) is the lack of slugability (I can't do /business/details/marinos-auto-repair, but I can do /business/details/id/5).

I used their tools to create a simple database of company names, phone numbers, addresses, photos, etc, and then got the layout working.

So, here's where the magic happens.  I use jQuery to get the phone number from the .phone in each .business element and pass it off to the Yelp query.  Using the callback=? is required for jQuery to perform a cross-domain JSON query.  jQuery replaces the ? with a custom identifier transparently.

I have three different views - small, regular, and details.  I want basic data displayed on the small, a bit more for regular, and some custom stuff for the details (adding in trends, individual reviews, etc).

I am pretty pleased with how it came out.  If you want to take a look, it can be found over at www.NewboldNeighbors.org/Businesses.  As someone who fancies himself a .Net programmer, jQuery has made the transition back to client-side processing fun again.  Hope this saves someone a few hours of coding.  Enjoy!


.Net 3.5 in a Virtuozzo Windows 2003 container

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.


Logging In After Password Change Breaks DotNetNuke Active Directory Authentication

I've developed a few DotNetNuke 4.9.x sites that use the Active Directory authentication provider, and on the whole, it works quite well.  DotNetNuke caches the given username and password, using that to query the domain when a user attempts to log in.  On the sites in question, having both the standard DNN and Windows login was confusing to the users, so I disabled the DNN login by going to Admin -> Authentication and unchecking Enabled? under DNN Auth Settings.  This cuts out the ability to log in as host, so every time host functionality is needed, the site must be logged into using an AD user with admin privileges, DNN login must be re-enabled, and then you can log back in as host.

However, upon updating my domain password, I noticed that all of the AD-enabled sites broke.  Without being able to log in using the DNN login, and with no AD logins working, what was I to do?

Hidden In The Database

The answers are not clear from any of the DNN forums, but it turns out when you enable Active Directory integration for a portal, a module is created to store the configuration.  The DNN standard login has no settings of its own.  To re-enable DNN login from the database, look through the ModuleSettings table for a ModuleID that has entries prefixed with AD_.  There are two other settings for the same module, prefixed with DNN_.  The setting we're looking for is DNN_Enabled, which, if you had DNN login disabled, should be false.  Simply changing that back to true should give you back your DNN login ability and get you back onto your site.

Avoiding This Issue In The Future

The best way to avoid this is to create an additional user in your AD with no rights-all it has to do is authenticate as a valid user.  I went with dotnetnuke-ad, but you could chose anything.  Give it a sufficiently difficult password, and set the password to never expire.  This way, you never have to worry about your password changes taking down your DNN site.


Blocking web.config Inheritance

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.


DotNetNuke is a designer's nightmare

I hate to say this about the framework I love, but it's true.  Anyone who's ever spent more than a few minutes developing skins or modules knows it, but so far it hasn't inspired anyone to step up to the plate and take on refactoring/refining the CSS into a more workable state.  Maybe it's not as big a deal as I think, but I'm tired of looking through the CSS that gets sent downstream and comparing it to other frameworks that do a tremendously better job of producing clean code.  I'm not a huge fan of reading other people's rants if they're not willing to offer suggestions, so here goes.  Please feel free to agree or disagree with any of these points- that's what comments are for.

Create a consistent CSS naming scheme

Enforce (or at least strongly recommend) that developers use a distinct namespace when creating the CSS for their modules.  Take, for instance, the DotNetNuke Blog module.  IMO, all of the CSS entries should be proceeded by a distinct selector (i.e. .dnnblog) that all of the blog sub-modules are wrapped in.  If I want every blog module on my site to have a certain background image, or use a certain typeface, I should be able to do it in a straight-forward manner.

Zero out default.css

And then add back in any of the available CSS reset files out there.  Yahoo's, Eric Meyers's, any of them will do.  After that, define basic styles: headers, paragraphs, etc., using the minimum amount of CSS.  Don't define font-family anywhere, especially not for every textual element possible.

If DNN wants the default skin to look polished and awesome, great!  Keep it all in the skin.css-that's what it's there for.  But don't make me hunt down and override items, especially when I'm banging my head against the wall because a div > div > ul > li is displaying in a different font than div > ul > li (remove all font-family definitions from default.css and the problem disappears).

Create an admin.css

All of the administration classes, both in the control panel up top and in the admin pages, should be rolled into an admin.css file that is only included if you're logged in as an admin.  There is no reason that CSS for the control panel is sent down with every page view.  Also, create a dnnadmin class that it's all wrapped in.

Get it done for DNN v6

The DotNetNuke skinning team lead, Timo Breumelhof, mentioned that this work was deliberately avoided so as not to introduce any breaking changes for existing installations.  Considering DNN5 broke existing skins and modules, wouldn't that have been the perfect time to also break the CSS?  I mean, if a developer already has to go back in and update class names, replace depreciated functions, etc., wouldn't that be the perfect time to do this?  No one ever said a cross-version upgrade would go smoothly (heck, even a lot of minor upgrades don't go smoothly…), but at some point, we've got to bite the bullet and move on.

Allow custom classes to be defined in the module settings

For instance, in a certain text module, let me go into the settings and assign a class to that module that would be injected into the container wrapper.  This is less important with the text module since I could just go in and wrap my text in a classed div, but say I want two surveys on the same page that look entirely different.  True, that can be done now using separate containers, but it seems like it would be a nice advanced option to have.

Update (3/14/2011)

While I haven't given up on DNN, the lack of designer friendliness has led me into the arms of Umbraco.  I'm writing this update as I'm migrating my old blog entries over, and I must say-I'm impressed with the Umbraco I see now versus what I tried out two years ago.