NAME

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

SYNOPSIS

  # 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);

DESCRIPTION

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:

  "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);  # 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]

Security notes

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]);

Examples

 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})

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::Filter::_make_sql(qq[SELECT * FROM mytable WHERE x = ], \$x)
 ...

DEPENDENCIES

This module depends on SQL::Interpolate, Filter::Simple (any), and Text::Balanced >= 1.87.

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.

cpanm SQL::Interpolate

perl -MCPAN -e shell
install SQL::Interpolate