Re: Handling data with that pesky ampersand in a WHERE clause

Dave,

The ampersand needs to be URL encoded--as should all of your data that 
gets sent as part of a URL-- to prevent this kind problem. Further, data 
that will be displayed as html should be html encoded.

There are functions to accomplish this. Look at: 
http://livedocs.adobe.com/coldfusion/6.1/htmldocs/funca114.htm#wp1114140 
for URLEncodedFormat(string [,charset])
and
http://livedocs.adobe.com/coldfusion/6.1/htmldocs/funca115.htm#wp1105634 
for HTMLCodeFormat(string [,version])

It's pretty simple.

Randy Clamons
Systems Programming
[email protected]



Dave Long wrote:
> Thanks for the help.
>
> I'm sure you are correct, Armando... I was just being lazy.
>
> Being lazy, John, I tried escaping the ampersand with the backslash first
> but that didn't work because the "\" was only present until the form action
> which inserted the record was completed.
>
> Actually, I was trying to allow my client to be lazy by providing a
> "one-step" process for dynamic creation of new categories. Armando, your
> solution isn't really that much more work for him though, because once he
> has entered a category in a separate "category" table, he won't have to type
> it again, just select it from a dynamic select box or drop-down list.
> However, the drop-down is likely to become bulky and clumsy as the list of
> categories grows beyond, oh, say 15 or more.
>
> Perhaps I'll create the table and see how it looks and feels, then wait for
> feedback from the client.
>
> Thanks again!!
>
> Dave
>
>
>
> -----Original Message-----
> From: Armando [mailto:[email protected]] 
> Sent: Tuesday, April 17, 2007 10:38 PM
> To: [email protected]
> Subject: Re: Handling data with that pesky ampersand in a WHERE clause
>
> You shouldn't use a textual description to do what you are trying to 
> achieve if possible. Invariably, at some point (such as now) the text 
> will give you a ton of headaches because it will contain characters that 
> shouldn't go into a URL, especially the ampersand because that is used 
> as a delimiter to separate multiple variables you are passing from one 
> page to another through the URL (ie: 
> http://somesite.com?var1=value&var2=value&var3=value)
>
> Your gallery_category page query is returning 0 results because the 
> RecordID variable in the URL is truncated from "T & C Bar" to just "T " 
> (or "T%20" as the %20 represents a space) because the #URL.RecordID# 
> grabs everything in the URL after "RecordID=" and before the next 
> ampersand, which of course is the delimiter.
>
> What I would recommend instead is to give each of your categories a 
> unique ID with no scharacters at all (an autoincrementing integer field 
> usually works fine for these purposes) then use THAT as the variable to 
> pass instead of the actual category text description. Cheers.
>
> Armando
>
> Bonnett, John wrote:
>   
>> I don't understand why, but perhaps your need to put a '\' before the &
>> before including it in the query. I know you would need to do that if
>> the category contained a single quote like "O'Hara". There are a few
>> other characters that need to be escaped in sting literals for MySQL but
>> I didn't think & was one of them.
>>
>> John Bonnett
>>
>> -----Original Message-----
>> From: Dave Long [mailto:[email protected]] 
>> Sent: Wednesday, 18 April 2007 11:05 AM
>> To: [email protected]
>> Subject: Handling data with that pesky ampersand in a WHERE clause
>>
>> Wanting to sort a collection of images by category in a ColdFusion page,
>> I set the ID_Field to the category field as shown below:
>>
>> 	<CFQUERY name="GetRecord" dataSource="xxxxxx">
>> 		SELECT DISTINCT Category, Category AS ID_Field
>> 		FROM gallery
>> 		WHERE gallery.Active = 1
>> 		ORDER BY gallery.Category
>> 	</CFQUERY>
>>
>> and passed the appropriate RecordID on to the next page:
>>
>> 	<a
>> href="gallery_category.cfm?RecordID=#ID_Field#">#Category#</a>
>>
>> On the gallery_category page, I filter the records with this query:
>>
>> 	<CFQUERY name="GetRecord" dataSource="xxxxxx">
>> 		SELECT gallery.GalleryID AS ViewField1,
>> gallery.ImageFile AS ViewField2, gallery.Caption AS ViewField3,
>> gallery.Rank AS ViewField4, gallery.Active AS ViewField5,
>> gallery.Category AS ViewField6, gallery.Title AS ViewField7,
>> gallery.GalleryID AS ID_Field
>> 		FROM gallery
>> 		WHERE gallery.Category = '#URL.RecordID#' AND
>> gallery.Active = 1
>> 		ORDER BY gallery.Category, gallery.Rank
>> 	</CFQUERY>
>>
>> All is fine UNTIL... until the data entered in a record's category field
>> includes an ampersand "&". At that point the query returns 0 records.
>>
>> Example: one of the categories is named "T & C Bar". That is the name of
>> the bar, not "T and C Bar".
>>
>> Any suggestions as to how can I get around this problem?
>>
>> Dave Long
>> Web Design, Programming, & Hosting
>> http://www.northgoods.com
>>
>>
>>
>> --
>> This message has been scanned for viruses and dangerous content by
>> MailScanner, and is believed to be clean.
>>
>>
>>
>>     
>
>