DBI prepared statement - bind hex-wildcard string

Question

I'm pretty new to Perl and I've been stumped by an error with DBI. I'm trying to query for a series of characters defined by their hex values:

my @compare = ( '\'%\'+x\'0A\'+\'%\'',
                '\'%\'+x\'92\'+\'%\'',
                '\'%\'+x\'96\'+\'%\'',
                '\'%\'+x\'A0\'+\'%\'' );

my $fetch_bad_rows = $dbh->prepare( qq{
    SELECT B.* FROM ... AS B
    WHERE B. ... LIKE ?
        OR B. ... LIKE ?
        OR B. ... LIKE ?
        OR B. ... LIKE ?
});

$fetch_bad_rows->execute(@compare)

my $counter;
for( $counter = 0; $fetch_bad_rows->fetch; $counter++ ) {};
print LOG sprintf("Found %d bad rows\n", $counter);

Executing the statement this way finds 0 rows. However, if I hard code the strings in the query, I get 437 records:

my $fetch_bad_rows = $dbh->prepare( qq{
SELECT B.* FROM ... AS B
    WHERE B. ... LIKE '%'+x'0A'+'%'
        OR B. ... LIKE '%'+x'92'+'%'
        OR B. ... LIKE '%'+x'96'+'%'
        OR B. ... LIKE '%'+x'A0'+'%'
});

$fetch_bad_rows->execute

I haven't been able to figure out what I'm doing wrong with passing in the bind values.

Anyone have any ideas? Thanks in advance.

Answer 1

The ? in the prepare will make sure that everything is escaped. So if you pass in stuff that has ' it will escape the quotes:

'\'%\'+x\'0A\'+\'%\''

Which can be more easily written as:

q{'%'+x'0A'+'%'}

will turn into:

... LIKE '\'%\'+x\'0A\'+\'%\''

And thus it does not find anything.