Did you print out your SQL statements and run them in the command line SQL client? It's very hard to tell you anything without seeing the input data, table data and the "various ways" you tried and how they failed. What is %d,%d supposed to be in the LIMIT clause?

The multiword searches will obviously not work because your string will look like:

SELECT name,address,city,phone
FROM valley
WHERE CONCAT_WS(' ',keywords,category) LIKE '%Antiques Dealers%'
ORDER BY name LIMIT %d,%d
[download]

when your table could contain

Dealers with Antiques
[download]

Maybe you wanted something (horribly inefficient) like:

SELECT name,address,city,phone
FROM valley
WHERE keywords LIKE '%Antiques%'
  AND keywords LIKE '%Dealers%'
ORDER BY name LIMIT %d,%d
[download]

Most likely, you're better off taking advantage of the full text search features of your database or by normalizing the keywords in your database, for example by ordering them alphabetically:

SELECT name,address,city,phone
FROM valley
WHERE keywords LIKE '%Antiques%Dealers'
ORDER BY name LIMIT %d,%d 
-- if all keywords are always ordered alphabetically
[download]

What DB do you use?

I'm sure this code must work:

SELECT name, address, city, phone
FROM valley
WHERE keywords LIKE '%Dealers%'
OR category LIKE '%Dealers%'
ORDER BY name
[download]

Please provide your real SQL code (which script send to SQL server).

Here is the complete script, the only thing I have removed is the comments....

Thanks for looking!
Jim

my $dbh = DBI->connect('DBI:mysql:farthing_valleyweb','farthing_farthi
+n','ginajim')

if ($type eq 'alpha') {
$query = sprintf (
                "SELECT name, address, city, phone
                FROM valley
                where name like '$search%'
                ORDER BY name LIMIT %d,%d",
                $start - 1,         
                $per_page + 1);     
} else {

$query = sprintf (
                "SELECT name,address,city,phone
                  FROM valley
                  WHERE CONCAT_WS(' ',keywords,category) LIKE '%$searc
+h%'
                  ORDER BY name LIMIT %d,%d",
                $start - 1,         # number of records to skip
                $per_page + 1);     # number of records to select
}             
               
my $tbl_ref = $dbh->selectall_arrayref ($query);

$dbh->disconnect ( );

my @rows;
my $z = @rows;
if ($z = 0) {
print "We are sorry, but your search did not return any results.<P />\
+n";
print "<input type=\"button\" value=\"  Try again  \" onClick=\"histor
+y.go(-1)\">\n";
print "$tbl_ref\n";
}
 
for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i+=2)
{
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    my @cells2 = @{$tbl_ref->[$i+1]};  # get data values in row $i+1
    @cells = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells;
    @cells2 = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells2;
    @cells="<b>$cells[0]</b><br>$cells[1]<br>$cells[2]<br>$cells[3]<br
+>$cells[4]";
    @cells2="<b>$cells2[0]</b><br>$cells2[1]<br>$cells2[2]<br>$cells2[
+3]<br>$cells2[4]";
    push (@rows, Tr (td (\@cells),(td (\@cells2))));
}

$page .= table ({-border => 0, width=> 550}, @rows) . br ( );

if ($start > 1)                 
{
    my $url = sprintf ("%s?start=%d;per_page=%d;search=$search",
                        url ( ),
                        $start - $per_page,
                        $per_page);
    $page .= "[" . a ({-href => $url}, "previous page") . "] &nbsp; &n
+bsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &
+nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
+&nbsp; &nbsp; &nbsp; &nbsp; ";
}
else                            
{
    $page .= "&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
+&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
+ &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp
+; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ";
}

if (@{$tbl_ref} > $per_page)    # live link
{
    my $url = sprintf ("%s?start=%d;per_page=%d;search1=$search;type=$
+type",
                        url ( ),
                        $start + $per_page,
                        $per_page);
    $page .= "[" . a ({-href => $url}, "next page") . "]";
}
else                            # static text
{
    $page .= "";
}

$page .= "Search: $search Cat1: $cat1 Cat2: $cat2\n";

$page .= end_html ( );

print $page;
[download]

You are interpolating into a string and then using that string as a sprintf template. You are also not using placeholders, nor quoting things that go directly into the SQL.

Consider the following things that can go wrong when generating your SQL:

• The first character of $search is right next to a "%" character. So by putting in a character that sprintf recognizes, you can "steal" the number meant for the LIMIT clause.

  my $search = "foobar";
  printf "WHERE blah LIKE '%$search%' LIMIT %d,%d\n", 1, 2;
  
  # ... LIKE '%foobar' ...
  #           ^^
  
  # WHERE blah LIKE '1.000000oobar%' LIMIT 2,0
  [download]

• Steal both numbers meant for the LIMIT clause, since "%" symbols in $search are not escaped

  my $search = "f %d";
  printf "WHERE blah LIKE '%$search%' LIMIT %d,%d\n", 1, 2;
  
  # WHERE blah LIKE '1.000000 2%' LIMIT 0,0
  [download]

• Other special symbols are not escaped, and directly included in the SQL:

  my $search = "' OR '1' LIKE '1";
  printf "WHERE blah LIKE '%$search%' LIMIT %d,%d\n", 1, 2;
  
  # WHERE blah LIKE '%' OR '1' LIKE '1%' LIMIT 1,2
  [download]

You can even do nastier stuff with a sprintf injection attack -- like change the value of variables in the script! So it is not just the SQL statement or database that might be affected.

Solutions/suggestions:

• use DBI placeholders for inserting user-supplied data into the SQL statement (for automatic escaping of SQL special chars).
• Don't directly interpolate user-supplied data into a s/printf statement (to avoid sprintf injection).
• Don't use s/printf when you have "%" characters all over the SQL statement (it's not clear whether the "%" chars of the SQL statement will be eaten up by s/printf or not).

blokhead

If the following snippet is really what you are running now, this explains why you are not getting any results I'm pretty sure this does not do what you intend it to do -- in fact, it's kind of pointless:

my @rows;
my $z = @rows;  # There is nothing in @rows yet, so $z is 0
if ($z = 0) {   # but in any case, this will never return true!
print "We are sorry, but your search did not return any results.<P />\
+n";
print "<input type=\"button\" value=\"  Try again  \" onClick=\"histor
+y.go(-1)\">\n";
print "$tbl_ref\n";
}
[download]

Your "if" statement is not testing whether $z equals zero -- you would need to use "==" to test that. But you also need to do that "if" statement after you try to push things onto @rows, not before. (See below.)

Apart from that, I have a couple suggestions that I hope will make your life as a perl programmer more enjoyable... First, use placeholders in your queries -- they are so much better, in so many ways:

my $column = ( $type eq 'alpha' ) ? 'name' : "CONCAT_WS(' ',keywords,c
+ategory)";

my $query = "SELECT name, address, city, phone FROM valley ".
    "WHERE $column LIKE ? ORDER BY name LIMIT ?,?";
             
my $sth = $dbh->prepare( $query );
$sth->execute( "%$search%", $start-1, $per_page+1 );

my $table_ref = $sth->fetchall_arrayref;

# here is where/how you check whether anything was returned:

if ( ref( $table_ref ) ne 'ARRAY' or @$table_ref == 0 ) {
    ...
}
[download]

(Updated to include the check for empty return from the DB.)

Next, given that your query results are sorted by name, I don't know why you seem to want your html table to be organized like this:

   name1   name2
   name3   name4
   ...
[download]

(Actually, I'm not sure what your intention is, but whatever...) Your loop for creating the table markup is probably broken. You start with @cells (and @cells2) containing 4 elements, and then do @cells="ugly string"; which reduces it to a single-element array, containing just the one "ugly string". (And you are referencing 5 elements in each row, where your query was only requesting 4.) There's a better way to code all that. Here's how I would arrange a simpler table:

for my $table_row ( @$table_ref ) {
    my @table_cols = map { (defined($_) && /\S/) ? $_ : ' '} @$ta
+ble_row;

    push @rows, Tr( \@table_cols );
}
$page .= "<b>" . table( {-border => 0, width=> 550}, \@rows) . "</b>" 
+. br();
[download]

That has not been tested (I might not have it right), but if it's wrong, it should be easy to fix. And if you really want a more complicated table layout, it'll be easier to build from this simpler approach.

Finally, the "x" operator will make it easier for you to get all those " " thingies to come out right, and your code will be easier to read and maintain:

    $page .= '  ' x 35;
[download]