DBI and variable numbers of bind parameters

friedo has asked for the wisdom of the Perl Monks concerning the following question:

When writing DBI code I use bind parameters religiously, however, an occasion came up recently where I wasn't sure about the best method for doing so. I have a "WHERE foo IN ( 1, 2, 3 )" type clause where the size of the "IN" list is variable. I came up with two ways of doing this, both of which work:

First way:

# @ins is actually calculated at runtime. 
# Defined here for demonstration.
my @ins = qw(1 2 3 4 5 6);
my $in = join ', ', @ins;
my $sth = $dbh->prepare("SELECT foo FROM Bar WHERE baz IN ( ? )");
$sth->execute( $in );
[download]

Second way:

my @ins = qw(1 2 3 4 5 6);
my $in;  $in .= '?, ' for @ins;

# discard hanging comma
$in =~ s/, $//;
my $sth = $dbh->prepare("SELECT foo FROM Bar WHERE baz in ( $in )");
$sth->execute( @ins );
[download]

From an elegance standpoint, it's nice to have each "IN" item be its own bind parameter, but I don't know if that's best for efficiency. With a single bind parameter, prepare_cached could be used since the rest of the statement doesn't change. On the other hand, I don't know if prepare_cached offers any advantage whatsoever when dealing with this type of query. If it matters, I'm using MySQL with InnoDB tables.

Comment on DBI and variable numbers of bind parameters Select or Download Code

Replies are listed 'Best First'.
Re: DBI and variable numbers of bind parameters by davidrw (Prior) on Oct 18, 2005 at 02:55 UTC
The second way is fine i think.. personally, i generally write it like this (easily extendable to handle conditional building of the statement): `my $sql = "SELECT foo FROM Bar WHERE 1=1"; my @bind; $sql .= " AND baz in ( " . join(',', map {'?'} @ins) . ") "; push @bind, @ins; $dbh->selectall_arrayref($sql, {}, @bind);` [download] Another way is to use something like SQL::Abstract: `use SQL::Abstract; my $SA = SQL::Abstract->new; my ($sql, @bind) = $SA->select('Bar', ['foo'], {baz => \@ins}); $dbh->selectall_arrayref($sql, {}, @bind);` [download]	[reply] [d/l] [select]
Re^2: DBI and variable numbers of bind parameters by diotalevi (Canon) on Oct 18, 2005 at 15:10 UTC
Between `( '?' ) x @ins` and `map { '?' } @ins`, the first is more efficient. There's no loop - it just creates the right number of elements in place.	[reply] [d/l] [select]
Re: DBI and variable numbers of bind parameters by graff (Chancellor) on Oct 18, 2005 at 04:51 UTC
I'm actually surprised that you say your first approach works. It doesn't seem to work for me, unless the string being passed for the single placeholder happens to contain only one field value: `my $in = "a,b"; my $sth = $dbh->prepare("SELECT foo FROM bar WHERE baz IN (?)"); $sth->execute( $in ); # fetch returns no results -- it would work if $in were just "a" my @in = qw/a b/; $sth = $dbh->prepare("SELECT foo FROM bar WHERE baz in (?,?)"); $sth->execute( @in ); # fetch returns expected results for "a" and "b"` [download] (I'm using perl 5.8.6 on freebsd, with mysql 4.0.21) Given that circumstance, I would definitely use the `join( ',', map {'?'} @ins` approach for putting together the text for the sql select statement (like davidrw showed in his reply).	[reply] [d/l] [select]
Re: DBI and variable numbers of bind parameters by Errto (Vicar) on Oct 18, 2005 at 04:13 UTC
One slightly more succint (perhaps "elegant" even) way of implementing this part of your second approach `my $in; $in .= '?, ' for @ins; # discard hanging comma $in =~ s/, $//;` [download] is to say `my $in = join ', ', ('?') x @ins;` [download] But actually I have a question on your first approach. Does it actually work? I've been trying it here, passing a string of comma separated numeric values as the bind value for a single placeholder in an IN clause, and in my case (MySQL 4.1, DBD::mysql 2.9007) it seems to just take the first value in the list. Can you post some more sample code and output to show how this works? I ask because it seems like it shouldn't work; that is, it shouldn't let you pass multiple values for a single placeholder.	[reply] [d/l] [select]
Re^2: DBI and variable numbers of bind parameters by friedo (Prior) on Oct 18, 2005 at 04:46 UTC
I've been trying it here, passing a string of comma separated numeric values as the bind value for a single placeholder in an IN clause, and in my case (MySQL 4.1, DBD::mysql 2.9007) it seems to just take the first value in the list. Oops! You're right -- I was mistaken. Looking back, I had confused two separate pieces of code when thinking about this problem. The code that made me think of the first method actually has `$in` inlined right into the SQL. Obviously that's no good! I guess that pretty much answers the question, then. <Emily Litella>Nevermiiiind!</ET> (And I didn't write that piece, I swear. :) But I am going to fix it post-haste.)	[reply] [d/l]
Re: DBI and variable numbers of bind parameters by dragonchild (Archbishop) on Oct 18, 2005 at 04:16 UTC
The problem with the first option is that you lose the major benefit of placeholders - security. When you use placeholders, you get a DBI->quote() call for you. That means that you (almost) never have to worry about SQL injection attacks. The second method is the proper way to go, period. See Re: best way to inline code? (i.e. macro) for a good example of how I write this type of code. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]
Re: DBI and variable numbers of bind parameters by pg (Canon) on Oct 18, 2005 at 02:52 UTC
Personally I prefer the first one, the one using place holder. "From an elegance standpoint, it's nice to have each "IN" item be its own bind parameter," Not true, for the elegance, one place holder for the entire in list is better than multiple ones (and one for each element). From an elegance point of view, you don't use dynamic query, unless there is absolutely no other (easier) way. In this case, in order to have the right number of place holders for the elements of the in list, you have to go with dynamic query, which is less perfect. One purpose of place holder is performance as you mentioned, it is not clear to me whether the latest MySQL actually started to use the true prepared query, but you probably should use it any way for two reasons: 1) other benefits that prepared query bring to table; 2) even if there is no performance benefit with the current MySQL implementation, I believe there will be.	[reply]
Re: DBI and variable numbers of bind parameters by perrin (Chancellor) on Oct 18, 2005 at 14:53 UTC
Your first way won't work. Use the way davidrw suggested, and use prepare_cached. It will give you an advantage if you call the query multiple times.	[reply]
Re: DBI and variable numbers of bind parameters by Anonymous Monk on Nov 25, 2013 at 15:34 UTC
it is better to replace the first example by `# @ins is actually calculated at runtime. # Defined here for demonstration. my @ins = qw(1 2 3 4 5 6); my $in = join ',', map { "?" } @ins; my $sth = $dbh->prepare("SELECT foo FROM Bar WHERE baz IN ( $in )"); $sth->execute( @ins );` [download]	[reply] [d/l]
A reply falls below the community's threshold of quality. You may see it by logging in.