portal entry

select a category, or use search below
(searches all categories and all time range)

The Hidden Power Of CFQUERYPARAM!

| View in Portal
December 24, 2018 05:28:17 PM GMT
<p>Everyone knows that you should be using CFQUERYPARAM.  It offers many benefits in the areas of security, data validation, and enhanced performance for your applications.  In fact, Adobe recommends that you use the CFQUERYPARAM tag within every CFQUERY, and I completely agree with this recommendation.  I think you would be very hard pressed to find a valid reason NOT to use it. Many of us know the benefit of CFQUERYPARAM from the application side, though we may not always be aware […]</p>
<p>The post <a rel="nofollow" href="https://coldfusion.adobe.com/2018/12/the-hidden-power-of-cfqueryparam/">The Hidden Power Of CFQUERYPARAM!</a> appeared first on <a rel="nofollow" href="https://coldfusion.adobe.com">ColdFusion</a>.</p>
Labels: Blog, CFML Tag/Function, cfquery, blog, cfml tag/function, cfqueryparam, cfsummit, ColdFusion, SQL


You got to this one before I did. I like it a lot. A good follow up would be the QueryExecute version on this. I believe, but I might be wrong, that that passing parameters as an array, does some auto-parameterization.
Comment by James Mohler
1526 | January 03, 2019 05:03:36 PM GMT
How does this affect hard coded variables that aren’t variables?  For example:<blockquote>SELECT firstName, lastName FROM People WHERE (Deleted = 0) AND (personID = <cfqueryparam cfsqltype=”integer” value=”#local.personID#”>)</blockquote> Should the “0” in this instance be in a cfqueryparam?
Comment by David Byers
1527 | January 08, 2019 09:34:57 PM GMT
Here is my best guess. It helps, but not a lot. I am imagining that Deleted. If you are only looking for deleted=0 then there is only going to be one query plan. Using <cfqueryparam>, you still have only one query plan.  
Comment by James Mohler
1532 | January 10, 2019 06:33:29 PM GMT
Hi Adobe, If you see my comment here, can you please document the fact that "cf_sql_" isn't needed (ex: cfsqltype="cf_sql_integer" can be shortened to cfsqltype="integer")? And can you please document which version removed that requirement? Was it CF11? Thanks!, -Aaron
Comment by Aaron Neff
1533 | January 11, 2019 07:08:01 AM GMT
What Statement  do you use to query the execution_count?
Comment by Bernhard Döbler
1942 | March 25, 2019 09:56:10 AM GMT
Bernhard, what "execution_count" are you referring to? I just don't see the quoted string you list here, which is why I am asking. You don't mean the count of records in the query result (the classic queryname.recordcount variable), do you?
Comment by Charlie Arehart
1946 | March 27, 2019 03:00:19 AM GMT
Bernhard, to query SQL Server's internal query statistics, I use the following: SELECT dm_exec_sql_text.text AS TSQL_Text, dm_exec_query_stats.execution_count FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle) CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle) INNER JOIN sys.databases ON dm_exec_sql_text.dbid = databases.database_id WHERE sys.databases.name = 'YourDatabaseNameHere';
Comment by Eric Cobb
1950 | March 28, 2019 02:16:10 AM GMT
Ah, I see now that Bernhard was referring to the count of executions of a given query plan, which I now see was listed in an image here. I was (and still am)  viewing this on my phone and the image is small. And when I searched for the string, I didn't find it because it was in that image. Sorry for my confusion, and of course thanks for the post, Eric.
Comment by Charlie Arehart
1951 | March 28, 2019 12:07:01 PM GMT
Is it possible to implement in the SELECT portion of the QUERY? If yes, a tutorial please. Been toying with this for the past fifteen minutes turning to Microsoft Bing to search for an answer. SELECT   u.#FORM.DesiredField# FROM   dbo.Users WHERE   ... Allowing the user to return and output one specific field of choice (DisplayName, ActualName, etc) <cfoutput>  #Evaluate("getUsers.#FORM.DesiredField#")#  </cfoutput>
Comment by jaschu
4781 | July 09, 2020 02:03:45 PM GMT