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.

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