Re: in, ands, ors in query

[davidrw]

I think (might be db-dependent) you can simplify the SQL to be:

WHERE engine IN (...) AND 'public' IN (settings1, settings2, settings3
+, settings4)

Also, it seems from some of the above comments that quoting is one of the issues here.. here's a couple/few options:

my $engines = join(",",map($dbh->quote($_), @choices));
my $sql = <<EOF;
  SELECT engine, name1, name2, name3, name4, lable1, lable2, lable3, l
+able4, settings1, settings2,
                  settings3, settings4
  FROM special_fields
  WHERE engine IN ( $engines )
    AND (
       settings1="public" OR settings2="public" OR settings3="public" 
+OR settings4="public"
  )
EOF

Placeholders are probably a good idea here, too:

my $qs = join(",",map('?', @choices));
my $sql = <<EOF;
  SELECT engine, name1, name2, name3, name4, lable1, lable2, lable3, l
+able4, settings1, settings2,
                  settings3, settings4
  FROM special_fields
  WHERE engine IN ( $qs )
    AND (
       settings1="public" OR settings2="public" OR settings3="public" 
+OR settings4="public"
  )
EOF
my $rows = $dbh->selectall_arrayref( $sql, {Slice=>{}}, @choices );

And there's also solutions with things like SQL::Abstract:

use SQL::Abstract;
my $sa = SQL::Abstract->new( logic=>'and' );
my($sql, @bind) = $sa->select(
    'special_fields',
    [qw/
        engine name1 name2 name3 name4
        lable1 lable2 lable3 lable4
        settings1 settings2 settings3 settings4
    /],
    [
      { engine => \@choices, },
      [
        settings1 => 'public',
        settings2 => 'public',
        settings3 => 'public',
        settings4 => 'public',
      ]
    ],
);
my $rows = $dbh->selectall_arrayref( $sql, {Slice=>{}}, @bind );