Single quotes woe while using the new CF9 CFScript Query syntax and bound parameters

[Guest]

Hi all,

don't know if anybody else has run into this, but this morning I just spent around two hours troubleshooting a very simple query.

I'm running the CF9 Server Developer Edition, and using the new CFScript component syntax. My function is something like this, irrelevant code omitted:

    public query function getCountryOfficiers() {
    var officiers = new query();
    officiers.addParam(name="cc", value=this.countryCode, cfsqltype="varchar");
    officiers.setSQL(
          "
          select
                    P.FirstName + ' ' +P.LastName as Name,

                    ...
                    P.EMailAddress as EMail
              from ...
        where CountryCode = :cc
         ");

    return officiers.execute().getResult();
    }

While testing this function, the server keeps giving me error of this kind:

The web site you  are accessing has experienced an unexpected error.
Please contact the website administrator.

The following information is meant for the website developer  for debugging purposes.

Error Occurred While Processing Request

Error Executing Database Query. Incorrect syntax near ':'. The error occurred in D:\ColdFusion9\CustomTags\com\adobe\coldfusion\base.cfc:  line 364 Called from D:\ColdFusion9\CustomTags\com\adobe\coldfusion\query.cfc: line 614 Called from D:\ColdFusion9\wwwroot\ExtranetNG\Country\CountryDAO.cfc: line 71 Called from D:\ColdFusion9\wwwroot\ExtranetNG\CFCTestPage.cfm: line 5 Called from D:\ColdFusion9\CustomTags\com\adobe\coldfusion\base.cfc: line 364 Called from D:\ColdFusion9\CustomTags\com\adobe\coldfusion\query.cfc: line 614 Called from D:\ColdFusion9\wwwroot\ExtranetNG\Country\CountryDAO.cfc: line 71 Called from D:\ColdFusion9\wwwroot\ExtranetNG\CFCTestPage.cfm: line 5 362 :                         #getPreserveSingleQuotes(sqlArray[1])# 363 :                         <cfif sqlType neq "" and arraylen(sqlParams) gt 0> 364 :                             <cfloop index="i" from="2" to="#ArrayLen(sqlArray)#"> 365 :                                 <cfif (i-1) lte arraylen(sqlParams)> 366 :                                     <cfqueryparam attributeCollection="#sqlParams[i-1]#"/>

It really took me a long time including debugging into the query.cfc and base.cfc components provided by Adobe to figure it out - it seems if there are an odd number of single-quoted strings in the SQL statement, then the bound parameter parsing doesn't work correctly and the statement stored in sqlArray gets screwed up.

To illustrate the point, the parser would happily process the following (TWO single-quoted strings in total):

    officiers.setSQL(
           "
           select
                     P.FirstName + '1st' +P.LastName as Name,

                     ...
                    P.EMailAddress+'2nd ' as EMail
               from ...
        where CountryCode = :cc
         ");

But not this (THREE  single-quoted strings in total):

    officiers.setSQL(
           "
           select
                     P.FirstName + ' ' +P.LastName+' 2nd' as Name,

                     ...
                    P.EMailAddress+'3rd' as EMail
               from ...
        where CountryCode = :cc
         ");

I must say I was dumbfound when I discovered this, really don't have much time to do more testing now, so I just wrote a note to our developers to keep an eye on that, but hopefully we can get a fix for this soon?

I'm actually quite enjoying using the new syntax - makes logic much easier to read. But this kind of things really makes you think twice...

regards,

Billy

[Adam Cameron.]

Bleah.

Have you raised a bug for this?

http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html

--
Adam

[Guest]

Thanks for the pointer! Will do!

Billy

[Guest]

I looked closer on this and my original verdict was not correct:

First, using even number of single quoted strings doesn't really solve it - although it doesn't give an error, the statement intepretation is different and most likely wrong.

Second, it only happens when there are single quoted EMPTY strings involved, this is because of the way the function replaceDelimsWithMarkers in CustomTags/com/adobe/coldfusion/query.cfc works - it splits the statement by single quote using listtoarray, and only processes even numbered elements, which is *supposed* to be the single quoted content.

BUT, in case of empty string, ListToArray ignore the empty fields (empty strings) by default, this screws up the content interpretation.

All it takes to fix it is to use the attribute "includeEmptyFields" in the call of ListToArray function in CustomTags/com/adobe/coldfusion/query.cfc.