Search code examples
mysqlperlsearch-engine

Advanced Search mysql perl

I'm a novice. I've searched Stackoverflow for weeks trying to find code for an advanced search.
Finally created my own. It's working pretty good. I'm listing it here for others who might want to use it.
The questions are:

  • when no item_category is chosen I default to * but it's not working. item_category is the only variable required for a search?

  • Do I need to be concerned about sanitization or hack injections?

  • The exact match doesn't seem to be working. It may be searching 100 words for 'FREE' and won't find a match. Any ideas?

  • Any suggestions on reliability and speed issues?

    $ANDOR = param('andor');#creates an AND OR search if($ANDOR eq ""){ $ANDOR="AND";}

    if($item_category){ $item_category = "$item_category"; } else{ $item_category=" * "; } $statement .= "item_category LIKE '$item_category' ";

    if($item_state){ $statement .= " $ANDOR item_state LIKE '$item_state' "; } if($item_city){ $statement .= " $ANDOR item_city LIKE '$item_city' "; } if($db_id){ $statement .= " $ANDOR db_id = '$db_id' "; }

    $keywords=param('keywords');

    if($keywords) { if(param('searchmatch') eq "exact") { $statement .= " $ANDOR item_name = \"$keywords\" OR item_desc = \"$keywords\" OR item_desc2 = \"$keywords\" ";#

    } else { $statement .= " $ANDOR "; my @keywords = split(/ /,$keywords);

    foreach my $keyword(@keywords)
{
$statement .= " item_name LIKE '%$keyword%' 
OR item_desc LIKE '%$keyword%' 
OR item_desc2 LIKE '%$keyword%' ";
}

    } }

    $date_begin=param('date_begin'); if($date_begin){ $statement .= " $ANDOR modification_time > '$date_begin' "; }

    if($user){ $statement .= " $ANDOR user LIKE '$user' "; }

    $price_low=param('price_low'); $price_high=param('price_high'); if (($price_low) && ($price_high)){ $statement .= " $ANDOR item_price BETWEEN '$price_low' AND '$price_high' "; } elsif (($price_low) && ($price_high eq "")){ $statement .= " $ANDOR item_price BETWEEN '$price_low' AND * "; } elsif (($price_high) && ($price_low eq "")){ $statement .= " $ANDOR item_price BETWEEN '0' AND '$price_high' "; } else { $statement .= "";

    }

    my $sth = $dbh->prepare(qq(SELECT * FROM table WHERE $statement )) or die $DBI::errstr; $sth->execute(); while ((my (@rows)) = $sth->fetchrow_array) { $total_row_count= $sth->rows; $database_rows = join ("\|", @rows); push (@database_rows,$database_rows); }

Solution

    1. '' is not a valid conditional for the where clause. You could just put "1" instead of "", but better yet just leave out that portion of the where clause.

    2. YES; use paramater binding. e.g.

      if ($item_state) {
    $statement .= " $ANDOR item_state LIKE ? ";
    push(@binds, $item_state);
}

      Then to have DBI correctly sanitize and include your params:

      $sth->execute(@binds);

    3. Examine the full query generated. I suspect if you look at AND/OR precedence rules it doesn't do what you think, You MAY be able to get away with some judicious use of parenthesis.

    4. Don't do this. Look on CPAN for something that does the back-end work for you, and just call into that with the appropriate params. After you've used a tool someone else wrote for this you'll have a good idea of all the cases you AREN'T handling with this, and can then think about how to add what you think is missing if you decide to revisit the idea of making a search again.

    At this point I've written a simple DB query engine/tiny ORM about 4 times, 3 of them in perl. Every time I learn something new, but more importantly every time I remember why I should have just used a solution from someone else who's already gone through finding all the edge cases.