RE: double single quotes in cfc cfquery

[Knipp, Eric]

 

    Have you tried using the PreserveSingleQuotes() function?  I think this helped me in the past when I had this problem.

 

    Eric

-----Original Message-----
From: Kelly, Joe [mailto:[EMAIL PROTECTED]
Sent: Monday, December 27, 2004 2:00 PM
To: list@dfwcfug.org
Subject: double single quotes in cfc cfquery

Has anybody run into this issue?  Is there a better resolution?  Or am I just doing this wrong?

 

Setting up variable query statements for a <cfquery> within a cfc,

when using variables containing strings,

the evaluated string ends up with double single quotes around it in when the <cfquery> is run.

 

There is a hot fix that addressed this issue:

http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_19108

and it is supposedly contained within the updater but neither fixes this issue.

 

Here's my example cfc with my workaround (notice the Replace()):

 

<cfcomponent displayName="GetAndSortData" hint="Accepts sort criteria, runs the query and returns the data sorted.">

            <cffunction name="GetAllData" displayName="Sort Criteria" access="public" returnType="query" output="false" roles="3000,3001">

                        <cfargument name="QueryOrderBy" type="string">              

           

                        <cfif IsUserInRole("3000")>

                                    <cfset SecurityQryString = "">

                        <cfelseif IsUserInRole("3001")>

                                    <cfset SecurityQryString = "AND Status = 2 and UserName = '#SESSION.InfoStruct.UserName#'))">

                        <cfelse>

                                    <cfset SecurityQryString = "AND 1 = 0">

                        </cfif>

                       

                        <cfquery name="qryGetAllData" datasource="mydatasource">

                                    SELECT *

                                    FROM   MyTable

                                    WHERE #Replace(SecurityQryString, "''", "'", "All")#

                                    ORDER BY         #QueryOrderBy#

                        </cfquery>

 

                        <cfreturn qryGetAllDocuments />                 

                       

            </cffunction>

</cfcomponent>

 

I can assure you that I tried many different combinations of ideas and this is the best I solution I have found.  Please let me know if you have encountered this and have found something better!

 

Thanks,

Joe Kelly

 

 

---

CONFIDENTIALITY NOTICE: The information contained in this e-mail and attached document(s) may contain confidential information that is intended only for the addressee(s). If you are not the intended recipient, you are hereby advised that any disclosure, copying, distribution or the taking of any action in reliance upon the information is prohibited. If you have received this e-mail in error, please immediately notify the sender and delete it from your system.