ColdFusion CFQueryParam Binding vs. SQL Execution

Posted December 18, 2007 at 8:29 AM

Tags: ColdFusion, SQL

I ran into a really frustrating "bug" last night that took me over an hour to debug! I was trying to run this massive ColdFusion query and kept getting an error about a data type not casting property in a CFQueryParam tag. The thing that was frustrating me so much, and making the error so hard to debug, was that the CFQueryParam tag was not even in a chunk of SQL that was supposed to be executing!!

Now, this query was massively huge (over 1,000 lines) and could certainly have been refactored into several small queries; but, it was one of those queries that started out small and grew very quickly as the client asked for more and more functionality to be added to the same page. But don't get bogged down with that aspect - instead, notice that what I am demonstrating in this dumbed down version of the query is a complete misunderstanding between ColdFusion CFQueryParam binding and SQL execution:

 Launch code in new window » Download code as text file »

There's a lot going on here, but basically, we want to either insert or update a row in the Contact datatable. To determine whether we are updating or inserting, we take the given ID and try to match it up to a record in the database. If the ID exists, we are updating the row with ONLY the data that is available. If the ID does not exist, we are inserting the new row with all the required data fields.

Now, this should be broken down into different queries, probably, but again, not the point of the post. When I run the code above, we should expect to update the only row in the table, but instead, we get the following ColdFusion error:

Invalid data for CFSQLTYPE CF_SQL_INTEGER. coldfusion.sql.Parameter$DataTypeMismatchException: Invalid data for CFSQLTYPE CF_SQL_INTEGER.

Ok, so there's a problem with the integer value. But which CFQueryParam tag? Well, since I know that we have an ID that lines up with an existing ID, I know that we are running the UPDATE portion of the SQL, not the INSERT. And, if you look in the update portion, you will see that the only CF_SQL_INTEGER value is the age field. However, you will also notice that I am only updating that field if the age value is numeric (which it is not since we left it NULL in our incoming query).

So, WTF?!? The only other CF_SQL_INTEGER is in the INSERT portion of the SQL statement, but that code is not even executing! Furthermore, when I comment out the INSERT portion, the code runs the UPDATE portion without error. At this point in debugging, I was seriously losing my mind; I had code that was erroring out, but when I commented out a piece of code that wasn't even being executed, the rest of the code ran perfectly well.

I was just about to start rewriting this massive query when I had my eureka moment! It suddenly become so obvious: ColdFusion CFQueryParam data binding does NOT happen at the same time as the execution of the SQL code. Furthermore, CFQueryParam data binding doesn't even depend on the path of SQL execution.

Think about the ColdFusion CFQuery tag as a function call and the CFQueryParam tags are your function arguments. Arguments need to be defined and set no matter what the internals of the function are doing; the same is true for the CFQueryParam tags. My error in thinking in the above query was that the CFQueryParam tags within the INSERT portion were not being evaluated since that part of the SQL was not being executed. This was dead wrong. All of the CFQueryParam tag data bindings were being evaluated, but only some of those bindings were then put into use.

And, since all of the CFQueryParam tags were being evaluated, the CFQueryParam tag for AGE in the INSERT portion was also being evaluated. However, since the age column in the incoming data set was NULL, ColdFusion could not properly convert it into an INTEGER value (hence the data conversion error). As a quick fix for this, I put a Val() around the value:

 Launch code in new window » Download code as text file »

Now, even though that part of the SQL was not being executed, the CFQueryParam tag was evaluated properly.

It is rare that I have a query with such complicated logic that combines both ColdFusion and SQL conditional statements; but, when it does come up, it is very important to understand when ColdFusion CFQueryParam tag data binding occurs and to know that this happens regardless of SQL statement execution. In fact, you should probably think of all of your CFQueryParam tag bindings as taking place as the first step in the ColdFusion CFQuery tag; then, once all data bindings have been implemented, your full SQL statement will executed.

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

Reader Comments

Can I suggest something to try?

I found that overall unless very few of the statements will actually be an update on the table that performing the update statement then checking @@rowcount (SQL Server specific of course) to check the number of rows affected then doing an insert if neccisary at that point is quite a bit faster than doing a select then doing the update or insert depending on the select.

Or if 99% of the statements are going to be inserts (it really has to be nearly all of them) than trying the insert and catching the duplicate key errors (of course that almost never happens) and updating at that point can also be faster if you are performing a significant number of inserts.

Word to the wise and heed my warning:

It's a very common misconception in the CF world that using val() around a value will ensure the value is an integer.

THIS IS WRONG!

Val() returns the numeric value of a string.

val("3.2456") => 3.2456
val("1234") => 1234

If you're application calls strictly for integer values (such as primary keys in a database), you need to use the Int() in conjunction with val() to ensure an integer value is returned.

int(val("3.2456")) => 3
int(val("1234")) => 1234

Damn browser. Finishing my previous comment.

As such your cfqueryparam:

<cfqueryparam value="#Val( qContact.age )#" cfsqltype="cf_sql_integer" />

Should be:

<cfqueryparam value="#Int(Val( qContact.age ))#" cfsqltype="cf_sql_integer" />

@Mark,

I like that. I never thought about checking the @@RowCount after an update and then performing an optional insert. Very clever.

@Tony,

You make a good point about the Val() method in general. However, as far as it goes when dealing with the CFQueryParam tag, ColdFusion (I think) should convert the data type for you based on the cfsqltype attribute (I think!?!).

To test, I ran this:

<cfquery name="qTest" datasource="#REQUEST.DSN.Source#">
SELECT
( <cfqueryparam value="3.5" cfsqltype="cf_sql_integer" /> ) AS test
</cfquery>

In the final query, the "test" column is "3", not "3.5". I don't know if ColdFusion is doing the conversion, or if SQL is going the converstion, but somehow, the CF_SQL_INTEGER type is making the conversion happen.

@Ben,

Actually it's the driver that is doing the conversion. The only reason I stress the point about doing the conversion within CF with int() is because the Oracle driver I'm using doesn't and throws an error.

@Tony,

Very interesting! I wish the documentation was a bit more obvious on this matter (maybe I just missed it). Thanks for pointing that out.

Jesus Christ on a stick.

He also likes being on a bicycle :)

Seems like a prime example of code that belongs in a stored procedure to me.

@Eric,

You might be right. I know very little about stored procedures; but, this is certainly a place where ALL speed boosts would be helpful. And, since parameters need to be passed into a stored procedure, certainly, we wouldn't run into the problem of not understanding what data is where, when.

I am finding CFQueryParam weirdness of a different sort. I have this part of a query

AND lt.ltName = <CFQUERYPARAM VALUE="#REQUEST.thisProp#" CFSQLTYPE="CF_SQL_VARCHAR">

Where ltName is a varchar(50), and REQUEST.thisProp is "hartstone".

The result is an "Unsupported data conversion" error. Even if I do this it fails:

AND lt.ltName = <CFQUERYPARAM VALUE="hartstone" CFSQLTYPE="CF_SQL_VARCHAR">

To fix it, I just did this:

AND lt.ltName = 'hartstone'

I have CFQueryParam doing the identical thing throughout the application, and it is only in this query it blow up.

Not directly seeking answers, but wanted to put this out there, since it was my own hour of debugging along the same lines.

@Paul,

Did you recently change the structure of the database table that is referencing?

If so, the CFQueryParam will get ColdFusion to use a cached structure of the query, which can cause problems. This is usually what is happening if CFQueryParam causes a data-type-conversion error.

Also, please please please tell me you are not using SELECT *.

It is a SELECT *, and I had added a column in the past day, but it's not a cached query. Would CF still cache anyway?

I'm trying to remember the other times this has happened (it's uncommon, and my previous troubleshooting was rushed), and it's possible I had made changes around then, too.

I suppose I'm also confused since I've made changes to other tables that have SELECT * queries w/ CFQueryParm, and have not seen this before.

@Paul,

1. By using CFQueryParam, ColdFusion automatically caches the query (or at least tries to).

2. Don't use "SELECT *"... period :)

1) I certainly never knew that, and wouldn't have guessed it, either, since I use it in updates, inserts, etc. I also suppose only having gotten the error in this one case, based on all of my uses of it, makes it even more suprising.

I am espcially suprised because the error started hours after I made the change. I'm going to assume this is a long cache.

2) I know, it's lazy in a lot of ways, but I found it necessary over time. We have a lot of code we reuse on other sites, but the clients customize the data the site will hold. I ended up having to change too many pages; adding values in some, and removing unused ones in others.

I have tried to think of a custom app to address this, and I suppose one is possible, but I get the feeling it would have nearly as much overhead to maintain the calls as it would to do what I am doing now.

If I start running into the same problem elsewhere, I will probably change my wicked ways ;)

@Paul,

I don't really understand how the underlying caching mechanism works. I think it works based on the actual text in the query. So, if you are getting this, you can probably add some spaces or a line return to the query string and ColdFusion will *think* it is a different query since it has different characters.

I hear you about the select * in a big app, especially when its being used in a lot of places. I don't really have any good suggestions for that :)

I think it must be more than that, because I changed the query considerably when degugging. I took out different parts to narrow down what was the cause, and would error in different states. If it's a cache, it behaves much differently than a standard cache.

If it weren't for the fact our customers like when our sites work ;), I would put it back and try to force the error (I've never been able to get it to happen locally). That way I would change the SELECT * aspect and see how it behaves.

If I run into it again, I'll desconstuct it differently when debugging so I can poke it w/ a stick w/ all this in mind. I'll let you know if I come up w/ anything insightful.

Thx for the feedback. Btw, thx for the site, too, and pls keep it up. It's been a resource on more than one occasion.

@Paul,

Yeah, keeping those clients happy is always a good priority to have :)

Glad you like the site!

Nice article; I have helped a few developers that have had troubles understanding this too. The way that I help them is through examples; think of your query in cfquery like html in cfsavecontent. Any SQL that you have in the cfquery tag while get sent, unless you have cfif (or cfswitch) excluding that portion. This is similar to html being saved in cfsavecontent. Once you can separate coldfusion and SQL code logic in your mind, you will find you can debug these issues a lot quicker.

I have also found the use of parsing of XML into a variable table very useful (it seems to be exclusive to MS SQL). By parsing XML into a variable table, you can send multiple rows to be added, updated & deleted in the one parameter.

With the unsupported data conversion error, I have found a coldfusion restart refreshes the cache.

Ran into this again today, but not questioning the problem itself. I'm actually adding to the last post, about clearing the cache.

Is there a way to clear the query cache, similar to the clear template cache button in CF admin? I'd like to be able to do this w/o a restart when the error comes up.

You could always change the query slightly. As one of the previous posts suggest, add an alias.

Re: Clearing the query cache - set the max number of cached queries in CF administrator to zero. I don't think you even have to reload the page once you do this, though I generally do to make certain. I find this a heck of a lot easier than making edits to my queries (I am picky about whitespace) every time I want the query to be refreshed. CF supposedly looks at BOTH the name of the query and the query SQL when determining caching so if you change either it won't be cached any more. But I prefer the CF admin route as it doesn't require any changes to my code. Hope this helps.

I think using "SELECT *" can slow down your queries as the database will first have to check which columns it should be returning before returning them. I generally only use SELECT * in CF query of queries.

I have stopped using CFQUERYPARAM on 'safe' values, meaning only VAL()'d data.

As far as I can tell #Int(Val(qContact.age))# is equally as safe and uses less resources than <cfqueryparam value="#Int(Val(qContact.age))#" cfsqltype="cf_sql_integer" />.

I don't believe there is any SQL injection which can get through a VAL() function. Am I wrong?

If the query requires an integer, how is the cfqueryparam any safer than a real verified integer? Additionally, using #Int(Val(qContact.age))# defaults to zero, so it can't be Null and won't throw an exception.

My simply saying 'it hasn't bitten me yet' only goes so far. I am very careful when I don't use cfqueryparam. I have been called in to clean up a SQL injection mess in the database and secure all the code when a previous developer didn't do their job. Not fun. I clearly understand the importance of cfqueryparam. I have also learned that many cfqueryparams can weigh on the load time, so I try to only use it when required - on text and date values (anything not VAL()'d). But if I'm VAL()'ing it then I don't use cfqueryparam. Asking you and the community, is this a bad practice?

@Bagar,

I believe you are correct that Val() will cut out any SQL injection attacks as it strips the non-numeric values *Before* the SQL statement is even finished being designed.

The only thing that I can say is that I have been taught that CFQueryParam offers some performance benefits due to the way SQL statements get executed with variable bindings. I don't know the internal mechanisms of this, but it's what I've been told.

That said, I have stopped using it for static values, such as:

is_deleted = 1

... as I found this to be easier to read / write.

There are performance gains associated with CFQUERYPARAM if the underlying DBMS supports bind variables and the query statement is executed multiple times. For example, if you use bind variables in Oracle, it will check to see if there is an existing execution plan for the SQL statement, whereas if you don't use them, it will parse the statement and come up with a new execution plan for it. I believe this is the case even when static variables are used.

@Dcs,

Thanks for the insight.

I'm having an issue where CFQUERY PARAM is slowing down the query to the point where using them the query times out, without them, it runs in 10-15 seconds. I've consistently seen this. Anyone hear of this?

This is using ORACLE as the database

@Alan,

Funky! I've never heard of anything like that.

I've had something like that happen when I've had code inside of <CFLOCK> statements that was bad, and caused everything to timeout (I can't remember what I had miscoded). It didn't throw a helpful error, and I only figured it out when I started commenting out portions of code.

Thanks for your quick responses. CFLOCK is not used.

I'm not a ColdFusion developer, I'm the Oracle DBA. I work with 3 Cold Fusion Developers. I can extract the query out of ColdFusion, run it in pl/sql developer and it returns in about 15 seconds.

I had suspected that it may be a JDBC issue. We are running Oracle RAC and use the Oracle JDBC drivers so Oracle TAF is supported. After just finding this article maybe it's not a JDBC issue?

http://www.coldfusionmuse.com/index.cfm/2008/11/18/performance.and.cfqueryparam

Anybody have any idea what is the best proven method to connect CF7 MX to an Oracle RAC Cluster?

Have you tried running the query in ColdFusion without CFQUERYPARAM? That would give you more information as to whether CFQUERYPARAM is the actual cause.

Yes I have, and without the CFQUERYPARAM, performance is consistent as seen when run directly in PLSQL/Developer.

I ran it at the same time of two different web servers, with and without cfqueryparam, without was 15 seconds, with timed out.

Hmmm, well, as a practical matter I would just avoid using CFQUERYPARAM for that query - just make sure you verify your parameters to avoid SQL injection (which isn't as much of a problem with Oracle as it is with SQL Server anyway).

It sounds to me like an issue with converting from coldfusion data types to oracle data types (or something similar). How many different data types are you using in your queryparams? Can you try putting the queryparams in one at a time and see if it is a specific one?

I've recently run into a similar issue with CFQUERYPRAM as well. I have the simplest query running at 750ms to an SQL server. Within SQL Studio it returns in under 1 ms.

If I isolate the same query in a separate CFM template, it returns just as fast. But if the query is embeded in a function within a component, it executes in 750ms. I took out the CFQUERYPARAM and it results in 4ms.

This never used to be an issue, until CFMX8. Hadn't touched ColdFusion in about a year prior to that which was then CFMX7.

DonV69,

Thanks for your response. While I did not update, eXcalibur.lk hit the nail on the head. It was a parameter conversion problem.

Thanks for all.

Alan

Post Comment  |  Ask Ben

Post A Comment

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

 

* If you subscribe, any new posts to this thread will be sent to your email address.