Simple guide to switching to CFQUERYPARAM
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