Re: SQL::Interpolate - request for comments

Jim Cromie Thu, 01 Jan 2004 12:54:21 -0800

David Manura wrote:

Yes, I shall shorten the name. I'm not certain if you mean something similar to what I'm thinking of when using the "get_authors : dbi_interp" syntax, but I am considering adding support for Filter::Simple to simplify the syntax even further as such:
use SQL::Interpolate FILTER => 1;
...
my $rows = $dbh->selectall_arrayref(dbi_interp qq[
    SELECT * FROM table
    WHERE color IN [EMAIL PROTECTED]
          AND y = {$x}
    LIMIT {[1, 10] }
]);

heres another possibility - without the attributes. With a wrapper class, you can make the interpolate function disappear altogether You can disregard the package alias games I played, that was a bit of hackery to alias the SqlStr package name to something else, like iDBI


use SqlStr (alias => 'iDBI');
my $dsn = "dbi:mysql:database=test;user=test;host=localhost;";
my $dbh = iDBI->connect($dsn, { RaiseError => 1, PrintError => 1 });
my $sth = $dbh->prepare('select * from foo');
$sth->execute;


package SqlStr;
use DBI; # wrap DBI, pretend we are one.

sub import {
   my ($pkg,%args) = @_;
   if ($args{alias}) {
   *{$args{alias}} = *{$pkg};
   *{$args{alias}.'::AUTOLOAD'} = *{$pkg.'::AUTOLOAD'};
   *{$args{alias}.'::connect'} = *{$pkg.'::connect'};
   }
}

# methods for which interpolation is appropriate
my @interpolatable = (qr/prepare/, qr/select/);

sub AUTOLOAD {
   my ($self, @args) = @_;
   (my $meth = $AUTOLOAD) =~ s/.*:://;
   return if $meth eq 'DESTROY';
   print "ima: ", ref $self, " wrapping: ", ref $$self, "\n";

   if (grep $meth =~ /$_/, @interpolatable) {
   $$self->$meth(interpolate(@args));
   } else {
   $$self->$meth(@args);
   }
}

sub connect { # DBIs new()
   my $pkg = shift;
   my $self = DBI->connect(@_);
   bless \$self, SqlStr;
}

sub interpolate {
   # do your Filter thing here.
   @_;
}

1;

__END__

[EMAIL PROTECTED] sql]$ perl try.pl ima: SqlStr wrapping: DBI::db DBD::mysql::st execute failed: Table 'test.foo' doesn't exist at try.pl line 11.

Other than that, this is an interesting package, and one Ill watch for.

My biggest concern is that all this interpolation could inadvertently be used to inject bad SQL into the statement, and the user, whose taking advantage of your module, would be unaware of such risks, hence more likely to not 'prevent' it. That said, your pod could 'educate' them on the subject, and you might be able to add some checks to help prevent it.

Id be more comfortable with things if you were to use placeholders more fully, tho there are places it wont work (like an IN @list).

Re: SQL::Interpolate - request for comments

Reply via email to