Interesting bug to watch out for involving cfqueryparam and sql

This post is more than 2 years old.

Credit for this find goes to Lance Staples, a coworker of mine who is apparently too cool to blog. He noticed an error today in a ColdFusion script. See if you can spot what the problem is:

<cfquery name="getBeer" datasource="cfunited"> select type, description, quantity, price from beer where type like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.search#%"> --and description like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.search#%"> </cfquery>

When run, the error was: Invalid parameter binding(s)

Spotted it yet? Someone edited the SQL to comment out the second part of the where clause. However, the "comment" was a SQL comment. ColdFusion doesn't know diddly poop about those. ColdFusion Builder recognizes it though and even grays it out, so it can definitely be confusing. Because ColdFusion doesn't ignore anything outside of it's own CFML comments, it still tries to send the second bound parameter. SQL Server ends up getting two bound params when only one is actually being used.

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 https://www.raymondcamden.com

Archived Comments

Comment 1 by SuperAlly posted on 7/28/2010 at 1:48 AM

Interesting. I don't think it would ever even occur to me to use SQL comments in a cfquery though. I think I would just assume they wouldn't work.
Then again, if I used them and CFBuilder greyed out the text, I might just assume it DID work, so good to know.

Comment 2 by Ryan Vikander posted on 7/28/2010 at 5:27 AM

Yea we noticed this error too when using sql comments to comment out coldfusion code. You MUST comment coldfusion code out with COLDFUSION comments.

Comment 3 by Dominique posted on 7/28/2010 at 10:54 AM

This is weird. I have used SQL comments in a CFQUERY, withouth breaking it. Maybe it's not the comment, but the CFQUERYPARAM within the comment?

Comment 4 by Tim Dawe posted on 7/28/2010 at 11:51 AM

This behaviour makes sense to me. I think of it in the same way way as using CF code inside an HTML comment. I'd expect CF to process any code in there and just pass it to the browser to deal with.

If you used -- to comment out straight SQL, it would work as expected. The problem is that CF doesn't know that the SQL engine is going to ignore that bit of code so it sends both params, which the database isn't expecting.

Comment 5 by Gary Funk posted on 7/28/2010 at 5:12 PM

This looks more like a source code error to me. I have commented out SQL code and have not had any problems.

Comment 6 by Eric Cobb posted on 7/28/2010 at 5:17 PM

What version of CF? We're using CF 8 with Oracle and put SQL comments in our queries with no problems. Maybe it's only an issue when using cfqueryparam? I'm not sure, the lazy Goobers here tend to not use cfqp, so I'm not sure if we have that combination or not.

Comment 7 by Tom Jenkins posted on 7/28/2010 at 5:42 PM

Its interesting, I did a quick test on this and it definitely is only with cfqueryparam. If you do the below:

<cfset testID=0>
<cfquery name="rsTest" datasource="#datasource#">
SELECT PageID FROM tblSitePages
WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="1">
-- OR PageID = #testID#
</cfquery>
<cfdump var="#rstest#">

It works fine. What's interesting is within the dump you can see the SQL that was used ... with cfqueryparam value showing as "?".

is this to do with how it is rendered somehow?

Comment 8 by Steve posted on 7/28/2010 at 5:48 PM

Yeah I'd have to agree with a few of the others and say it's probably the cfqueryparam tab being processed by CF, but with the SQL comment messing it up. Try commenting out a line of normal SQL that contains no tags. I would have run a test before making this comment, but I'm on the train home sorry.

I've used the -- SQL comment syntax in a few queries lately and have not had any problems. Running CF9 + Oracle.

Comment 9 by Steve posted on 7/28/2010 at 5:50 PM

There you go, Tom beat me :-D Nice one.

Comment 10 by Steve posted on 7/28/2010 at 5:54 PM

@Tom
Yeah the queryparam values are never shown in the dump or where it's also rendered on an error page, etc.

Comment 11 by Adam Cameron posted on 7/29/2010 at 12:59 AM

All the people coming to their own conclusion that "this is only with CFQUERYPARAM" could possibly do with polishing their specs or at least *reading* what Ray has written. The heading of the article is "Interesting bug to watch out for ***involving cfqueryparam*** and sql" (my emphasis) and Ray *explains* that specifically relates to CFQUERYPARAM.

I think the only *bug* here though is in CFB's colour coding. Other than that, it's to be expected. The person who commented that this is just like an equivalent situation with HTML comments has summed it up pretty nicely.

Good article, btw, Ray. Whilst suggesting "this is to be expected", I'm sure had I come across this in my own code (and I do use SQL comments sometimes, for whatever reason), I'd be scratching my head for a while until the penny dropped.

Have you raised a bug re the CFB colour-coding?

--
Adam

Comment 12 by Bjorn Jensen posted on 7/29/2010 at 1:22 PM

I'd say it's a pretty logical bug, and as it's been mentioned before, the biggest issue is really the color coding shown.

A much worse bug/feature is something like this:
<cfquery datasource="a">
bla bla <cfqueryparam cfsqltype="cf_sql_integer" value="#returnvalue()#">
</cfquery>

<cffunction name="returnvalue">
<cfquery datasource="b" name="q">
select id from table
</cfquery>
<cfreturn id>
</cffunction>

You'd think the first query would use datasource a and the function call would use datasource b, but instead because of the function call inside the cfqueryparam the first query will have it's dsn changed to b.
This has already bitten us a few times already, and it's pretty hard to spot.

Comment 13 by Adam Cameron posted on 7/29/2010 at 1:52 PM

Bjorn, I think this issue might have been fixed: http://cfbugs.adobe.com/cfb...

Of course it's very difficult to tell why/how an issue was deal with in the bug tracker, as it just says "closed", and that's it.

Might be worth re-testing on CF9.0.1 to see if it's been sorted out, though.

--
Adam

Comment 14 by Raymond Camden posted on 7/29/2010 at 3:43 PM

Adam, I'm not so sure it is a cfb bug. It _is_ a SQL comment. I don't know - given that CFB has to make a choice there on how to colorize, I think it is making the right choice. Maybe.

But yeah - to your point - this is 100% expected - but definitely something I can see people doing by accident. (Myself included!)

Comment 15 by Adam Cameron posted on 7/29/2010 at 3:56 PM

Yep, and as you say: one should not expect an SQL comment to have any effect on the <cfqueryparam tag>. So when the <cfqueryparam> is resolved, it does two things
1) swaps the <cfqueryparam> tag in the "body" of the <cfquery> tag for a param "marker" (you know, it shows up as a question mark in the SQL string when you output the SQL string in the debug) in the SQL string. This marker is on the same line as the SQL comment, so by the time it gets to the DB server, it's commented out:

select type, description, quantity, price
from beer
where
type like ?
--and description like ?

2) passes the actual param value to the DB driver int he param structure.

So the SQL string the DB receives has only one param marker in it, but CF is passing two params. Error.

Well that's my take on what's happening, anyhow.

--
Adam

Comment 16 by Raymond Camden posted on 7/29/2010 at 4:40 PM

My take too. And - I think the DB server says: "Dude, your SQL has 1 bound param, but you sent me 2. This may not be a hack attempt, but it's not right. Error."

Comment 17 by Adam Cameron posted on 7/29/2010 at 5:21 PM

Ah, sorry mate: my turn to misread what you were writing. I thought when you said it might not be a CFB bug that you were suggesting it was a CF bug instead (not that you said that). You're suggesting that possibly it's not actually a bug at all: just a nice quirk to be aware of?

Cheers.

--
Adam

Comment 18 by Raymond Camden posted on 7/29/2010 at 5:22 PM

Yeah - no bug at all - just one more thing for us to watch out for. ;)

Comment 19 by Collin Schroeder posted on 5/5/2016 at 10:51 PM

This appears to have come back in CF2016

Comment 20 (In reply to #19) by Raymond Camden posted on 5/5/2016 at 11:14 PM

Um, to be clear, this isn't a bug. You get that right - I think I said so in the post.

Comment 21 (In reply to #20) by Collin Schroeder posted on 5/5/2016 at 11:48 PM

Thanks for the quick reply. If not a bug there does seem to be a behavioral change from previous versions that will cause us to clean up possibly hundreds of queries. I haven't run into this problem in CF 10 or 11. But when I run the same apps on CF2016 that have worked for years I have to go through and strip out all the comments to get them running.

Maybe the problem here (aside from not using CF comments) or difference between CF11 is that it is executing all the SQL on a single line. CF doesn't have to care about the SQL comments as long as the newlines are retained right?

In and of itself, it wouldn't be a big deal if Adobe still offered CF11 for download but unfortunately it is nowhere to be found and I'm trying to get a new developer set up on one of our projects.

Comment 22 (In reply to #21) by Raymond Camden posted on 5/6/2016 at 12:57 AM

If it worked in CF10/11, I'd call it a bug there. Can you show an example query where it worked?

Comment 23 (In reply to #22) by Collin Schroeder posted on 5/6/2016 at 1:25 AM

Here is a literal example from one of our projects:

http://pastebin.com/eUJZUSws

But a far more simple example will break as well:

<cfquery name="test" datasource="#get('dataSourceName')#" username="#get('dataSourceUserName')#" password="#get('dataSourcePassword')#">
select
logonname, --primary key field
area
from b_logon
</cfquery>

yields:

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','.

or this example from the master db in MS SQL Server

Select
UserID --offending comment
from Users

which yields:

[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'UserID'.

Comment 24 (In reply to #23) by Raymond Camden posted on 5/6/2016 at 1:29 AM

Err... wait. This blog post was about how a SQL comment was used in the same line as a cfqueryparam. The point was that the SQL comment did *not* comment out the CF tag since the CF code was executed first.

In all your examples, you aren't using cfqueryparam, or another CF tag commented out.

This looks to be a completely different issue. Do you agree?

Comment 25 (In reply to #24) by Collin Schroeder posted on 5/6/2016 at 1:54 AM

Yes it is a different issue. I happened on your page by chance. I got the same error because a cfqueryparam occurred after a SQL comment on a subsequent line.

After diving into it, the problem is ColdFusion 2016 stripping the newlines out of queries.

I created a stackoverflow post for it:

http://stackoverflow.com/qu...

Think I should submit this as a bug to Adobe?

You helped me before with a XML XSS scripting issue by the way. You were a huge help and it worked flawlessly.

Comment 26 (In reply to #25) by Raymond Camden posted on 5/6/2016 at 1:56 AM

Wow, that is a *damn* bad bug. You should absolutely file a bug report. I wouldn't hold your breath though.

And sorry - but I'm not terribly pleased with the Adobe CF team these days.

I'd dump your code base and switch to Node, but I assume that's not an option. ;)