Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

SQL prepared statements using MySQL In ()

by djlerman (Sexton)
on Aug 10, 2023 at 17:24 UTC ( [id://11153797]=perlquestion: print w/replies, xml ) Need Help??

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

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?

Replies are listed 'Best First'.
Re: SQL prepared statements using MySQL In ()
by talexb (Chancellor) on Aug 10, 2023 at 17:56 UTC

    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.

    Alex / talexb / Toronto

    Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

Re: SQL prepared statements using MySQL In ()
by LanX (Saint) on Aug 10, 2023 at 17:54 UTC
    ==== 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)

    Cheers Rolf
    (addicted to the 𐍀𐌴𐍂𐌻 Programming Language :)
    Wikisyntax for the Monastery

    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 ...

Re: SQL prepared statements using MySQL In ()
by kcott (Archbishop) on Aug 11, 2023 at 12:47 UTC

    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);

    — Ken

      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.
Re: SQL prepared statements using MySQL In ()
by Marshall (Canon) on Aug 10, 2023 at 18:39 UTC
    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.

Re: SQL prepared statements using MySQL In ()
by Anonymous Monk on Aug 10, 2023 at 23:30 UTC
    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))

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11153797]
Approved by LanX
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others exploiting the Monastery: (6)
As of 2024-07-15 09:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.