NAME

SQL::Interp - Interpolate Perl variables into SQL statements

SYNOPSIS

use SQL::Interp ':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

SQL::Interp converts a list of intermixed SQL fragments and variable references into a conventional SQL string and list of bind values suitable for passing onto DBI. This simple technique creates database calls that are simpler to create and easier to read, while still giving you full access to custom SQL.

SQL::Interp properly binds or escapes variables. This recommended practice safeguards against "SQL injection" attacks. The DBI documentation has several links on the topic.

Besides the simple techniques shown above, The SQL::Interp integrates directly with DBIx::Simple for an excellent alternative to raw DBI access:

use DBIx::Simple;

...

my $rows = $db->iquery("
    SELECT title
        FROM threads
        WHERE date > ",\$x," AND subject IN ",\@subjects
 )->arrays;

Since DBIx::Simple still allows you complete access to the DBI API, using it as wrapper is recommended for most applications.

The One Function You Really Need

`sql_interp`

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

sql_interp() is the one central function you need to know. sql_interp() strings together the given list of elements and returns both an SQL string ($sql) with placeholders ("?") and a corresponding list of bind values (@bind) suitable for passing to DBI.

The interpolation list can contain elements of these types:

* SQL - string containing a raw SQL fragment such as 'SELECT * FROM mytable WHERE'.

* variable reference - scalarref, arrayref, hashref, or A sql_type() object referring to data to interpolate between the SQL.

* other 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

Context ('ARRAY', $x)

A scalarref or arrayref can be turned into an array value. Such values are supported by PostgreSQL.

IN:  'SELECT ARRAY', $aref
OUT: 'SELECT ARRAY[?, ?]', @$aref

IN:  'SELECT ARRAY', $sref
OUT: 'SELECT ARRAY[?]', $$sref

IN:  'SELECT ARRAY', []
OUT: 'SELECT ARRAY[]'

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_type() or sql().

Security: sql_interp_strict

The sql_interp function has a security weakness. Consider these two statements, one easily a typo of the other:

sql_interp("SELECT * FROM foo WHERE a = ",\$b)
sql_interp("SELECT * FROM foo WHERE a = ",$b)

Both would produce valid SQL, but the first would be secure due to use of bind variables, while the second is potentially insecure, because $b is added directly to the SQL statement. If $b contains a malicious value, it could be used for a SQL injection attack.

To prevent this accident, we also supply sql_interp_strict(), which works exactly the same as sql_interp(), but with an additional check that two non-references never appear in a row . If they do, an exception will be thrown.

This does mean some previously safe-but-valid SQL be need to be rewritten, such as when you are building a complex query from pieces. Here's a contrived example:

sql_interp("SELECT * FROM ","foo","WHERE a = ",\$b);

To work under strict mode, you need to concatenate the strings instead:

sql_interp("SELECT * FROM "."foo"."WHERE a = ",\$b);

Note that strict mode only checks the immediate arguments of sql_interp_strict(), it does not check nested interpolation using the sql() function (described below). Thus, the previous example can also be written as:

sql_interp(sql("SELECT * FROM ","foo","WHERE a = "),\$b);

A Couple Helper Functions You Sometimes Need

`sql()`

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

sql() is useful if you want insert raw SQL as a value in an arrayref or hashref.

`sql_type()`

my $sqlvar = sql_type($value_ref, type => $sql_type, %params);

sql_type() provides a general way to represent a binding variable along with metadata. It is necessary in rare applications which you need to explicity give the bind type of a SQL variable.

$value_ref - variable reference contained

$sql_type - any DBI SQL_DATA_TYPE (e.g. SQL_INTEGER). Optional. Default is undef.

Any other named parameters (%params) passed in will be saved into the object as attributes.

sql_type objects are useful only in special cases where additional information should be tagged onto the variable. For example, DBI allows bind variables to be given an explicit type:

my ($sql, @bind) = sql_interp 'SELECT * FROM mytable WHERE',
    'x=', \$x, 'AND y=', sql_type(\$y, SQL_VARCHAR), 'AND z IN',
    sql_type([1, 2], SQL_INTEGER);
# RESULT: @bind =
#   ([$x, sql_type(\$x)], [$y, sql_type(\$y, type => SQL_VARCHAR)],
#    [1, sql_type([1, 2], type => SQL_INTEGER)],
#    [2, sql_type([1, 2], type => SQL_INTEGER)]);

my $idx = 1;
for my $var (@bind) {
    $sth->bind_param($idx++, $var->[0], $var->[1]->{type});
}
$sth->execute();
my $ret = $sth->selectall_arrayref();

If the interpolation list contains at least one sql_type object, then all the variable references are transparently converted into sql_type objects, and the elements of @bind take a special form: an arrayref consisting of the bind value and the sql_type object that generated the bind value. Note that a single sql_type holding an aggregate (arrayref or hashref) may generate multiple bind values.

Enabling debugging output

To have the generated SQL and bind variables sent to STDOUT, you can set the environment variable TRACE_SQL to "1"

TRACE_SQL=1 perl my_script.pl

Here's some example output:

DEBUG:interp[sql=INSERT INTO mytable VALUES(?),bind=5]

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   => [-and => {'>=', 10}, {'<=', 20}])
Plain SQL:
"lname LIKE '%son' AND (age >= 10 AND age <= 20)"

In contrast, SQL::Interp does not abstract away your SQL but rather makes it easier to interpolate Perl variables into your SQL. Now, SQL::Interp does load some meaning into "{, "[" and "\", but we try to limit its use to obvious cases. Since your raw SQL is exposed, you can use your particular dialect of 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, and if doubt, examine the SQL output yourself with the TRACE_SQL environment variable set. 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_type(\$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 its been tested mostly on MySQL and PostgreSQL. Please inform the author of any incompatibilities.

Contributor and Contributing

David Manura is the author of the original SQL::Interpolate, Mark Stosberg (http://mark.stosberg.com/) created the SQL::Interp fork. It is now maintained by Yoran Heling (https://yorhel.nl/).

Also thanks to: Mark Tiefenbruck (syntax), Wojciech Pietron (Oracle compat), Jim Chromie (DBIx::Interp idea), Juerd Waalboer, Terrence Brannon (early feedback), and others.

The SQL::Interp Git repository is hosted at https://code.blicky.net/yorhel/SQL-Interp.

License

This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself. See http://www.perl.com/perl/misc/Artistic.html.

SQL::Abstract shares with SQL::Interp the purpose of making SQL generation easier. SQL::Abstract differs in that it expresses queries in terms of OO method calls. It's syntax may impair readability because it uses the subtle difference between a brace and bracket to denote the difference between AND and OR in a query (the user can change whether a bracket implies "AND" or "OR"). Some complex where clauses are difficult or impossible with SQL::Abstract. SQL::Interp gives the author more direct access to the underlying SQL. This permits using the full expressivity of the database query language.

DBIx::Simple

DBIx::Simple strives to simplify SQL generation as well as the data structures returned from DBI. DBIx::Simple currently can use SQL::Interp to help generate SQL.

Class::DBI

Class::DBI is a popular "complete" solution for abstract database access through an OO interface. It currently has a plugin called Class::DBI::AbstractSearch that allows it to use SQL::Abstract to generate SQL. It's possible that SQL::Interp could be integrated with it as well.

SQL::Preproc

SQL::Preproc provides an "embedded SQL" approach where the Perl and SQL languages are extended (via source filtering) to support interwoven Perl and SQL. The syntax supports interpolating Perl variables into SQL and passing query results back into Perl variables. In contrast, SQL::Interp extends neither SQL nor Perl, and it deals only with interpolating Perl variables into queries, whereas returning variables from queries is the job of something like DBI, DBIx::Interp, or DBIx::Simple.

SQL::String

SQL::String shares a number of similiarities to SQL::Interp but it is more rudimentary. Both let you combine "chunks" of SQL that have their parameters attached to them and then transform it into an SQL string and list of bind parameters suitable for passing to DBI.

SQL::KeywordSearch

SQL::KeywordSearch helps generate SQL for a keyword search. It can return the result in a format compatible with SQL::Interp to become part of a larger query.

To install SQL::Interp, copy and paste the appropriate command in to your terminal.

cpanm

cpanm SQL::Interp

CPAN shell

perl -MCPAN -e shell
install SQL::Interp

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

The One Function You Really Need

`sql_interp`

Default scalarref behavior

Default hashref behavior

Default arrayref of (hashref or arrayref) behavior

Context ('IN', $x)

Context ('INSERT INTO tablename', $x)

Context ('SET', $x)

Context ('FROM | JOIN', $x)

Context ('ARRAY', $x)

Other Rules

Security: sql_interp_strict

A Couple Helper Functions You Sometimes Need

`sql()`

`sql_type()`

Enabling debugging output

Philosophy

Limitations

Contributor and Contributing

License

See Also

Fork

Other modules in this distribution

SQL::Abstract

DBIx::Simple

Class::DBI

SQL::Preproc

SQL::String

SQL::KeywordSearch

NAME

SYNOPSIS

DESCRIPTION

The One Function You Really Need

sql_interp

Default scalarref behavior

Default hashref behavior

Default arrayref of (hashref or arrayref) behavior

Context ('IN', $x)

Context ('INSERT INTO tablename', $x)

Context ('SET', $x)

Context ('FROM | JOIN', $x)

Context ('ARRAY', $x)

Other Rules

Security: sql_interp_strict

A Couple Helper Functions You Sometimes Need

sql()

sql_type()

Enabling debugging output

Philosophy

Limitations

Contributor and Contributing

License

See Also

Fork

Other modules in this distribution

Related modules

SQL::Abstract

DBIx::Simple

Class::DBI

SQL::Preproc

SQL::String

SQL::KeywordSearch

Module Install Instructions

`sql_interp`

`sql()`

`sql_type()`