/ 
FU-0.2
/ FU::SQL

NAME

FU::SQL - Small and Safe SQL Query Builder

EXPERIMENTAL

This module is still in development and there will likely be a few breaking API changes, see the main FU module for details.

SYNOPSIS

use FU::SQL;

my $data = { name => 'John', last_updated => RAW 'NOW()' };

my $upd = SQL 'UPDATE table', SET $data;

my $ins = SQL 'INSERT INTO table', VALUES $data;

my $sel = SQL 'SELECT id, name FROM table', WHERE { id => IN([1,2,3]) };

my($sql, @params) = $sel->compile;

DESCRIPTION

Compiling SQL

All functions listed under "Constructing SQL" return an object that can be passed to other construction functions or compiled into SQL and bind parameters. These objects support one method call:

($sql, $params) = $obj->compile(%options)

Compile an object into a SQL string and a (possibly empty) arrayref of bind parameters. The following options are supported:

placeholder_style => 'dbi' or 'pg'

Set the style to use for placeholders in the SQL string. When set to 'dbi' (default), placeholders are indicated with a single question mark. When set to 'pg', placeholders use PostgreSQL-style numbered variables instead. For example:

my $obj = SQL 'SELECT', 1, ',', 2;
my ($sql) = $obj->compile(placeholder_style => 'dbi');
# $sql = 'SELECT ?, ?'

($sql) = $obj->compile(placeholder_style => 'pg');
# $sql = 'SELECT $1, $2'

All DBI drivers support the 'dbi' method just fine, but you need to use 'pg' when your SQL is going to FU::Pg or Pg::PQ.

in_style => 'dbi' or 'pg'

Set the style to use for IN expressions, refer to the IN() function below for details.

Constructing SQL

All of the functions below return an object with a compile() method. All functions are exported by default.

SQL(@args)

Construct an SQL object by concatenating the given arguments. There are three types of supported arguments:

  1. String literals are interpreted as raw SQL fragments.

  2. Objects returned by other functions listed below are included as SQL fragments.

  3. Everything else is considered a bind parameter.

These rules allow for flexible SQL construction:

SQL 'SELECT 1';       # Raw SQL statement
SQL 'WHERE id =', 1;  # SQL with a bind parameter

my $fifteen = SQL('5 + ', 10);
SQL 'WHERE number =', $fifteen; # Composing SQL objects

There is some magic going on in order to differentiate between a string literal and other arguments. The rule is that anything that is builtin::created_as_string() and read-only (as per Internals::SvREADONLY()) is considered raw SQL. Regular variables, array elements and hash values are always writable:

my $x = 'SELECT 1';
SQL $x;  # BAD: $x is used as bind parameter instead

# Better:
my $x = SQL 'SELECT 1';
SQL $x;

Constants created with use constant are considered string literals, and there are probably plenty of other creative ways to end up with variables that may be considered a "string literal" by this module. Hash::Util, Scalar::Readonly, other modules and tied hashes or arrays all have the potential to create read-only strings, but I don't expect that these are commonly applied on untrusted user input.

In most cases, this heuristic should work out well. In the few cases where it doesn't, or when you're not entirely sure what kind of value you're dealing with, you can always use P() or RAW() to force an argument as bind parameter or SQL string.

P($val)

Return an object where $val is forced into a bind parameter, for example:

SQL 'WHERE name =', 'John';  # BAD, 'John' is a string literal

SQL 'WHERE name =', P 'John'; # Good, 'John' is now a parameter
RAW($sql)

Force the given $sql string to be included as SQL. For example:

# BAD:
my $tables = ['a', 'b', 'c'];
SQL 'SELECT * FROM', $tables[1];
# 'SELECT * FROM ?', that's a syntax error.

# Better:
SQL 'WHERE * FROM', RAW $tables[1];
# 'SELECT * FROM b'

Never use this function with untrusted input.

PARENS(@args)

Like SQL() but surrounds the expression by parens:

SQL 'WHERE x AND', PARENS('y', 'OR', 'z');
# 'WHERE x AND ( y OR z )'
INTERSPERSE($value, @args)

Concatenate @args with $value as separator. Same way as join() works for strings, but I had to come up with a different name because "join" tends to have a completely different meaning in the SQL world.

INTERSPERSE 'OR', 'true', 'false';
# 'true OR false'
COMMA(@args)

Short-hand for INTERSPERSE(',', @args).

AND(@conditions)

Construct an SQL expression to test that all given conditions are true. Returns '1=1' (i.e. true) if @conditions is an empty list.

AND 'x IS NOT NULL',
    SQL('id <>', $not_this_id);
# '( x IS NOT NULL ) AND ( id <> ? )'

AND;
# '1=1'
AND($hashref)

A special form of AND() that tests the given columns for equality instead. The keys of the hashref are interpreted as raw SQL and the values as bind parameters.

AND { id => 1, number => RAW 'random()', x => undef }
# '( id = ? ) AND ( number = random() ) AND ( x IS NULL )'
OR(@conditions)
OR($hashref)

Like AND() except OR. These return '1=0' (i.e. false) on an empty list.

WHERE(@conditions)
WHERE($hashref)

Like AND() but prefixed with 'WHERE'.

SET($hashref)

Construct a SET clause:

SQL 'UPDATE table', SET {
  name => 'John',
  last_updated => RAW('NOW()'),
};
# 'UPDATE table SET name = ? , last_updated = NOW()'
VALUES(@args)

Construct a VALUES clause, @args is interpreted as in SQL():

SQL 'INSERT INTO table (name, last_updated)', VALUES(P('John'), 'NOW()');
# 'INSERT INTO table (name, last_updated) VALUES ( ? , NOW() )'
VALUES($arrayref)

Same as VALUES(@args) but arguments are interpreted as bind parameters:

SQL 'INSERT INTO table (name, last_updated)', VALUES(['John', RAW 'NOW()']);
# 'INSERT INTO table (name, last_updated) VALUES ( ? , NOW() )'
VALUES($hashref)

Like VALUES($arrayref) but also constructs a list of column names from the hash keys:

SQL 'INSERT INTO table', VALUES {
  name => 'John',
  last_updated => RAW('NOW()'),
};
# Same as above examples

Note how this allows for re-using the same hashref with SET(), allowing for convenient insert-or-update:

my $data = {
  name => 'John',
  last_updated => RAW('NOW()'),
};
SQL 'INSERT INTO table', VALUES($data),
    'ON CONFLICT (name) DO UPDATE', SET($data);

(The bind parameters are duplicated though)

IN($arrayref)

Construct an IN() clause for matching an SQL expression against multiple values. This function results in different SQL depending on the in_style option given to compile(). The default 'dbi' style passes each value as a bind parameter:

SQL 'WHERE id', IN([1, 2, 3, 4]);
# 'WHERE id IN(?, ?, ?, ?)', parameters: 1, 2, 3, 4

The 'pg' style passes the entire array as a single bind parameter instead:

SQL 'WHERE id', IN([1, 2, 3, 4]);
# 'WHERE id = ANY(?)', parameter: [1, 2, 3, 4]

The 'pg' style allows for more efficient re-use of cached prepared statements, since the generated query does not depend on the number of values. Unfortunately, the only Postgres module that supports arrays as bind parameters that I am aware of is FU::Pg. This approach does not, as of writing, work with DBD::Pg or Pg::PQ.

Can be used in the $hashref versions of AND, OR and WHERE as well:

WHERE { id => IN([1, 2]) }
# 'WHERE id IN(?, ?)'

SEE ALSO

SQL::Interp and the many other related modules on CPAN. This module was heavily inspired by SQL::Interp, but differs in a few key areas:

  • SQL::Interp expects bind parameters to be passed as a scalar reference (e.g. \$x), but this is easy to forget and the result of forgetting to do so is an SQL injection vulnerability - the worst possible outcome. sql_interp_strict() was introduced in an attempt to provide a safer alternative, but that limits the flexibility of the query builder. This module instead attempts to identify string literals through some trickery and considers everything else a bind parameter, which is much less prone to accidental SQL injection.

  • SQL::Interp parses your input query in an attempt to guess the context for interpolation. While this has (to my surprise) always worked out well for anything I've written, it does feel a tad too magical for my taste. This module instead requires you to more explicitly state your intentions, while hopefully remaining as concise and readable.

  • SQL::Interp assigns various semantics to hashrefs and arrayrefs, which means those can't easily be used as bind parameters. Not at all a problem if you're using DBI - which doesn't support that anyway, but it can cause trouble with FU::Pg.

COPYRIGHT

MIT.

AUTHOR

Yorhel <projects@yorhel.nl>