Is there SQL parameter binding for arrays?

Question

Is there a standard way to bind arrays (of scalars) in a SQL query? I want to bind into an IN clause, like so:

SELECT * FROM junk WHERE junk.id IN (?);

I happen to be using Perl::DBI which coerces parameters to scalars, so I end up with useless queries like:

SELECT * FROM junk WHERE junk.id IN ('ARRAY(0xdeadbeef)');

Clarification: I put the query in its own .sql file, so the string is already formed. Where the answers mention creating the query string dynamically I'd probably do a search and replace instead.

Edit: This question is kind of a duplicate of Parameterizing a SQL IN clause?. I originally thought that it should be closed as such, but it seems like it's accumulating some good Perl-specific info.

This is a dupe of a well known question. Let me just find it.... — Ray
– Ray, Commented Feb 4, 2009 at 23:16
Have a look at this question. I don't know much about perl but if you can join the array together so it's in one string you could probably get it to work. Or just use the most up voted answer in that question. — Ray
– Ray, Commented Feb 4, 2009 at 23:17
Found it, but it's not with Arrays, but you might be able to adapt. — Ray
– Ray, Commented Feb 4, 2009 at 23:18

Ovid · Accepted Answer · 2016-03-30 08:36:15Z

14

If you don't like the map there, you can use the 'x' operator:

my $params = join ', ' => ('?') x @foo;
my $sql    = "SELECT * FROM table WHERE id IN ($params)";
my $sth    = $dbh->prepare( $sql );
$sth->execute( @foo );

The parentheses are needed around the '?' because that forces 'x' to be in list context.

Read "perldoc perlop" and search for 'Binary "x"' for more information (it's in the "Multiplicative Operators" section).

edited Mar 30, 2016 at 8:36

answered Feb 5, 2009 at 11:44

Ovid

11.7k9 gold badges50 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ask Bjørn Hansen · Accepted Answer · 2013-02-22 09:35:56Z

12

You specify "this is the SQL for a query with one parameter" -- that won't work when you want many parameters. It's a pain to deal with, of course. Two other variations to what was suggested already:

1) Use DBI->quote instead of place holders.

my $sql = "select foo from bar where baz in ("
           . join(",", map { $dbh->quote($_) } @bazs)
           . ")";
my $data = $dbh->selectall_arrayref($sql);

2) Use an ORM to do this sort of low level stuff for you. DBIx::Class or Rose::DB::Object, for example.

edited Feb 22, 2013 at 9:35

answered Feb 5, 2009 at 4:29

Ask Bjørn Hansen

6,9832 gold badges32 silver badges40 bronze badges

1 Comment

hetaoblog Over a year ago

however, in my side, using DBI->quote is not working; i used $dbh->quote; here $dbh is from $dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);

JDrago · Accepted Answer · 2009-02-05 00:53:00Z

9

I do something like:

my $dbh = DBI->connect( ... );
my @vals= ( 1,2,3,4,5 );
my $sql = 'SELECT * FROM table WHERE id IN (' . join( ',', map { '?' } @vals ) . ')';
my $sth = $dbh->prepare( $sql );
$sth->execute( @vals );

answered Feb 5, 2009 at 0:53

JDrago

2,09914 silver badges15 bronze badges

2 Comments

mpeters Over a year ago

@Dems, JDrago's example is already using bind variables, so he doesn't need to do anthing else to prevent SQL injection.

ysth Over a year ago

map { '?' } @vals is more simply ('?') x @vals

AndyG · Accepted Answer · 2017-04-21 15:09:38Z

6

And yet another way to build SQL is to use something like SQL::Abstract....

use SQL::Abstract;
my $sql    = SQL::Abstract->new;
my $values = [ 1..3 ];
my $query  = $sql->select( 'table', '*', { id => { -in => $values } } );

say $query;   # => SELECT * FROM table WHERE ( id IN ( ?, ?, ? ) )

edited Apr 21, 2017 at 15:09

AndyG

41.5k8 gold badges123 silver badges155 bronze badges

answered Feb 5, 2009 at 20:17

draegtun

22.6k5 gold badges51 silver badges72 bronze badges

1 Comment

jrockway Over a year ago

I was just about to post this, and I definitely think it's the "correct answer". If you can't use an ORM, at least use something like SQL::Abstract (or maybe Fey).

8jean · Accepted Answer · 2009-02-05 11:25:08Z

4

With plain DBI you'd have to build the SQL yourself, as suggested above. DBIx::Simple (a wrapper for DBI) does this for you automatically using the '??' notation:

$db->query("select * from foo where bar in (??)", @values);

answered Feb 5, 2009 at 11:25

8jean

9,2802 gold badges25 silver badges14 bronze badges

2 Comments

cdleary Over a year ago

But then can you bind parameters elsewhere in the SQL? I don't see how you could bind parameters after the ?? unless it optionally takes an array reference instead.

8jean Over a year ago

Good question. If I understand the docs correctly, you can have either multiple single placeholders or a single '??', but not both.

John Fouhy · Accepted Answer · 2009-02-05 01:20:27Z

2

In python, I've always ended up doing something like:

query = 'select * from junk where junk.id in ('
for id in junkids:
  query = query + '?,'
query = query + ')'

cursor.execute(query, junkids)

...which essentially builds a query with one '?' for each element of the list.

(and if there's other parameters in there too, you need to make sure you line things up correctly when you execute the query)

[edit to make the code easier to understand for non-python people. There is a bug, where the query will have an extra comma after the last ?, which I will leave in because fixing it would just cloud the general idea]

edited Feb 5, 2009 at 1:20

answered Feb 4, 2009 at 23:22

John Fouhy

42.4k19 gold badges66 silver badges79 bronze badges

5 Comments

Ray Over a year ago

Sure, if you're not worried about SQL Injection then you don't need parameters at all.

cdleary Over a year ago

@Ray: I don't understand your comment -- he is doing parameter binding, just adding an appropriate number of parameters to bind dynamically.

Ray Over a year ago

hm.. so he is. Sorry my mistake.

cdleary Over a year ago

No problem. Just wanted to make sure we were on the same page. :-)

cdleary Over a year ago

Here's a Perl version: $params = '?, ' x scalar(@junk_ids); $query = 'SELECT * FROM junk WHERE junk.id IN (' . substr($params, 0, -2) . ');'; # Strip off the trailing comma-space.

Peter Stuifzand · Accepted Answer · 2009-02-05 12:57:49Z

0

I use DBIx::DWIW. It contains a function called InList(). This will create the part of the SQL that is needed for the list. However this only works if you have all your SQL in the program instead of outside in a separate file.

answered Feb 5, 2009 at 12:57

Peter Stuifzand

5,1041 gold badge26 silver badges28 bronze badges

Comments

Martin Janota · Accepted Answer · 2017-05-31 13:08:45Z

0

Use

SELECT * FROM junk WHERE junk.id = ANY (?);

instead

answered May 31, 2017 at 13:08

Martin Janota

1

Collectives™ on Stack Overflow

Is there SQL parameter binding for arrays?

8 Answers 8

Comments

1 Comment

2 Comments

1 Comment

2 Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

1 Comment

2 Comments

1 Comment

2 Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related