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
Comments
select #somecol#
from #sometable#
where x = <cfqueryparam value="1" />
While I can't remember who wrote it, they argued that putting the cfqueryparam in the WHERE clause in the above example would actually speed up the query by forcing the query to using value binding. So, while this is not a matter of security, apparently CFQueryParam will bring about a performance increase in simple queries.
Forgive me if this information is wrong, but it is what I recall.
SELECT
price * <cfqueryparam cfsqltype="cf_sql_integer" value="#form.qtty#"> AS amount
FROM
orders
You can not use cfqueryparam to substitute identifiers, but you can use it for values everywhere in the SQL Nstatement.
Jochem - Good example there.
select id, name, email
from users
where name like '%#form.name#%'
and active=1
Where the active is a bit column... Would it be necessary to queryparam the active bit, since it isn't really dynamic? (i.e. the form.name is likely to change for every user of the website, but the active bit might not change...)
If active was always (or most often) = 1, you would not want to use queryparam for it.
If you use SQL Server, start SQL Profiler - New Trace to see what is happening under the hood.
The SQL server is caching the query as a stored procedure. Items that are in queryparams are dynamically passed in and evaluated on every query. The other items, such as active = 1, would be built-in to the procedure.
For example, the query in CF written as:
select id, name, email
from users
where name like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.name#%">
and active=1
would actually be passed to the sql server as:
exec sp_execute 1, 'Smith'
If you added queryparam to the active bit, it would called like this:
exec sp_execute 1, 'Smith',1
That is why there is a performance gain using queryparams (and why it is slower on first run - or if the query is not used often)
<cfqueryparam value="#trim(form.name)#" cfsqltype="CF_SQL_VARCHAR" null="#YesNoFormat(Len(Trim(FORM.name)))#">
(If form.name has a length, null should be "no")...
<cfqueryparam cfsqltype="cf_sql_integer" value="#form.categorylist#" list="true" separator=",">
Select * from tablename
order by #fieldname#
Since you can't put fieldname inside a cfqueryparam, currently I've only come up with testing #fieldname# inside if or case/switch statements but with a lot of fieldnames, it gets ugly.
A simple version of this query would look like this:
SELECT DescribeName, RETAIL, Longdescribe
From Table
Where longdecribe like "%kids%" or longdescribe like "%toy%" or longdescribe like "%8%" or longdescribe like "%years%" or longdescribe like "%old%"
This query would be from a customer search where they typed in "Kids toy 8 years" Then the query would return the the records with these words in them. I have the customer's search in a list. How would you convert this where statement to using the cfqueryparam tag?
Thanks for any help.
cfloop index="word" list="#form.something#"
Thanks