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

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

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?

Solution

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