Prepared DBI statement - hexadecimal string binding

Question

I am new to Perl and I encountered a bug with DBI. I am trying to query a series of characters defined by their hexadecimal 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 hardcode the strings in the request, I get 437 entries:

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 was not able to figure out what I am doing wrong by passing the binding values.

Does anyone have any ideas? Thanks in advance.

Answer 1

Thanks for answers. I did some more digging based on the information provided and found a way to make it work:

my @compare = ( '0A','92','96','A0' );

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

$fetch_bad_rows->execute(@compare)

'x?' , "x" . UNHEX , , .

Answer 2

? prepare , . , , ', :

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

:

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

:

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

, , .

Answer 3

, , SQL-. , . :

my $sql = "SELECT x FROM y WHERE z LIKE '%' + x? + '%'";
my $sth = $dbh->prepare($sql);
$sth->execute("0A");