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]

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).

Yeah ... interpolation really makes a hash of things. ;^)

%$search

...roboticus

Well, I did try the following test before putting "%$search%" into the snippet suggestion in my own reply:

perl -Mstrict -le 'my $s="blah"; print "%$s%"'
[download]

which prints "%blah%". Putting a "%" in front of a scalar variable in a double-quoted string will not turn that variable into a hash ref.

If the variable happens to already be a hash ref, then of course it will be interpolated as such, though not in a way that most folks would consider useful:

perl -Mstrict -le 'my $s={foo=>"bar"}; print "%$s%"'
[download]

prints something like "%HASH(0x1801380)%". It's only arrays (and array refs) that get interpolated into the list of values when placed inside double-quotes -- and only if the sigils are right:

perl -Mstrict -le 'my %s=(foo => "bar"); print "%s"'
%s

perl -Mstrict -le 'my $s=[qw/foo bar/]; print "%$s%"'
%ARRAY(0x1801380)%

perl -Mstrict -le 'my $s=[qw/foo bar/]; print "@$s@"'
foo bar@
[download]

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]

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]

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]

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]

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]

Holy K-Rap, Batman!
Atsa lotsa stuff!

Thanks blokhead and graff! You both brought up some very good points, it is greatly appreciated. I am going to put in placeholders where I can.

graff, that table code outputs 2 columns in the format

<b>Name</b>                   <b>Name</b>    
Address                       Address
City                          City
Category                      Category
[download]

One thing though, graff. Can you explain this section to me?

I am not real sure that I am following the logic.

Thanks for everything guys! It is much appreciated and the points are well taken.
Jim

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 ?,?";
[download]

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

It's just a compact way to write if (..) {} else {}. Look for Ternary "?:" in perlop.

my $query = "SELECT name, address, city, phone FROM valley ". "WHERE $column LIKE ? ORDER BY name LIMIT ?,?";

The concept of Placeholders and Bind Values is fairly well described in the DBI documentation.
--
Andreas

The thing about building the query string was that you had two different ways to do it, and once you start using placeholders, the only difference between the two is the column spec that follows "where ". So I used the ternary operator ( (condition) ? expr1 : expr2) to assign a value to $column, and just put that into the common SQL statement.

By the way, when I do something like this with a MySQL database of my own, I get the sort of results that I think you want:

   my @search = qw/%this% %that%/;
   my $sql = "select something from table where column1 like ? or colu
+mn2 like ?";
   my $sth = $dbh->prepare( $sql );
   $sth->execute( @search );
[download]

It also works as expected if the second "like ?" applies to the same column as the first one.

As for the table layout, you might try something like this:

my $left_col = '';
for my $table_row ( @$table_ref ) {
    my @table_cols = map { (defined($_) && /\S/) ? $_ : ' '} @$ta
+ble_row;
    my $cell_str = join( '<br>', "<b>$table_cols[0]</b>", 
                          @table_cols[1..$#table_cols] );
    if ( $left_col eq '' ) {
        $left_col = $cell_str;
    } else {
        push @rows, Tr([ $left_col, $cell_str ]);
        $left_col = '';
    }
}
$page .= table( {-border => 0, width=> 550}, \@rows);
[download]

(expression).

(Again, that's not tested, but it should be close to what you want. Updated to fix the sigils on table_cols in the "join()" expression.)