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
Archived Comments
Ray - It might be worth mentioning that you should use <cfqueryparam> for dynamic data when inserting and updating data.
Big DUH there. Thanks Scott. I updated the main quote. Is it clear now? Should I add an update/insert example as well?
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.
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.
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.
Thanks for the example, I didn't realize you could use cfqueryparam with a list like that - cool!
lists, not having to remember adding quotes to my queries and it handling quotes in the data sold me on using queryparams.
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...)
Ben spoke to this, and while he said he wasn't 100% sure (right Ben?) I belive he is right as well.
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.
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)
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)))#">
<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")...
Hey, this is a test.
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=",">
Comma is the default.
Thanks for this post Ray. This helps.
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?
Well, I think it is just priorities. Maybe wait and see what CF8 offers. :)
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.
VC - That's actually what I do normally.
OK, thanks Ray!
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.
Just cfloop over the value like a list.
cfloop index="word" list="#form.something#"
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
Has anyone discovered a more eloquent way to handle values passed to the 'Order By' clause?
Jason, I'm not sure you can, outside of manually checking the values against a valid list. Which isn't too hard of course.
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.
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.
That's the ticket! Thanks Ray. :)
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>
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.
What we've done to protect against SQL injection is to perform the initial query with query params:
<cfquery name="qryName" datasource="#variables.dsn#">
SELECT
fieldName
FROM tableName
WHERE
fieldName2 = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.field#">
</cfquery>
And then we do a query of queries with the Order By clause using CF substitution:
<cfquery name="qryOrdered" dbtype="query">
SELECT
fieldName
FROM qryName
ORDER BY #form.orderBy#
</cfquery>
By doing this we remove the possibility of performing any illicit activity against the database since the only time we used non-paramaterized values is against a ColdFusion query result set which has no direct connection to the database. We haven't seen any adverse performance since the source is already in memory.
Hey, Ray. I know this blog post is ancient but I didn't see too many newer cfqueryparam related posts (some sort of datetime filter in your search would be nice even if only something simple like: within the last day, week, month, year,...)
Anyway, my company was seriously humbled on the security end of things at the 2013 ColdFusion Summit and I have a question about cfqueryparam. We now have cfqueryparam implemented across our code but there are certain portions of our code where we were building the where clause in a local variable and then plugging that local variable into the cfquery:
SELECT *
FROM table
#local.where#
I've reached the conclusion that I must place the where conditions into my cfquery tag rather than building it out in a local variable. But there is one particular part of my code where I build out a where clause in a function and then pass it into several other functions. I do this because the where clause is the same among many other functions and I wanted to prevent duplicate code. Is there any solution to this problem or am I stuck with transferring my where clause to each function?
You can use a case statement if the number of possible options is manageable.
You can break it into tablename and whereclause criteria, validate the tablename against a list of known tablenames like my post above, and then do a query of queries against that result set as in Kevin's post above, if the result set without filtering isn't too terrible and the filtering is all stuff that a query of query will allow.
You can parse out the string and split it into the parts you know are safe and include those without cfqueryparam (iif(myRowName is "foo=",de("foo="),de("")) or whatever) and wrap the user entered data in cfqueryparam.
Probably some other options, but those are the ones I thought of when I got the notification email. Maybe Ray will have a better one. :)
I don't have much better to offer on top of what JC said. I'd lean towards the CASE block to help possibly manage it and see if that would also let you switch to cfqueryparam. I don't think I'd go the QoQ route though.
Solid advice, guys. Thank you. My concern wasn't so much in how I would put it into the cfquery but finding out if there is a way I could build the where clause once and insert it into each query while still using cfqueryparam. I don't like the idea of having to duplicate the where clause in each function but if that is what is necessary for the sake of security then I'll suck it up and do it haha.
Thanks again.
It would be possible - just difficult I suppose depending on how complex your code is for creating the where clause.
Currently I am building the where clause in one function and then passing it into other functions like so:
<cffunction ...>
<cfset where = "" />
<cfif filter1 neq 0>
<cfset where = where & " AND column_name = filter1" />
</cfif>
<cfif filter2 neq 0>
<cfset where = where & " AND ..." />
</cfif>
...
<return where />
</cffunction>
<cffunction ...>
<cfargument name="where" />
<cfquery name="myQuery">
SELECT *
FROM table_name
#where#
</cfquery>
<return myQuery />
</cffunction>
Hmm. I wonder if you could put the CFQueryParam code in the function and then call the function from inside the query… or change it to a custom tag, or just a CFINCLUDE. I'm 99% sure doing a cfinclude would work… then you'd just put the cfqueryparam around any of the user-entered data. Much easier than having to maintain the code in every single query.
Cody, Did you ever find a solution to your problem above from 10-31-2013? I ran across the same issue in a very complex query.