After banging my head against the wall for a couple hours over an odd application error, I've finally come up with the best, most definitive reason to use <cfqueryparam> absolutely 100% of the time: It helps you avoid odd--dare I say erroneous--SQL parsing performed by ColdFusion in the <cfquery> tag. 

Huh? Say again?

That's right. In my experience, anyway, the <cfquery> tag will erroneously parse the SQL and tag legitimate content as disallowed SQL. Let's take a look at an example.

Here's a basic query that inserts some HTML content into a field:

Never mind why you might do this; the point is that it is allowable SQL. There's just an HTML special entity in the string. However, ColdFusion 8 sees the semicolon followed by the word "GRANT", and if the GRANT statement is disallowed for the ColdFusion data source, ColdFusion will throw an error similar to: "Error Executing Database Query. Executing the SQL statement is not allowed."

Of course, there is no error in the SQL, and the same statement executes just fine when you run it at the command line or SQL Query Analyzer. ColdFusion is doing a security check by parsing the SQL and attempting to verify that the SQL statement(s) only use allowed SQL as defined for the data source in ColdFusion Administrator.

If a SQL keyword is not immediately following the semicolon, ColdFusion won't make this mistake. And naturally, if your data source is configured to allow the offending keyword (in my example, "GRANT"), it will execute the SQL and it will execute properly, not actually executing the grant statement.

But if my code had used <cfqueryparam>, this wouldn't have been an issue:

This code will happily pass the SQL on to the database server.

This scenario may be a bit apparent when presented cleanly as I have here, but when the string being passed in is 3,000 characters or something similar, you're banging your head on the wall wondering what the heck is wrong with your query, especially when it has been working for years and now fails on just a single particular set of data. To my credit, the offending code is about 6 years old and all of my queries do indeed use <cfqueryparam> now. And more than ever, I'm now convinced to continue that habit.

This oddity probably comes up rarely, but I'm sure someone else has or will experience this. I hope this post will help you.

I have an app that uses <cfexchangecalendar> to look at appointments in an Exchange calendar and display them on a form on a web page. Everything was going smoothly until the department called and notified me that new appointments they were entering were an hour off on the web app. The calendar in Outlook would say 11am, for instance, but my app would say 12pm. 

After a little investigation, I came to realize that only new appointments with a date after daylight savings time ends were an hour off. Okay, that makes sense, but the query results returned by <cfexchangecalendar> don't provide any time zone information for me to properly handle the hour difference caused by DST!

As it turns out, Adobe has tracked this as Issue #70474, and the hot fix can be downloaded in ColdFusion 8 Cumulative Hot Fix 3. After applying the cumulative hot fix, <cfexchangecalendar> now returns the appointments exactly as they appear in Outlook.

It seems to me that it would be nice if the query results returns the GMT offset. But at least Adobe has addressed the inconsistency between the data ColdFusion retrieves and what Outlook displays.

This is a nice clean way to implement string trimming for the String object in JavaScript:

With that implemented, you can then trim a string in the object-oriented way you would prefer and love:

This is handy for client-side clean-up of form information, although bear in mind that depending on client-side code for data entry clean-up isn't necessarily wise unless you have some server-side code also checking the data.

In light of the departure of the ColdFusion Weekly podcast (so long Matt and Peter, and thank you!), I have assembled a list of my favorite "shows" or "episodes". Really, all of the episodes were good and worth hearing. But these episodes were fantastic.

Presented in chronological order:

• v1.5 - IDEs of...April: Came at a perfect time for me, because I had just switched to CFEclipse recently. So it was great confirmation for me and I enjoyed the Eclipse plugin recommendations. This episode is good for anyone still using Dreamweaver or HomeSite.
• v1.6 - Version Control: Also had great timing, because I had already committed myself to exclusively using Subversion but hadn't yet set up my Subversion server. While you're at it, check out the follow-up episode on v1.29 - Source Control Revisited. These episodes are a nice way to be introduced to version control for web development.
• v1.10 - Design Pattern Safari: Probably one of the most classic episodes and a must-hear. All about design patterns like beans, DAOs, etc.
• v2.01 - Rise of the Virtual Machines: Discussion of virtualization technology on both Macs and Windows. Great discussion, because I think this is an important technology for web developers; I am a big proponent of virtualization.
• v2.02 - Cryptology and Security: This was very interesting because of the information that was discussed regarding encryption, hashing, and salt.
• v2.15 - Mark Mandel on Transfer: Very good consideration of the popular ORM, Transfer.
• v2.27 - Selenium: Discussion on testing your apps on the client side with Selenium.
• v3.06 - Open Source BlueDragon: Discussion with Vince Bonfanti about BlueDragon going open source.

I will miss Matt's Vista rants.   Thanks to both of you for all of your hard work.

If you're hankering for a podcast to listen to, Brian Meloche is picking up where Matt and Peter left off with a new podcast called CFConversations. W00T!