RE: double single quotes in cfc cfquery

[Schreck, Tom]

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: 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