|
Coldfusion: Switching To CFQUERYPARAM
By Raymond Camden
Expert Author
Article Date: 2007-02-19
I've had a few requests to quickly review how to switch a dynamic query not using cfqueryparam to one that is using cfqueryparam.
I've covered the reasons for using them many times (basically sql injection and performance). There are also things you lose (like ColdFusion's built in query caching). With that in mind - here is basic rule to consider when figuring out if you need cfqueryparam:
If any portion of the WHERE/VALUES/SET clause in a query is dynamic, the cfqueryparam tag should be used.
So here is a simple example:
<cfquery name="searchUsers" datasource="data">
select id, name, email
from users
where name like '%#form.name#%'
</cfquery>
Now here is the same query switched to cfqueryparam:
<cfquery name="searchUsers" datasource="data">
select id, name, email
from users
where name like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.name#%">
</cfquery>
There are two things to note here. First is the cfsqltype value. This value tells the database what type of data is being passed in. There is a whole list of types that you can use. See the table on the cfQuickDocs cfqueryparam page. In general you will use: * cf_sql_varchar for simple strings, like my example above.
* cf_sql_integer for simple numbers, like those used in primary keys Another example of the power of cfqueryparam is lists. Imagine this query:
<cfquery name="searchUsers" datasource="data">
select id, name, email
from users
where usertype in (#form.categorylist#)
</cfquery>
This can be changed to cfqueryparam like so:
<cfquery name="searchUsers" datasource="data">
select id, name, email
from users
where usertype in (<cfqueryparam cfsqltype="cf_sql_integer" value="#form.categorylist#" list="true">)
</cfquery>
Lastly - I mentioned above in my "rule" (and since I called it that a few hundred of my readers will find exceptions :) that cfqueryparam should be used in the WHERE clause. You can't use it elsewhere. This query would not be a candidate for cfqueryparam usage.
<cfquery name="getSomething" datasource="data">
select #somecol#
from #sometable#
where x = 1
Comments
Tag: Coldfusion
 Digg |  Reddit |  Furl
About the Author:
Raymond Camden, ray@camdenfamily.com
http://ray.camdenfamily.com
Raymond Camden is Vice President of Technology for roundpeg, Inc. A long
time ColdFusion user, Raymond has worked on numerous ColdFusion books
and is the creator of many of the most popular ColdFusion community web
sites. He is an Adobe Community Expert, user group manager, and the
proud father of three little bundles of joy.
|
|
