Placeholders and bind values

When you want to insert into SQL your own parameters you of course can always do it this way:

my $dbh =
  DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1});

my $firstname = 'John';
my $sth       = $dbh->prepare(
    "SELECT firstname, lastname FROM users WHERE firstname = '$firstname'"
);
$sth->execute;

while (my @row = $sth->fetchrow_array) {
    my ($firstname, $lastname) = @row;

    say "$firstname $lastname";
}

But DON'T!

This kind of interpolation when used not carefully can end up with an SQL injection which can be very dangerous and result into data loss and server damage.

When you want to insert parameters into SQL use placeholders and bind values:

my $dbh =
  DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1});

my $firstname = 'John';
my $sth       = $dbh->prepare(
    'SELECT firstname, lastname FROM users WHERE firstname = ?');
$sth->execute($firstname);

while (my @row = $sth->fetchrow_array) {
    my ($firstname, $lastname) = @row;

    say "$firstname $lastname";
}

Placeholders can also be used for performance gain, since the query doesn't have to be recompiled every time.

my $dbh =
  DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1});

my $sth = $dbh->prepare(
    'SELECT firstname, lastname FROM users WHERE firstname = ?');

for (qw/John Pete Sam/) {
    $sth->execute($_);

    while (my @row = $sth->fetchrow_array) {
        my ($firstname, $lastname) = @row;

        say "$firstname $lastname";
    }
}