How can I use placeholders for variadic SQL functions with Perl's DBI?

Question

I don't know if "variadic" is actually the right word, but I'm talking about things that can take a list of values, like IN(). If you've been working with DBI for long, you've probably tried to do this:

(Note: All examples extremely simplified for brevity)

my $vals = join ', ', @numbers;
my $sth = $dbh->prepare( "SELECT * FROM mytbl WHERE foo IN( ? )" );
$sth->execute( $vals );     # doesn't work

DBI placeholders simply don't support these kinds of shenanigans, it's a single value for each ? or nothing, as far as I know.

This leads me to end up doing something like:

my $sth = $dbh->prepare( "SELECT * FROM mytbl WHERE foo IN ( $vals )" );

which isn't so horrible, but consider a function, like one I wrote today, that has to accept some arbitrary SQL with an IN clause and a list of values

sub example { 
    my $self = shift;
    my ( $sql, @args ) = @_;

    my $vals = join ', ', @args;
    $sql =~ s/XXX/$vals/;    <---- # AARRRGHGH
    my $sth = $self->dbh->prepare( $sql );
    ...
}

This ends up getting called by stuff that looks like

my $sql = "SELECT * FROM mytbl WHERE foo IN( XXX ) AND bar = 42 ORDER BY baz";
my $result = $self->example( $sql, @quux );

This really offends my sense of aesthetics. Building custom SQL programmaticly is a big enough pain as it is; I don't want to go down the road of regexing my SQL strings if I don't have to.

Is there a better way?

Answer 1

Food for thought.

DBIx::Simple offers a syntax for this type of thing using a double-question mark placeholder:

$db->query( 'SELECT * FROM mytbl WHERE foo IN ( ?? )', @args );

Also, SQL::Abstract is powerful, but I find sometimes the abstractions don't result in optimal SQL.

Answer 2

Why not:

  my $sql = "SELECT * FROM mytbl WHERE foo IN(" . join(',', ('?')x@quux) . ") AND bar = 42 ORDER BY baz";
  my $sth = $dbh->prepare($sql);
  $sth->execute(@quux);

Answer 3

sprintf is handy in such situations:

my $sth = $dbh->prepare( 
    sprintf(
        'SELECT * FROM mytbl WHERE foo IN( %s )',
        join(',', ('?') x @numbers) )
);

Answer 4

If using placeholders and bind values gets clumsy, there's always DBI::quote().

my $sql = sprintf 'SELECT * FROM mytabl WHERE foo IN ( %s )',
     join( ',', map { $dbh->quote( $_ ) } @args );

Answer 5

If you don't mind breaking from pure DBI and using some modules, I'd take a look at SQL::Abstract for your example. SQL::Abstract can take a Perl hash and turn it into a where clause.

my $sql  = SQL::Abstract->new;
my @numbers = (1 .. 10);
my ($stmt, @bind) = $sql->where({foo => {'in', \@numbers}});
# $stmt is " WHERE ( foo IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) )"
# @bind contains the values 1 through 10.