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:351757
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
