title: Perl DBI - binding a list
body: <p>How do I bind a variable to a SQL set for an IN query in Perl DBI?</p>
  
  <p>Example:</p>
  
  <pre><code>my @nature = ('TYPE1','TYPE2'); # This is normally populated from elsewhere
  my $qh = $dbh-&gt;prepare(
        "SELECT count(ref_no) FROM fm_fault WHERE nature IN ?"
  ) || die("Failed to prepare query: $DBI::errstr");
  
  # Using the array here only takes the first entry in this example, using a array ref gives no result
  # bind_param and named bind variables gives similar results
  $qh-&gt;execute(@nature) || die("Failed to execute query: $DBI::errstr");
  
  print $qh-&gt;fetchrow_array();
  </code></pre>
  
  <p>The result for the code as above results in only the count for <code>TYPE1</code>, while the required output is the sum of the count for <code>TYPE1</code> and <code>TYPE2</code>. Replacing the bind entry with a reference to <code>@nature</code> (<code>\@nature</code>), results in 0 results.</p>
  
  <p>The main use-case for this is to allow a user to check multiple options using something like a checkbox group and it is to return all the results. A work-around is to construct a string to insert into the query - it works, however it needs a whole lot of filtering to avoid SQL injection issues and it is ugly...</p>
  
  <p>In my case, the database is Oracle, ideally I want a generic solution that isn't affected by the database.</p>