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:


SELECT DISTINCT Category, Category AS ID_Field
FROM gallery
WHERE gallery.Active =3D 1
ORDER BY gallery.Category


and passed the appropriate RecordID on to the next page:



On the gallery_category page, I filter the records with this query:


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 =3D '#URL.RecordID#' AND gallery.Active
=3D 1
ORDER BY gallery.Category, gallery.Rank


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



--=20
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=3Dgcdmw-win32@m.gmane.org

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

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]=20
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:


SELECT DISTINCT Category, Category AS ID_Field
FROM gallery
WHERE gallery.Active =3D 1
ORDER BY gallery.Category


and passed the appropriate RecordID on to the next page:

href=3D"gallery_category.cfm?RecordID=3D#ID_Field#">#Categor y#

On the gallery_category page, I filter the records with this query:


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 =3D '#URL.RecordID#' AND
gallery.Active =3D 1
ORDER BY gallery.Category, gallery.Rank


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.



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

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:
>
>
> SELECT DISTINCT Category, Category AS ID_Field
> FROM gallery
> WHERE gallery.Active = 1
> ORDER BY gallery.Category
>
>
> and passed the appropriate RecordID on to the next page:
>
> > href="gallery_category.cfm?RecordID=#ID_Field#">#Category#
>
> On the gallery_category page, I filter the records with this query:
>
>
> 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
>
>
> 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.
>
>
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

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

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]=20
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=20
achieve if possible. Invariably, at some point (such as now) the text=20
will give you a ton of headaches because it will contain characters that=20
shouldn't go into a URL, especially the ampersand because that is used=20
as a delimiter to separate multiple variables you are passing from one=20
page to another through the URL (ie:=20
http://somesite.com?var1=3Dvalue&var2=3Dvalue&var3=3Dvalue)

Your gallery_category page query is returning 0 results because the=20
RecordID variable in the URL is truncated from "T & C Bar" to just "T "=20
(or "T%20" as the %20 represents a space) because the #URL.RecordID#=20
grabs everything in the URL after "RecordID=3D" and before the next=20
ampersand, which of course is the delimiter.

What I would recommend instead is to give each of your categories a=20
unique ID with no scharacters at all (an autoincrementing integer field=20
usually works fine for these purposes) then use THAT as the variable to=20
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.
>=20
> John Bonnett
>=20
> -----Original Message-----
> From: Dave Long [mailto:dave@northgoods.com]=20
> Sent: Wednesday, 18 April 2007 11:05 AM
> To: win32@lists.mysql.com
> Subject: Handling data with that pesky ampersand in a WHERE clause
>=20
> 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:
>=20
>
> SELECT DISTINCT Category, Category AS ID_Field
> FROM gallery
> WHERE gallery.Active =3D 1
> ORDER BY gallery.Category
>
>=20
> and passed the appropriate RecordID on to the next page:
>=20
> > href=3D"gallery_category.cfm?RecordID=3D#ID_Field#">#Categor y#
>=20
> On the gallery_category page, I filter the records with this query:
>=20
>
> 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 =3D '#URL.RecordID#' AND
> gallery.Active =3D 1
> ORDER BY gallery.Category, gallery.Rank
>
>=20
> 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.
>=20
> Example: one of the categories is named "T & C Bar". That is the name of
> the bar, not "T and C Bar".
>=20
> Any suggestions as to how can I get around this problem?
>=20
> Dave Long
> Web Design, Programming, & Hosting
> http://www.northgoods.com
>=20
>=20
>=20
> --
> This message has been scanned for viruses and dangerous content by
> MailScanner, and is believed to be clean.
>=20
>=20
>=20

--=20
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Ddave@northgoods.com


--=20
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


--=20
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=3Dgcdmw-win32@m.gmane.org

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

--------------080303080003080001070806
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.h tm#wp1114140
for URLEncodedFormat(string [,charset])
and
http://livedocs.adobe.com/coldfusion/6.1/htmldocs/funca115.h tm#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:
>>
>>
>> SELECT DISTINCT Category, Category AS ID_Field
>> FROM gallery
>> WHERE gallery.Active = 1
>> ORDER BY gallery.Category
>>
>>
>> and passed the appropriate RecordID on to the next page:
>>
>> >> href="gallery_category.cfm?RecordID=#ID_Field#">#Category#
>>
>> On the gallery_category page, I filter the records with this query:
>>
>>
>> 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
>>
>>
>> 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.
>>
>>
>>
>>
>
>

--------------080303080003080001070806--

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

That did it!

Randy, you're a champ.

Thanks.

Dave

-----Original Message-----
From: Randy Clamons [mailto:randy@novaspace.com]=20
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=20
gets sent as part of a URL-- to prevent this kind problem. Further, data=20
that will be displayed as html should be html encoded.

There are functions to accomplish this. Look at:=20
http://livedocs.adobe.com/coldfusion/6.1/htmldocs/funca114.h tm#wp1114140=20
for URLEncodedFormat(string [,charset])
and
http://livedocs.adobe.com/coldfusion/6.1/htmldocs/funca115.h tm#wp1105634=20
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]=20
> 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=20
> achieve if possible. Invariably, at some point (such as now) the text=20
> will give you a ton of headaches because it will contain characters that=
=20
> shouldn't go into a URL, especially the ampersand because that is used=20
> as a delimiter to separate multiple variables you are passing from one=20
> page to another through the URL (ie:=20
> http://somesite.com?var1=3Dvalue&var2=3Dvalue&var3=3Dvalue)
>
> Your gallery_category page query is returning 0 results because the=20
> RecordID variable in the URL is truncated from "T & C Bar" to just "T "=
=20
> (or "T%20" as the %20 represents a space) because the #URL.RecordID#=20
> grabs everything in the URL after "RecordID=3D" and before the next=20
> ampersand, which of course is the delimiter.
>
> What I would recommend instead is to give each of your categories a=20
> unique ID with no scharacters at all (an autoincrementing integer field=
=20
> usually works fine for these purposes) then use THAT as the variable to=
=20
> pass instead of the actual category text description. Cheers.
>
> Armando
>
> Bonnett, John wrote:
> =20
>> 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]=20
>> 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:
>>
>>
>> SELECT DISTINCT Category, Category AS ID_Field
>> FROM gallery
>> WHERE gallery.Active =3D 1
>> ORDER BY gallery.Category
>>
>>
>> and passed the appropriate RecordID on to the next page:
>>
>> >> href=3D"gallery_category.cfm?RecordID=3D#ID_Field#">#Categor y#
>>
>> On the gallery_category page, I filter the records with this query:
>>
>>
>> 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 =3D '#URL.RecordID#' AND
>> gallery.Active =3D 1
>> ORDER BY gallery.Category, gallery.Rank
>>
>>
>> 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.
>>
>>
>>
>> =20
>
> =20

--=20
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.



--=20
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=3Dgcdmw-win32@m.gmane.org