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.
Archived Comments
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.
Yea we noticed this error too when using sql comments to comment out coldfusion code. You MUST comment coldfusion code out with COLDFUSION comments.
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?
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.
This looks more like a source code error to me. I have commented out SQL code and have not had any problems.
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.
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?
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.
There you go, Tom beat me :-D Nice one.
@Tom
Yeah the queryparam values are never shown in the dump or where it's also rendered on an error page, etc.
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
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.
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
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!)
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
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."
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
Yeah - no bug at all - just one more thing for us to watch out for. ;)
This appears to have come back in CF2016
Um, to be clear, this isn't a bug. You get that right - I think I said so in the post.
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.
If it worked in CF10/11, I'd call it a bug there. Can you show an example query where it worked?
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'.
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?
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.
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. ;)