Posted in ColdFusion | Posted on 07-10-2009 | 1,818 views
Doug asks:
Hey Ray, do you think sometime you could discuss using cfqueryparam versus the need to have "elegant" ways of handling bad data? It seems like a better solution would be to redirect the user back to a default page than to have a page crash because of bad data or a hacking attempt.
I guess my point is: isn't this code:
<cfif isNumeric(url.this)> <cfquery name="theQuery" datasource="ds"> SELECT this, that FROM theTable WHERE this = url.this </cfquery> <cfelse> <cflocation url="/SomePlaceSafe.cfm"> </cfif>
more elegant than this code:
<cfquery name="theQuery" datasource="#ds#"> SELECT this, that FROM theTable WHERE this = <cfqueryparam value="url.this" cfsqltype="cf_sql_integer" /> </cfquery>I've seen you complain about this before in your Twitter feed, so I figured you had an opinion about it. (And for the sake of argument let's ignore claims that cfqueryparam actually improves performance with certain databases.)
Interesting question, Doug. I'll try my best to answer it. First off, I think you make a mistake when you say that 'elegant' handling of the error is impossible with cfqueryparam. It certainly isn't. In fact, one could simply take your code examples and merge them:
2<cfquery
3name="theQuery" datasource="#ds#">
4SELECT this, that FROM theTable WHERE this =
5<cfqueryparam value="#url.this#" cfsqltype="cf_sql_integer" />
6</cfquery>
7<cfelse>
8<cflocation
9url="/SomePlaceSafe.cfm">
10</cfif>
And in fact, this is actually exactly what I do. Well, normally I'm writing in Model-Glue and instead do the validation at the controller layer and the query in the model layer, but I think you get my point. You certainly can actually double up on the validation here. It may be overkill, but when it comes to URL parameters and other things coming in from the remote client, you can't be too careful. (We all learned this last week with the FCK Editor issue, right?) Also note that both of those checks in the code above even aren't truly enough. 3.14159 is a numeric value, right? But I'm pretty sure that will either fail in the cfqueryparam or change to 3. -3 is also an integer value, but if you are using autonumber keys then you will not have any negative values. I'll often do something like this:
And if you want to get truly evil, you could also check for values greater than MAXINT (the largest number the underlying Java code can handle before flipping over).
So where exactly to use validation is a big issue. As I said, I'm normally going to do it in the controller. I've done it in beans before as well. Frankly, I'm still learning what works best for me, but the main point is, you can get both the security/performance benefit of cfqueryparam and still gracefully handle input validation yourself as well. Shoot, even if you are lazy you could get by with an onError in your Application.cfc and notice all errors thrown by queryparam and log them as (possible) url hack attempts before pushing the user to the safe place.
I am a huge proponent of cfqueryparam only in this scenario. I believe the best approach here is to try catch the query and either bubble or handle accordingly with the cflocation. I wrap all 3rd party calls; db, file, com, etc with try catch as they are ultimately outside the the cf engine and can produce unexpected results (for instance if the db is down or unstable).
It is also much less code and validation is really an after thought. I believe the input (text box in html or flex) should be validated client side before ever going to the server as well.
Hope this code comes out in the comment:
<cftry>
<cfquery name="theQuery" datasource="#ds#">
SELECT this,
that
FROM theTable
WHERE this = <cfqueryparam value="#url.this#" cfsqltype="cf_sql_integer" />
</cfquery>
<cfcatch type="database">
<!--- Either bubble with cferror and pass on to global handler, i.e. onError in app.cfc
or just place your cflocation in the catch (used in this example --->
<cflocation url="/SomePlaceSafe.cfm">
</cfcatch>
</cftry>
And hopefully everyone is using the jQuery validation plugin :)
1.) populate my object with data
2.) validate
3.) if object has errors display them
else save
Jim, if you're subscribing to this post, what's the "jQuery validation plugin"..?
Check out Jorn's plugin here: http://bassistance.de/jquery-plugins/jquery-plugin...
Oh, absolutely. If the exception message gives details about the database system, table names, column names, etc, this is valuable information for further attacks.
Defense in depth should be the order of the day. A nice looking default error message is good, but preventing information leakage is better. User input is pure evil until you prove otherwise.
My approach has changed quite a bit and I find myself often using cfajaxproxy along with front end javascript validation a lot more than serverside error handling techniques. I'm consistent in using cfqueryparam on the back end but trapping crap data up front and stopping the process before the submit even happens is really working out for me.
It is a lot of extra work but using cfajaxproxy to check for primary key violations in instances where the user names a key is helpful (subsidiary tables that employ meaningful user defined codes rather than manufactured primary key values to make records unique). For instance, nobody wants a unit of measure code named 2, they want lbs or units.
Just my opinion.
Thanks for the tips Ray! (And I've gotta put my fear aside and start dabbling in ModelGlue. Time to read your next blog post...)
Take a look here: http://www.martinfowler.com/bliki/AnemicDomainMode...
Though, I'm concerned about some of the responses here. Who says the db throws a native error in my scenario @Dave Crawford? It never gets to the db if the cfqueryparam fails.
As for custom error handling that is a given. I eluded to bubble to a global handler; CF 101 or app dev in general, never show or present native errors to the user. I usually roll my own error handler .cfc which handles the error per the given process (make the error as pretty as you like here) i.e. Send an email, custom log with cflog, etc.
There you can present a more intelligent response to the user so that customer support can do their job effectively. This kind of information may go as far as including template, line number, and query name info. Alot of flexibility here or just cflocation afterward per Doug's initial example.
As for the validation discussion, whether you incorporate that client or server side is personal choice. I don't like taxing the server if I can do it client side (remember the old cfform js trick?). This goes years back, but I would have the server gen the js for me and switch back to html form. Many advances with jQuery here agreeed... I have worked on projects where it is done both client and server. I personally have no issue with implementing in the Bean per @Martin's response.
Ultimately I don't want to rely on CFQUERYPARAM to catch data type failures, it's the last line of defense for me. I'll cover every variable in a query, but I'm more interested in the performance boosts CFQUERYPARAM provides. The added security is an extra benefit to me, since I should have covered the security needs beforehand.
[Add Comment] [Subscribe to Comments]