Posted in ColdFusion | Posted on 07-27-2010 | 2,618 views
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:
1<cfquery name="getBeer" datasource="cfunited">
2select type, description, quantity, price
3from beer
4where
5type like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.search#%">
6--and description like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.search#%">
7</cfquery>
2select type, description, quantity, price
3from beer
4where
5type like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.search#%">
6--and description like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.search#%">
7</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.
Then again, if I used them and CFBuilder greyed out the text, I might just assume it DID work, so good to know.
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.
<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?
I've used the -- SQL comment syntax in a few queries lately and have not had any problems. Running CF9 + Oracle.
Yeah the queryparam values are never shown in the dump or where it's also rendered on an error page, etc.
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
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.
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
But yeah - to your point - this is 100% expected - but definitely something I can see people doing by accident. (Myself included!)
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
Cheers.
--
Adam
[Add Comment] [Subscribe to Comments]