Wesley Harris

Software Tester

ColdFusion xmlFormat, maxlength, and column size

Any user input that is reflected back to users in a web application is a potential vector for cross-site scripting and similar code injection attacks by marauding nasties. One way to thwart these reprobates is to encode special characters in the user input before saving or displaying it. And in ColdFusionxmlFormat is available to help with that.

It’s also good practice to enforce maxlengths on user input. But if we allow special characters and then sanitize them with xmlFormat, the maxlength on the text field will no longer match the size of the string that we then need to store.

That’s because those special characters will be escaped. An apostrophe uses one character when the user inputs it but 6 when it has been escaped:

'

How do we calculate the column size to store our expanding string?

Easy. The longest escapes produced by xmlFormat are ' and the ASCII characters 128 to 255, which also take six characters (eg É). So the column size is simply (maxlength * 6).

If we are enforcing a maxlength of 50 characters on the text field, then the longest string we need to store in the database will be 300 characters.

NOTE: Calculating the column size doesn’t mean you don’t have catch and handle truncated data errors! Just because you enforce a maxlength via HTML doesn’t stop evildoers posting parameters of arbitrary length to your application. The first thing an attacker will do is reconnaissance on your application, which means fuzzing those parameters until it breaks. With luck, the platform or a web application firewall will be there to keep errors from leaking juicy info about the app, but let’s not rely on it. It pays to be a little paranoid.

Besides, handling errors gracefully is a hallmark of elegant code. We all prefer elegant code to ugly hacks, right?

Staff Favorite: LastPass password manager

Bogged down by too many passwords? Concerned about making sure you have secure passwords? You’re not alone.

We at Matrix Group have the same struggles. We need to juggle multiple passwords for multiple accounts yet need to keep the information safe and secure. The best way to do this is to use a password manager and LastPass is one of our favorites.LastPass logo

LastPass stores all of your account passwords in a secure, encrypted and synchronized vault. The password generator is particularly useful for creating complex passwords without needing to memorize them. It’s totally free to use on your PC and/or Mac, plus if you want it available on your mobile device it’s a mere $12/year.

PRO TIP: Run the “Security Check” (within the Tools menu) after LastPass has collected all of your account passwords. This will give you a breakdown of the strength of your passwords and show you where you have duplicates/weaknesses. Your ultimate goal is to have unique and complex passwords for each of your accounts.

Have you used LastPass? What’s your favorite LastPass tip or trick?

Liz Norton

Programmer

Cfqueryparam and Query Schema Caching

The other day I was working on a report that was running for minutes before finally timing out. The report was written in ColdFusion and queried the database.  I manually ran the query in SQL Manager.  It ran in less than a second.  Checked ColdFusion and server timeouts: all OK.  What in the world was causing this query to behave so strangely?Puzzle pieces

At last I tried an experiment: Remove the cfqueryparam tags from the code … the query runs fine; add the cfqueryparam tags back … the query slows to a crawl.

After being baffled as to why in the world the use of cfqueryparam tags would cause a query to misbehave so badly, I shared my mystery with fellow developer Eric Mosel, who promptly found an answer:  The query schema was being cached.  

Queries with cfqueryparam tags use Java prepared statements, causing the database to treat the query as a stored procedure. This means that even if changes to the table are made, the old query schema is being used when ColdFusion tries to execute the query, as nothing flags the database changes to your “stored procedure.” [1]

What to do, then, to refresh the query schema?

Option 1: OPTION (RECOMPILE)

To fix my long-running query, Eric suggested that I use a T-SQL Query Hint, OPTION (RECOMPILE), to force my query to re-compute its execution plan every time it runs.   This isn’t ideal for all queries, as the SQL server usually does the best job of coming up with an execution plan for queries. However, in this case it was necessary and solved the problem.

Option 2: Add a Semicolon

When the query schema is being cached, the exact text of the query matters.   If you change the query in ColdFusion, it causes the query schema to be refreshed.  But how to change the query when you don’t want to change the query but do want to refresh the schema? Add a semicolon to the end of your query.  Semicolons are the end-of-statement characters in SQL, and while not required for ColdFusion queries, are allowed and are enough of a change to force your query schema to refresh.

Have you run into this problem?  How did you solve it?

[1] Comment from Brandon Purcell, on Ray Camden’s blog post Odd Issue w/ CFQueryParam

Rich Frangiamore

Systems Admin

Tracking the Trackers

It’s old news that most websites track your surfing habits.  Advertising providers, online marketers, and other behavioral tracking elements are spread quite wide across many of the sites that we visit every day.  These elements could hide inside of a hidden frame, or even a single pixel.  Fortunately, these days, there are many new tools available to look behind the curtain and see exactly who is tracking you.

One of my favorite new browser plugins is Ghostery, a handy tool for Ghostery.com screenshotrevealing all of the tracking elements on any web page.  It installs in seconds and is cross-platform, cross-browser.  Once installed, the friendly blue ghost icon will alert you, with a small bubble, the number of tracking elements on the current page.  From there, you may elect to block any of these scripts from tracking you on any other site.

Read more about it at www.ghostery.com, download the extension for your favorite browser, and then go exploring!  You’ll be amazed at how many tracking elements Ghostery will alert you to on sites you visit every day.

What tools do you use to avoid being tracked, or do you think that tracking is a necessary part of website data collection?