Interesting bug to watch out for involving cfqueryparam and sql

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

Comments