Using What We've Learned

Over the last year, we have looked at ways to create sophisticated web sites using little more than CGI programs written in Perl. Among other things, we have explored HTML/Perl templates (for separating design from programs), HTTP cookies (to identify returning users) and relational databases (to store information in a readily retrievable format while gaining robustness and security).

This month, we will see how we can use all of these techniques together. Such a combination of techniques is used on many commercial sites and is the basic idea behind several web server projects, including Microsoft's Active Server Pages (ASPs), Vignette's StoryServer, AOL's freely distributed AOLServer and the freely distributable PHP/FI.

Note that many of the examples in this month's installment refer to techniques and ideas that were discussed in previous issues of Linux Journal. If you are new to the magazine or to the ideas, please see the “Resources” sidebar, which should give you some good starting points for learning about these subjects.

Our example site will revolve around a single table tracking users' birthdays. Once we have created the table for the birthdays, we will write a CGI program that allows users to enter their birthdays into the table. Finally, we will combine the use of cookies and Perl/HTML templates to create personalized home pages drawing upon the information stored in the database.

Assuming that MySQL is our relational database server and that our table will be in the “test” database, at the prompt type the command:

mysql test

This starts the MySQL client, allowing us to enter SQL queries interactively. The “test” database used in these examples comes with MySQL and is completely insecure—it cuts down on the space required to explain how to secure new databases created with MySQL. As a result, you should seriously consider creating separate databases and users for each of your web applications, so as to reduce the risk of unauthorized users modifying or viewing data on your system.

First, we must create a table with information about our users and their birthdays. Here are some simple SQL commands that create such a table:

mysql> create table birthdays (person_id int unsigned
auto_increment,
        ->   firstname varchar(15) not null,
        ->   lastname varchar(15) not null,
        ->   email varchar(50) not null primary key,
        ->   birthdate date not null,
        ->   key id (person_id));

This creates the “birthdays” table in the “test” database. The table has five columns (person_id, firstname, lastname, email and birthdate), none of which may be blank. Each row in the table represents a distinct user whose birth date we wish to track; we ensure that no user is entered twice by setting the “email” column to be a primary key, which is a fancy way of saying that no value of “email” may be repeated. Since users may have multiple e-mail addresses, we cannot ensure that a user will not enter his or her birthday twice. However, this is likely to reduce such repetition, and is better than using the person's name, which is rarely unique.

The first column, person_id, will be set automatically by MySQL each time we add a user to the database. The first entry in the system will have person_id set to 1, the second entry will have it set to 2 and so forth. Because person_id is of type int unsigned, our system can accept no more than 4,294,967,295 unique entries—smaller than your particular database might need, but large enough for most of my purposes.

We can get a good picture of our database using the “describe” command at the “mysql” prompt, as follows:

mysql> describe birthdays;
Field       Type    Null        Key     Default Extra
person_id   int(10) unsigned    MUL     0       auto-increment
firstname   varchar(15)
lastname    varchar(15)
email       varchar(50)         PRI
birthdate   date                        0000-00-00

Now that we have created the infrastructure, we need applications that will allow people to enter their birthdays. The easiest way to do this is to create an HTML form whose contents are submitted to a CGI program that takes information from the form and saves it to the database. One such form is shown in Listing 1.

The form is relatively straightforward, although it might seem a bit daunting because of the long selection lists defined using the “select” and “option” tags. Using such lists, rather than allowing the user to enter a birthday into a text field, reduces the number of errors that a user might enter. It is certainly possible, though, that someone could create an HTML form with identical field names, using text fields instead of selection lists, and thus circumvent our system. The moral, then, is that you should always try to reduce the number of errors that users might enter, but always check to be certain that data were entered correctly.

As you can see from looking at Listing 1, our form gathers six pieces of information: first name, last name, e-mail address and user's birth month, day and year. The latter three pieces of information are separated so that we can simplify the user interface; as we will soon see, combining these to create an valid MySQL “date” type is fairly simple.

Our form's “form” tag indicates that data should be submitted using the POST method to a CGI program named “enter-birthday-info.pl”.

We take the user's input and create a SQL query that creates a new entry in the table:

# Now that we have the basic information, create
# an SQL query
my $command = "insert into birthdays ";
$command .= "(firstname, lastname, email, birthdate) ";
$command .= "values ";
$command .= "(\"$firstname\", \"$lastname\",
\"$email\", \"$birthdate\")";

Of course, you do not need to store the command in the variable $command. Indeed, you can create the command directly when using $dbh->query, rather than putting it together and then passing $command as an argument to $dbh->query. Putting the query together in this fashion makes it easier to read when programming and easier to send the SQL query to the screen if bugs appear.

After we send our query to the database server, the row is probably added. However, we do not want to just assume it was added because something serious might have happened, and we wish to give a correct indication of the outcome to the user.

First, we check to see if $dbh->errno--the value of an error returned by MySQL—was set to 2000. This is the specific error code returned when trying to insert a row that conflicts with another row. Since we have defined “email” to be a primary key, the odds are rather high that if errno is set to 2000, then we have tried to enter a duplicate e-mail address:

if ($dbh->errno == 2000)
   {
        &log_and_die(
"There is already an entry in\ the database for \"
$email\". Try another\ e-mail address!");
   }

If this was not the case, then we should check for any other error. The easiest way to detect errors is to see if $sth is undefined; if it has not been given any value, then an error occurred, which we identify for the user and in the error log. Note that our general error-catching mechanism needs to come after the mechanism for catching error 2000.

elseif (!defined $sth)
   {
     &log_and_die("MySQL error " . $dbh->errno .
"\ on command \"$command\"<P>" . $dbh->errmsg)
 unless (defined $sth);
   }

else
   {
        # Return something to the user
        print "<P>Done!</P>\n";
   }