/ 
SQL-Interpol-1.104
River stage one • 1 direct dependent • 1 total dependent
/ SQL::Interpol

NAME

SQL::Interpol - interpolate Perl variables into SQL statements

SYNOPSIS

use SQL::Interpol ':all';

my ($sql, @bind) = sql_interp 'INSERT INTO table', \%item;
my ($sql, @bind) = sql_interp 'UPDATE table SET',  \%item, 'WHERE y <> ', \2;
my ($sql, @bind) = sql_interp 'DELETE FROM table WHERE y = ', \2;

# These two select syntax produce the same result
my ($sql, @bind) = sql_interp 'SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v;
my ($sql, @bind) = sql_interp 'SELECT * FROM table WHERE', {x => $s, y => \@v};

DESCRIPTION

This module converts SQL fragments interleaved with variable references into one regular SQL string along with a list of bind values, suitable for passing to DBI. This makes database code easier to read as well as easier to write, while easily providing ready access to all SQL features.

SQL::Interpol is a drop-in replacement for most of SQL::Interp. (Some features have been removed; please refer to the changelog.)

INTERFACE

The recommended way to use SQL::Interpol is via its DBIx::Simple integration, which provides an excellent alternative to plain DBI access:

use DBIx::Simple::Interpol;
# ...
my $rows = $db->iquery( '
    SELECT title
    FROM threads
    WHERE date >', \$x, '
    AND subject IN', \@subjects, '
' )->arrays;

The iquery method integrates "sql_interp" directly into DBIx::Simple. Note that this requires loading DBIx::Simple::Interpol instead of (or after) DBIx::Simple, as its native integration will use SQL::Interp otherwise.

sql_interp

($sql, @bind) = sql_interp @params;

This function rearranges the list of elements it is passed, returning it as an SQL string with placeholders plus a corresponding list of bind values, suitable for passing to DBI.

The interpolation list can contain elements of these types:

SQL

A plain string containing an SQL fragment such as SELECT * FROM mytable WHERE.

Variable reference

A scalarref, arrayref, or hashref referring to data to interpolate between the SQL.

Another interpolation list

An interpolation list can be nested inside another interpolation list. This is possible with the "sql" function.

Interpolation Examples

The following variable names will be used in the below examples:

$sref  = \3;                      # scalarref
$aref  = [1, 2];                  # arrayref
$href  = {m => 1, n => undef};    # hashref
$hv = {v => $v, s => $$s};        # hashref containing arrayref
$vv = [$v, $v];                   # arrayref of arrayref
$vh = [$h, $h];                   # arrayref of hashref

Let $x stand for any of these.

Default scalarref behavior

A scalarref becomes a single bind value:

IN:  'foo', $sref, 'bar'
OUT: 'foo ? bar', $$sref

Default hashref behavior

A hashref becomes a logical AND:

IN:  'WHERE', $href
OUT: 'WHERE (m=? AND n IS NULL)', $h->{m},

IN:  'WHERE', $hv
OUT: 'WHERE (v IN (?, ?) AND s = ?)', @$v, $$s

Default arrayref of (hashref or arrayref) behavior

This is not commonly used.

IN:  $vv
OUT: '(SELECT ?, ? UNION ALL SELECT ?, ?)',
        map {@$_} @$v

IN:  $vh
OUT: '(SELECT ? as m, ? as n UNION ALL
          SELECT ?, ?)',
        $vh->[0]->{m}, $vh->[0]->{n},
        $vh->[1]->{m}, $vh->[1]->{n}

# Typical usage:
IN: $x
IN: $x, 'UNION [ALL|DISTINCT]', $x
IN: 'INSERT INTO mytable', $x
IN: 'SELECT * FROM mytable WHERE x IN', $x

Context ('IN', $x)

A scalarref or arrayref can used to form an IN clause. As a convenience, a reference to an arrayref is also accepted. This way, you can simply provide a reference to a value which may be a single-valued scalar or a multi-valued arrayref:

IN:  'WHERE x IN', $aref
OUT: 'WHERE x IN (?, ?)', @$aref

IN:  'WHERE x IN', $sref
OUT: 'WHERE x IN (?)', $$sref

IN:  'WHERE x IN', []
OUT: 'WHERE 1=0'

IN:  'WHERE x NOT IN', []
OUT: 'WHERE 1=1'

Context ('INSERT INTO tablename', $x)

IN:  'INSERT INTO mytable', $href
OUT: 'INSERT INTO mytable (m, n) VALUES(?, ?)', $href->{m}, $href->{n}

IN:  'INSERT INTO mytable', $aref
OUT: 'INSERT INTO mytable VALUES(?, ?)', @$aref;

IN:  'INSERT INTO mytable', $sref
OUT: 'INSERT INTO mytable VALUES(?)', $$sref;

MySQL's REPLACE INTO is supported the same way.

Context ('SET', $x)

IN:  'UPDATE mytable SET', $href
OUT: 'UPDATE mytable SET m = ?, n = ?', $href->{m}, $href->{n}

MySQL's ON DUPLICATE KEY UPDATE is supported the same way.

Context ('FROM | JOIN', $x)

This is not commonly used.

IN:  'SELECT * FROM', $vv
OUT: 'SELECT * FROM
     (SELECT ?, ? UNION ALL SELECT ?, ?) as t001',
     map {@$_} @$v

IN:  'SELECT * FROM', $vh
OUT: 'SELECT * FROM
     (SELECT ? as m, ? as n UNION ALL SELECT ?, ?) as temp001',
     $vh->[0]->{m}, $vh->[0]->{n},
     $vh->[1]->{m}, $vh->[1]->{n}

IN:  'SELECT * FROM', $vv, 'AS t'
OUT: 'SELECT * FROM
     (SELECT ?, ? UNION ALL SELECT ?, ?) AS t',
     map {@$_} @$v

# Example usage (where $x and $y are table references):
'SELECT * FROM', $x, 'JOIN', $y

Other Rules

Whitespace is automatically added between parameters:

IN:  'UPDATE', 'mytable SET', {x => 2}, 'WHERE y IN', \@colors;
OUT: 'UPDATE mytable SET x = ? WHERE y in (?, ?)', 2, @colors

Variables must be passed as references; otherwise, they will processed as SQL fragments and interpolated verbatim into the result SQL string, negating the security and performance benefits of binding values.

In contrast, any scalar values inside an arrayref or hashref are by default treated as binding variables, not SQL. The contained elements may be also be "sql".

sql

sql_interp 'INSERT INTO mytable',
    {x => $x, y => sql('CURRENT_TIMESTAMP')};
# OUT: 'INSERT INTO mytable (x, y) VALUES(?, CURRENT_TIMESTAMP)', $x

This function is useful if you want to use raw SQL as the value in an arrayref or hashref.

PHILOSOPHY

The query language is SQL. There are other modules, such as SQL::Abstract, that hide SQL behind method calls and/or Perl data structures (hashes and arrays). The former may be undesirable in some cases since it replaces one language with another and hides the full capabilities and expressiveness of your database's native SQL language. The latter may load too much meaning into the syntax of {}, [] and \, thereby rendering the meaning less clear:

SQL::Abstract example:
%where = (lname => {like => '%son'},
          age   => {'>=', 10, '<=', 20})
Plain SQL:
"lname LIKE '%son' AND (age >= 10 AND age <= 20)"

In contrast, SQL::Interpol does not abstract away your SQL but rather makes it easier to interpolate Perl variables into it. Now, SQL::Interpol does overload some meaning into {}, [] and \, but the aim is to make common obvious cases easier to read and write — and leave the rest to raw SQL.

This also means SQL::Interpol does not need to support every last feature of each particular dialect of SQL: if you need one of these, just use plain SQL.

LIMITATIONS

Some types of interpolation are context-sensitive and involve examination of your SQL fragments. The examination could fail on obscure syntax, but it is generally robust. Look at the examples to see the types of interpolation that are accepted. If needed, you can disable context sensitivity by inserting a null-string before a variable.

"SET", "", \$x

A few things are just not possible with the 'WHERE', \%hashref syntax, so in such case, use a more direct syntax:

# ok--direct syntax
sql_interp '...WHERE', {x => $x, y => $y}, 'AND y = z';
# bad--trying to impose a hashref but keys must be scalars and be unique
sql_interp '...WHERE',
    {sql($x) => sql('x'), y => $y, y => sql('z')};

In the cases where this module parses or generates SQL fragments, this module should work for many databases, but is known to work well on MySQL and PostgreSQL.

AUTHOR

Aristotle Pagaltzis <pagaltzis@gmx.de>

Documentation by David Manura and Mark Stosberg.

COPYRIGHT AND LICENSE

This software is copyright (c) 2014 by Aristotle Pagaltzis. Its documentation is copyright (c) 2003–2005 by David Manura and copyright (c) 2006–2012 by Mark Stosberg.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.