For some time now I’ve had an application running on Coldfusion 7 that will randomly throw exceptions because of poorly formed SQL in seemingly random queries. I could not explain the malformed SQL from looking at the queries – they were always fine. Also, the horribly mangled SQL I would see in the exception logs could not possibly have been generated by any conditional logic in the query. Here is an example:
<cffunction name="getUsers" access="public" output="false" returntype="query"> <cfargument name="username" type="string" required="false" default=""> <cfset var local = StructNew()> <cfquery name="local.qryUsers" datasource="dsn"> SELECT usr.username ,usr.email ,usr.name FROM users usr WHERE 1 = 1 <cfif Len(arguments.username)> AND usr.username = <cfqueryparam value="#arguments.username#" cfsqltype="cf_sql_varchar"> </cfif> ORDER BY usr.username ASC </cfquery> <cfreturn local.qryUsers> </cffunction>
The above function would run fine 99% of the time until the SQL generated would cause an exception. The SQL from the previous cfquery that caused the exception would end up resembling something like this:
ELECT usr.username ,usr.email ,usr.name FROM users usr 1 = 1 ASC
Needless to say it has become completely and utterly mangled – and with no way to account for it! Where has half the query run off to?! It was likely only a matter of time until a completely malformed query executed and resulted in data loss or corruption. So… what was the solution to this craziness?
My initial hypothesis was that SQL statements sometimes band together and run away at the thought of being transported to the database server for execution. This may very well have been true! Adobe could not be reached for questioning on this topic but they did release a hotfix that erected a 12 foot high fence around the edges of cfquery in order to prevent any bits from falling out or otherwise escaping at inopportune times.
The hotfix is available here.