More Linux Humor....

This had me rolling on the floor laughing....

CFQUERY in Site Wide Error Handler Does Not Escape Single Quotes for Error.DateTime

At last week's Boston CFUG presentation by Ben Forta, one audience member described a problem that has prevented them from migrating to ColdFusion MX from ColdFusion 5. The problem was that their custom error handler template wasn't working when configured as the Site Wide Error Handler template in the ColdFusion Administrator.

That ColdFusion user has since emailed me the error template and a description. The template attempted to present some error information to the user and then log all the error data by inserting it into a database with CFQUERY. The problem was that the error handler template rendered some of the error handling info to the client, but then instead of inserting the data the original error that first invoked the error handler was returned to the client. The client then saw a partial page with "Ooops, there's been an error" followed by the original error as if it were never handled.

debugged the error template and found that when inserting the error.datetime variable into the database, the error.datetime value did not have its single quotes escaped. Normally the error.datetime variable would look like this, including the single quotes: {ts '2004-07-07 15:32:51'}. When passing such a value to CFQuery, the single quotes are normally escaped to prevent invalid SQL. However, in this case, since the error.datetime was not escaped for single quotes, the error handler itself errored, and hence did not actually work as designed.

The solution, or the workaround, for this is to use PreserveSingleQuotes( ) on the error.datetime variable in the SQL when inserting the error data into a database in the site wide error handler. Example

[CFQUERY name="trackError" datasource="errors">
INSERT INTO ErrorEvents(Template,Diagnostics,ErrorDate)

While reviewing the ColdFusion application.log, I found that my testing had actually logged numerous entries indicating that the site wide error handler had failed. The log entry identified exactly what the problem was, too. Each time I ran the test, the following entries appeared in pairs:

"Error","web-10","07/07/04","14:17:16",,"Error thrown by site-wide exception handler:"
"Error","web-10","07/07/04","14:17:16",,"Error Executing Database Query.[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '{ts '2004-07-07 14:17:15'}'. The specific sequence of files included or processed is: C:CFusionMXwwwrootcustomercfug2error.cfm "