Processing Web Input with MySQL - MySQL - page 701

1. Generate the form and send it to the users.
2. Interpret the submitted form and construct an SQL statement based on its contents.
This includes proper use of placeholders or quoting to prevent bad input from
crashing or subverting your script.
3. Execute the statement and display its result. This can be simple if the result set is
small, or more complex if it is large. In the latter case, you may want to present the
matching records using a paged display—that is, a display consisting of multiple
pages, each of which shows a subset of the entire statement result. Multiple-page
displays have the benefit of not overwhelming the user with huge amounts of in‐
formation all at once. Recipe 20.10 discusses how to implement them.
This recipe demonstrates a script that implements a minimal search interface: a form
with one keyword field, from which a statement is constructed that returns at most one
record. The script performs a two-way search of the states table. That is, if the user
enters a state name, it looks up the corresponding abbreviation. Conversely, if the user
enters an abbreviation, it looks up the name. The script, search_state.pl , looks like this:
#!/usr/bin/perl
# search_state.pl: Simple "search for state" application.
# Present a form with an input field and a submit button. User enters
# a state abbreviation or a state name into the field and submits the
# form. Script finds the abbreviation and displays the full name, or
# vice versa.
use strict ;
use warnings ;
use CGI qw(:standard escapeHTML) ;
use Cookbook ;
my $title = "State Name or Abbreviation Lookup" ;
print header (), start_html ( - title => $title );
# If keyword parameter is present and nonempty, perform a lookup.
my $keyword = param ( "keyword" );
if ( defined ( $keyword ) && $keyword !~ /^\s*$/ )
{
my $dbh = Cookbook:: connect ();
my $found = 0 ;
my $s ;
# first look for keyword as a state abbreviation;
# if that fails, look for it as a name
$s = $dbh -> selectrow_array ( "SELECT name FROM states WHERE abbrev = ?" ,
undef , $keyword );