The other trick is to not explicitly care, but insert and
let DBI take care of it. You must do this as:
my $sth = $dbh->prepare ("INSERT INTO x (col1) VALUES(?)");
foreach my $value (@list_of_evil_stuff)
{
$sth->execute($value);
}
Bound parameters using the '?' operator are handled correctly
by DBI on the way in, quoting as required, and such. They can
even be binary data which would upset your terminal, for BLOB
fields.
As the DBI documentation says:
Using placeholders and `@bind_values' with the `do' method can be useful because it avoids the need to correctly quote any variables in the
`$statement'. But if you'll be executing the statement many times then
it's more efficient to `prepare' it once and call `execute' many times
instead.
You should not do something like this:
$dbh->do("INSERT INTO x (col1) VALUES('$evil_value')");
It is, as you have noted, far too risky since the $evil_value
might be "); DROP TABLE foo;", or something sinister.
Quoting is really a non-issue. What is more likely to cause
trouble is a user submitting non-numeric data in an INT field,
or something equivalent which causes a DBI error. Make
sure you handle these elegantly, or at least allow for
the possiblity that an INSERT, UPDATE or REPLACE might fail
because of user error. | [reply]
[d/l]
[select] |
| [reply]
[d/l] |