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.

It's handy to establish an SSH key between machines so that SSH-related commands don't prompt you for a password. This is handy to quickly SSH into another machine, and it's even more handy when setting up SSH commands in automated scripts. For instance, you may want to execute some rsync statements in a script that runs on a regular basis. It's better to have an established SSH key between the two machines than to have a password embedded in the script.

I recently reinstalled the OS on one of my Macs, and I've got backup scripts on my CentOS Linux box that use rsync to back up some pertinent data, so I had to reestablish the SSH key between the machines and had a hard time remembering how to do it. So this time I'm documenting what I had to re-learn.

First of all, there's a great post over at nixCraft that basically explains how to do it. But allow me to explain more thoroughly, ahem, dumbed down to my level. 

The key is remembering which machine is filling which role when you're reading the instructions. I'll call them the "Acting" machine--the one who is taking action and performing a command, let's say an rsync command--and the "Target" machine--the one who is being acted upon. In my case, the Linux server is the acting machine performing the rsync command, and my Mac is the target.

The process is simple. On the "Target" machine, generate a key, and then give that key to the "Acting" machine, which effectively gives it "permission" to login without the need to supply username/password credentials.

So, from the "Target" machine, in this case, my Mac, type the following command:

ssh-keygen -t rsa

This will generate a couple files that serve as a key for accessing the Mac. The ssh-keygen command may ask you where to store the key and what password to use. Just hit enter to use the default path and a blank password.

Next, still from the "Target" machine (my Mac), type:

ssh MyUsername@ActingServer "mkdir .ssh"
scp .ssh/id_rsa.pub MyUsername@ActingServer:.ssh/authorized_keys2

In the code above, MyUsername@ActingServer would be the username and address (for instance, perhaps the IP address) of the "Acting" machine, in my case, the Linux server. In the first line, you're just creating the .ssh directory if it doesn't exist. In the second line, you're copying the key you generated from the "Target" machine to the "Acting" machine, or from the Mac to the Linux server.  Note that the scp command will ask for the password to the MyUsername account because it is connecting to that server to send it the key.

Voile. As if by magic, the "Acting" machine should now be able to SSH into the "Target" machine without a password prompt. Correspondingly, you should be able to perform rsync and other SSH commands without a password prompt. Please note, however, that this is only a one-way key. We only gave my Linux server permission to access my Mac.

What if I want my Mac to similarly login to the server without a password prompt? In that case, the Mac and the server have effectively switched roles; the Mac is now the "Acting" machine and the server is the "Target" machine, so we just have to repeat the process from the other direction. Generate a key from the server and send it to the Mac. At that point, both machines will be able to access each other without a password prompt. 

What if I have multiple "Targets" that the "Acting" machine will connect to? For instance, perhaps I have multiple Macs, and the Linux server is running scripts on all of them. When you're sending the key to the "Acting" server with the scp command, use a different name for each key file, don't overwrite the same file each time! So in the example code above, we're sending the key as "authorized_keys2". When repeating this process for multiple targets, send the keys as "authorized_keys3", and so forth.

Hopefully this will clear up some confusion regarding this process.