Re: BUG #6314: The like command does not handle a long string of special chars

The following bug has been logged on the website:

Bug reference: 6314
Logged by: David Rericha
Email address: d(dot)rericha(at)healthcareoss(dot)com
PostgreSQL version: 8.4.5
Operating system: OpenSuSE 11.4 64-bit
Description:

Simply set a varchar field in your db to the following string:
!"#$%'()*+,-/:;=?(at)[\]^_`{|}~0000&<>

I know, I know, who would do this, right? Well, its for a certification.
The like command works fine up with escapes up to:
!"#$%''()*+,-/:;=?(at)[%
Notice, I added the % to the end. However, if you go any further - no
matches:
!"#$%''()*+,-/:;=?(at)[\\%
Strangely, this works and shouldn't:
!"#$%''()*+,-/:;=?(at)[\%

Thanks.

<d(dot)rericha(at)healthcareoss(dot)com> wrote:

> Simply set a varchar field in your db to the following string:
> !&quot;#$%&#39;()*+,-/:;=?(at)[\]^_`{|}~0000&amp;&lt;&gt;

Do you have standard_conforming_strings = on?

> The like command works fine up with escapes up to:
> !&quot;#$%&#39;&#39;()*+,-/:;=?(at)[%
> Notice, I added the % to the end. However, if you go any further -
> no matches:
> !&quot;#$%&#39;&#39;()*+,-/:;=?(at)[\\%
> Strangely, this works and shouldn&#39;t:
> !&quot;#$%&#39;&#39;()*+,-/:;=?(at)[\%

It is always better to include a self-contained test case. For
example:

test=# set standard_conforming_strings = on;
SET
test=# create table t (v text not null);
CREATE TABLE
test=# insert into t values
('!&quot;#$%&#39;()*+,-/:;=?(at)[\]^_`{|}~0000&amp;&lt;&gt;');
INSERT 0 1
test=# select * from t where v like
'!&quot;#$\%&#39;()*+,-/:;=?(at)[\\]^_`{|}~0000&amp;&lt;&gt%'
escape '\';
v
--------------------------------------------------------
!&quot;#$%&#39;()*+,-/:;=?(at)[\]^_`{|}~0000&amp;&lt;&gt;
(1 row)

So this is not a bug on HEAD. What do you get when you run it?

-Kevin

d(dot)rericha(at)healthcareoss(dot)com writes:
> Simply set a varchar field in your db to the following string:
> !&quot;#$%&#39;()*+,-/:;=?(at)[\]^_`{|}~0000&amp;&lt;&gt;

> I know, I know, who would do this, right? Well, its for a certification.
> The like command works fine up with escapes up to:
> !&quot;#$%&#39;&#39;()*+,-/:;=?(at)[%

Doesn't match for me, rather unsurprisingly since this string contains
two occurrences of "&#39;" not one.

> Notice, I added the % to the end. However, if you go any further - no
> matches:
> !&quot;#$%&#39;&#39;()*+,-/:;=?(at)[\\%
> Strangely, this works and shouldn&#39;t:
> !&quot;#$%&#39;&#39;()*+,-/:;=?(at)[\%

It's hard to tell for sure, since you've presented a garbled
interpretation of what you did rather than showing us exactly what you
did, but I'm suspecting the problem boils down to forgetting that
backslash is an escape character in Postgres string literals, and
also for LIKE itself. You should reread the manual's discussion of
LIKE:
http://www.postgresql.org/docs/8.4/static/functions-matching.html

regards, tom lane

On Thursday, December 1, 2011, Tom Lane wrote:

> d(dot)rericha(at)healthcareoss(dot)com <javascript:;> writes:
> > Simply set a varchar field in your db to the following string:
> > !&quot;#$%&#39;()*+,-/:;=?(at)[\]^_`{|}~0000&amp;&lt;&gt;
>
> > I know, I know, who would do this, right? Well, its for a certification.
> > The like command works fine up with escapes up to:
> > !&quot;#$%&#39;&#39;()*+,-/:;=?(at)[%
>
> Doesn't match for me, rather unsurprisingly since this string contains
> two occurrences of "&#39;" not one.
>
> > Notice, I added the % to the end. However, if you go any further - no
> > matches:
> > !&quot;#$%&#39;&#39;()*+,-/:;=?(at)[\\%
> > Strangely, this works and shouldn&#39;t:
> > !&quot;#$%&#39;&#39;()*+,-/:;=?(at)[\%
>
> It's hard to tell for sure, since you've presented a garbled
> interpretation of what you did rather than showing us exactly what you
>

A fair amount of that garbling is unfortunately the fault of a bug in the
new website code that applied HTML escapes to plaintext emails, which in
the end caused double escaping. I've just pushed a fix for this, so from
now on bugreports won't do that.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/