In article <6.1.2.0.1.20040728170935.01f2ef38@localhost>,
Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Just curious on what are the correct/official ways for dealing with
> the following scenarios:
> 1) Input string contains % and _ and would be used in a LIKE query
> that should not have user controllable wildcards.
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.
> 2) Input string are numbers which can hold negative and positive
> values and could potentially be used in the following query:
> update tablea set a=10-$inputstring where key=1;
> When I tested at least one version of DBD::Pg doesn't appear to escape
> the inputstring when it's a number.
> e.g.
> $SQL="update tablea set a=10-? where key=1";
> And $SQL is used in a prepared statement with $inputstring as a parameter.
> I found that when $inputstring contained a negative number, all rows
> in tablea are set to the same value.
What exactly did you do? I tried
$sth = $dbh->prepare (q{
UPDATE t1
SET val = 10-?
WHERE id = ?
});
$sth->execute (-1, 1);
and it surely set val to 11.
> 3) Postgresql queries with select .... from ... where ... LIMIT $inputstring
The same thing:
$sth = $dbh->prepare ("SELECT id, val FROM t1 ORDER BY id LIMIT ?");
$sth->execute ($inputstring);
