That is a false statement. There are reasons not to use it. They come with perils. If the data being sent to the query does not come from the outside then the only risk to SQL injection comes from the developers working on the query, and they have other ways to cause havoc without having to write SQL injection attacks.
When you use cfqueryparam the statement that gets compiled uses sp_prepexec. This causes MSSql server to generate the query execution plan for the query before actually executing the query. This can actually end up causing the server to run the query 2x. If you have a very long running query for a report for example that queries millions of records of data against millions of other records of data etc. and takes several minutes or hours to execute you do not want that to happen 2x every call. In theory when sp_prepexec generates the execution plan that is cached and not needed to be created again however on high volume / traffic sql servers it is not possible to cache every execution plan forever. Often long running queries that are executed rarely fall out of the execution plan cache. This can create a huge performance problem for a sql server. I would agree with the statement that you should use cfqueryparam whenever you can and only not use it when you absolutely have to. For the cases like I describe above you can write the bind variables yourself into the query and gain the same benefit without sp_prepexec being called. IE SELECT columnA FROM tableb WHERE columnC = <cfqueryparam cfsqltype='cf_sql_varchar' maxlength='2' value='ab' /> Could become DECLARE @param1 varchar(2) SET @param1 = 'ab' SELECT columnA >From tableb WHERE columnC = @param1 -----Original Message----- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, August 10, 2007 11:18 AM To: CF-Talk Subject: RE: Is cfqueryparam worth it? As many others have said, there is never a reason NOT to use cfqueryparam. You can still use your trick and cfqueryparam doesn't have to bomb: <cfqueryparam cfsqltype="cf_sql_integer" value="#val(url.userid)#"> M!ke -----Original Message----- From: Ben Mueller [mailto:[EMAIL PROTECTED] Sent: Friday, August 10, 2007 12:01 PM To: CF-Talk Subject: Is cfqueryparam worth it? I'm trying to determine if cfqueryparam is really worth using. For data validation, we tend to do something like this: SELECT username FROM user where userID = #int(URL.userID)# the nice thing about this is that if "URL.userID" isn't an integer, int() returns 0, and the query executes and simply returns no records. For us, this is a far preferable method than what cfqueryparam would do, which is to bomb before executing the query. Then today I discovered that cfqueryparam supports bind variables, which theoretically will improve database performance. So now the question is: how much does it improve performance? Am I really going to notice it? Should I really switch my queries over to something like this: SELECT username FROM user where userID = <cfqueryparam CFSQLType="CF_SQL_INTEGER" value=#int(URL.user_id)#> Any advice is appreciated. Thanks, Ben ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285989 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
