Re: Sql injection attacks

In article <20040728184609.1900596@uruguay.brainstorm.fr>,
"Daniel Verite" <daniel@manitou-mail.org> writes:

>      Harald Fuchs writes
>> Perhaps you mean something like the following:
>>
>> my $sth = $dbh->prepare (q{
>> SELECT whatever
>> FROM mytable
>> WHERE somecol LIKE ? || '%'
>> });
>> $sth->execute ($input);
>>
>> Even if $input contains '%' or '_', those characters get properly escaped.

> Hum, what makes you think that? if $input is "_foo%", then the DBD
> driver will produce this query:
> SELECT whatever FROM mytable WHERE somecol like  '_foo%'||'%'
> The % and _ characters aren't escaped at all.

> That can be confirmed by setting $dbh->trace_level to something greater or equal
> than 2 and looking at the Pg DBD driver's output.

Shit, you're right.  The $dbh->quote() called for the placeholders
escapes strings for INSERTing, but not for LIKE comparisons.  So this
is one of the few places where using placeholders is not enough.

At least my erroneous assumption can't be used for an SQL injection
attack - you just get more results than you would get if you escape
the wildcards by hand.