10.4.4.2 Querying a Database From a CGI Program

Submitted by admin on Wed, 2007-11-14 10:39

10.4.4.2 Querying a Database From a CGI Program

In Section 10.4.4.1 , we see how a database can be used to maintain values of named access counters for Web pages. The database can potentially contain tens or hundreds of thousands, or even millions of counters. However, in the SELECT query used to find the current value of a counter returns with only one value because names of counters are unique in the table. In most programs that use a database, a SELECT query produces not one, but many rows of response. The program that follows deals with such a situation. This program is invoked by the Submit Query button of the HTML form shown in Figure 10.26. The form allows a user to search a database of friends based on certain criteria. It
is an illustrative program and hence the form’s search criteria are simple. The database queried also contains only a handful of entries. The program searches the database discussed in Section 10.4.1. The database is called test. The table of interest is friends. It has the following fields: STUDENT_ID, SSNUM, NAME, AGE, HOMETOWN, SCHOOL and NOTES. The STUDENT_ID, SSNUM, and AGE fields are integers. The CGI program is invoked by the following form, shown below in HTML.

<form method="post" action=

"http://pikespeak.uccs.edu/cgi-bin/kalita/webQueryDB.pl" name="CGIDBTest">

<div align="center"><b>Find friends who satisfy the following search criteria:

</b>

<tr>

<td>

</td>

</tr>

<tr>

<td><b>Age between</b></td>

<td>

</td>

</tr>

<tr>

<td><b>Find friends</b></td>

<td>

</td>

</tr>

</table>

</div>

</form>

The HTML page looks like Figure 10.26 when seen on the Web browser. The CGI program is given below.

Figure 10.26: A HTML From in a Web Page; Querying a Database

Program 10.14

#!/usr/bin/perl -T
#webQueryDB.pl
#use strict;
use DBI;
use CGI qw(:standard);
use CGI::Carp  qw(fatalsToBrowser);
use Untaint;
use Data::Table;

my ($driver, $database,  $user, $password);
my ($dsn, $dbh,, $table,  $sth, $DBQuery);
$driver = "mysql";
#$database = q{database=test;host=pikespeak.uccs.edu;port=3306};
$database = "test";
$dsn = "DBI:$driver:$database";
$user = "Project";
$password = "Project";

my ($CGIQuery, $startAge, $endAge, $city);

#get the parameters from the HTML form
$CGIQuery = new CGI;
$startAge = $CGIQuery -> param (start_age);
$endAge = $CGIQuery -> param (end_age);
$city = $CGIQuery -> param (city);

#untaint the parameters obtained from the Web
$startAge = untaint (qr(^\d{1,3}$), $startAge);
$endAge = untaint (qr(^\d{1,3}$), $endAge);
$city = untaint (qr(^[\s\w]{1,50}$), $city);

print header ("text/html");
print start_html ("Results of Database Search");
print h2("Results of MySQL Database Search");

##need to start the connection 
$dbh = DBI->connect ($dsn, $user, $password) or
    die "Cannot connect to $dsn: $DBI::errstr";

#prepare a query; 
$table = "friends";

#Find entries  from Springs
print "\n\nAll $city entries in TABLE $table";
print " between $startAge and $endAge years old
";
print " sorted by AGE, STUDENT_ID and SSNUM\n\n";
$DBQuery = qq{select STUDENT_ID, SSNUM, NAME, 
               AGE, HOMETOWN, SCHOOL from $table  where HOMETOWN like}
          . " \"%" . $city . "%\" " .
          qq{and AGE >= $startAge and AGE <= $endAge
               order by AGE, STUDENT_ID, SSNUM};
fetchPrintTable ($dbh, $sth, $DBQuery);

#disconnect from the database
$dbh -> disconnect ();

print end_html;

#############################
#subroutine to fetch the rows and print a table
sub fetchPrintTable{
    my ($dbh, $sth, $query) = @_;
    my @data = ();
    $header = ["ID", "SSNUM", "NAME", "AGE", "HOME TOWN", "COLLEGE"];
    $sth = $dbh -> prepare ($query);
    $sth -> execute () or warn $dbh -> errstr();
    while (my $rowRef = $sth -> fetchrow_arrayref()){
        @data = (@data, [@$rowRef]);
    }
    my $dataRef = \@data;
    my $table = new Data::Table ($dataRef, $header, 0);
    print $table -> html;
}

The program uses the DBI.pm module to talk to a database, the CGI.pm module to deal with the CGI query coming from the Web browser, and the CGI::Carp.pm module to request that fatal CGI errors be displayed on the Web browser. It uses the Untaint.pm module to cleanse any data that have come from outside the program; here, obviously such data come from the HTML form displayed on the Web and filled by the user. The Untaint.pm module provides facilities to check such data so that they conform to pre-specified requirements. To be able to use the Untaint.pm
module, Perl has to be run with the -T option as shown below.

#!/usr/bin/perl -T

The program also uses the Data::Table.pm module to produce a nice-looking HTML table from the data obtained as search results from the database.

The program interacts with a MySQL database called test using the user name Project. The database server is running on the same machine as the CGI program although it does not have to be the case.

The program obtains the values of the three parameters entered in the Web browser by the user before initiating the search. We use an object-oriented interface to the CGI.pm module.

$CGIQuery = new CGI;

$startAge = $CGIQuery -> param (start_age);

$endAge = $CGIQuery -> param (end_age);

$city = $CGIQuery -> param (city);

The program untaints the parameter values by calling the untaint function of the Untaint.pm module. The untaint method takes a pattern as the first argument and the variable (here, a scalar) to untaint. If the variable’s value passes the test, the value is returned which can then be used in an assignment statement. The program untaints all three variables. qr is an operator that is used to quote a pattern in Perl. This operator quotes (and possibly compiles) its string argument as a regular expression. The string argument is interpreted the same way as PATTERN
in m/PATTERN/. If "" is used as the delimiter, no variable expansion is done. qr returns a Perl value which may be used instead of the corresponding /STRING/modifiers expression. For example,

$regularExp = qr/STRING/im;

s/$regularExp/abc/;

is the same as

s/STRING/abc/im;

The program prints some HTML header information using some of CGI.pm module’s functions. Then, the program connects to the database server; it dies if it cannot. The goal of the CGI program is to obtain names of friends for a specified city who are between the two ages given in the form. For example, in Figure 10.26, the query requires the program to search the database and find all friends who are from Colorado Springs and are between 18 and 20 years of age. The corresponding SQL query is given below.

select STUDENT_ID, SSNUM, NAME,

AGE, HOMETOWN, SCHOOL from $table where HOMETOWN like}

. " \"%" . $city . "%\" " .

qq{and AGE >= $startAge and AGE <= $endAge

order by AGE, STUDENT_ID, SSNUM

The query is constructed by concatenating a few strings. The part that deals with the hometown is given below.

HOMETOWN like "%$city%"

The SQL operator like allows patterns to be used. % stands for one or more characters. Thus, the city name provided can be preceded and followed by additional characters. For example, if the query provides the word Springs, any city that contains the word Springs will satisfy the query’s city requirements.

The program calls a subroutine fetchPrintTable that sends the query to the server, obtains the rows of the response, makes the rows into a nice HTML table, and sends the table back to the browser for display. A sample result table is shown in Figure 10.27. The HTML table constructed has the headers given in the anonymous array given below.

$header = ["ID", "SSNUM", "NAME", "AGE", "HOME TOWN", "COLLEGE"];

$header is a reference to an anonymous list of strings that are to become the headers of the HTML table’s columns. This reference is passed as an argument to the Data::Table.pm module’s new constructor later. The portion of the program that prepares and executes the query, and fetches the rows of result is given below.

$sth = $dbh -> prepare ($query);

$sth -> execute () or warn $dbh -> errstr();

while (my $rowRef = $sth -> fetchrow_arrayref()){

@data = (@data, [@$rowRef]);

}

The SQL query passed to the subroutine is prepared and executed. The database returns a number of rows that satisfy the query. The conditional of the while loop is a call to the following method on the statement handle.

$sth -> fetchrow_arrayref()

The fetchrow_arrayref method of a statement handle object returns a reference to the next row of data returned by the database server. Perl makes sure that the reference returned is a reference to an array that contains the row’s data. When there is no more selected row left, fetchrow_arrayref returns undef. Inside the body of the while loop, an array of references is constructed. Each element of the array @data is an anonymous array containing all the elements of a row. Thus, @data is an array of anonymous rows.

The HTML table can be easily constructed by printing out the appropriate HTML tags along with the data. However, we take a short cut by using the Data::Table.pm module that can perform sophisticated table construction and manipulation. It is powerful although we use it only for constructing a simple HTML table.

my $table = new Data::Table ($dataRef, $header, 0);

print $table -> html;

The new constructor for the Data::Table.pm class takes three arguments, the first being a reference to an anonymous array of anonymous arrays. The inside arrays must have the same number of elements as the number of elements in the anonymous array containing the header strings discussed earlier. The second argument is the reference to this list of header strings. The third argument is a zero saying that the data is an array of table rows. An one means that the data is an array of table columns. The table is sent by the CGI program to the Web browser. The actual HTML produced for the table is shown below.

<TR BGCOLOR="#CCCC99"><TH>ID</TH><TH>SSNUM</TH><TH>NAME</TH><TH>

AGE</TH><TH>HOME TOWN</TH><TH>COLLEGE</TH></TR>

<TD>Joe White</TD><TD>18</TD><TD>Colorado Springs</TD><TD>PPCC</TD></TR>

<TD>Shane Jahnke</TD><TD>19</TD><TD>Colorado Springs</TD>

<TD>Justin O'Malley</TD><TD>19</TD>

<TD>Colorado Springs</TD><TD>NULL</TD></TR>

<TD>Nick Freeman</TD><TD>19</TD><TD>Colorado Springs</TD>

<TD>Seth Gross</TD><TD>20</TD><TD>Colorado Springs</TD><TD>UCCS</TD></TR>

</TABLE>

</body></html>

Figure 10.27: An HTML Table Produced by a CGI Program

Add new comment

On Perl

10.4.4.2 Querying a Database From a CGI Program

10.4.4.2 Querying a Database From a CGI Program