Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Using multiple values in a SQL "in" statement

by perlvroom (Acolyte)
on May 01, 2013 at 21:04 UTC ( #1031647=perlquestion: print w/replies, xml ) Need Help??
perlvroom has asked for the wisdom of the Perl Monks concerning the following question:

Can someone tell me the best way to go about this? I've seen a few examples but i want to set it up with as little editing as possible so that if i need to add a new value, i just have to add the new value to the array.
my @id=(1,2,3,4); my $sth = $dbh1->prepare("SELECT name FROM table where id in ?") or d +ie "Couldn't prepare statement: " . $dbh1->errstr; $sth->execute(@id)

Replies are listed 'Best First'.
Re: Using multiple values in a SQL "in" statement
by scorpio17 (Abbot) on May 01, 2013 at 21:30 UTC
    my @id=(1,2,3,4); my $question_mark_string = join(',', ('?') x scalar(@id) ); # ?,?,?,? my $sth = $dbh1->prepare("SELECT name FROM table where id in ( $questi +on_mark_string )") or die "Couldn't prepare statement: " . $dbh1->err +str; $sth->execute(@id)

      I either use that logic, or when I'm lazy about typing:

      my $sth = $dbh1->prepare('SELECT name FROM table WHERE id IN ( ?'.(',?' x $#id).' )') or ...

      ... but I wouldn't recommend this if other people might be expected to actually maintain it.

      This worked great. thanks. Can you give me a brief explanation of what's happening at this line?  my $question_mark_string = join(',', ('?') x scalar(@id) );

        It's three operations. The x operator, is either a string or list multiplier. Here, it's being used on an list, as the item is in parens:

           ('?') x scalar(@id)

        scalar(@id) gives the number of items in the array. So we end up with a list of question marks the same size as the @id array.

        The join will flatten a list into a single string, placing the first argument in between each item. So we're placing a comma between each of the question marks.

Re: Using multiple values in a SQL "in" statement
by trwww (Priest) on May 02, 2013 at 07:59 UTC

    Use SQL::Abstract to write extendable code that builds SQL. This is the tool DBIx::Class uses, so it gets lots of support:

    $ cat use warnings; use strict; use Data::Dumper; use SQL::Abstract; my $sql = SQL::Abstract->new; my($stmt, @bind) = $sql->select( 'table', # table name [ 'name' ], # fields { id => { -in => [ 1, 2, 3, 4 ] } # the ids }, [ 'id' ] # order by ); print Data::Dumper->Dump( [ $stmt, \@bind ], [ qw( stmt bind ) ], ); my $sth = $dbh->prepare( $stmt ); $sth->execute( @bind ); $ perl $stmt = 'SELECT name FROM table WHERE ( id IN ( ?, ?, ?, ? ) ) ORDER B +Y id'; $bind = [ 1, 2, 3, 4 ]; ...
Re: Using multiple values in a SQL "in" statement
by runrig (Abbot) on May 01, 2013 at 22:53 UTC
    Why check the status of prepare(), but not execute()? Use RaiseError and check the status of both w/o even trying.
Re: Using multiple values in a SQL "in" statement
by hdb (Monsignor) on May 02, 2013 at 06:47 UTC

    Assuming you wanted the contents of @id in place of the question mark:

    use strict; use warnings; my @id=(1,2,3,4,5); my $sql = "SELECT name FROM table where id in (".join( ",", @id).")"; print "$sql\n";

      Normally your answers are very good, but this is not one of those answers. :-)

      Putting values directly into a SQL statement is a classic example of what not to do, because it is vulnerable to SQL injection attacks if the ID array values are externally sourced. The reason it is recommended to bind values in is to protect themselves from this sort of thing! :-)

      If you spot any bugs in my solutions, it's because I've deliberately left them in as an exercise for the reader! :-)

        Notice how, in one of the examples above, a string is (easily ...) constructed that consists of one-or-more question marks separated by commas.   This string is inserted dynamically (and safely) into the SQL string.   A corresponding parameter is added to the list of parameters at the same time, and the two are provided together when the query is run.   This technique is suitably dynamic, yet safe from injection.

        I will also endorse the notion of using DBIx::Class ... or SQL::Abstract if you have a lot of existing code to deal with.   They are solid and helpful.

        Thanks for the explanation. I had already realized that I did not read the question careful enough but I had not understood the full significance.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1031647]
Front-paged by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (3)
As of 2018-04-22 15:05 GMT
Find Nodes?
    Voting Booth?