Advice on multiple variable searches

[sly]

Hello

I have a html file with 22 different form fields for searching a .csv
file. I want Perl to work out what fields have been selected and query
the data accordingly, then displaying matches.

What is the best way to handle such variable searches without loads of
IF statements depending on what fields were selected ?

Any advice and links would be a great help.

Thanks
Si

 

[A. Sinan Unur]

I have a html file with 22 different form fields for searching a .csv
file. I want Perl to work out what fields have been selected and query
the data accordingly, then displaying matches.

What is the best way to handle such variable searches without loads of
IF statements depending on what fields were selected ?

Any advice and links would be a great help.

Please read the posting guidelines for this group.

What have you done so far, and what do you have a problem with?

Sinan

 

[Paul Lalli]

Hello

I have a html file with 22 different form fields for searching a .csv
file. I want Perl to work out what fields have been selected and query
the data accordingly, then displaying matches.

What is the best way to handle such variable searches without loads of
IF statements depending on what fields were selected ?

Any advice and links would be a great help.

Put your possible search fields into an array.
Loop through that array
If the current field is found in the parameter list, add the
corresponding search keys to the search string.

For example, untested:
for my $field (@search_fields) {
if (my $val = param($field)){
push @search_keys, "$field = '$val'";
}
}
my $sql = "SELECT * FROM foo WHERE " . join (' AND ', @search_keys);

That should at least get you started.

Paul Lalli

 

[A. Sinan Unur]

Put your possible search fields into an array.
Loop through that array
If the current field is found in the parameter list, add the
corresponding search keys to the search string.

For example, untested:
for my $field (@search_fields) {
if (my $val = param($field)){
push @search_keys, "$field = '$val'";
}
}

What happens when the un-sanitized param($field) contains a single
apostrophe?

Again, untested and not compilable:

my @search_fields = qw(first_name last_name city state);

my $sql = qq{SELECT @search_fields FROM foo WHERE }
. join(' AND ', map { "$_ = ?" } @search_fields);

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

for my $i (1 .. @search_fields) {
$sth->bind_param($i, $cgi->param($search_fields[$i-1]));
}

$sth->execute;

Sinan

 

[Brian Wakem]

What happens when the un-sanitized param($field) contains a single
apostrophe?

I was going to post something similar regarding SQL injection. You could
use push @search_keys, "$field = " . $dbh->quote($val);

 

[Brian Wakem]

I was going to post something similar regarding SQL injection. You could
use push @search_keys, "$field = " . $dbh->quote($val);

Oh and of course this way assumes none of the entries of @search_fields is a
reserved word is SQL.

 

[A. Sinan Unur]

I was going to post something similar regarding SQL injection. You
could use push @search_keys, "$field = " . $dbh->quote($val);

What can I say ... Great minds think alike. ;-)

Sinan

 

[Paul Lalli]

What happens when the un-sanitized param($field) contains a single
apostrophe?

Then, the OP takes the code I generously gave and specifically said was
"to get you started", and fixes it.

Paul Lalli

 

[Paul Lalli]

my @search_fields = qw(first_name last_name city state);

my $sql = qq{SELECT @search_fields FROM foo WHERE }
. join(' AND ', map { "$_ = ?" } @search_fields);

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

for my $i (1 .. @search_fields) {
$sth->bind_param($i, $cgi->param($search_fields[$i-1]));
}

$sth->execute;

Out of curiousity, is there a reason you would prefer the above to (in
my mind, simpler but equivalent):

my $sql = qq{SELECT @search_fields FROM foo WHERE }
. join(' AND ', map { "$_ = ?" } @search_fields);

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

$sth->execute(map {$cgi->param($_)} @search_fields);

?

Paul Lalli

 

[A. Sinan Unur]

Then, the OP takes the code I generously gave and specifically said
was "to get you started", and fixes it.

Agreed. But it's a slow day here ... ;-)

Sinan

 

[A. Sinan Unur]

my @search_fields = qw(first_name last_name city state);

my $sql = qq{SELECT @search_fields FROM foo WHERE }
. join(' AND ', map { "$_ = ?" } @search_fields);

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

for my $i (1 .. @search_fields) {
$sth->bind_param($i, $cgi->param($search_fields[$i-1]));
}

$sth->execute;

Out of curiousity, is there a reason you would prefer the above to (in
my mind, simpler but equivalent):

my $sql = qq{SELECT @search_fields FROM foo WHERE }
. join(' AND ', map { "$_ = ?" } @search_fields);

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

$sth->execute(map {$cgi->param($_)} @search_fields);

Not really except that the map creates an extra array which may or may
not be important.

I was typing straight into the newsreader, and I was more confident that
I remembered the bind_param syntax correctly. My memory is weird like
that.

Sinan

 

[sly]

Thanks for your replies and help.

Here is what I have so far, but I am still no further on the query part
and haven't been able to get anywhere with the above code, I just get a
blank page printed.

#!/usr/bin/perl -wT

use strict;
use CGI ':standard';
use CGI::Carp qw(fatalsToBrowser);

my ($network, $tariff, $user_type, $mintype, $line, $contract,
@sp_data, $row, @search_fields);

$network = param('network');
$tariff = param('tariff');
$user_type = param('user_type');
$mintype = param('mintype');
$line = param('line');
$contract = param('contract');

mime();

push(@search_fields, $network, $tariff, $user_type, $mintype, $line,
$contract);

open(DB, "</location/db.csv") || Error ('open','file');

while ($line = <DB>)

{ HELP! }

close(DB);

sub mime {
print qq (Content-type: text/html\n\n);
print qq (<link rel="stylesheet"
href="/scholar/navigation/style/default.css" type="text/css">);
}

sub Error {
mime();
print qq (Error found);
}