Prevent SQL Injection in Perl DBI with Parameterized Queries

The problem: a single interpolated variable

Imagine a login check written like this:

my $username = $cgi->param('user');
my $sth = $dbh->prepare("SELECT * FROM users WHERE username = '$username'");
$sth->execute;

If a user submits admin' -- as the username, the resulting SQL becomes SELECT * FROM users WHERE username = 'admin' --'. The comment marker neutralizes the rest of the query, and the application may log the user in without a password. This is SQL injection, and string interpolation is the vector.

The practical takeaway is simple: never build SQL by embedding variables. Use Perl DBI placeholders instead.

What placeholders do

DBI (Perl Database Interface) separates the SQL statement from the values it needs. You write the statement with placeholders—usually question marks—and pass the actual values to execute. The DBD driver (the database-specific module beneath DBI, such as DBD::Pg for PostgreSQL or DBD::mysql for MySQL) transmits the statement and parameters separately, so the database receives the data as literal values, not executable SQL.

Positional placeholders look like this:

my $sth = $dbh->prepare("SELECT id, email FROM users WHERE username = ? AND active = ?");
$sth->execute($username, 1);

Worked example: a safe lookup script

Run this from a terminal where Perl can load DBI and the appropriate DBD driver. It assumes a database account with read access to the target table; do not hardcode passwords in version control.

#!/usr/bin/env perl
use strict;
use warnings;
use DBI;

my $dsn      = "dbi:Pg:dbname=appdb;host=localhost";
my $db_user  = $ENV{DB_USER} // 'app_reader';
my $db_pass  = $ENV{DB_PASS} // die "set DB_PASS";

my $dbh = DBI->connect($dsn, $db_user, $db_pass, {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
});

my $lookup = $ARGV[0] // die "usage: $0 ";

my $sth = $dbh->prepare("SELECT id, username, email FROM users WHERE username = ?");
$sth->execute($lookup);

while (my $row = $sth->fetchrow_hashref) {
    print join("\t", @{$row}{qw(id username email)}), "\n";
}

$dbh->disconnect;

Expected check: pass a username containing a quote, such as O'Brien, and confirm the script returns the matching row instead of a syntax error. The placeholder binds the apostrophe as data, so the database does not terminate the string early.

Risk: RaiseError => 1 makes connection or query failures die with a DBI error. In production, catch those errors rather than exposing database details to end users.

Limitations and driver caveats

• Placeholders work for values, not identifiers. You cannot use a placeholder for a table or column name. If you need dynamic sorting, whitelist the allowed column names in Perl before interpolating the identifier.
• Named placeholders like :name are not supported by every DBD driver. Check your driver documentation before using that syntax.
• Some drivers have incomplete support for placeholders in clauses such as LIMIT or OFFSET, or for data types like arrays. Test your specific driver version.
• Mixing placeholders with inline values in the same statement reintroduces risk if any value is interpolated directly into the SQL string.

Verify the behavior

1. Check the installed driver: perl -MDBD::Pg -e 'print $DBD::Pg::VERSION, "\n"' (adjust for your driver).
2. Enable DBI tracing to see how the statement is sent: set DBI_TRACE or use $dbh->trace(2) and confirm parameters appear as bound values rather than interpolated text.
3. Review your codebase for prepare calls that concatenate variables into the SQL string and replace them with placeholders.

Closing

Parameterized queries are a baseline, not a complete security strategy. Combine them with strict, warnings, least-privilege database accounts, and input validation where appropriate. If your code still builds SQL with string interpolation, switch to placeholders before the next deploy.