Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE

[jZed]

If you have not already read Use Placeholders for SECURITY, read that first, it is a more important point. But if you do serious database work, you should probably consider my point below *after* fully implementing the SECURITY stuff.

Many (but not all) RDBMSs support pre-parsing and/or pre-optimizing of SQL statements. That means that the RDBMS has to do a certain amount of work every time it prepares a statement. In situations where you are going to execute the same statement with varying values, you can (sometimes immensely) increase performance with a prepare-once, execute-many approach. In all cases, read the DBD and RDBMS docs on placeholders and performance for your DBD/RDBMS, but in general here's an example of how to use placeholders for performance:

RIGHT:
my $sth = $dbh->prepare(
    "UPDATE foo SET bar=7 WHERE baz=?"
);
for my $val(@values) {
    $sth->execute($val);
}

WRONG:
for my $val(@values) {
    $val = $dbh->quote($val);
    my $sth = $dbh->do(
       "UPDATE foo SET bar=7 WHERE baz=$val"
    );
}

The WRONG code does a prepare() and an execute() i.e. do() every time through the loop. The RIGHT code does the prepare() once at the top of the loop, and then does an execute() each time through the loop.