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?
