Mojo::SQL

Safely generate and compose SQL statements from Perl.

use Mojo::SQL qw(sql);

# {text => 'SELECT * FROM users WHERE name = $1', values => ['sebastian']}
my $query = sql('SELECT * FROM users WHERE name = ?', 'sebastian')->to_query;

To prevent SQL injection attacks, every ? in the input becomes a placeholder in the generated query, with the corresponding value bound to it. Partial statements can even be used recursively to build more complex queries.

my $role    = 'admin';
my $partial = sql('AND role = ?', $role);
my $name    = 'root';

# {text => 'SELECT * FROM users WHERE name = $1 AND role = $2', values => ['root', 'admin']}
my $query = sql('SELECT * FROM users WHERE name = ? ?', $name, $partial)->to_query;

Make partial statements optional to dynamically generate WHERE clauses.

my $optional = $foo ? sql('AND foo IS NOT NULL') : sql('');
my $query    = sql('SELECT * FROM users WHERE name = ? ?', 'sebastian', $optional)->to_query;

And if you need a little more control over the generated SQL query, you can also bypass safety features with sql_unsafe. But make sure to handle unsafe values yourself with appropriate escaping functions for your database. For PostgreSQL there are escape_literal and escape_identifier functions included with this module.

use Mojo::SQL qw(sql sql_unsafe escape_literal);

my $role    = 'role = ' . escape_literal('power user');
my $partial = sql_unsafe('AND ?', $role);
my $name    = 'root';

# {text => "SELECT * FROM users WHERE name = $1 AND role = 'power user'", values => ['root']}
my $query = sql('SELECT * FROM users WHERE name = ? ?', $name, $partial)->to_query;

For databases that do not support numbered placeholders like $1 and $2, you can set a custom character with the placeholder option.

# {text => 'SELECT * FROM users WHERE name = ?', values => ['root']}
my $query = sql('SELECT * FROM users WHERE name = ?', 'root')->to_query({placeholder => '?'});

Installation

All you need is Perl 5.20 or newer.

$ cpanm -n Mojo::SQL

We recommend the use of a Perlbrew environment.