ON-THE-FLY FILTERS

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 ?

Have you tried the following? SELECT tblY.fldX FROM tblY #PreserveSingleQuotes(SESSION.F)#; ----- Excess quoted text cut - see Original Post for more ----- ?

Thanks! First time I've ever had to use that one. Have you tried the following? SELECT tblY.fldX FROM tblY #PreserveSingleQuotes(SESSION.F)#; ----- Excess quoted text cut - see Original Post for more ----- ?

Glad I could help! I remember pulling my hair out a while ago before I figured that one out. Another trick you can use: <cfset SESSION.F = "WHERE tblY.fldA='#FORM.Result#'"> instead of <cfset SESSION.F = "WHERE tblY.fldA='" & FORM.Result & "'"> It's mainly just a matter of preference, but I've found the first way easier to debug when there are a lot of text and variables being concatenated, especially if creating URIs with lots of &s. >Thanks! First time I've ever had to use that one. > > >Have you tried the following? >SELECT tblY.fldX >FROM tblY >#PreserveSingleQuotes(SESSION.F)#;

I cannot think of a single use case for preserveSingleQuotes() on data provided by the client. That is extremely dangerous, as you are exposing your database to all kinds of delightful attacks. I would urge you to find a solution that utilizes <cfqueryparam /> for that stuff, but at the very least you *must* sanitize user input before passing it off to a query - especially if you are using preserveSingleQuotes()! HTH ----- Excess quoted text cut - see Original Post for more -----

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: ----- Excess quoted text cut - see Original Post for more -----

+1 On Thu, Jun 28, 2012 at 11:36 AM, Carl Von Stetten <vonner.lists@vonner.net>wrote: ----- Excess quoted text cut - see Original Post for more -----

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. 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: ----- Excess quoted text cut - see Original Post for more -----