Perl DBI: uneven-number of bind variables with OR Statement (called with x bind variables when y are needed)

Question

Definition of task: Fetch data from two different columns using OR.

Problem: While its working with the plain (MySQL) query, Perl DBI throws an exception due to the uneven-number of bind variables.

Let's assume the following DB schema:

customer    vpn_primary_ip   vpn_secondary_ip
1000        1.1.1.1          2.2.2.2
1001        3.3.3.3          NULL
1002        4.4.4.4          5.5.5.5
1003        NULL             6.6.6.6

Side note: As the column where the ip address is stored is not predictable, I combine the search for the columns vpn_primary_ip AND vpn_secondary_ip using the OR operator. The plain SQL query is as follows:

SELECT
     customer,
     vpn_primary_ip,
     vpn_secondary_ip,
 FROM
     table
 WHERE
     vpn_primary_ip IN ( '1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6' )
 OR  
     vpn_secondary_ip IN ( '1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6' );

The query above gives the following (appropriate) result:

+----------+-----------------+------------------+
| customer | vpn_primary_ip  | vpn_secondary_ip |
+----------+-----------------+------------------+
|   1000   | 1.1.1.1         | 2.2.2.2          |
|   1002   | 4.4.4.4         | 5.5.5.5          |
|   1003   | NULL            | 6.6.6.6          |
+----------+-----------------+------------------+

The same SQL query with Perl DBI:

 my @ip_addresses = ('1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6');

 my $sth = $dbh->prepare (
     "SELECT
       customer,
       vpn_primary_ip,
       vpn_secondary_ip,
     FROM
       table
     WHERE
       vpn_primary_ip IN ( @{[join',', ('?') x @ip_addresses]} )
     OR  
       vpn_secondary_ip IN ( @{[join',', ('?') x @ip_addresses]} )"
    );

 $sth->execute(@ip_addresses);

Throws the following exception:

DBD::mysql::st execute failed: called with 4 bind variables when 8 are needed at get_vpn_customers line 211, <DATA> line 1.
DBD::mysql::st execute failed: called with 4 bind variables when 8 are needed at get_vpn_customers line 211, <DATA> line 1.

The only idea to make it work, is to pass @ip_addresses to the execute method twice:

$sth->execute(@ip_addresses, @ip_addresses);

Question: Is this the proper approach or is there another, let's say, best or better practice?

This is the proper approach. Perl can't know that you are passing the same set of values twice, so it just expects all 8 values to be specified in the parameters of the call. — Sergey Kudriavtsev, Mar 02 '20 at 12:47

score 3 · Accepted Answer · answered Mar 02 '20 at 13:49

3

$sth->execute(@ip_addresses, @ip_addresses);

This is the correct approach. All DBI knows is that you have passed it an SQL query containing eight bind points. It, therefore, needs eight matching values passed to the execute() method.

There is no way for Perl, DBI or MySQL to know that the bind values are repeated.

answered Mar 02 '20 at 13:49

Dave Cross

68,119
3
51
97

1

PostgreSQL supports parameters in the form `$1`, `$2`, so you could repeat these in the query and only pass four parameters. Sadly not possible with MySQL. – Grinnz Mar 02 '20 at 16:37
You could also write `$sth->execute((@ip_addresses)x2)` (the parentheses are oddly significant here) – Grinnz Mar 02 '20 at 16:39
@Grinnz: I was thinking what you really need is a syntax like `WHERE (vpn_primary_ip OR vpn_secondary_ip) IN (LIST)`. But I've never seen anything like that in any version of SQL. – Dave Cross Mar 02 '20 at 16:40
1

You could actually do this with the PostgreSQL array overlap operator, which I believe would look something like `WHERE ARRAY[vpn_primary_ip, vpn_secondary_ip] && ?`, passing `\@ip_addresses` as the single parameter. Not sure how well it would be optimized though. – Grinnz Mar 02 '20 at 16:44

score 1 · Answer 2 · answered Mar 02 '20 at 18:26

Other possible solution is to massage SQL query to workable state before $sth->execute()

use strict;
use warnings;
use feature 'say';

my @ip_addresses = ('1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6');

 my $query = "
    SELECT
       customer,
       vpn_primary_ip,
       vpn_secondary_ip,
     FROM
       table
     WHERE
       vpn_primary_ip IN ( @{[join',', ('?') x @ip_addresses]} )
     OR  
       vpn_secondary_ip IN ( @{[join',', ('?') x @ip_addresses]} )
    ";

say $query;

my $ip_addresses;
my $flag = 0;

for (@ip_addresses) {
    $ip_addresses .= ', ' if $flag;
    $ip_addresses .= "'$_'";
    $flag = 1;
}

$query = "
    SELECT
       customer,
       vpn_primary_ip,
       vpn_secondary_ip,
     FROM
       table
     WHERE
       vpn_primary_ip IN ( $ip_addresses )
     OR  
       vpn_secondary_ip IN ( $ip_addresses )
    ";

say $query;

Output

SELECT
   customer,
   vpn_primary_ip,
   vpn_secondary_ip,
 FROM
   table
 WHERE
   vpn_primary_ip IN ( ?,?,?,? )
 OR
   vpn_secondary_ip IN ( ?,?,?,? )


SELECT
   customer,
   vpn_primary_ip,
   vpn_secondary_ip,
 FROM
   table
 WHERE
   vpn_primary_ip IN ( '1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6' )
 OR
   vpn_secondary_ip IN ( '1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6' )

Perl DBI: uneven-number of bind variables with OR Statement (called with x bind variables when y are needed)

2 Answers2