/ 
SQL-Interpolate-0.31
River stage zero No dependents
/ SQL::Interpolate::Filter

NAME

SQL::Interpolate::Filter - Source filtering for SQL::Interpolate

SYNOPSIS

use SQL::Interpolate FILTER => 1, qw(:all);

($sql, @bind) = sql_interp sql[
    SELECT * FROM mytable WHERE color IN @colors
    AND y = $x OR {z => 3, w => 2}
];

($sql, @bind) = sql_interp sql[
    INSERT INTO table {
        color  => $new_color,
        shape  => $new_shape
        width  => $width,
        height => $height,
        length => $length
    }
];

DESCRIPTION

This module adds source filtering capability to SQL::Interpolate. The source filtering option provides Perl an additional quote-like operator (see perlop) denoted sql//. The quote can contain SQL and Perl variables.

sql/SELECT * FROM mytable WHERE x = $x/;

Source filtering will transform this construct into a constructor for a macro object that simply contains the filtered interpolation list:

SQL::Interpolate::SQL->new("SELECT * FROM mytable WHERE x = ", \$x);

The macro expands into the contained interpolation list, which sql_interp (or dbi_interp) can then interpolate as usual:

"SELECT * FROM mytable WHERE x = ?", ($x)

Usage

To enable the quote-like sql// operator, add a "FILTER => 1" to your use statement:

use SQL::Interpolate FILTER => 1, qw(:all);

Just as it is possible to do with q// or qq// operators, you can use various delimiters on the sql// operator, such as

sql[SELECT * from mytable WHERE x = $x]
sql(SELECT * from mytable WHERE x = $x)
sql<SELECT * from mytable WHERE x = $x>
sql/SELECT * from mytable WHERE x = $x/

SQL:::Interpolate::SQL objects (and sql// operators representing them) come with a string concatenation operator (.), so you can do things like

sql[
  SELECT partnum, desc, price, stock
  FROM inventory
] . $show_all ? sql[] : sql[WHERE price > $price AND stock IN $stocks]

Security notes

An sql// object concatenated with a string will append the string verbatim into your result SQL. Future versions of SQL::Interpolate may throw an error if one attempts to concatenate an sql// object with a string. It's a better idea to do this if you must:

$dbx->do(sql[UPDATE mytable SET y = 0 WHERE x = ] . sql[$name]);

or simply

$dbx->do(sql[UPDATE mytable SET y = 0 WHERE x = $name]);

because then the $name value will be properly interpolated.

Examples

INPUT:  sql[WHERE one=$x AND $y]
OUTPUT: "WHERE one=", \$x, " AND ", $y

INPUT:  sql[INSERT INTO mytable @x]
OUTPUT: "INSERT INTO mytable ", \@x

INPUT:  sql[INSERT INTO mytable [1, 2]]
OUTPUT: "INSERT INTO mytable ", [1, 2]

INPUT   sql[INSERT INTO mytable %x]
OUTPUT: "INSERT INTO mytable ", \%x

INPUT:  sql[INSERT INTO mytable {one => 1, two => 2}]
OUTPUT: "INSERT INTO mytable ", {one => 1, two => 2}

Exports and Use Parameters

TRACE_FILTER

To enable tracing on the source code filter, do

use SQL::Interpolate TRACE_FILTER => 1, FILTER => 1;

The source code of the module after source filtering will be sent to STDERR.

...
SQL::Interpolate::SQL->new(qq[SELECT * FROM mytable WHERE x = ], \$x)
...

DESIGN NOTES

Limitations / characteristics

Source filtering is somewhat experimental and has the potential to give unexpected results because lexing Perl is hard. The module relies on Text::Balanced for the lexing. Even though Text::Balanced makes a valiant effort, the task is difficult and the results not always precise, especially for very obscure Perl constructs. It should work fine though on many things. If in doubt, check the output yourself by enabling the TRACE_FILTER option.

Proposed enhancements

Support Text::Balanced 2.0 and improved Perl lexing.

Should a distinction be made between q// v.s. qq// for sql//? Which semantics should sql// have?

How should Perl variables containing SQL literals (rather than than binding variables) be interpolated? Maybe with a stringified macro? e.g. sql/...LITERAL($x).../. Should stringified macros be allowed in the interpolated SQL literal? (probably no for security).

Variables inside a stringified macro probably prevents the macro from being un-stringified. e.g. sql/...MYMACRO($x).../ --> "...MYMACRO(", \$x, ")..."

LEGAL

Copyright (c) 2004-2005, David Manura. 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.

SEE ALSO

Other modules in this distribution: SQL::Interpolate, SQL::Interpolate::Macro, DBIx::Interpolate.

Dependent: Text::Balanced, Filter::Simple.