Creating a database

We will start out by discussing how a database is created. Suppose you have the following information:

The SQL code to create this database is:

create table Player_Info
(
    Player            character varying (30) not null,
    Years             integer,
    Points            integer,
    Rebounds          integer,
    Assists           integer,
    Championships     integer
);

The create table command creates a database, or a table. The Player field is stored as a non-null varying character string. In other words, if the data in the field is less than 30 characters, the database will not pad it with spaces, as it would for a regular character data type. Also, the database forces the user to enter a value for the Player field; it cannot be empty.

The rest of the fields are defined to be integers. Some of the other valid data types include date, time, smallint, numeric, and decimal. The numeric and decimal data types allow you to specify floating-point values. For example, if you want a five-digit floating-point number with a precision to the hundredth place, you can specify decimal (5, 2).

Accessing data

Let's say you want a list of the entire database. You can use the following code:

select * 
    from Player_Info;

The select command retrieves specific information from the database. In this case, all columns are selected from the Player_Info database. The "*" should be used with great caution, especially on large databases, as you might inadvertently extract a lot of information. Notice that we are dealing only with columns, and not rows. For example, if you wanted to list all the players in the database, you could do this:

select Player
    from Player_Info;

Now, what if you want to list all the players who scored more than 25 points? Here is the code needed to accomplish the task:

select *
    from Player_Info
    where (Points > 25);

This would list all the columns for the players who scored more than 25 points:

But, say you wanted to list just the Player and Points columns:

select Player, Points
    from Player_Info
    where (Points > 25);

Here is an example that returns all the players who scored more than 25 points and won a championship:

select Player, Points, Championships
    from Player_Info
    where (Points > 25) and
          (Championships > 0);

The output of this SQL statement would be:

You could also use wildcards in a select command. For example, the following will return all the players that have a last name of "Johnson":

select *
    from Player_Info
    where Player LIKE '% Johnson';

This will match a string ending with "Johnson".

Updating a database

Let's suppose that Shaquille O'Neal won a championship. We need to update our database to reflect this. This is how it can be done:

update Player_Info
    set Championships = 1
    where Player = 'Shaquille O'Neal';

SQL also has methods to modify entire columns. After every basketball season, we need to increment the Years column by one:

update Player_Info
    set Years = (Years + 1);

Now, let's discuss insertion into a table. Say we need to add another player to the database. We could do it this way:

insert into Player_Info
    values
    ('Hakeem Olajuwon', 10, 27, 11, 4, 2);

As you can see, it is very simple to insert an element into the table. However, if you have a database with a large number of columns, and you want to insert a row into the table, you can manually specify the columns:

insert into Player_Info
    (Player, Years, Points, Rebounds, Assists, Championships)
    values
    ('Hakeem Olajuwon', 10, 27, 11, 4, 2);

When used in this context, the order of the fields does not necessarily have to match the order in the database, as long as the fields and the values specified match each other.

Deleting information

If you wanted to delete "John Stockton" from the database, you could do this:

delete from Player_Info
    where Player = 'John Stockton';

SQL also allows you remove entire columns. You should be very careful when attempting such a move. Instead, it is much safer to create another database, and copy only the columns you want to the new database. Here is how you would remove a column:

alter table Player_Info
    drop column Championships;

If you want to delete all the records in the table, the following statement is used:

delete from Player_Info;

And finally, the drop table command deletes the entire database:

drop table Player_Info;

(For more information on SQL, see the reference guide on SQL-92 at http://sunsite.doc.ic.ac.uk/packages/perl/db/refinfo/sql2/sql1992.txt).

Employee database

Let's assume that you have a text file that contains a list of your company's employees, as well as some information about them:

Last,First,Job_Title,Department,EMail,Phone
Supra,John,System Operator,Systems,jsupra,(617) 555-1578
Painton,Todd,Network Engineer,Systems,tpainton,(617) 555-6530
Martin,Robert,Sales Representative,Sales,martinr,(617) 555-7406
Levine,Julia,Administrative Assistant,Administration,julia,(617) 555-3056
Keenan,Jeff,Manager,Software,jeffk,(617) 555-7769
Nets,Laurie,Group Leader,Development,lnets,(617) 555-9962

The first line of the file contains the field names (delimited by commas). This is all you need to use the database. Unlike other databases that store the data in a unique (and strange) format, Sprite operates on plain text.

Here is the form that will act as the front end to the database:

<HTML>
<HEAD><TITLE>CGI Corporation</TITLE></HEAD>
<BODY>
<H1>Employee Database</H1>
Welcome to the CGI Corporations's Employee Search Form. You can use
this to find information about one of our employee. 
Enter as much information as possible to narrow down the search. 
<HR>
<FORM ACTION="/cgi-bin/db_phone.pl" METHOD="POST">
<PRE>
Last Name:      <INPUT TYPE="text" NAME="Last" SIZE=40>
First Name:     <INPUT TYPE="text" NAME="First" SIZE=40>
Job Title:      <INPUT TYPE="text" NAME="Job_Title" SIZE=40>
Department:     <INPUT TYPE="text" NAME="Department" SIZE=40>
EMail Address:  <INPUT TYPE="text" NAME="EMail" SIZE=40>
Phone Number:   <INPUT TYPE="text" NAME="Phone" SIZE=40>
</PRE>
<INPUT TYPE="submit" VALUE="Submit the search">
<INPUT TYPE="reset"  VALUE="Clear all fields">
</FORM>
<HR>
</BODY></HTML>

The form is shown in Figure 9.5.

Now, let's build the CGI application that will decode the form information, process the user's query, and create a document displaying the results, as seen in Figure 9.6.

The program begins:

#!/usr/local/bin/perl5
use Sprite;
$webmaster = "shishir\@bu\.edu";
$query = undef;

The use command instructs Perl to load the module (or extension). You can load more than one module at a time. For example, if we wanted to create dynamic GIF images from the data contained in a database, we would have to load both the GD and the Sprite modules:

use GD;
use Sprite;

To continue with the program:

&parse_form_data(*FORM);
$fields = '(Last|First|Job_Title|Department|EMail|Phone)';

The form data is decoded. The parse_form_data subroutine used in this program is the one we've been using throughout this book. The fields variable contains a list of all the fields in the form. You might wonder why we would need to have such a list when then the parse_form_data subroutine decodes all the fields in the form. The reason for this is to make sure that only valid fields are processed, as the search query is dynamically created from the user-specified information. Remember, forms are very insecure; a cracker can download a form, edit it, add an extra field, and submit the form to the program. If the program is not carefully designed, we could have a major problem!

foreach $key (keys %FORM) {
    if ( ($key !~ /\b$fields\b/o) || ($FORM{$key} =~ /[^\w\-\(\) ]/) ) {
        &return_error (500, "CGI Corporation Employee Database Error",
                            "Invalid Information in Form.");

The foreach construct iterates through all of the fields stored in the FORM associative array, and checks for two things, represented by the two expressions separated by the || operator. First, the field is checked against the list stored in the fields variable for validity. Second, it makes sure the information entered by the user is constrained to the following characters: A-Z, a-z, 0-9, (, ), and the space character. This ensures that no shell metacharacters are passed.

    } else {
        $FORM{$key} =~ s/(\W)/\\$1/g;
        if ($FORM{$key}) {
            $query = join (" and ", $query, "($key =~ /$FORM{$key}/i)");
        }
    }
}

The conditional is executed if the field is valid. It checks to see if any information was entered in the field. If there is information, a query is built by joining each field and value with "and". You would normally have to escape the "/" character if you are using the regular expression search in Sprite. In this case, you don't need to because the user cannot enter "/" in any search field.

Once the loop terminates, a query might look something like the following:

and (Last =~ /Martin/i) and (First =~ /Robert/i) and (Department =~ /Sales/i)

The reason the query has an "and" at the beginning has to do with the way in which the query was created. If you look back at the join command, you can see that the information stored in the query variable is concatenated to a combination of a key and a value with "and", and is finally stored in query. Remember, $query will be undefined the first time through the loop, and thus will end up with an "and" at the beginning. Let's remove the unwanted initial string.

if ($query) {
    $query =~ s/^ and //;
} else {
    &return_error (500, "CGI Corporation Employee Database Error",
                        "No query was entered.");
}

If the user failed to enter any information, an error message is displayed. Otherwise, the "and" at the beginning of the query is removed to create a normal query:

(Last =~ /Martin/i) and (First =~ /Robert/i) and (Department =~ /Sales/i)

Note that Sprite allows you to use regular expression operators to search for data. If the user entered "M" in the last name field, this program instructs the database to return all records that contain the letter "M" (or "m", as the "i" flag indicates case insensitivity). There are cases when this is not desirable. In such cases, you would need to modify the way the query is joined:

$FORM{$key} = s/(['"])/\\$1/g;
$query = join (" and ", $query, "($key = '$FORM{$key}')");

This will return only exact matches. Since the value in the field is a string, you need to enclose $FORM{$key} in single quotes and escape all other quotes (or Sprite will return an error).

$rdb = new Sprite ();
$rdb->set_delimiter ("Read", ",");

This is some object-oriented Perl syntax that you saw in Chapter 6, Hypermedia Documents. A new database object is created, and the reference to it is stored in the variable rdb. The set_delimiter function sets the delimiter for the data stored in the database. The set_delimiter function takes two arguments. In the first, we specify that we are reading from the database. In the second, we specify the comma as the field delimiter (so we have to know what the data file looks like).

@data = $rdb->sql (<<End_of_Query);
    select * from phone.db
    where $query
End_of_Query

The query is passed to Sprite with the sql function. In this case, a here document is used to pass the query (so it looks readable to humans). You could just as easily do this:

@data = $rdb->sql ("select * from phone.db where $query");

Sprite returns the matched records as an array, with all the fields in each record joined by the null character "\0". However, the first element of the array is not a record, but a flag indicating success or failure. For instance, if you passed the following query:

select * from phone.db where (Department =~ /Systems/i)

the array would look like this:

$data[0] = 1
$data[1] = Supra\0John\0System Operator\0Systems\0jsupra\0(617) 555-1578
$data[2] = Painton\0Todd\0Network Engineer\0Systems\0tpainton\0(617) 555-6530

A value of 1 indicates success, while a 0 indicates failure.

$status = shift (@data);
$no_elements = scalar (@data);

The shift statement removes the first element of the array and stores it in the variable status. Then scalar is used to determine the number of elements in the array. You can also evaluate the array in a scalar context, without using the scalar command:

$no_elements = @data;

This is the same as using the scalar command, but different from:

$last_element = $#data;

This returns the index of the last element of the array (so in most cases, it would have a value one less than the number of elements, as arrays are zero-based).

if (!$status) {
    &return_error (500, "CGI Corporation Employee Database Error",
                        "Sprite Database Error!");
} elsif (!$no_elements) {
    &return_error (500, "CGI Corporation Employee Database Error",
                        "The record you specified does not exist.");

Two things are checked: the error status and the number of records returned by Sprite. If either the status is 0 or no records were returned, an error is displayed.

} else {
    print <<End_of_HTML;
Content-type: text/html
<HTML>
<HEAD><TITLE>CGI Corporation Employee Directory</TITLE></HEAD>
<BODY>
<H1>CGI Corporation Employee Directory</H1>
<HR><PRE>
End_of_HTML

This code is executed if valid records were returned by Sprite. We are now formatting the output for display. One of Perl's original attractions was the report-generating features it offered; Larry Wall even said that the "rl" in Perl stood for "Reporting Language." We will use some of those powerful features here. What we have to do is create a format and assign it to the $~ variable. Then, whenever we issue a write statement, Perl will print the data according to the format.

    $~ = "HEADING";
    write;

The "HEADING" format is selected to display header information.

    $~ = "EACH_ENTRY";
    foreach (@data) {
         s/([^\w\s\0])/sprintf ("&#%d;", ord ($1))/ge;
        ($last, $first, $job, $department, $email, $phone) =
            split (/\0/, $_, 6);
        write;
    }
    print "</PRE>", "\n";
    print "<HR>";
        print "</BODY></HTML>", "\n";
}

The "EACH_ENTRY" format is selected to display each record from the phone database. The foreach loop iterates through each record, splits it into the different fields, and issues a write to display the data. Note that no variable was supplied as part of the foreach loop. Normally, we would have something like this:

foreach $record (@data) {
    .
    .
    .
}

Since we did not supply a variable, Perl automatically places it in its default variable: $_.

$rdb->close ();
exit (0);

Finally, the database is closed, and the script terminates. Now, let's look at the two format statements:

format HEADING = 
Last       First      Job Title      Department   EMail       Phone
----       -----      ---------      ----------   -----       -----
.

This is a simple one! It is used as a header to display all of the fields. The period on a line by itself terminates the format.

format EACH_ENTRY = 
@<<<<<<<<  @<<<<<<<<  @<<<<<<<<<<<<  @<<<<<<<<<<  @<<<<<<<<<  @<<<<<<<<<<<<<
$last,     $first,    $job,          $department, $email,     $phone
.

This one is a little more complex. The "@<<<<<<<<" indicates an eight-character, left-justified field holder. The value stored in the variable, which is listed below a field holder, is displayed each time a write is called. This will allow for a neat and clean display, as shown in Figure 9.7.

Student database

A CGI program is not limited to just reading information from a database; it can also manipulate the information. Here is a CGI program that can read, modify, and delete a database consisting of student information. Before we go any further, let's look at the supporting HTML documents:

<HTML>
<HEAD><TITLE>Welcome to CGI Educational Center</TITLE></HEAD>
<BODY>
<H1>Student Database</H1>
You can use a combination of forms and CGI to access and modify information in
the student database. Please choose one of the following options:
<HR>
<A HREF="/add.html">Add New Student</A><BR>
<A HREF="/modify.html">Modify Student Information</A><BR>
<A HREF="/view.html">View Student Information</A><BR>
<A HREF="/delete.html">Delete Student</A><BR>
<HR>
</BODY>
</HTML>

This is the initial document containing links to the various forms that allow the user to view, add, modify, and delete information from the student database.

<HTML>
<HEAD><TITLE>Welcome to CGI Educational Center</TITLE></HEAD>
<BODY>
<H1>Add New Student</H1>
<HR>
<FORM ACTION="/cgi-bin/student.pl?add" METHOD="POST">
<PRE>
Student Name:       <INPUT TYPE="text" NAME="Student" SIZE=40>
Year of Graduation: <INPUT TYPE="text" NAME="YOG" SIZE=4 MAXLENGTH=4>
Address (Mailing Information):
<TEXTAREA NAME="Address" ROWS=4 COLS=40></TEXTAREA>
</PRE>
<INPUT TYPE="submit" VALUE="Add New Student">
<INPUT TYPE="reset"  VALUE="Clear the Information">
</FORM>
<HR>
</BODY></HTML>

This is the form used to add information into the database. When the user submits this form, a query of "add" is sent to the CGI program.

<HTML>
<HEAD><TITLE>Welcome to CGI Educational Center</TITLE></HEAD>
<BODY>
<H1>Modify Student Information</H1>
<HR>
<FORM ACTION="/cgi-bin/student.pl?modify_form" METHOD="POST">
Student Name: <INPUT TYPE="text" NAME="Student" SIZE=40>
<P>
<INPUT TYPE="submit" VALUE="Modify Student Information">
<INPUT TYPE="reset"  VALUE="Clear the Information">
</FORM>
<HR>
</BODY>
</HTML>

This form allows the user to modify information for a particular student. When this form is submitted, the program builds and displays another form dynamically. Here is the form used to view the results of a specified query.

<HTML>
<HEAD><TITLE>Welcome to CGI Educational Center</TITLE></HEAD>
<BODY>
<H1>View Student Information</H1>
<HR>
<FORM ACTION="/cgi-bin/student.pl?view" METHOD="POST">
Student Name: <INPUT TYPE="text" NAME="Student" SIZE=40>
<P>
Year of Graduation:
<INPUT TYPE="radio" NAME="Sign" VALUE="greater"> Greater Than
<INPUT TYPE="radio" NAME="Sign" VALUE="equal" CHECKED> Equal To
<INPUT TYPE="radio" NAME="Sign" VALUE="less"> Less Than
<INPUT TYPE="text" NAME="YOG" SIZE=4 MAXLENGTH=4>
<P>
Address Information: <INPUT TYPE="text" NAME="Address" SIZE=40>
<P>
<INPUT TYPE="submit" VALUE="View Student Information">
<INPUT TYPE="reset"  VALUE="Clear the Information">
</FORM>
<HR>
</BODY>
</HTML>

This form is used to view records that match certain criteria. The user can select records based on a conditional year of graduation (either greater than, less than, or equal to a certain year). We could have just as easily allowed mathematical operators (>, <, and =) to be entered, but this can be a potential security hole, as some of them have a special meaning to the shell (i.e., shell metacharacters). It is far better and safer to use strings like "equal", "greater", and "less", and let the CGI program convert them to the appropriate operators when creating a query.

<HTML>
<HEAD><TITLE>Welcome to CGI Educational Center</TITLE></HEAD>
<BODY>
<H1>Delete Student</H1>
<HR>
<FORM ACTION="/cgi-bin/student.pl?delete" METHOD="POST">
<PRE>
Student Name:       <INPUT TYPE="text" NAME="Student" SIZE=40>
Year of Graduation: <INPUT TYPE="text" NAME="YOG" SIZE=4 MAXLENGTH=4>
</PRE>
<INPUT TYPE="submit" VALUE="Delete Student">
<INPUT TYPE="reset"  VALUE="Clear the Information">
</FORM>
<HR>
</BODY>
</HTML>

A user can use this form to delete information from the database. In this case, only the student name and year of graduation fields are presented. Records for an entire class can be deleted by specifying the year of graduation, and leaving the name field empty. You should not normally allow such a dangerous option! However, it is shown here to illustrate the power of databases and the Web.

Now, let's look at the CGI program that works with these forms.

#!/usr/local/bin/perl5
use Sprite;
$query_string = $ENV{'QUERY_STRING'};
$script = $ENV{'SCRIPT_NAME'};
$request_method = $ENV{'REQUEST_METHOD'};
$webmaster = "shishir\@bu\.edu";
$database = "/home/shishir/student.db";
$main_form = "/student.html";
$commands = '(add|modify_form|modify|view|delete)';

The Sprite module is loaded. The full path to the student database and the relative path to the main HTML document (the one that contains links to the other forms) are stored in the database, and main_form variables, respectively. Finally, commands contains a list of the valid queries that forms can pass to this program. If you look carefully at the list, you will notice that none of the forms listed above passes the "modify" query. The form that passes this is dynamically created by this program, as you will later see.

$delimiter = "::";
$error = "CGI Student Database Error";

Fields in the student database are delimited by the "::" characters.

if ($query_string =~ /^\b$commands\b$/) {
    &parse_form_data (*DB);

If the query is valid, the POST form data is decoded and placed in the DB associative array. (As always, the parse_form_data subroutine used in this program is the one we've been using throughout all our examples.)

    &check_all_fields ();
    &check_database ();

The check_all_fields subroutine iterates through the DB associative array to ensure that there are no shell metacharacters. The check_database subroutine checks to see if the student database exists. If not, a new one is created.

    $rdb = new Sprite ();
    $rdb->set_delimiter ("Read",  $delimiter);
    $rdb->set_delimiter ("Write", $delimiter);

A new database object is created. The set_delimiter function sets the delimiter to be used when reading from and writing to a database.

    $command_status = &$query_string ();

This is a construct that you may not have seen before. The subroutine corresponding to the value stored in query_string is called. It is equivalent to saying:

if ($query_string eq "add") {
    $command_status = &add ();
} elsif ($query_string eq "modify_form") {
    $command_status = &modify_form ();
} elsif ($query_string eq "modify") {
    $command_status = &modify ();
} elsif ($query_string eq "view") {
    $command_status = &view ();
} elsif ($query_string eq "delete") {
    $command_status = &delete ();
}

How convenient! Now, let's continue on with the program.

    if ($command_status) {
        $rdb->close ($database);
        print "Location: ", $main_form, "\n\n";
    } else {
        $rdb->close ();
    }

Depending on the status returned from one of the subroutines above, a server redirect is done with the Location: header. There is a subtle difference between the two $rdb->close subroutines. If you specify a database as part of the close subroutine, the modifications performed on that database are saved. Otherwise, the changes are discarded.

} else {
    &return_error (500, $error,
                "Invalid command passed through QUERY_STRING.");
}
exit (0);

If an invalid query was passed to this program, an error is returned.

The following subroutine checks to see if the database exists. If it does not, a new database is created, and a header line containing the field names, delimited by "::", is output.

sub check_database
{
    local ($exclusive_lock, $unlock, $header);
    $exclusive_lock = 2;
    $unlock = 8;
    if (! (-e $database) ) {
        if ( open (DATABASE, ">" . $database) ) {
            flock (DATABASE, $exclusive_lock);
            $header = join ($delimiter, "Student", "YOG", "Address");
            print DATABASE $header, "\n";
            flock (DATABASE, $unlock);
            close (DATABASE);
        } else {
            &return_error (500, $error, "Cannot create new student database.");
        }
    }
}

The check_all_fields subroutine makes sure the form elements do not contain shell meta-characters:

sub check_all_fields
{
    local ($key);
    foreach $key (keys %DB) {
        if ($DB{$key} =~ /[`\!;\\\*\\$[amp   ][lt    ][gt    ]]/) {\n              [amp   ]\|return_error (500, $error,
                                  "Invalid characters in the [$key] field.");
        }
    }
}

The subroutine iterates through the DB associative array checking to make sure that none of the elements contains any dangerous shell metacharacters. If any are found, an error message is displayed.

sub build_check_condition
{
    local ($columns) = @_;
    local ($all_fields, $loop, $key, $sign, $sql_condition);

This is a very useful subroutine that dynamically builds a query. It expects a string in the following format:

"Student,=,Address,=~"

From this, the following query is constructed (assuming that the user entered "Ed Surge" in the student field, and "Elm Street" in the address field):

(Student = 'Ed Surge') and (Address =~ 'Elm Street')

(You might have noticed that the regular expression is not the usual format ($string =~ /abc/). You are correct! However, Perl accepts this format as well.)

    @all_fields = split (/,/, $columns);

The all_fields array consists of successive elements of the field name, followed by the operator that should be used to search that field. In this example, the array would look like this:

$all_fields[0] = "Student";
$all_fields[1] = "=";
$all_fields[2] = "Address";
$all_fields[3] = "=~";

Now, let's look at the loop that iterates through this array to build the query.

    for ($loop=0; $loop <= $#all_fields; $loop = $loop + 2) {
        $key  = $all_fields[$loop];
        $sign = $all_fields[$loop + 1];

The key and the sign variables consist of the field name and the operator, respectively.

        if ($DB{$key}) {
            $DB{$key} =~ s/([\W])/\\$1/g;
            $sql_condition = join (" and ", $sql_condition,
                    "( $key $sign '$DB{$key}' )", );
        }
    }

The query is built in nearly the same manner as in the preceding example, except that the operator can be different for each field.

    if ($sql_condition) {
        $sql_condition =~ s/^ and //;
        return ($sql_condition);
    } else {
        &return_error (500, $error, "No query was entered.");
    }
}

If the user did not enter any information into the fields, an error message is displayed. Otherwise, the dynamically created query is returned (to the subroutine that called).

This is a very simple subroutine (if you can call it that) that returns an error.

sub database_error
{
    &return_error (500, $error,
        "Sprite database error. Please check the log file.");
}

The only reason this statement was placed in a subroutine is for convenience. For example, it is much shorter and quicker to say:

$rdb->update (<<Update_Command) || &database_error ();

than to say:

$rdb->update (<<Update_Command) || &return_error (500, $error,
        "Sprite database error. Please check the log file.");

This is especially true if the same error needs to be returned for various problems.

The check_select_command subroutine is generally used after an SQL "select" statement. It checks the first element of the returned data, as well as the number of records returned, and displays an error if either of these values equal 0. Otherwise, a status of 1 is returned.

sub check_select_command
{
    local ($value, $no_elements) = @_;
    if (!$value) {
        &database_error ();
    } elsif (!$no_elements) {
        &return_error (500, $error, 
            "The record you specified does not exist.");
    } else {
        return (1);
    }
}

The add subroutine inserts a record into the database.

sub add
{
    $DB{'Address'} =~ s/\n/<BR>/g;
    $DB{'Address'} =~ s/(['"])/\\$1/g;
    $DB{'Student'} =~ s/(['"])/\\$1/g;
    $rdb->sql (<<End_of_Insert) || &database_error ();
        
insert into $database
    (Student, YOG, Address)
values
    ('$DB{'Student'}', '$DB{'YOG'}', '$DB{'Address'}')
End_of_Insert
    return (1);
}

All newline characters are converted to "<BR>" and all single and double quotes are escaped. Remember, all records in a text-delimited database are delimited by newline characters! This ensures that the data will be correctly displayed by the browser when the user decides to view it.

The format for the "insert" SQL statement is the same as described in the SQL primer earlier. If the record could not be inserted into the database, an error is returned. Otherwise, a status of 1 is returned. This instructs the script to save the database and perform a server redirect to display the main HTML document.

Now for the most complicated action--modifying a row.

sub modify_form
{
    local (@info, $modify_status, $no_elements, $status);
    $DB{'Student'} =~ s/(['"])/\\$1/g;
    @info = $rdb->sql (<<End_of_Select);
select * from $database
where (Student = '$DB{'Student'}')
End_of_Select
    $status = shift (@info);
    $no_elements = scalar (@info);
    $modify_status = &check_select_command ($status, $no_elements);
    if ($modify_status) {
        &display_modify_form ($info[0]);
    }
    return (0);
}

This subroutine performs two actions. First, it uses the student's name, as specified in the modify form (shown with the other forms at the beginning of this section), to retrieve the record for that student. The check_select_command subroutine ensures that data was returned by the database. Second, display_modify_form is called (with the first record in the array as an argument) to display a new form that contains all of the information about the student. The user can then modify the data in the form and submit it.

A status of 0 is returned by this subroutine. As a result, the database is not saved (which is what we want, since it was not modified), and no server redirection is performed.

The display_modify_form subroutine returns a form for changing a student's record.

sub display_modify_form
{
    local ($fields) = @_;
    local ($student, $yog, $address);
    ($student, $yog, $address) = split (/\0/, $fields);
     $address =~ s/<BR>/\n/g;
     $student = &escape_html ($student);
     $yog = &escape_html ($yog);

The record that is passed to this subroutine by modify_form is split on the "\0" delimiter, and the "<BR>" characters are converted back to newlines. In addition, we call the escape_html subroutine to "escape" characters that have a special significance to the browser, such as the double quote, "<", ">", and "&". We perform these steps so that the information is displayed properly.

    print <<End_of_Modify_Form;
Content-type: text/html
<HTML>
<HEAD><TITLE>CGI Educational Center</TITLE></HEAD>
<BODY>
<H1>Modify Student Information</H1>
<HR>
<B>Student Name: $student</B>
<P>
<FORM ACTION="$script?modify" METHOD="POST">
<INPUT TYPE="hidden" NAME="Student" VALUE="$student">
Year of Graduation:
<INPUT TYPE="text" NAME="YOG" SIZE=4 MAXLENGTH=4 VALUE="$yog">
<P>
Address (Mailing Information):
<TEXTAREA NAME="Address" ROWS=4 COLS=40>
$address
</TEXTAREA>
<P>
<INPUT TYPE="submit" VALUE="Modify Record For: $student">
<INPUT TYPE="reset"  VALUE="Clear the Information">
</FORM>
<HR>
</BODY>
</HTML>
End_of_Modify_Form
}

The form containing the information for the specified student is output. The user can now modify this form. We use $student twice: once to remind the user which student was chosen, and once to pass the name back to this CGI program so it modifies the right row. The form is shown in Figure 9.8.

The escape_html subroutine escapes certain characters so that they are displayed correctly by the browser.

sub escape_html
{
    local ($string) = @_;
    local (%html_chars, $html_string);
    
    %html_chars = ('&', '&',
                   '>', '>',
                   '<', '&lt;',
                   '"', '&quot;');
    $html_string = join ("", keys %html_chars);
    $string =~ s/([$html_string])/$html_chars{$1}/go;
    return ($string);
}

The view subroutine shows a student's current record.

sub view
{
    local ( $fields, $query, @students, $view_status, $status, $no_elements);
    $fields = 'Student,=,Address,=~';
    if ($DB{'YOG'}) {
        if ($DB{'Sign'} eq 'greater') {
            $DB{'Sign'} = '>';
        } elsif ($DB{'Sign'} eq 'less') {
            $DB{'Sign'} = '<';
        } else {
            $DB{'Sign'} = '=';
        }
        $fields = join (",", $fields, 'YOG', $DB{'Sign'});
    }
    $query = &build_check_condition ($fields);

If the user entered information into the year of graduation field, the search operator is determined. This is then appended to the value stored in the fields variable. The build_check_condition subroutine is called to dynamically construct the search query.

    @students = $rdb->sql (<<End_of_Display);
select * from $database
    where $query
End_of_Display
    $status = shift (@students);
    $no_elements = scalar (@students);
    $view_status = &check_select_command ($status, $no_elements);

The query is passed to the select command. The information returned by the database is checked for possible errors. If there are no errors, view_status contains the value of 1.

    if ($view_status) {
        &display_results ("View Students", *students);
    }
    return (0);
}

If the data returned by the database is valid, the display_results subroutine is called to display the search results. The two arguments passed to the subroutine are the header for the HTML document and the reference to the array that contains the results.

sub display_results
{
    local ($title, *data) = @_;
    local ($student, $yog, $address);
    print "Content-type: text/html", "\n";
    print "Pragma: no-cache", "\n\n";
        print "<HTML>", "\n";
    print "<HEAD><TITLE>CGI Educational Center</TITLE></HEAD>";
        print "<BODY>", "\n";
    print "<H1>", $title, "</H1>";
    print "<HR>";

The Content-type: and Pragma: MIME headers are output. We do not want the browser to cache the page containing the results. As a result, the displayed data reflects the true status of the database.

    foreach (@data) {
        s/([^\w\s\0])/sprintf ("&#%d;", ord ($1))/ge;
        ($student, $yog, $address) = split ("\0", $_, 3);
        $student = "NULL"                      if (!$student);
        $yog     = "Unknown graduation date"   if (!$yog);
        $address = "No address specified"      if (!$address);

If any of the fields for a record are null, certain default values are used, so as not to display empty fields.

        $address =~ s/&#60;BR&#62;/<BR>/g;
        print "<BR>", "\n";
        print "<B>", $student, "</B> ", "($yog)", "<BR>", "\n";
        print $address, "<BR>", "\n";
    }
    print "<HR>", "\n";
        print "</BODY></HTML>", "\n";
}

The foreach loop iterates through the matched records, and displays them.

The delete subroutine removes records from the database.

sub delete
{
    local ($fields, $query);
    $fields = 'Student,=,YOG,=';
    $query = &build_check_condition ($fields);
    $rdb->sql (<<End_of_Delete) || &database_error ();
delete from $database
    where $query
End_of_Delete
    return (1);
}

Multiple records can be deleted by leaving the student field empty, but entering a valid year for the YOG field. If the specified records cannot be deleted, an error message is displayed.

Existing records are modified with the modify subroutine.

sub modify
{
    local (@fields, $key);
    @fields = ('YOG', 'Address');
    $DB{'Address'} =~ s/\n/<BR>/g;
    $DB{'YOG'} =~ s/(['"])/\\$1/g;
    $DB{'Student'} =~ s/(['"])/\\$1/g;
    $DB{'Address'} =~ s/(['"])/\\$1/g;
    foreach $key (@fields) {
        $rdb->sql (<<Update_Database) || &database_error ();
update $database
set $key = ('$DB{$key}') 
where (Student = '$DB{'Student'}');
Update_Database
    }
    return (1);
}

The current version of Sprite does not support multiple fields in a update statement. As a result, a loop is used to update the record multiple times. If the user entered "1991" in the year of graduation field, and "Elm Street, 02215" in the address field, the two update statements are generated:

update /home/shishir/student.db
set YOG = ('1991')
where (Student = 'Ed Surge')
update /home/shishir/student.db
set Address = ('Elm Street, 02215')
where (Student = 'Ed Surge')

That concludes the section on Sprite.