[Info-ingres] Similar to what exactly

[Paul White]

Hi Marty,

Looks a bit like an SQL injection hack. 

Are tabs (x09) allowed also?

 

try.

similar to  x'255b002d080b0c0e2d1f7f2dff5d25' 

 

 

Paul

 

From: info-ingres-bounces at lists.planetingres.org
[mailto:info-ingres-bounces at lists.planetingres.org] On Behalf Of Martin
Bowes
Sent: Thursday, 30 August 2018 6:45 PM
To: info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] Similar to what exactly

 

Yes it can!

 

insert into search_pattern values('%[^' + x'0a' + x'0d' + '[:print:]]%');

 

select id, a_string from test_search a, search_pattern b

where a.a_string similar to b.pattern;

 

And it works!

 

Bwahahaha

 

Marty

 

From: Martin Bowes 
Sent: 30 August 2018 09:26
To: Martin Bowes; info-ingres at lists.planetingres.org
Subject: RE: [Info-ingres] Similar to what exactly

 

Got it with a ceaseless five minutes of hacking!

 

select id, a_string from test_search

where a_string similar to '%[^' + x'0a' + x'0d' + '[:print:]]%';

 

I didn't know you could build up search expressions like that!

 

I wonder if I could store the search expression in a column?

 

Marty

 

From: Martin Bowes [mailto:martin.bowes at ndph.ox.ac.uk] 
Sent: 30 August 2018 09:07
To: info-ingres at lists.planetingres.org
Subject: [Info-ingres] Similar to what exactly

 

Hi All,

 

My boss (bless his little cotton socks) has asked me to find strings which
contain any non printing characters.

 

Sadly the 'contain' predicate doesn't allow any fancy pattern matching so we
have to do this using the 'similar to' predicate.

 

So I came up with:

 

select id, a_string from test_search

where a_string similar to '%[^[:print:]]%';

 

And that works well.

 

But now he says, in some columns he'd like to allow it to have the
characters \n (x'0a') and \r (x'od').

 

Okay then.

 

This works:

select id, a_string from test_search

where replace(replace(a_string, x'0d', ''), x'0a', '') similar to
'%[^[:print:]]%';

 

But that means on each row I have to call the replace function twice and
then apply the similar to pattern. That doesn't sound terribly efficient to
me.

 

What I'd like to do is extend my character set to be
'%[^x'0a'x'od'[:print:]]%' . but pretty clearly that syntax isn't going to
fly.

 

Anyone got any ideas?

 

Martin Bowes

 

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180830/0f683e7d/attachment.html>