Dynamic Where Clause using DBI

[ssmith001]

I've got an interesting problem that I'm trying to resolve in Perl and I need some advice. Perhaps someone has run into this before. I'll do my best to keep this simple. I have a form my user fills out. This form is basically nothing more than "where clause" criteria from which I need to build a SQL statement. The problem I have is that there are 6 option buttons that can check. I want to avoid having a SQL statement for every and all possible combinations of each of these 6 options. Do you see where I'm going with this? That would be UGLY!

Is there a way to dynamically build the where clause based on what option buttons they have chosen? I have a bunch of variables that I set based on what option buttons that have selected.

Here's an example. If they have chosen promo_code and stock_class options, run the sql below minus the supp_code and min_name lines. If they have chosen promo_code and min_name options, run it without stock_class and supp_code. I think you get the picture.

Select item, loc, abccode
from sku
where loc = ?
  and item = ?
  and promo_code in (blah, blah, blah)
  and stock_class in (blah, blah, blah)
  and supp_code in (blah, blah, blah)
  and min_name in (blah, blah, blah)

 

[ssmith001]

Here's the code you are looking for. As far as your concern about the SQL injection attack, is this a concern if this is an app that is only run within our network?

#
# Prepare a SQL statement for execution  
#
my $sql = "SELECT param
                , value 
             FROM pac.promo_params 
            WHERE plnrcode = ? 
              AND processflag = 'N'
         ORDER BY param, value";

my $sth = $dbh->prepare( $sql );

#
# Execute the statement in the database
#
$sth->execute($planner);

my $category = what_category_do_we_process($planner);
LogMsg("");
LogMsg("You are processing Category " . $category . " criteria records.");
LogMsg("");
LogMsg("Reading criteria records from PAC.PROMO_PARAMS TABLE in database = " . $instance);
LogMsg("");

#
# Fetch rows into a hash reference with lowercase field names and load arrays
#
while ($hash_ref = $sth->fetchrow_hashref('NAME_lc')) 
{
  LogMsg("Param: $hash_ref->{param}   \tValue: $hash_ref->{value}");

	$dealerlocations_found 	= 1 if ($hash_ref->{param} eq "Loc"						  );
	$dealergroups_found 		= 1 if ($hash_ref->{param} eq "DlrGrp"				  );
	$abc_found							= 1 if ($hash_ref->{param} eq "Abc"						  );
	$daysofsupply_found			= 1 if ($hash_ref->{param} eq "DaysOfSupply"	  );	
	$promocodes_found 			= 1 if ($hash_ref->{param} eq "PromoCode"			  );
	$stkclasses_found 			= 1 if ($hash_ref->{param} eq "StkClass"			  );
	$dlrsplrcodes_found 		= 1 if ($hash_ref->{param} eq "DlrSplrCode"		  );
	$altdlrsplrcodes_found	= 1 if ($hash_ref->{param} eq "AltDlrSplrCode"  );
	$distchannels_found 		= 1 if ($hash_ref->{param} eq "DistChannel"		  );
	$targetamount_found 		= 1 if ($hash_ref->{param} eq "TargetAmount"	  );
	$items_found 						= 1 if ($hash_ref->{param} eq "Item"					  );
	$minnames_found 				= 1 if ($hash_ref->{param} eq "MinName"				  );

	$targetamt    = $hash_ref->{value} if ($hash_ref->{param} eq "TargetAmount");
	$daysofsupply = $hash_ref->{value} if ($hash_ref->{param} eq "DaysofSupply");

	#
  # push all data from the promo_params table into arrays. From these arrays we will
  # build the strings that are fed to the various queries
  #
  push @dlrloc_array, $hash_ref->{value} 				 if ($hash_ref->{param} eq "Loc");
  push @dlrgrp_array, $hash_ref->{value} 				 if ($hash_ref->{param} eq "DlrGrp");
  push @abccode_array, $hash_ref->{value} 			 if ($hash_ref->{param} eq "Abc");
  #push @daysofsupply_array, $hash_ref->{value} 	 if ($hash_ref->{param} eq "DaysOfSupply");
  push @promocode_array, $hash_ref->{value} 		 if ($hash_ref->{param} eq "PromoCode");
  push @stkclass_array, $hash_ref->{value} 			 if ($hash_ref->{param} eq "StkClass");
  push @dlrsuppcode_array, $hash_ref->{value} 	 if ($hash_ref->{param} eq "DlrSplrCode");
  push @altdlrsuppcode_array, $hash_ref->{value} if ($hash_ref->{param} eq "AltDlrSplrCode");
  push @distchan_array, $hash_ref->{value} 			 if ($hash_ref->{param} eq "DistChannel");
  push @items_array, $hash_ref->{value} 				 if ($hash_ref->{param} eq "Item");
  push @minnames_array, $hash_ref->{value} 			 if ($hash_ref->{param} eq "MinName");

}

 

[ishnid]

IMHO, you're using waay too many data structures there that you really don't need. This is something like what I had in mind (I've only used the flags you mentioned in your earlier posts but it should be easy enough to adapt to do the whole lot). I haven't tested this properly BTW - it's mostly to show what I'm on about:

# set the table to map the values we get from fetchrow_hashref to the field names
# we need for our $qry
my %table => (
   PromoCode => 'promo_code',
   StkClass => 'stock_class',
   DistChannel => 'dist_channel',
   DlrSplrCode => 'dlr_supp_code',
   AltDlrSplrCode => 'alt_dlr_supp_code',
   MinName => 'min_name'
);

# get our data
my %data;
while ($hash_ref = $sth->fetchrow_hashref('NAME_lc'))
{
  LogMsg("Param: $hash_ref->{param}   \tValue: $hash_ref->{value}");
  # only add the data if it's a recognised key in %table - support for extra
  # data can be added by just inserting a single extra line into %table
  push @{ $data{ $hash_ref->{param} }, $hash_ref->{value} if ( $table{ $hash_ref->{param} );

}

# start with the initial query
my $qry = q!SELECT a.loc,
       a.item
     , a.p_abc
     FROM stsc.sku a
        , stsc.loc b
        , stsc,item c
    WHERE a.loc = '$loc'
      AND a.p_abc IN ($abc)!;

# store the parameters for our placeholders
my @bind_params;

# for all the data we're given, extend the query appropriately
for my $k ( keys %data ) {
   my @values = @{ $data{$k} };
   $qry .= " AND $table{$k} IN (" . join( ', ', ('?') x @values ) . ')';
   push @bind_params, @values;
}

# add on the last part
$qry .= ' ORDER BY a.loc, a.item';

# prepare and execute the query
my $sth = $dbh->prepare( $qry );
$sth->execute( @bind_params );

 

[ssmith001]

Wow, that's an interesting way of doing this. Thanks so much for the education. I've learned a lot.

I'm getting this error:

Useless use of hash element in void context at promo1.pl line 134

on this line:

  push @{ $data{ $hash_ref->{param} }, $hash_ref->{value} if ( $table{ $hash_ref->{param} } );

 

[ssmith001]

I resolved it myself so thanks for all your help. Is there a way to print out what the $qry variable looks like before the execute statement? I put in a print stmt but all I see is the placeholders. I want to see the AND a.p_abc IN ('A','B','C'), etc. because I'm still getting this error:

Can't call method "execute" on an undefined value at promo1.pl line 206

 

[ishnid]

Not that I'm aware of. That error suggests that the "prepare" call didn't behave the way we expected.

See if you can get an error message with this:

my $sth = $dbh->prepare( $qry ) or die $dbh->errstr;

 

[ssmith001]

Thanks. That caught the fact that I had fat fingered a table name that wasn't being found. Good call!

I've added this to my code to dump the fetched rows into an array. When I display each row however, I am seeing something like this: ARRAY(0x110c465a0)

I've seen this before. What does this mean?

my ($col1, $col2, $col3);
	
$sth_X->bind_col(1, \$col1);
$sth_X->bind_col(2, \$col2);
$sth_X->bind_col(3, \$col3);

my @results;

while ($sth_X->fetch) 
	{ 
		# push fetched columns into an array
  	push @results, [ $col1, $col2, $col3 ];
	}

foreach my $line (@results)
{
	LogMsg($line);
}

 

[ishnid]

It means that you're printing a reference to an array. You're making a 2-dimensional array with that code (i.e. everything you're pushing into @results is a reference to an array). For checking the contents of data structures like this, the Data:umper module is invaluable:

# at the top of your script
use Data::Dumper;

print Dumper(@results);

 

[ssmith001]

That worked! One last question and I promise I'll leave you alone. You have taught me a lot and I truly appreciate the effort.

If the user has chosen the PromoCode option, I need to check to see if the values they enter exist in any of 20 fields, each called p_model01ind, p_model02ind, p_model03ind, etc. What I want to end up with is something that looks like this:

AND ((c.p_model01ind IN (?, ?, ?, ?)
   OR c.p_model02ind IN (?, ?, ?, ?)
   OR c.p_model03ind IN (?, ?, ?, ?)
   OR c.p_model04ind IN (?, ?, ?, ?)
   OR c.p_model05ind IN (?, ?, ?, ?)
   OR c.p_model06ind IN (?, ?, ?, ?)
   OR c.p_model07ind IN (?, ?, ?, ?)
   OR c.p_model08ind IN (?, ?, ?, ?)
   OR c.p_model09ind IN (?, ?, ?, ?)
   OR c.p_model10ind IN (?, ?, ?, ?)
   OR c.p_model11ind IN (?, ?, ?, ?)
   OR c.p_model12ind IN (?, ?, ?, ?)
   OR c.p_model13ind IN (?, ?, ?, ?)
   OR c.p_model14ind IN (?, ?, ?, ?)
   OR c.p_model15ind IN (?, ?, ?, ?)
   OR c.p_model16ind IN (?, ?, ?, ?)
   OR c.p_model17ind IN (?, ?, ?, ?)
   OR c.p_model18ind IN (?, ?, ?, ?)
   OR c.p_model19ind IN (?, ?, ?, ?)
   OR c.p_model20ind IN (?, ?, ?, ?))

I've made up this code that will create this part of the where clause, but I can't figure out how to build it back into the script that you have helped me with. I had to replace the @val with a 4 in this example.

$col_beg = "c.p_model";
$col_end = "ind";

print "\n";

for ($i = 1; $i < 21; $i++)
{
  $num = sprintf '%02s', $i;
  $newcol = $col_beg . $num . $col_end;

  if ($i == 1)
  {
    print "AND \(\($newcol IN (" . join( ', ', ('?') x 4 ) . ')',"\n";
  }
  elsif ($i > 1 && ($i < 20))
  {
    print "   OR $newcol IN (" . join( ', ', ('?') x 4 ) . ')',"\n";
  }
  else
  {
    print "   OR $newcol IN (" . join( ', ', ('?') x 4 ) . '))',"\n";
  }
}

 

[ssmith001]

If there is anyone out there that can help with this last part I would greatly appreciate your help.