Not including quotes or placeholders in the second argument to LIKE is very
dangerous. See below.
You are also assuming that all fields will have an associated full_entry
that is not NULL and is suitable as a LIKE matching expression.
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Ian Summers" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, April 04, 2001 1:32 AM
Subject: Re: Composing a SQL statement
> Try something like this:
>
> At 16:26 04/04/2001 +1000, you wrote:
>
> > Hi all,
> >
> > I have a perl CGI form like thus:
> >
> > X | Call No | Call No textfield
> > X | Problem | Problem textfield
> >
> > The X's represent checkboxes.
> >
> > In the backend, I have two arrays:
> >
> > @fields: checked fields
> > @entries: text fields with entries.
> >
> > my code for generating the SQL is below. My problem
> > occurs when one enters text into more than one text
> > field. For example, say I check "Call No" and "Problem"
> > and enter "111" and "seating" into their respective
> > text fields. The SQL becomes:
> >
> > SELECT call_no, problem FROM HELP WHERE
> > ( call_no LIKE 111 problem LIKE seating )
> >
> > There needs to be an "AND" in the WHERE clause. The SQL
> > should read:
> >
> > SELECT call_no, problem FROM HELP WHERE
> > ( call_no LIKE 111 AND problem LIKE seating )
> >
> >
> > I'll be stuffed if I know how to do it.
> > ***********
> >
> >
> > $nfields = @fields;
> >
> > foreach $i (@entries) {
> > if ($i ne '') {
> > push @full_entries, $i;
> > }
> > }
> >
> > $sql = sprintf "SELECT %s FROM HELP WHERE (", join(", ", @fields);
> # new line
> my @where;
>
> > for ($j = 0; $j < $nfields; $j++) {
> > if ($full_entries[$j] ne '') {
>
> # line modified
>
> > push @where, sprintf " %s LIKE %s ", $fields[$j],
$full_entries[$j];
> > +
> > }
> > }
>
> # line modified
>
> > $sql .= (join ' AND ', @where).')';
$dbh -> {RaiseErrors} = 1; # _always_ check for errors
# If placeholders are allowed
$sql = 'SELECT ' . join( ', ', @fields ) . ' FROM HELP WHERE ' .
join( ' AND ', map { "$_ LIKE ?" } @fields );
# ...
$sth -> execute( @full_entries );
# If placeholders are not allowed
my $i = 0;
$sql = 'SELECT ' . join( ', ', @fields ) . ' FROM HELP WHERE ' .
join( ' AND ', map { "$_ LIKE " . $dbh -> quote( $full_entries[$i++] ) }
@fields );
# ...
$sth -> execute;
