escaping character in sql statements

Hi,
I am writing an sql to retrieve data relating to certain files in a folder from my database. Some times part of the data may contain a ' character.

here's an example of the sql

select * from files where location = '\\\\\\\\192.168.0.100\\\\clients\\\\o''orielly\\\\file%'


this recults returns no records from my database, even though there are records there. the records are path + file names, thats why i have to use the \ charater to escape them, this does not seem to like me using ' to escape the ' character, any ideas?



__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--
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: escaping character in sql statements

Mark Mchugh wrote:
> Hi,
> I am writing an sql to retrieve data relating to certain files in a
> folder from my database. Some times part of the data may contain a '
> character.
>
> here's an example of the sql
>
> select * from files where location =
> '\\\\\\\\192.168.0.100\\\\clients\\\\o''orielly\\\\file%'
>
>
This should be

select * from files where location =
'\\\\\\\\192.168.0.100\\\\clients\\\\o\'orielly\\\\file%'

Petr Vileta, Czech republic
(My server rejects all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)




--
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: escaping character in sql statements

for some reason this does not seem to work either?

----- Original Message ----
From: Petr Vileta
To: mysql list
Sent: Thursday, October 25, 2007 1:04:10 AM
Subject: Re: escaping character in sql statements


Mark Mchugh wrote:
> Hi,
> I am writing an sql to retrieve data relating to certain files in a
> folder from my database. Some times part of the data may contain a '
> character.
>
> here's an example of the sql
>
> select * from files where location =
> '\\\\\\\\192.168.0.100\\\\clients\\\\o''orielly\\\\file%'
>
>
This should be

select * from files where location =
'\\\\\\\\192.168.0.100\\\\clients\\\\o\'orielly\\\\file%'

Petr Vileta, Czech republic
(My server rejects all messages from Yahoo and Hotmail. Send me your
mail
from another non-spammer site please.)




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=mark_mch@yahoo.com





__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--
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: escaping character in sql statements

Mark Mchugh wrote:
> for some reason this does not seem to work either?

I think Petr's comment is correct, but perhaps there are other reasons
this is not working the way you expect. It seems like you have double
the number of backslashes you need:

mysql> select '\\\\\\\\192.168.0.100\\\\clients\\\\o''orielly\\\\file%';
+----------------------------------------------+
| \\\\192.168.0.100\\clients\\o'orielly\\file% |
+----------------------------------------------+
| \\\\192.168.0.100\\clients\\o'orielly\\file% |
+----------------------------------------------+
1 row in set (0.00 sec)

You might try this format instead:

mysql> select '\\\\192.168.0.100\\clients\\o''orielly\\file%';
+-----------------------------------------+
| \\192.168.0.100\clients\o'orielly\file% |
+-----------------------------------------+
| \\192.168.0.100\clients\o'orielly\file% |
+-----------------------------------------+
1 row in set (0.00 sec)

Is that more like what you are hoping to match?


--
Todd Farmer


--
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: escaping character in sql statements

hi,
i need double the number of them cos in the database the file/path name is stored as

\\192.168.0.100\clients\\o''orielly\file1.pdf

wierd?


----- Original Message ----
From: Todd Farmer
To: Mark Mchugh
Cc: Petr Vileta ; mysql list
Sent: Thursday, October 25, 2007 10:55:43 AM
Subject: Re: escaping character in sql statements


Mark Mchugh wrote:
> for some reason this does not seem to work either?

I think Petr's comment is correct, but perhaps there are other reasons
this is not working the way you expect. It seems like you have double
the number of backslashes you need:

mysql> select
'\\\\\\\\192.168.0.100\\\\clients\\\\o''orielly\\\\file%';
+----------------------------------------------+
| \\\\192.168.0.100\\clients\\o'orielly\\file% |
+----------------------------------------------+
| \\\\192.168.0.100\\clients\\o'orielly\\file% |
+----------------------------------------------+
1 row in set (0.00 sec)

You might try this format instead:

mysql> select '\\\\192.168.0.100\\clients\\o''orielly\\file%';
+-----------------------------------------+
| \\192.168.0.100\clients\o'orielly\file% |
+-----------------------------------------+
| \\192.168.0.100\clients\o'orielly\file% |
+-----------------------------------------+
1 row in set (0.00 sec)

Is that more like what you are hoping to match?


--
Todd Farmer





__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--
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: escaping character in sql statements

Just a na=EFve question. Was your query to find a client of o'oreilly =
(as
shown) or o'reilly (a more traditional spelling)?

Regards,



Lawson Cronlund
lawson@vrtinc.com
+1(480)308-0641 (voice)
+1(602)996-0376 (fax)
=20

-----Original Message-----
From: Mark Mchugh [mailto:mark_mch@yahoo.com]=20
Sent: Wednesday, October 24, 2007 12:50 PM
To: mysql list
Subject: escaping character in sql statements

Hi,
I am writing an sql to retrieve data relating to certain files in a =
folder
from my database. Some times part of the data may contain a ' character.

here's an example of the sql

select * from files where location =3D
'\\\\\\\\192.168.0.100\\\\clients\\\\o''orielly\\\\file%'


this recults returns no records from my database, even though there are
records there. the records are path + file names, thats why i have to =
use
the \ charater to escape them, this does not seem to like me using ' to
escape the ' character, any ideas?



__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around=20
http://mail.yahoo.com=20

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


--
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: escaping character in sql statements

sorry, thats a typo, its the more traditional spelling.... -----=
Original Message ----=0AFrom: Lawson Cronlund =0ATo: Ma=
rk Mchugh ; mysql list =0ASent: =
Thursday, October 25, 2007 4:36:53 PM=0ASubject: RE: escaping character in =
sql statements =0AJust a na=EFve question. Was your query to find a c=
lient of o'oreilly=0A (as=0Ashown) or o'reilly (a more traditional spelling=
)? Regards, Lawson Cronlund=0Alawson@vrtinc.com=0A+1(480)30=
8-0641 (voice)=0A+1(602)996-0376 (fax)=0A -----Original Message-----=
=0AFrom: Mark Mchugh [mailto:mark_mch@yahoo.com] =0ASent: Wednesday, Octobe=
r 24, 2007 12:50 PM=0ATo: mysql list=0ASubject: escaping character in sql s=
tatements Hi,=0AI am writing an sql to retrieve data relating to certa=
in files in a=0A folder=0Afrom my database. Some times part of the data may=
contain a '=0A character. here's an example of the sql select * =
from files where location = '\\\\\\\\192.168.0.100\\\\clients\\\\o''ori=
elly\\\\file%' =0Athis recults returns no records from my database, ev=
en though there are=0Arecords there. the records are path + file names, tha=
ts why i have to=0A use=0Athe \ charater to escape them, this does not seem=
to like me using ' to=0Aescape the ' character, any ideas? ____=
______________________________________________=0ADo You Yahoo!?=0ATired of =
spam? Yahoo! Mail has the best spam protection around =0Ahttp://mail.yahoo=
..com -- =0AMySQL Windows Mailing List=0AFor list archives: http://lis=
ts.mysql.com/win32=0ATo unsubscribe: http://lists.mysql.com/win32?unsub=
=3Dlawson@vrtinc.com =0A--=0AMySQL Windows Mailing List=0AFor list arc=
hives: http://lists.mysql.com/win32=0ATo unsubscribe: =0A http://lists.my=
sql.com/win32?unsub=3Dmark_mch@yahoo.com __ _______________=
_________________________________=0ADo You Yahoo!?=0ATired of spam? Yahoo!=
Mail has the best spam protection around =0Ahttp://mail.yahoo.com

--
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: escaping character in sql statements

Mark,

My experience is mainly Oracle but it may help. I would expect you to
have to include the ' in quotes i.e. '''.

If I read your statement correctly I would expect something like the
following to work.

select * from files where location = '\\\\\\\\192.168.0.100\\\\clients\\\\o'''orielly\\\\file%'

It gets more interesting if you want to find two quotes (''). You will have to experiment.

Good luck Bob



Mark Mchugh wrote:

>Hi,
>I am writing an sql to retrieve data relating to certain files in a folder from my database. Some times part of the data may contain a ' character.
>
>here's an example of the sql
>
>select * from files where location = '\\\\\\\\192.168.0.100\\\\clients\\\\o''orielly\\\\file%'
>
>
>this recults returns no records from my database, even though there are records there. the records are path + file names, thats why i have to use the \ charater to escape them, this does not seem to like me using ' to escape the ' character, any ideas?
>
>
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam? Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com
>
>
>

--
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: escaping character in sql statements

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

Mark,

If you are trying to find the row as listed below, you still have too
many backslashes.

To find: '\\192.168.0.100\clients\\o'rielly\file1.pdf'

Look for: '\\\\192.168.0.100\\clients\\\\o\'rielly\file1.pdf'
OR: "\\\\192.168.0.100\\clients\\\\o'rielly\file1.pdf"

This assumes you are testing the query using the mysql console or query
analyzer.

Now, it could be more complicated than that if you are writing the query
in a Perl or other language script. If you are doing this with a script,
I would suggest that after creating the sql statement, print it to your
page so you can see exactly what you are sending to mysql. Copy the
generated statement and paste it into your query analyzer to see your
result set. Then keep playing with it until you get what you want.

Randy Clamons
Systems Programming
randy@novaspace.com



Mark Mchugh wrote:
> hi,
> i need double the number of them cos in the database the file/path name is stored as
>
> \\192.168.0.100\clients\\o''orielly\file1.pdf
>
> wierd?
>
>
> ----- Original Message ----
> From: Todd Farmer
> To: Mark Mchugh
> Cc: Petr Vileta ; mysql list
> Sent: Thursday, October 25, 2007 10:55:43 AM
> Subject: Re: escaping character in sql statements
>
>
> Mark Mchugh wrote:
>
>> for some reason this does not seem to work either?
>>
>
> I think Petr's comment is correct, but perhaps there are other reasons
> this is not working the way you expect. It seems like you have double
> the number of backslashes you need:
>
> mysql> select
> '\\\\\\\\192.168.0.100\\\\clients\\\\o''orielly\\\\file%';
> +----------------------------------------------+
> | \\\\192.168.0.100\\clients\\o'orielly\\file% |
> +----------------------------------------------+
> | \\\\192.168.0.100\\clients\\o'orielly\\file% |
> +----------------------------------------------+
> 1 row in set (0.00 sec)
>
> You might try this format instead:
>
> mysql> select '\\\\192.168.0.100\\clients\\o''orielly\\file%';
> +-----------------------------------------+
> | \\192.168.0.100\clients\o'orielly\file% |
> +-----------------------------------------+
> | \\192.168.0.100\clients\o'orielly\file% |
> +-----------------------------------------+
> 1 row in set (0.00 sec)
>
> Is that more like what you are hoping to match?
>
>
>

--------------070206080506060609010209--

Re: escaping character in sql statements

Mark Mchugh wrote:
> hi,
> i need double the number of them cos in the database the file/path
> name is stored as
>
> \\192.168.0.100\clients\\o''orielly\file1.pdf
>
> wierd?
>
Oh sorry, in this case you must write it as
select * from files where location =
'\\\\192.168.0.100\\clients\\\\o\'\'orielly\\file1.pdf'
In other word, you must quote this way
char(s) quoted
--------------------------
\ \\
\\ \\\\
' \'
'' \'\'

Petr Vileta, Czech republic
(My server rejects all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)



--
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