Technology Wizard

I am a Technology Wizard.

No, really, I mean it, a real life Wizard with special powers and everything.  Arthur C. Clarke said, "Any sufficiently advanced technology is indistinguishable from magic."  And he was right.  What I, and many other Technology Wizards, do on a day-to-day basis equates to magic in the eyes of the unenlightened.  Like other Wizards, I use a wand and utter special phrases to alter the universe to my will.  My wand is made of plastic and slides across my desk.  My special phrases are lines of code spoken to software, and if I don't get them just right everything will blow up in my face.

We are Wizards in the sense that we aspire to achieve more and be better than we ever thought possible.  We take risks.  We dare to dream big.  We look into the unknown and say, "That's where I want to go exploring."  Mere mortals see what we do and look upon our deeds with wonder and awe.  "How did they do that?" they will say.  Yes, there is a method to our ways.  Aspiring Wizards must learn their spells through training and experience.  Most of all though, they must have desire to be better than they currently are.  This applies to all kinds of Wizards, not just the Technology variety.

You see, there are Wizards all around us.  I look upon the Culinary Wizards with a sense of amazement and awe.  How is it they can take a pile of seemingly unrelated raw materials and, using their many specialized wands, form them into a delicious meal for the masses?  Some of their spells are so secret that they have to be moved from one location to another by armed guards!  And the Medical Wizards, there is nothing I can do but look on them with wonder as they use their magic for mend the broken bodies and minds of others.

Unfortunately, not everyone can claim the title of Wizard.  These people just don't have the ambition, the drive, or the motivation to do great things.  You can tell when you've met one of them.  Nothing is ever their fault.  There is always some obstacle that cannot be overcome.  There is always some excuse.  They spend their time and energy on petty things, leech off of others, and have no sense of responsibility to anyone but themselves.  They settle for "good enough" in everything that they do.  They have no sense of curiosity or adventure.

These are the things that distinguish the Wizards from everybody else.

Are you a Wizard?

1 comments - Posted by Justin Scott at 3:11 AM - Categories: Misc.

High Flying and Query Adjustments

The weather in Florida has been less than perfect for the last several weeks.  Between the clouds and rain and the astronomical expenses I incur while flying, I have not taken to the skies for a month.  Yesterday's weather was just perfect though, and I couldn't resist dusting off my gear for a quick flight.  I departed Sarasota just after 7:30pm and had a relatively uneventful hour at 3,500 feet before landing at Kissimmee Gateway in Orlando.  There were several other aircraft coming in and out of Kissimmee, but that's not ususual.  After landing I taxied back to the runway, took off, and came back to Sarasota.  Overall it was a textbook flight, frankly, and not much to write about.

The reason I bring this up, though, is that web development is much the same way.  We write applications, put them on the server, and most of the time everything just works.  Like flying, there are times when something isn't right and you have to call your special skills and training into play.  During flight training, the instructors make a big deal about the emergency procedures.  What do you do if the engine stops working?  What if a cloud layer forms up around you?  What if the engine catches on fire?  These are all things that do not happen often, and I may never experience, but we train for them anyway because the possibility is there.

Unfortunately, many web developers "train" as far as getting things working, but no further.  Essentially, they learn how to take off and land with their applications, but would be caught unaware if the engine failed.  There are many things that can go wrong with a web application as well, and developers should know what to do and how to handle them before the event occurs.  What if the web server disappears (could be hardware failure, network issue, whatever, but your server is just gone)?  What if the web site gets popular and you get tons of traffic all at once that your server can't handle?  What do you do if the site is hit with a successful SQL injection attack?  Oh, the possibilities!

One of the things I do is deploy an early-warning system.  Using FusionReactor, an application that gets under the skin of ColdFusion to peek inside, we get an e-mail alert for any long-running request, and can use it to monitor which pages need attention.  Lately one of the sites we manage has been getting slower as the traffic increases.  This site is very heavy on database access.  Each page has more than its share of queries, and some of them are very resource intensive.  The site is also very large, pushing hundreds of thousands of dynamic pages.

Last year we had the same problem, and we made one change that has held us over until now.  At that time, each page performed a radial zip code search to return other zip codes within a certain radius of a given zip code.  This is a fairly resource intensive process on the SQL Server, and the results hardly ever change.  My caching tooth was sore, and something had to be done.  To combat this, a table was created to store the results of the query.  It has two columns, one of the zip code you're checking, and another for the list of results.  Just before we run the radial search query, we check the new caching table to see if we have a list already.  If not, then we run the big query as usual, but then we take the results and stuff them into the caching query.  At first, this actually makes the pages slightly slower because of the extra insert.  However, over a few days all of the common zip codes had been requested and they were all cached.  A one-time hit for much faster processing later on.  This is similar to how ColdFusion compiles the CFM pages into bytecode the first time they are run.  A small performance hit on the first run for faster results going forward.

So it has been nice over the last year or so, but the performance issues are creeping back in as the site gets larger and brings in more traffic.  Last night I spent some time with FusionReactor watching the request history and picked out some of the ones that I felt could do better.  Not all of these were sounding the alarm on the monitoring, but I wanted a good sample to work with.  For the pages I was looking at, the database queries were once again the cause of the delay.  At first glance you might think we need a better database engine.  Unfortunately, the database server itself is not the cause, but rather the way some of the queries were written.  They worked great when we didn't have tons of traffic, but now they're holding up the show and have to be updated.

In particular, I identified two place where we had used a subquery to filter the results of another query.  Unfortunately, the SQL server was treating it like a join, so internally it had to take all the rows (over 100,000 records in one, and 30,000 in the other) and combine them all up together, then apply the various filters.  Due to the nature of the data and the size of the tables, this process was taking between 250ms and 300ms to execute.  This may not seem like a lot, but when the server is busy it really shows.  On some queries, if there were a lot of matches in the subquery, it would take upwards of 1300ms to run.  Unacceptable!

To combat this, I just broke the subquery out into its own query, then passed the results through to the original query using CFQUERYPARAM with the list="yes" attribute.  The subquery, run by itself, is very fast, and the main query, run by itself, is also pretty quick.  Running them together with an implied join in the database made them crawl.  Now that they're both in their own, the combined execution time is averaging less than 20ms, a vast improvement.  As a result the server is humming along against, pages are snappy, and it's able to handle more requests per second than it was before.  I can only hope it will be another year before we have to find something else to optimize that we don't see an an issue today.

So, to bring this all together, web programming is a lot like flying.  Plan ahead, monitor your guages, and train for emergencies before they happen so you can respond with confidence.  Address problems early, on the ground, before they become matters of life and death.

0 comments - Posted by Justin Scott at 11:19 AM - Categories:

Preventing SQL Injection Attacks

Over the last several weeks, ColdFusion web sites have been under attack all over the Internet.  In what is apparently the first large-scale automated SQL injection attack, what is assumed to be a botnet is attempting to use vulnerable sites to inject JavaScript code into the back-end database.  In this case, the attack is directed toward Microsoft SQL Server.  Once the JavaScript is injected, the hope is that the code from the database will be displayed somewhere on the web site, causing the visitor to download and execute the JavaScript code they had posted elsewhere on the Internet.

While this is all very unfortunate, it is entirely preventable.  If the web developer used proper secure coding techniques and considered the security implications of what they were doing, then of the many sites that were affected wouldn't be in the mess they're in now.  Fortunately this particular attack is easy to reverse within the database, and simple to prevent in the code.  Specifically, it exploits areas where the developer is assuming that the input from a URL variable will be safe to use and pass directly to the database query.  ColdFusion provides a tag called CFQUERYPARAM that tells the database to create and execute a "prepared statement" which negates the possibility of success in nearly all SQL injection attacks.  Unfortunately, many ColdFusion developers either don't know about this, or don't understand why it should be used.

On the CF-Talk mailing list there was a lot of discussion about this topic as the attacks began to infect more and more sites.  We all know that code can live on a site for years after it is written, and we can inherit code from other developers who don't know any better.  For them, it may take weeks, or even months, to go through their code and make the necessary updates.  In the mean time, their sites are sitting out there vulnerable to attack.  If you are in that position there are other preventative measures you can take:

1. Install a filter on your web server that performs URL rewriting.  This can look for specific keywords often used in these attacks (DECLARE and EXEC specifically for the current automated attack) and then rewrite or redirect and URL containing those keywords.  This prevents the attack from making it to your ColdFusion code at all.  Helicon makes an ISAPI filter for IIS called ISAPI_Rewrite that can do this on Windows servers.

2. Create a rule in your Intrusion Detection System that looks for and drops requests that contain the forbidden SQL keywords.  There are several custom Snort rules out there that will do this for you.

3. If you're on a shared server without access to these resources, you can implement code filters as well.  I wrote one back in late 2005 for a site I took over from another developer just after it launched.  Unfortunately the original developer had pretty much no concept of security, and I wrote several filters for it to hold us over until we had time to go back and clean up the code.  One of these was dubbed SQLPrev and has since been released into the public domain.  It can easily be included into any ColdFusion application to provide high-level protection from most SQL injection attacks.  The "official" ColdFusion MX code, as of this writing, can be found at http://www.gravityfree.com/_sqlprev.cfm.txt, and the ColdFusion 5 code can be found at http://www.gravityfree.com/_sqlprev5.cfm.txt.  I also maintain copies (MX, 5) here on my web site if the links above ever stop working.

I can't stress enough that these methods are not "full scale security" but rather a temporary perimeter filters to be used until your actual code is secure.  Someone once likened them to sentinels keeping watch at the city gates.  Just because the sentinels are there does not mean you shouldn't also lock the gates.

0 comments - Posted by Justin Scott at 11:24 PM - Categories: ColdFusion