SUMMARY: SQL::Interpolate is a Perl module that provides a simplified, more natural, and less error-prone syntax for writing SQL statements containing interpolated variables. SQL::Interpolate converts your simplified syntax into a correctly formatted SQL string, with placeholders, along with a list of binding values. These result values can then be passed to DBI or used for another purpose. SQL::Interpolate serves a purpose similar to that of SQL::Abstract <http://search.cpan.org/%7Enwiger/SQL-Abstract/Abstract.pm> except that SQL::Interpolate still exposes and utilizes the full native SQL syntax of your database.
Portion of the synopsis:
============ # with source filtering
use SQL::Interpolate FILTER => 1, qw(:all); use DBIx::Interpolate;
my @colors = ('blue', 'green');
my $rcolors = [EMAIL PROTECTED];
my ($start, $count) = (5, 10);
my $x = 5; my $rows = $dbh->selectall_arrayref( sql[
SELECT * FROM table
WHERE color IN @colors
OR color IN ['green', 'red', @colors]
AND color IN $rcolors
AND d = $x
LIMIT(start => $start, count => $count*2)
] );# without source filtering:
$dbh->do(dbi_interp qq[
UPDATE table SET ],
{ color => $new_color,
shape => $new_shape }, qq[
WHERE color <> ], \$color
);============
Notable points:
- Optional source filtering (via Filter::Simple) provides an string-like sql// operator that interpolates variables into SQL as binding parameters according SQL syntax. Each sql// phrase internall maps to an object that overrides the concatenation operator (so you can even do sql[...] . $hashref . sql[...]).
- dbi_interp is an "adaptor function" that converts a list of SQL strings, variable references for interpolation, sql// objects, and macro objects into a single SQL string with a list of binding values suitable for passing into the various DBI methods. DBIx::Interpolate is a wrapper around DBI that eliminates the need for this adaptor function.
For all the details, including manual and download, see
http://www.math2.org/david/sql-interpolate/
I also have an idea of creating a module named "HTML::Interpolate." This will safely but conveniently interpolate variables into HTML via the CGI::escapeHTML method. Like SQL::Interpolate, it might also optionally support a new string-like operator via source filtering, so you might do
use SQL::Interpolate FILTER => 1; use CGI;
my $q = new CGI;
my $name = $q->param('name');
my $username = $q->param('username'); my $html = html{
<html><body>
Hello, <b>$name</b>.
<form method="post" action="test.pl">
<input type="text" name="username" value="$username">
<input type="submit">
</form>
<a href="test2.pl?page=$page_id">test</a>
</body></html>
};Note that $page_id and $username must be escaped differently (the choice of escaping method possibly could be made transparent to the programmer with some HTML analysis). There might even be some escape characters to selectively disable/enable HTML escaping:
my $html = html{ .... \N$header_html .... $username}
There are some related modules out there as well:
"Perl6::Interpolators" - Supports Perl 6 function-interpolation syntax (http://dev.perl.org/perl6/rfc/222.html) via Filter::Simple. This is interestingly referred to in the POD as "Perl6::Interpolate".
"Interpolatation" - somewhat also addresses RFC222 by representing function calls instead using tied hashes, which do natively interpolate into Perl5 strings. You also must predeclare the functions you intend to interpolate, which is not ideal. Interestingly, they do mention an SQL example, but the design makes it much less agile than SQL::Interpolate.
"String::Interpolate" - Wrapper for builtin the Perl5 interpolatation engine.
"Math::Interpolate" / "Perl::Interpolate" - The word "Interpolate" is used in a much different (mathematical) context.
So, there seems like three main ways out there to implement string interpolation:
- use tied hashes
- use source filtering (e.g. Filter::Simple)
- use a function call on a list (e.g. as done with SQL::Interpolate with source filtering disabled: dbi_interp "SELECT * FROM mytable WHERE X =", \$x);
In the second case, the problem seems like it can be generalized. There exists a source filtering module called Sub::Quotelike that provides a generic interface for defining custom string-like operators. However, this could be more robust, and it wasn't suitable for SQL::Interpolate. For example, Sub::Quotelike scopes interpolated variables as inside your custom function rather than as within the same scope of the string--the latter more resembles what Perl does with its builtin variable-string interpolation: my $x = 5; print "test $x";.
-davidm
