SQL prepared statements using MySQL In ()

[djlerman]

I am trying to create a prepared statement with perl using MySQL's IN (). It looks something like this:

$valuesForIn = "1,2,3,4,5";

$query = "   SELECT field1
                FROM table1
                WHERE field2 IN (?) ";

$sth1 = $dbh->prepare($query) or die->$errstr();

$sth1->execute($valuesForIn) or die $dbh->errstr;

This doesn't seem to work. :-( What is the useful wisdom around this?

[talexb]

I expect you need to make values be a list, and do something like this:

  my @values = ( 1,2,3,4,5 );
  my $placeholders = join( ',', ('?') x scalar @values );
  my $query = "SELECT field1
                 FROM table1
                WHERE field2 IN ( $placeholders )";

Your prepare and execute calls should then work correctly.

You example is providing a single variable, so I guess DBI is not interpreting "1,2,3,4,5" as a list.

[LanX]

==== Short answer (updated)

there is no DBI/SQL-placeholder for lists (unfortunately)

==== Long answer:

Provided that the number of values is dynamic, please try converting an array, like

my @valuesForIn  = (1,2,3,4,5); 
my $placeholders = join ',' , map {"?"} @valuesForIn;

my $query = qq~ SELECT field1
                FROM table1
                WHERE field2 IN ($placeholders) ~;

my $sth1 = $dbh->prepare($query) or die->$errstr();

$sth1->execute(@valuesForIn) or die $dbh->errstr;

(Disclaimer: Typed into mobile, hence untested)

PS there is a fancier way to avoid the map

("?") x @valuesForIn

, but I'm not sure about the precedence here. :)

update2

please also be aware of the speed penalty of repeatedly preparing a statement for different length of lists ...

[kcott]

G'day djlerman,

"What is the useful wisdom around this?"

TMTOWTDI. Here's how I might tackle this.

• Make use of the strict and warnings pragmata.
• Use lexical (e.g. my) variables instead of package variables.
• Present "valuesForIn" as an array.
• Interpolate the "IN (...)" code directly into $query. See "perlsecret: Baby cart" if you're unfamiliar with the construct that I've used.
• Fix "die->$errstr()", which should be "die $dbh->$errstr()", if you enjoy typing and want to repeatedly code that same exception message. Alternatively, and my preference, employ "the first great virtue of a programmer", namely laziness, and take advantage of DBI's RaiseError attribute.

In the code example below, I've used all of these features. The two print statements show how the variables are interpolated into the prepare() and execute() methods (note that @valuesForIn expands to a space-separated list in the double-quoted string).

$ perl -e '
    use strict;
    use warnings;

    # Somewhere earlier in your code, something like this:
    # my $dbh = DBI->connect(
    #   $data_source, $username, $auth,
    #   {RaiseError => 1, ...}
    # );

    my @valuesForIn = 1..5;

    my $query = qq{
        SELECT field1
        FROM table1
        WHERE field2 IN (@{[join ",", qw{?} x @valuesForIn]})
    };

    print "my \$sth1 = \$dbh->prepare($query);\n";
    print "\$sth1->execute(@valuesForIn);\n";
'
my $sth1 = $dbh->prepare(
        SELECT field1
        FROM table1
        WHERE field2 IN (?,?,?,?,?)
    );
$sth1->execute(1 2 3 4 5);

[djlerman]

Thank You. This is similar to how I solved it.

# Set up variable for the Parameters for the entire query
my @sqlParameters = ();

# set up variable for just the IN() statement
my @valuesForIn = (1,2,3,4,5);

# a value for another field
my $field3 = 'abc123';

my $query = "   SELECT field1
                FROM table1
                WHERE field2 IN (@{[join',', ('?') x @valuesForIn]}) 
                    AND field3 = ? ";

push(@sqlParameters, @valuesForIn);
push(@sqlParameters, $field3);

my $sth = $dbh->prepare($query) or die $dbh->errstr;

$sth->execute(@sqlParameters) or die $dbh->errstr;

Note: I kept separate @sqlParameters variable J.I.C. the query needs to be extended with more parameters.

[Marshall]

Is this what you intended?

$query = "   SELECT field1
                FROM table1
                WHERE field2 IN (1,2,3,4,5) ";

$sth1 = $dbh->prepare($query) or die->$errstr();

$sth1->execute($valuesForIn) or die $dbh->errstr;

Often in a case like this, the IN clause would be another SELECT statement. There is not enough info to figure out what that SELECT could be.

You can have  In ($valuesForIn) but of course you will have to prepare the statement again if $valuesForIn changes. You cannot have the $valuesForIn spec'd as runtime bindings using ? because the number or args be specified in advance and must match the number your call with.

I suppose you could have a fixed length of 5 and do something like:

$query = "   SELECT field1
                FROM table1
                WHERE field2 IN (?,?,?,?,?) ";

$sth1 = $dbh->prepare($query) or die->$errstr();

$sth1->execute(1,2,3,4,5) or die $dbh->errstr;

Update:
Something like this would be more typical:

"SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers)"

The IN array is derived from an SQL statement and has a variable number of elements in it. Here the number of Countries that have Suppliers is variable and you don't need to know that to successfully prepare and execute the statement.

I think this is a case where you have given us too simple of an example. That seldom happens but appears to be the case here. At bit more details about the application and some context about what 1,2,3,4 stand for would be very helpful.

[Anonymous Monk]

The way to do this while being safe against SQL injection is to have your values in an array - pass them as query parameters, and then construct your query with an interation over the size of the array, so you end up with whatever the mysql equivalent of this is: select field1 from table1 where ((field2 = @namedParameter1) or (field2 = @namedParameter2) or (field2 = @namedParameter3))