[prev in list] [next in list] [prev in thread] [next in thread]
List: mysql-win32
Subject: RE: Handling data with that pesky ampersand in a WHERE clause
From: "Dave Long" <dave () northgoods ! com>
Date: 2007-04-18 19:54:58
Message-ID: !~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAAqsO44Esa3UW6tA39Emwi8kKMAAAQAAAAQfI5U/sGs0uaXPoNdtPgDgEAAAAA () northgoods ! com
[Download RAW message or body]
That did it!
Randy, you're a champ.
Thanks.
Dave
-----Original Message-----
From: Randy Clamons [mailto:randy@novaspace.com]
Sent: Wednesday, April 18, 2007 1:15 PM
To: Dave Long
Cc: win32@lists.mysql.com
Subject: 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
randy@novaspace.com
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:dijital@shaw.ca]
> Sent: Tuesday, April 17, 2007 10:38 PM
> To: win32@lists.mysql.com
> 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:dave@northgoods.com]
>> Sent: Wednesday, 18 April 2007 11:05 AM
>> To: win32@lists.mysql.com
>> 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.
>>
>>
>>
>>
>
>
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=mysql-win32@progressive-comp.com
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic