Script based query issue to watch out for (and a fix!)

This post is more than 2 years old.

Credit for this find goes to Andrew Scott and his blog entry here. Essentially, he noticed that when using line breaks in a script based query along with bound parameters, he would get an error. Like so:

queryService = new query(); queryService.setDatasource("cfartgallery"); queryService.setSQL(" select artid, artname from art where mediaid =:mediaid order by artid "); queryService.addParam(name="mediaid", value=1, cfsqltype="CF_SQL_BIT"); result = queryService.execute(); </cfscript>

I wasn't able to replicate this until he pointed out that my test version had spaces in front of each line, not tabs. As soon as I switched to tabs, I got the error as well.

I reminded Andrew that the script based components are not encrypted. That means you can dig into the code and see what's going on. Out of all the script based components, the query one is probably the most complex. It does a lot of parsing on the SQL string. In there I found the bug. Part of the code that parses the string didn't check for tabs along with other white space.

I've written a fix for this and sent it along to ColdFusion Engineering. I've attached the CFC to this blog entry, but I'd recommend simply using spaces instead of tabs. No need to patch your server to be different from release for something so small.

Download attached file.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA

Archived Comments

Comment 1 by Ryan Vikander posted on 8/12/2011 at 4:32 PM

We have seen this as well in HQL. To solve it I just hit space before entering down.

Comment 2 by Andrew Scott posted on 8/12/2011 at 4:36 PM

Another fix is to have your where clause all on the one line.

Comment 3 by Ryan Vikander posted on 8/12/2011 at 4:37 PM

Also, the param variable is it supposed to have a space before the variable?

": mediaid" should it be ":mediaid"?

Comment 4 by Raymond Camden posted on 8/12/2011 at 4:45 PM

I saw that in Andrew's code and was convinced it was the issue, but it is not. But I do think it is bad formatting. (Well, maybe not bad, I don't like it though. :P ) Going to edit it now.

Comment 5 by Andrew Scott posted on 8/12/2011 at 5:06 PM

Agreed it is bad and it was an oversight on my part, normally I would type = :paramName

Comment 6 by Justin Carter posted on 8/13/2011 at 4:56 AM

I think Ben Nadel also found this bug a couple of years back :)

Comment 7 by Andrew Scott posted on 8/13/2011 at 5:52 AM

Justin I actually raised it as a bug with Adobe during the pre-release, but as I have mainly been doing ORM work on CF9 I had forgotten all about it.

I guess it was one of those things that Adobe just didn't think was important enough to fix at the time of the beta program, like another bug I am having with ORM event handlers and shared hosting that still exists.

Comment 8 by Raymond Camden posted on 8/13/2011 at 5:55 AM

To be fair to Adobe (ok, I wear my bias proudly now ;) this is a pretty easy issue to get around. Given limited time/budget to fix bugs, I can see skipping this one.

Comment 9 by Andrew Scott posted on 8/13/2011 at 6:21 AM

Agreed Ray, even then I did what I do now and keep the where clause all on the one line. Just now and then I do forget...

Comment 10 by Justin Carter posted on 8/13/2011 at 7:01 AM

Maybe they figured that everyone still fails to parameterise their queries so it wouldn't be much of an issue :) (Joke!)