SQL::Interpol - interpolate Perl variables into SQL statements
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};
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.)
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.
iquery
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:
A plain string containing an SQL fragment such as SELECT * FROM mytable WHERE.
SELECT * FROM mytable WHERE
A scalarref, arrayref, or hashref referring to data to interpolate between the SQL.
An interpolation list can be nested inside another interpolation list. This is possible with the "sql" function.
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.
$x
A scalarref becomes a single bind value:
IN: 'foo', $sref, 'bar' OUT: 'foo ? bar', $$sref
A hashref becomes a logical AND:
AND
IN: 'WHERE', $href OUT: 'WHERE (m=? AND n IS NULL)', $h->{m}, IN: 'WHERE', $hv OUT: 'WHERE (v IN (?, ?) AND s = ?)', @$v, $$s
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
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
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'
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.
REPLACE INTO
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.
ON DUPLICATE KEY UPDATE
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
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.
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.
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:
'WHERE', \%hashref
# 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.
Aristotle Pagaltzis <pagaltzis@gmx.de>
Documentation by David Manura and Mark Stosberg.
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.
To install SQL::Interpol, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Interpol
CPAN shell
perl -MCPAN -e shell install SQL::Interpol
For more information on module installation, please visit the detailed CPAN module installation guide.