You can use cfqueryparam mixed with other sql, as you see fit. It does
have one potential drawback though: the cfquery feature "cachedwithin"
will not work if you use cfqueryparam. If that's not a problem then it
is usually a drop-in replacement for a variable or expression.
Mark
-----Original Message-----
From: Jason Ruthenberg [mailto:[EMAIL PROTECTED]
Sent: Friday, September 22, 2006 2:10 PM
To: CF-Talk
Subject: RE: Single Quotes Problem
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).
Mark
-----Original Message-----
From: Ben Forta [mailto:[EMAIL PROTECTED]
Sent: Friday, September 22, 2006 12:24 PM
To: CF-Talk
Subject: RE: Single Quotes Problem
Use PreserveSingleQuotes()
--- Ben
-----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
etCl
ient.DBA#
^#qGetClient.ADDR1#^#qGetClient.CITY#^#qGetClient.STATE#^#qGetClient.ZIP
CODE
#^#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
etCl
ient.DBA#
^#qGetClient.ADDR1#^#qGetClient.CITY#^#qGetClient.STATE#^#qGetClient.ZIP
CODE
#^#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:253922
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
