Not-obvious bug in Query.cfc

G'day:
This came up on the Railo group y/day, and I've just investigated it on ColdFusion too.

Update

Belay all this. As Igal points out in a comment aganist the Railo ticket I raised:

remove the single quotes around the curly braces.

the problem is that the single-quote before 2012 closes the string that starts with the single quote at the opening brace, and then the colons are outside the string which make the ReFind line at Railo-Code/src/resource/component/org/railo/cfml/Query.cfc:93 match the colon as a named parameter with the Regex ":\w+"

the sql query should read:

SELECT * FROM q1 WHERE ts = {ts '2012-09-07 08:45:06'}

[slaps head in disbelief at his own stupidity]

Yeah.  Good point.

Please continue to read about my stupidity below... ;-)

Here's some code:

<cfprocessingdirective pageencoding="UTF-8">
<cfscript>
    q1 = queryNew("");
    queryAddColumn(q1, "id", [1,2,3,4,5,6,7]);
    queryAddColumn(q1, "daysofWeek", ["Rāhina","Rātū","Rāapa","Rāpare","Rāmere","Rāhoroi","Rātapu"]);
    queryAddColumn(q1, "ts", [now(),now(),now(),now(),now(),now(),now()]);

    o = new Query(
        dbtype    = "query",
        sql        = "
            SELECT    *
            FROM    q1
            WHERE    ts = '#now()#'
        "
    );

    o.setAttributes(q1=q1);

    o.execute();
    q2 = o.getResult();

    writeDump(variables);
</cfscript>

I ran this on Railo and got a pretty unhelpful error:

Railo 4.0.0.013 Error (org.railo.cfml.query.namedParameterNotFoundException)
Message	The named parameter [46] has not been provided
Stacktrace	The Error Occurred in  C:\Apps\railo-4.0.0.013-railo-express-without-jre\lib\ext\railo-server\context\library\function\throw.cfm: line 11 9: name="extendedInfo" type="string" required="no" hint="extended information to the exception."><cfargument  10: name="object" type="any" required="no" hint="Throws a Java exception from a CFML tag. This attribute is mutually exclusive with all other argments of this function."><!---  11: ---><cfthrow attributeCollection="#arguments#"><!---  12: ---></cffunction>  called fromC:\Apps\railo-4.0.0.013-railo-express-without-jre\webroot\WEB-INF\railo\components\org\railo\cfml\Query.cfc: line 180

(I mean not only unhelpful on the eyes, but unhelpful information-wise too ;-)

There aren't any params in that query, so what's it on about?

Running it on ColdFusion is a bit more enlightening:

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

Error Occurred While Processing Request

Error Executing Database Query Parameter '45' not found in the list of parameters specified SQL: SELECT * FROM q1 WHERE ts = '{ts '2012-09-07 08:45:06'}' The error occurred in C:\Apps\JRunservers\cf9.en01.hostelbookers.local\cfusion.ear\cfusion.war\WEB-INF\cfusion\CustomTags\com\adobe\coldfusion\query.cfc: line 108

And, yeah, when putting named params into a query, one uses that :name syntax.  So there's a bug in both ColdFusion and Railo's param-parsing routine.

Obviously (Well, I hope it's obvious), one should never hard-code values (especially dynamic ones) into the SQL string anyhow, and this problem goes away if one properly parameterises the timestamp value:

o = new Query(
    dbtype    = "query",
    sql        = "
        SELECT    *
        FROM    q1
        WHERE    ts = :ts
    "
);
o.AddParam(name="ts", value=now());

I've raised a bug for this with Adobe (3326717) and Railo (RAILO-2059).

Cheers.

--
Adam