Raymond Camden's ColdFusion Blog: Simple guide to switching to CFQUERYPARAM

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:

view plain print about

<cfquery name="searchUsers" datasource="data">
select id, name, email
from   users
where  name like '%#form.name#%'
</cfquery>

1  <cfquery name="searchUsers" datasource="data">
2  select id, name, email
3  from users
4  where name like '%#form.name#%'
5  </cfquery>

Now here is the same query switched to cfqueryparam:

view plain print about

<cfquery name="searchUsers" datasource="data">
select id, name, email
from   users
where  name like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.name#%">
</cfquery>

1  <cfquery name="searchUsers" datasource="data">
2  select id, name, email
3  from users
4  where name like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.name#%">
5  </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:

view plain print about

<cfquery name="searchUsers" datasource="data">
select id, name, email
from   users
where  usertype in (#form.categorylist#)
</cfquery>

1  <cfquery name="searchUsers" datasource="data">
2  select id, name, email
3  from users
4  where usertype in (#form.categorylist#)
5  </cfquery>

This can be changed to cfqueryparam like so:

view plain print about

<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>

1  <cfquery name="searchUsers" datasource="data">
2  select id, name, email
3  from users
4  where usertype in (<cfqueryparam cfsqltype="cf_sql_integer" value="#form.categorylist#" list="true">)
5  </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.

view plain print about

<cfquery name="getSomething" datasource="data">
select  #somecol#
from    #sometable#
where   x = 1

1  <cfquery name="getSomething" datasource="data">
2  select #somecol#
3  from #sometable#
4  where x = 1

This entry was posted on Sunday, February 18, 2007 at 12:25 PM. It was filed in the following categories: ColdFusion. It has been viewed 4,064 times and has 32 comments.

TweetBacks

Comments

[Add Comment] [Subscribe to Comments]

Comment 1 written by Scott Stroz on 18 February 2007, at 1:00 PM

Ray - It might be worth mentioning that you should use <cfqueryparam> for dynamic data when inserting and updating data.
Comment 2 written by Raymond Camden on 18 February 2007, at 1:09 PM

Big DUH there. Thanks Scott. I updated the main quote. Is it clear now? Should I add an update/insert example as well?
Comment 3 written by Ben Nadel on 18 February 2007, at 3:28 PM

I cannot remember where I saw it, but I read a blog post a few months back that would argue that the last example above would in fact be a candidate for cfqueryparam:

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.
Comment 4 written by Jochem van Dieten on 18 February 2007, at 4:12 PM

You can use cfqueryparam outside the WHERE too. Consider for instance:
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.
Comment 5 written by Raymond Camden on 18 February 2007, at 4:19 PM

Ben - while that may be true - I was using it as an example to tell folks they can't use cfqp to replace dynamic portions OUTSIDE of where/set/update.

Jochem - Good example there.
Comment 6 written by Rachel Maxim on 18 February 2007, at 4:32 PM

Thanks for the example, I didn't realize you could use cfqueryparam with a list like that - cool!
Comment 7 written by Scott P on 18 February 2007, at 5:26 PM

lists, not having to remember adding quotes to my queries and it handling quotes in the data sold me on using queryparams.
Comment 8 written by Geoff on 18 February 2007, at 5:58 PM

I always wondered about:

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...)
Comment 9 written by Raymond Camden on 18 February 2007, at 6:06 PM

Ben spoke to this, and while he said he wasn't 100% sure (right Ben?) I belive he is right as well.
Comment 10 written by Ben Nadel on 18 February 2007, at 6:54 PM

Yeah, I am not 100% sure. However, from what I can remember, it was not necessary to have all the "static" values query-paramed. So, in the above, the FORM.name value would be good as a cfqueryparam. Then, the active bit could stay as is. The point of the blog post was to show the difference between a SQL statement that had no query params and a SQL statement that had at least one. The one that had at least one SQL statement performed better.
Comment 11 written by Scott P on 18 February 2007, at 7:03 PM

Correct me if I'm wrong but this is true in my testing.

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)
Comment 12 written by Jim on 18 February 2007, at 9:59 PM

There is also the null value hack - can't remember where I saw this but it's neat:

<cfqueryparam value="#trim(form.name)#" cfsqltype="CF_SQL_VARCHAR" null="#YesNoFormat(Len(Trim(FORM.name)))#">
Comment 13 written by Geoff on 19 February 2007, at 4:07 AM

<cfqueryparam value="#trim(form.name)#" cfsqltype="CF_SQL_VARCHAR" null="#YesNoFormat(not Len(Trim(FORM.name)))#">

(If form.name has a length, null should be "no")...
Comment 14 written by Testing on 19 February 2007, at 7:46 AM

Hey, this is a test.
Comment 15 written by James, F.E. on 19 February 2007, at 9:36 AM

When you are using cfqueryparam for a list, isn't the separator attribute required? So it would look something like this:

<cfqueryparam cfsqltype="cf_sql_integer" value="#form.categorylist#" list="true" separator=",">
Comment 16 written by Raymond Camden on 19 February 2007, at 9:39 AM

Comma is the default.
Comment 17 written by Robert Owen on 19 February 2007, at 4:03 PM

Thanks for this post Ray. This helps.
Comment 18 written by johnb on 21 February 2007, at 4:02 AM

With regards to the whole CFQUERYPARAM and Caching etc etc, I've been wondering recently why CF doesn't support the two together yet BlueDragon does? How do they get round it yet CF doesn't support them together?
Comment 19 written by Raymond Camden on 21 February 2007, at 5:05 AM

Well, I think it is just priorities. Maybe wait and see what CF8 offers. :)
Comment 20 written by Vincent Collins on 17 September 2007, at 3:29 PM

What's the most elegant way to protect the following from SQL Injection?

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.
Comment 21 written by Raymond Camden on 17 September 2007, at 3:54 PM

VC - That's actually what I do normally.
Comment 22 written by Vincent Collins on 17 September 2007, at 4:13 PM

OK, thanks Ray!
Comment 23 written by Eric Bader on 21 October 2007, at 6:02 PM

I have one query that is killing me with the cfqueryparam tag. The query is a search query for products that contain words a customer is searching on. The column to be searched is a text column, so the cfqueryparam needs to be: CF_SQL_VARCHAR.
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.
Comment 24 written by Raymond Camden on 22 October 2007, at 1:25 PM

Just cfloop over the value like a list.

cfloop index="word" list="#form.something#"
Comment 25 written by Lance VL on 25 January 2008, at 2:18 PM

I saw how you and Vincent Collins were stating that you use case/switch or if statements for testing order by parameters. I was wondering if you had an example of this. Are you just testing for data type, and business logic or are you also testing for injections?

Thanks
Comment 26 written by Jason on 25 July 2008, at 9:59 AM

Has anyone discovered a more eloquent way to handle values passed to the 'Order By' clause?
Comment 27 written by Raymond Camden on 29 July 2008, at 8:54 AM

Jason, I'm not sure you can, outside of manually checking the values against a valid list. Which isn't too hard of course.
Comment 28 written by Jason on 29 July 2008, at 9:15 AM

Of course, I'm a gnat amongst giants and am still learning the ways of CF.

It seems that checking against a valid list adds quite a bit of extra markup to protected the Order By clause. Naturally, my head might not be screwed on straight. :)

For example, lets say you allow the list to be sorted by more than one column. You'll have to designate all of the potential columns the user can sort by and then loop through that list for each value passed in for sorting.

Would perhaps, a more eloquent solution be to use a function to strip out SQL buzz words (update/drop/create/whatever)? Should new vulnerabilities be discovered, making a change to that function would be swift and simple.
Comment 29 written by Raymond Camden on 29 July 2008, at 9:19 AM

Well, if you have a model CFC, for lets say Products, you can say that the valid sort by options are a specific list (name, price, quantity). You can definite these in the Variables scope of the CFC, and build a private validSort() function. You could have validSort() simply return adefault (name) if an invalid sort is passed.
Comment 30 written by Jason on 29 July 2008, at 9:29 AM

That's the ticket! Thanks Ray. :)
Comment 31 written by steve on 15 August 2008, at 11:31 AM

Here's an example of how I've done ORDER BY
Like Ray said, You should pre determine what columns are valid sorting columns, and then I use 1 parameter pOrderBy to determine my actualy Dynamic order by statement...
that way the only thing that can be passed into my query are values that I have pre-determined.

<cfelseif pOrderBy EQ "Contact">
   <cfset OrderByLine = "ORDER BY Contact #OrderByAscDesc#">
<cfelseif pOrderBy EQ "City">
   <cfset OrderByLine = "ORDER BY City #OrderByAscDesc#">
<cfelseif pOrderBy EQ "State">
   <cfset OrderByLine = "ORDER BY State #OrderByAscDesc#">
<cfelse>
   <cfset OrderByLine = "ORDER BY Company #OrderByAscDesc#">
</cfif>

<CFQUERY ...>
   SELECT someCols....
   From CompanyAddresses
   #OrderByLine#
</CFQUERY>
Comment 32 written by JC on 25 March 2009, at 12:43 PM

thread necromancy, I know... but I ran across this searching for how the hell to use sql user defined functions with cfqueryparam (anyone know??) and saw the bit about order by. A safe, agnostic way to do it would be something like...

<cfdbinfo
   datasource="#myDSN#"
   name="tblinfo"
   type="columns"
   dbname="#myDB#"
   table="#myTable#">
<cfset cols = listtoarray(lcase(valuelist(tblinfo.column_name)))>
<cfset index = cols.indexOf(lcase(orderby))>
<cfif index LT 0>
   <cfset index = 1>

ORDER BY #cols[index]#

that may need some tweaking to run, I typed up the last bit by hand instead of copying it from working code... but there's a built-in method in CF to get the column names, so you can easily grab those and test your input against them.

That could be much simpler, of course... just a listcontains or something to test if it's kosher and then using it... but we have anal auditors and it's easier to write a bit of extra code and not allow the user input.