portal entry | What iT iS dESign studios

Title:

The Hidden Power Of CFQUERYPARAM!

December 24, 2018 05:28:17 PM GMT

9 Comments

<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

Comments:

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