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

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 developer advocate. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support.

Lafayette, LA