RE: double single quotes in cfc cfquery

Tue, 28 Dec 2004 09:44:18 -0800

This did not fix the issue but my cfc looks a lot better.  Thanks for the help!

Excellent point about separating CFC from the calling page!  Darn it, I knew that!

I had stripped out the dummy clause for the example, sorry.

The ‘var’ scoping led to some interesting reading.  That may make a good sub topic at Tom’s presentation, the difference between ‘var’ and ‘this’, with when and why.

I appreciate your help.  The code has gone into production now, so if a solution comes up, it’ll have to wait for the next version.  But it’ll come up again.  It happens whenever I set those query statements as a string containing a text variable in a cfc.

 

Thanks,

Joe Kelly

 

 

From: Dan Blackman [mailto:[EMAIL PROTECTED]
Sent: Monday, December 27, 2004 4:04 PM
To: [email protected]
Subject: RE: double single quotes in cfc cfquery

 

I have never run into this.  Strange.

 

I would pass that Username into that method.  Decouple your CFC from the calling page.  This CFC, now, requires there to be a session available to it.

 

Make the CFC independent of the calling page.

 

Also, notice how Tom coded with the “Dummy” where clause.  That should fix the issue.

 

Dan

 

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Schreck, Tom
Sent: Monday, December 27, 2004 2:18 PM
To: [email protected]
Subject: RE: double single quotes in cfc cfquery

 

Try this:

 

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

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

 

                                                                        <cfscript>

                                                                                    var qryGetAllData = "";

                                                                        </cfscript>

                                                                       

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

                                                                                    SELECT *

                                                                                    FROM   MyTable

                                                                                    WHERE 0=0

                                                                                                <cfif IsUserInRole("3001")>

                                                                                                            AND Status = 2 and UserName = '#SESSION.InfoStruct.UserName#'))

                                                                                                </cfif>

                             ORDER BY #QueryOrderBy#

                        </cfquery>

 

                        <cfreturn qryGetAllDocuments/>                 

            </cffunction>

 

Note: you should var all variables used within your <cffunction>, even query names.  If not, you can run into issues.

 

Thanks -

 

Tom Schreck

972-361-9943

From: Kelly, Joe [mailto:[EMAIL PROTECTED]
Sent: Monday, December 27, 2004 2:00 PM
To: [email protected]
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

 

 

Reply via email to