Thread: weird quote bug

weird quote bug

From

Shane Wright

Date:

30 June 2003, 11:26:32

Hi

This is really driving me silly - I can't work it out, can anyone see what I'm doing thats stupid and causing this not
tomatch? 


This shows that the row exists in the table:


emystery=> select aid,useragent from useragent where useragent like '%ntserver-ps%';
    aid    |                                                useragent
-----------+---------------------------------------------------------------------------------------------------------
 875741007 | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
(1 row)


this tries to select it (note that I've escaped the backslashes):


emystery=> select * from useragent where useragent ilike 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
file://\\\\ntserver-ps\\publicsw\\nt\\ie6\\ins\\active.ins)';
 aid | useragent | date_added | data_browser | data_version | data_os
-----+-----------+------------+--------------+--------------+---------
(0 rows)


no match!  This one encodes the backslashes (\xxx octal for ASCII value) in a different way:


emystery=> select * from useragent where useragent ilike 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
file://\134\134ntserver-ps\134publicsw\134nt\134ie6\134ins\134active.ins)';
 aid | useragent | date_added | data_browser | data_version | data_os
-----+-----------+------------+--------------+--------------+---------
(0 rows)


again no match!  And to show that the above queries were correct:


emystery=> select 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
file://\\\\ntserver-ps\\publicsw\\nt\\ie6\\ins\\active.ins)';
                                                ?column?
---------------------------------------------------------------------------------------------------------
 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
(1 row)

emystery=> select 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
file://\134\134ntserver-ps\134publicsw\134nt\134ie6\134ins\134active.ins)';
                                                ?column?
---------------------------------------------------------------------------------------------------------
 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
(1 row)



They are identical!  I can't work out whats going on!  Please, if anyone can see what's wrong it'll stop me careering
intomy early grave! 

Thanks

Shane

Re: weird quote bug

From

Shane Wright

Date:

30 June 2003, 11:37:14


Should have said - I'm using postgreSQL 7.3.3 on Gentoo Linux.  The problem occurs both through psql and through
PHP4.3.3.

Cheers

Shane

On Monday 30 Jun 2003 12:25 pm, Shane Wright wrote:
> Hi
>
> This is really driving me silly - I can't work it out, can anyone see what
> I'm doing thats stupid and causing this not to match?
>
>
> This shows that the row exists in the table:
>
>
> emystery=> select aid,useragent from useragent where useragent like
> '%ntserver-ps%'; aid    |
> useragent
> -----------+---------------------------------------------------------------
>------------------------------------------ 875741007 | Mozilla/4.0
> (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins) (1 row)
>
>
> this tries to select it (note that I've escaped the backslashes):
>
>
> emystery=> select * from useragent where useragent ilike 'Mozilla/4.0
> (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\\\ntserver-ps\\publicsw\\nt\\ie6\\ins\\active.ins)'; aid |
> useragent | date_added | data_browser | data_version | data_os
> -----+-----------+------------+--------------+--------------+--------- (0
> rows)
>
>
> no match!  This one encodes the backslashes (\xxx octal for ASCII value) in
> a different way:
>
>
> emystery=> select * from useragent where useragent ilike 'Mozilla/4.0
> (compatible; MSIE 6.0; Windows NT 5.0;
> file://\134\134ntserver-ps\134publicsw\134nt\134ie6\134ins\134active.ins)';
> aid | useragent | date_added | data_browser | data_version | data_os
> -----+-----------+------------+--------------+--------------+--------- (0
> rows)
>
>
> again no match!  And to show that the above queries were correct:
>
>
> emystery=> select 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\\\ntserver-ps\\publicsw\\nt\\ie6\\ins\\active.ins)'; ?column?
> ---------------------------------------------------------------------------
>------------------------------ Mozilla/4.0 (compatible; MSIE 6.0; Windows NT
> 5.0; file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins) (1 row)
>
> emystery=> select 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\134\134ntserver-ps\134publicsw\134nt\134ie6\134ins\134active.ins)';
> ?column?
> ---------------------------------------------------------------------------
>------------------------------ Mozilla/4.0 (compatible; MSIE 6.0; Windows NT
> 5.0; file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins) (1 row)
>
>
>
> They are identical!  I can't work out whats going on!  Please, if anyone
> can see what's wrong it'll stop me careering into my early grave!
>
> Thanks
>
> Shane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

Re: weird quote bug

From

"alex b."

Date:

30 June 2003, 11:51:15

you might want to try escaping the simple slashes aswell... and you have
to double the number of backslashes....

learning=> SELECT * FROM test where x like '\\\\';
  x
---
  \
(1 row)


*tadaaa*


cheers,
alex



Shane Wright wrote:
> Hi
>
> This is really driving me silly - I can't work it out, can anyone see what I'm doing thats stupid and causing this
notto match? 
>
>
> This shows that the row exists in the table:
>
>
> emystery=> select aid,useragent from useragent where useragent like '%ntserver-ps%';
>     aid    |                                                useragent
> -----------+---------------------------------------------------------------------------------------------------------
>  875741007 | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
> (1 row)
>
>
> this tries to select it (note that I've escaped the backslashes):
>
>
> emystery=> select * from useragent where useragent ilike 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
file://\\\\ntserver-ps\\publicsw\\nt\\ie6\\ins\\active.ins)';
>  aid | useragent | date_added | data_browser | data_version | data_os
> -----+-----------+------------+--------------+--------------+---------
> (0 rows)
>
>
> no match!  This one encodes the backslashes (\xxx octal for ASCII value) in a different way:
>
>
> emystery=> select * from useragent where useragent ilike 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
file://\134\134ntserver-ps\134publicsw\134nt\134ie6\134ins\134active.ins)';
>  aid | useragent | date_added | data_browser | data_version | data_os
> -----+-----------+------------+--------------+--------------+---------
> (0 rows)
>
>
> again no match!  And to show that the above queries were correct:
>
>
> emystery=> select 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
file://\\\\ntserver-ps\\publicsw\\nt\\ie6\\ins\\active.ins)';
>                                                 ?column?
> ---------------------------------------------------------------------------------------------------------
>  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
> (1 row)
>
> emystery=> select 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
file://\134\134ntserver-ps\134publicsw\134nt\134ie6\134ins\134active.ins)';
>                                                 ?column?
> ---------------------------------------------------------------------------------------------------------
>  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
> (1 row)
>
>
>
> They are identical!  I can't work out whats going on!  Please, if anyone can see what's wrong it'll stop me careering
intomy early grave! 
>
> Thanks
>
> Shane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>

Re: weird quote bug

From

Paul Thomas

Date:

30 June 2003, 11:55:01

On 30/06/2003 12:25 Shane Wright wrote:
> Hi
>
> This is really driving me silly - I can't work it out, can anyone see
> what I'm doing thats stupid and causing this not to match?
>
>
> This shows that the row exists in the table:
>
>
> emystery=> select aid,useragent from useragent where useragent like
> '%ntserver-ps%';
>     aid    |                                                useragent
> -----------+---------------------------------------------------------------------------------------------------------
>  875741007 | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
> (1 row)
>
>
> this tries to select it (note that I've escaped the backslashes):
>
>
> emystery=> select * from useragent where useragent ilike 'Mozilla/4.0
> (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\\\ntserver-ps\\publicsw\\nt\\ie6\\ins\\active.ins)';
>  aid | useragent | date_added | data_browser | data_version | data_os
> -----+-----------+------------+--------------+--------------+---------
> (0 rows)
>
>
> no match!  This one encodes the backslashes (\xxx octal for ASCII value)
> in a different way:
>
>
> emystery=> select * from useragent where useragent ilike 'Mozilla/4.0
> (compatible; MSIE 6.0; Windows NT 5.0;
> file://\134\134ntserver-ps\134publicsw\134nt\134ie6\134ins\134active.ins)';
>  aid | useragent | date_added | data_browser | data_version | data_os
> -----+-----------+------------+--------------+--------------+---------
> (0 rows)
>
>
> again no match!  And to show that the above queries were correct:
>
>
> emystery=> select 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\\\ntserver-ps\\publicsw\\nt\\ie6\\ins\\active.ins)';
>                                                 ?column?
> ---------------------------------------------------------------------------------------------------------
>  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
> (1 row)
>
> emystery=> select 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\134\134ntserver-ps\134publicsw\134nt\134ie6\134ins\134active.ins)';
>                                                 ?column?
> ---------------------------------------------------------------------------------------------------------
>  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
> (1 row)
>
>
>
> They are identical!  I can't work out whats going on!  Please, if anyone
> can see what's wrong it'll stop me careering into my early grave!

I've never tried this but the docs for LIKE (secfion 6.6.1 in the 7.3
docs) say that to match a literal \ you need to type \\\\. An alternative
might be to try adding an ESCAPE '' clause at the end (also in the same
doc section).

HTH

  --
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Re: weird quote bug

From

Karsten Hilbert

Date:

30 June 2003, 12:00:36

> This shows that the row exists in the table:
>
> emystery=> select aid,useragent from useragent where useragent like '%ntserver-ps%';
>     aid    |                                                useragent
> -----------+---------------------------------------------------------------------------------------------------------
>  875741007 | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
Are you sure there's no trailing whitespace on this string ?

Because this:

> emystery=> select * from useragent
> where useragent ilike
> 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; file://\\\\ntserver-ps\\publicsw\\nt\\ie6\\ins\\active.ins)';
                                                                                                                 ^
would not match trailing whitespace.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: weird quote bug

From

Shane Wright

Date:

30 June 2003, 12:07:16

> > They are identical!  I can't work out whats going on!  Please, if anyone
> > can see what's wrong it'll stop me careering into my early grave!
>
> I've never tried this but the docs for LIKE (secfion 6.6.1 in the 7.3
> docs) say that to match a literal \ you need to type \\\\. An alternative
> might be to try adding an ESCAPE '' clause at the end (also in the same
> doc section).

Ah.  I had soooo read that bit of the manual - must have missed it.  Phew it works now!

Thanks (and thanks to the others who answered my question!)

Cheers

Shane