Mark, thanks for the info! I was looking at some sites online this afternoon that were saying the same. Trouble is, I am not too familiar with cfqueryparam, so I am wondering, can I use this just for this situation, or do have to use it throughout my code?
Jason -----Original Message----- From: Gaulin, Mark [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 12:50 PM To: CF-Talk Subject: RE: Single Quotes Problem Actually, Ben, PreserveSingleQuotes will not always work right. It's a known bug (for a long time), but apparently not one that is going to be fixed. The problem has something to do with the order that cfquery and PreserveSingleQuotes operate with respect to things like Left()... You can have a perfectly good string that has single quotes, but try to Left() it and cfquery's auto-doubled single quotes can get split off, giving you bad SQL as a result. If you want to use PreserveSingleQuotes then you had better be 100% sure that your string has all single quotes properly doubled, but there are even cases where that can get broken... something like calling a function from inside cfquery... I don't quite recall. The best work-around we were able to come with is to always use cfqueryparam, which sucks because it disables query caching (which I have heard will be addressed in the next version). -----Original Message----- From: Jason Ruthenberg [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 12:13 PM To: CF-Talk Subject: Single Quotes Problem I have a problem with some data that contains single quotes that I cannot figure out how to get past. In a nutshell, here is what I have happening. I have a web page where the user searches for a customer. When the run the search, one of the queries I run is the following: <cfquery name="qGetClient" datasource="#dbsource#"> SELECT CustNo, CustId, LastName, ISNULL(DBA,' ') DBA, Addr1, ISNULL(Addr2,' ') Addr2, City, State, ZipCode, BrokerCode FROM CX_CUSTOMER_VIEW WHERE SUBSTRING(LastName,1,#STRING_LEN#) = '#TRIM(Search_Name)#' OR (SUBSTRING(DBA,1,#STRING_LEN#) = '#TRIM(Search_Name)#' AND DBA IS NOT NULL) </cfquery> The website then displays the search results and gives the option to create a new policy for the customer or a renewal policy for the customer: <TD align="right"> <INPUT TYPE="radio" NAME="CLIENT" VALUE="#qGetClient.CUSTNO#^#qGetClient.CUSTID#^#qGetClient.LASTNAME#^#qG etClient.DBA#^#qGetClient.ADDR1#^#qGetClient.CITY#^#qGetClient.STATE#^#q GetClient.ZIPCODE#^#qGetClient.ADDR2# ^#qGetClient.BrokerCode#^NEW" <cfif qGetClient.currentrow eq 1>checked</cfif>> </TD> <td align="right"> <INPUT TYPE="radio" NAME="CLIENT" VALUE="#qGetClient.CUSTNO#^#qGetClient.CUSTID#^#qGetClient.LASTNAME#^#qG etClient.DBA#^#qGetClient.ADDR1#^#qGetClient.CITY#^#qGetClient.STATE#^#q GetClient.ZIPCODE#^#qGetClient.ADDR2# ^#qGetClient.BrokerCode#^RENEWAL"></td> When they select either of the radio buttons, the website pulls the customers info into the next screen. The problem comes in here: <CFSET SESSION.CX_BROKER_CODE = LISTGETAT(ATTRIBUTES.AMS_CLIENT,10,"^")> The #qGetClient.BrokerCode# value, which is the 10th value, contains a ' in it, (the value is actually a 3 digit code, and for this particular one it is !'8). So, when the user clicks submit, they recieve the following error: Invalid list index 10. In function ListGetAt(list, index [, delimiters]), the value of index, 10, is not a valid as the first argument (this list has 9 elements). Valid indexes are in the range 1 through the number of elements in the list. I have tried placing the 'PreserveSingleQuotes' wording in every place I can think, but this does not work. From my research on the web, I have come to understand that 'PreserveSingleQuotes' will not work when you are calling variables from another program. So, to make a short story long, does anyone know how I can resolve this error? Any and all help is greatly appreciated. Thanks! Jayruth44 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:253886 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
