Thanks for the advice (and to Dave). The values (form) are on a separate page which is why I'm using the session variable) and to preserve the value in case of a page reload (e.g., adding another item which I could do via Ajax but it's a long story). I've buit WHERE and ORDER BY clauses by just supplying the variables (as suggested here) in the past but for some reason just went another direction this time. The queryparam is certainly a good idea, although this app is secured behind ADS plus a login table so the audience is just a handful of people in my office. If they can break in through ADS then we (the university) have some big problems. I do need to make better use of queryparam in my public apps, though.
-----Original Message----- From: Carl Von Stetten [mailto:[email protected]] Sent: Thursday, June 28, 2012 12:37 PM To: cf-talk Subject: Re: ON-THE-FLY FILTERS Larry, Might I suggest a little bit different approach? Instead of storing the whole WHERE clause in a session variable, store only the string you need to use for filtering. In your session definition code (likely onSessionStart() in Application.cfc): <cfset session.F = ""> Then modify your query: SELECT tblY.fldX FROM tblY WHERE 1=1 <cfif len(trim(session.F))> AND tbly.fldA = <cfqueryparam value="#session.F#" cfsqltype="CF_SQL_VARCHAR"> </cfif> This protects your SQL database from SQL injection (as Matt Q. alluded to). If you haven't changed session.F from the initial blank string, the query will return all records. If you update session.F to contain an actual value, then that string will be used to filter the records. HTH, Carl On 6/28/2012 8:29 AM, Stephens, Larry V wrote: > CF8 > > SELECT tblY.fldX > FROM tblY > WHERE tblY.fldA='something'; > > That works okay. But I want to set the WHERE clause on-the-fly. So the > user selects the filter (via a form) and the result is stored as > <cfset SESSION.F = "WHERE tblY.fldA='" & FORM.Result & "'"> (where > FORM.Result = something) > > Do a cfoutput to check SESSION.F and that shows WHERE tblY.fldA='something' > All is well. > > So, my code is > > SELECT tblY.fldX > FROM tblY > #SESSION.F#; > > The problem is, it now displays as > > SELECT tblY.fldX > FROM tblY > WHERE tblY.fldA="something"; > > And that throws an error. Why does SESSION.F display correctly in the > cfoutput but CF8 changes the ' to " when I use it in cfquery ? > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:351759 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
