escaping % AND /%

I have a very simple search using ILIKE and binding like:

$sth->execute( '%' . $user_input . '%' );


The docs show this for escaping SQL pattern chars:

$esc = $dbh->get_info( 14 ); # SQL_SEARCH_PATTERN_ESCAPE
$search_pattern =~ s/([_%])/$esc$1/g;


But if $search_pattern is '\%' then you end up with '\\%'.

I suppose the easy thing is to s/$esc//g first. What's the approach
if the $esc is a valid character for the column data?



--
Bill Moseley
moseley@hank.org

Re: escaping % AND /%

Hi Bill,

On Fri, 29 Jun 2007, Bill Moseley wrote:
> The docs show this for escaping SQL pattern chars:
> $esc = $dbh->get_info( 14 ); # SQL_SEARCH_PATTERN_ESCAPE
> $search_pattern =~ s/([_%])/$esc$1/g;
>
> But if $search_pattern is '\%' then you end up with '\\%'.

If you have a user defined search pattern which is \%, then you can assume
that user wanted to match against the '%' litteral, right? So \\% is what
you'd want, isn't it?

> I suppose the easy thing is to s/$esc//g first. What's the approach
Yeah, but that wouldn't match against your \% litteral, which the user
intended - would it?

> if the $esc is a valid character for the column data?
then you'd match against '\'. $esc - as above, wouldn't you?

R.

Re: escaping % AND /%

On Fri, Jun 29, 2007 at 04:01:25PM +0100, Raf wrote:
> >But if $search_pattern is '\%' then you end up with '\\%'.
>
> If you have a user defined search pattern which is \%, then you can assume
> that user wanted to match against the '%' litteral, right? So \\% is what
> you'd want, isn't it?

No, I don't want to give the user access to the % or _. I'm using

'%' . $user_string . '%'

but I don't want $user_string to have any special characters. If
$user_string includes \ or % or _ I want them to be literal, without
special meaning.



--
Bill Moseley
moseley@hank.org

RE: escaping % AND /%

Then if you want all chars to be treated literally, then I presume you
want:
\%
To be translated to:
\\\%
So just adding your $esc to the left part of s/// should do the trick,
right?


-----Original Message-----
From: Bill Moseley [mailto:moseley@hank.org]=20
Sent: Friday, June 29, 2007 10:55 AM
To: dbi-users@perl.org
Subject: Re: escaping % AND \%

On Fri, Jun 29, 2007 at 04:01:25PM +0100, Raf wrote:
> >But if $search_pattern is '\%' then you end up with '\\%'.
>=20
> If you have a user defined search pattern which is \%, then you can=20
> assume that user wanted to match against the '%' litteral, right? So=20
> \\% is what you'd want, isn't it?

No, I don't want to give the user access to the % or _. I'm using

'%' . $user_string . '%'

but I don't want $user_string to have any special characters. If
$user_string includes \ or % or _ I want them to be literal, without
special meaning.



--
Bill Moseley
moseley@hank.org

Re: escaping % AND /%

Sounds like you want:

$esc = $dbh->get_info( 14 ); # SQL_SEARCH_PATTERN_ESCAPE
$user_input =~ s/([_%])/$esc$1/g;
$search_pattern = '%' . $user_input . '%';
$sth->execute( $search_pattern );

On 6/29/07, Bill Moseley wrote:
> On Fri, Jun 29, 2007 at 04:01:25PM +0100, Raf wrote:
> > >But if $search_pattern is '\%' then you end up with '\\%'.
> >
> > If you have a user defined search pattern which is \%, then you can assume
> > that user wanted to match against the '%' litteral, right? So \\% is what
> > you'd want, isn't it?
>
> No, I don't want to give the user access to the % or _. I'm using
>
> '%' . $user_string . '%'
>
> but I don't want $user_string to have any special characters. If
> $user_string includes \ or % or _ I want them to be literal, without
> special meaning.
>
>
>
> --
> Bill Moseley
> moseley@hank.org
>
>

Re: escaping % AND /%

On Fri, Jun 29, 2007 at 03:03:42PM -0500, CAMPBELL, BRIAN D (BRIAN) wrote:
> Then if you want all chars to be treated literally, then I presume you
> want:
> \%
> To be translated to:
> \\\%
> So just adding your $esc to the left part of s/// should do the trick,
> right?

The \\% is translated to:

\\\\%

Which leaves the % unescaped.

I'll just remove "\" from the input. But thought this might be a
reasonably common situation (use % around a search string but not
allow % or _ to be special in the user data.

--
Bill Moseley
moseley@hank.org

RE: escaping % AND /%

Well, a convenient way to "remove" your \ possibly, and then re-add
them, off the top of my head is to use:

Split-map-join

Where you split on \, map your previous s/// on the results, and then
join with \\.

I'm assuming you want to preserve user \ values as literal chars.

-----Original Message-----
From: Bill Moseley [mailto:moseley@hank.org]=20
Sent: Friday, June 29, 2007 4:58 PM
To: CAMPBELL, BRIAN D (BRIAN)
Cc: dbi-users@perl.org
Subject: Re: escaping % AND \%

On Fri, Jun 29, 2007 at 03:03:42PM -0500, CAMPBELL, BRIAN D (BRIAN)
wrote:
> Then if you want all chars to be treated literally, then I presume you
> want:
> \%
> To be translated to:
> \\\%
> So just adding your $esc to the left part of s/// should do the trick,

> right?

The \\% is translated to:

\\\\%

Which leaves the % unescaped.

I'll just remove "\" from the input. But thought this might be a
reasonably common situation (use % around a search string but not allow
% or _ to be special in the user data.

--
Bill Moseley
moseley@hank.org

Re: escaping % AND /%

What about changing / fixing the escape character just for that query?

$sql = "SELECT * FROM Table WHERE Field LIKE ? ESCAPE '!'";
$sth = $dbh->prepare($sql);
$user_input =~ s/([_%!])/!$1/g; # SQL-escape
$user_input =~ s/([$&%\\])/\\$1/g; # Perl-escape
$sth->execute( '%' . $user_input . '%' );

Doing it this way You avoid the trouble handling the conflicting Perl- and the
SQL-escaping in case of '%'.

Regards

Robert


Bill Moseley schrieb:
> I have a very simple search using ILIKE and binding like:
>
> $sth->execute( '%' . $user_input . '%' );
>
>
> The docs show this for escaping SQL pattern chars:
>
> $esc = $dbh->get_info( 14 ); # SQL_SEARCH_PATTERN_ESCAPE
> $search_pattern =~ s/([_%])/$esc$1/g;
>
>
> But if $search_pattern is '\%' then you end up with '\\%'.
>
> I suppose the easy thing is to s/$esc//g first. What's the approach
> if the $esc is a valid character for the column data?
>
>
>

Re: escaping % AND /%

--6e7ZaeXHKrTJCxdu
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2007-06-29 16:57:55 -0700, Bill Moseley wrote:
> On Fri, Jun 29, 2007 at 03:03:42PM -0500, CAMPBELL, BRIAN D (BRIAN) wrote:
> > Then if you want all chars to be treated literally, then I presume you
> > want:
> > \%
> > To be translated to:
> > \\\%
> > So just adding your $esc to the left part of s/// should do the trick,
> > right?
>=20
> The \\% is translated to:
>=20
> \\\\%
>
> Which leaves the % unescaped.

Nope:


#!/usr/bin/perl
use warnings;
use strict;

my $esc =3D '\\';
for my $s ('%', '\\%', '\\\\%') {
my $search_pattern =3D $s;
print "$search_pattern -> ";
$search_pattern =3D~ s/([\Q$esc\E_%])/$esc$1/g;
print "$search_pattern\n";
}

prints:

% -> \%
\% -> \\\%
\\% -> \\\\\%

which is as it should be.

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--6e7ZaeXHKrTJCxdu
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGiL1KMdFfQa64PCwRAgaoAJ9eSp9Zzsd3chT6Aedf3LM1QpdATACf Zcit
F/OamMDz4kWT1IwjHGBdxms=
=pueo
-----END PGP SIGNATURE-----

--6e7ZaeXHKrTJCxdu--