SQL::Interpolate::Filter - Source filtering for SQL::Interpolate
# This first line enables source filtering. 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 } ]; # Each result above is suitable for passing to DBI: my $res = $dbh->selectall_arrayref($sql, undef, @bind);
This module adds source filtering capability to the SQL::Interpolate and DBIx::Interpolate modules. 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 an sql() object containing the filtered interpolation list:
sql("SELECT * FROM mytable WHERE x = ", \$x);
which sql_interp (or dbi_interp) can then interpolate as usual:
sql_interp
dbi_interp
"SELECT * FROM mytable WHERE x = ?", ($x)
To enable the quote-like sql// operator, add a "FILTER => 1" to your use statement:
use SQL::Interpolate FILTER => 1, qw(:all); # or use DBIx::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() objects (and sql// string-like 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]
An sql// object concatenated with a string will append the string verbatim into your result SQL:
$dbx->do(sql[UPDATE mytable SET y = 0 WHERE x = ] . $name); # not good
Future versions of SQL::Interpolate may throw an error if one attempts to do this. If you want the value to bind, you must interpolate:
$dbx->do(sql[UPDATE mytable SET y = 0 WHERE x = ] . sql[$name]); # or $dbx->do(sql[UPDATE mytable SET y = 0 WHERE x = $name]);
INPUT: sql[WHERE one=$x AND $y] OUTPUT: sql("WHERE one=", \$x, " AND ", $y) INPUT: sql[INSERT INTO mytable @x] OUTPUT: sql("INSERT INTO mytable ", \@x) INPUT: sql[INSERT INTO mytable [1, 2]] OUTPUT: sql("INSERT INTO mytable ", [1, 2]) INPUT sql[INSERT INTO mytable %x] OUTPUT: sql("INSERT INTO mytable ", \%x) INPUT: sql[INSERT INTO mytable {one => 1, two => 2}] OUTPUT: sql("INSERT INTO mytable ", {one => 1, two => 2})
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::Filter::_make_sql(qq[SELECT * FROM mytable WHERE x = ], \$x) ...
This module depends on SQL::Interpolate, Filter::Simple (any), and Text::Balanced >= 1.87.
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.
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, ")..."
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.
Other modules in this distribution: SQL::Interpolate, SQL::Interpolate::Macro, DBIx::Interpolate.
Dependent: Text::Balanced, Filter::Simple.
To install SQL::Interpolate, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Interpolate
CPAN shell
perl -MCPAN -e shell install SQL::Interpolate
For more information on module installation, please visit the detailed CPAN module installation guide.