At the Forge

Integrating SQL with CGI, Part 1

This month, Reuven shows us how to send and retrieve postcards on the Web using a relational database and CGI.

By Reuven M. Lerner

Last month, we began our exploration of integrating relational databases into our CGI programs. CGI programs often have to save and retrieve information. They typically do this using text files on the server's file system. By using a relational database, however, we can make our programs flexible, powerful and robust, while at the same time reducing the amount of code that we have to write.

Like the Web, relational databases use the client-server model, dividing the world into database clients (which make requests) and servers (which respond to those requests). Requests are typically written in SQL (Structured Query Language), which can be embedded within programs.

This month, we look at a simple project that uses a relational database to allow users to send electronic postcards to each other. Next month, we will spend more time on this project, improving on our original database design and making the program even more useful.

Creating a Postcard System

Over the last few years, ``postcard'' web sites have become increasingly common. These sites, which often appear just before a major event or holiday, allow people to send electronic postcards to their friends and family.

One way to accomplish this task is to e-mail the postcard, perhaps as a MIME attachment. This is relatively easy to do and makes the system relatively simple. However, such a system requires us to send the entire postcard, an operation which can use up a great deal of bandwidth if our site gets a large number of visitors.

In addition, many users still have to pay for their Internet connection and might not wish to be sent an unsolicited 100 kilobyte mail message, even if it does contain a beautiful picture and warm wishes. This can be a particular problem in the modern age of spamming, when people send each other large quantities of mail without regard for the fact that the recipient might end up having to pay for telephone and networking charges.

Add to that the fact that not everyone uses a MIME-compliant mail reader, and it becomes clear that at least for the time being, sending large, sophisticated e-mail messages is not a good way to make friends.

Thus, we will use a different approach. The postcards will be housed in a database on our server and will be accessible via a CGI program. When a postcard is created, a short e-mail message will be sent to the recipient, indicating the URL from which she can retrieve the postcard, using the web browser to display any graphics.

We will write two CGI programs: one to create the postcard and send the notification via e-mail, another to allow the recipient to retrieve the postcard.

Creating the Database

Before we can write our programs, we need to create a table in our relational database. For the purposes of demonstration, I'm using MySQL 6.3, a SQL database that runs nicely on my Red Hat Linux system. You can get more information about MySQL at http://www.tcx.se/.

In order to create the database tables, we need to decide what information we wish to store about each of the postcards. And in order to do that, we need to know how we wish the postcards to look.

Let's assume that the postcards are web pages constructed on the fly by a visitor to our site. A specific postcard is created when a CGI program receives a URL containing a unique identifier following a question mark, known in Web lingo as the ``query string''. Thus, one postcard would be available via the URL http://www.oursitename.com/cgi-bin/show-postcard.pl?12345, while another postcard would be available via the URL http://www.oursitename.com/cgi-bin/show-postcard.pl?67890.

The CGI program show-postcard.pl takes the value of the query string (which, in the above examples, is either 12345 or 67890) and uses it as an index in our database table. That table contains a short message from the sender, as well as a picture of the sender's choice.

In order to make this work, we need a table with seven columns:

The postcard ID
The sender's name
The sender's e-mail address
The recipient's name
The recipient's e-mail address
The graphic of the postcard
The text of the postcard

To avoid dealing with MySQL's security system, we put all of our table information in the database named ``test''. We can enter the MySQL command-line interface by entering the following on the command line:

mysql test

On typing that command on my system, I was greeted with the following message:

Welcome to the mysql monitor. Commands ends with ; or \g.
Type 'help' for help.

mysql>

The mysql> prompt is MySQL's way of signalling me that it is waiting for an SQL command.

Just as in C and other programming languages with typed variables, columns in an SQL table must have a data type associated with them. The main data types we will use are mediumint (a medium-size integer), varchar (a string of variable length) and blob (an untyped storage element that accepts large amounts of data).

First, we'll create a table with seven columns, one for each of the pieces of data we wish to track, using the following SQL query:

create table postcards (
	id_number mediumint not null primary key,
	sender_name varchar(60) not null,
	sender_email varchar(50) not null,
	recipient_name varchar(60) not null,
	recipient_email varchar(50) not null,
	graphic_name varchar(100) not null,
	postcard_text blob);

When I enter this at the MySQL prompt, I get the following output:

mysql>     create table postcards (
   ->   id_number mediumint not null primary key,
   ->   sender_name varchar(60) not null,
   ->   sender_email varchar(50) not null,
   ->   recipient_name varchar(60) not null,
   ->   recipient_email varchar(50) not null,
   ->   graphic_name varchar(100) not null,
   ->   postcard_text blob);
Query OK, 0 rows affected (0.02 sec)

Our database now contains a table named ``postcards'' with the appropriate seven columns. Notice how each column, except for postcard_text, is defined as being ``not null''. This indicates that the field must contain a value. By defining the table in this way, the database server can enforce some conventions about how the data is stored, thus avoiding potential problems.

The first column, id_number, will be used to identify a particular postcard. In order to ensure that only one postcard has this particular ID number, we create the id_number column with the keywords ``primary key'', which is another way of saying that its value must be unique across all rows. By making id_number a primary key, we can retrieve postcards by asking for all rows with a particular value of id_number--the result will be either a single row (with a matching value of id_number) or no rows at all (indicating that no postcard exists with that ID number).

The second through sixth columns are of type varchar, which simply means that their lengths vary as necessary, up to the maximum number of characters indicated in the parentheses. I chose these numbers somewhat arbitrarily; if you suspect that e-mail addresses will always be fewer than 50 characters, then you might wish to shorten the appropriate fields. By the same token, if you suspect that users might have names longer than 60 characters, you should extend the lengths of these fields.

The sixth column, graphic_name, contains the pathname of the graphic to be inserted in the postcard.

The final column, defined as type ``blob'', contains the text that the sender wishes to send to the recipient. Because this message is optional, we have indicated that this column can contain a null value. MySQL allows us to enter a value in postcard_text, but if we fail to provide such a value, the column will simply contain a null value.

For a summary of our table, we can use the MySQL describe command:

mysql> describe postcards;

The output of this command is shown in Table 1.

Table 1. Output of describe Command

Storing and Retrieving Postcards

Now that we have created our ``postcards'' table, let's insert some dummy data directly at the MySQL prompt. Then we will write the program show-postcard.pl to display the dummy postcard. Finally, we will write a program to allow users to enter postcards via an HTML form.

We can insert data into a table by using the SQL insert command. Let's say we wish to insert a postcard with the following information:

ID: 12345
Sender name: Reuven Lerner
Sender e-mail: reuven@netvision.net.il
Recipient name: Bill Clinton
Recipient e-mail: president@whitehouse.gov
Graphic: smile.gif
Text: Hey there, Mr. President!

To insert this information, use the following SQL command:

insert into postcards
  (id_number, sender_name, sender_email, 
   recipient_name, recipient_email, graphic_name,
   postcard_text)
values
  (12345, "Reuven Lerner", 
   "reuven@netvision.net.il",
   "Bill Clinton", 
   "president@whitehouse.gov",
   "smile.gif",
   "Hey there, Mr. President!");

Typing this command at the MySQL prompt produces this response:

Query OK, 1 rows affected (0.34 sec)

In other words, one new row was successfully added to the table. To retrieve it, use the SQL command:

select * from postcards where id_number = 12345;

which produces the result shown in Table 2.

Table 2. New Database Entry

While it might look ugly, this output actually makes sense. The problem is that most CRTs are only 80 columns wide, while the table is nearly twice that width. (And this magazine is even narrower, making it even worse.) Luckily, when our program retrieves a row, it does not have to worry about the formatting.

If you are interested only in certain columns, you can specify a subset of the entire row, as follows:

select sender_name,graphic_name,postcard_text
 from postcards where id_number = 12345;

Submitting this query produces the result shown in Table 3. The output row contains only the row corresponding to our postcard, and the columns we have requested. As an added benefit, it is easier to read than the entire row that we retrieved earlier.

Table 3. Output of select Query

Now that we have seen the sort of SQL query that is necessary to retrieve information from the database, it should be a snap to write our CGI program. You can see an initial stab in Listing 1.

This version of show-postcard.pl expects to be invoked with a single argument (the postcard's ID number) in the query string, as we mentioned earlier. If we go to the URL /cgi-bin/show-postcard.pl?12345, we should see a postcard addressed to Bill Clinton, with the message that we inserted by hand at the MySQL prompt.

The program works in a relatively straightforward way. First, it creates an instance of CGI, a Perl object (available from CPAN at http://www.perl.com/CPAN/) that makes CGI programming easier. After sending a MIME header indicating that the program will send its output in HTML-formatted text, we retrieve the value of the query string by using the param method, as follows:

my $id = $query->param("keywords");

If the query string is empty, we print out an error message and give the user another chance to enter a postcard ID number, by using the little-known <isindex> tag. Note that we check the numeric value of $id, by using the == operator, rather than simply checking to see if $id is equal (with ``eq'') to the null string. This prevents problems if someone invokes show-postcard.pl with an argument that includes characters other than numbers.

Assuming that a number does arrive in the query string, we connect to the test database, and then build up our SQL command in the variable $command. We could, of course, simply insert the command string inside of the call to $dbh->query. However, building the command in a separate string makes it easier to understand. It also has the added benefit of allowing us to debug the program. We can print the literal query by uncommenting the following debugging line:

# Uncomment for debugging
# print "<P>SQL command: \"$command\
	"</P>\n";

When the above line is uncommented, we can see exactly what is being passed to MySQL and find our program's problems more easily.

Our program then sends the request to MySQL using the ``query'' method. The value returned is a handle into the database, which can contain one or more rows matching our query. Since we are requesting all rows matching a particular ID, we can be sure that no more than one row is returned. This is true because we set id_number to be a primary key, which makes it unique.

What happens if the user invokes show-postcard.pl with an ID number that does not correspond to any postcard? If we were to ignore this possible error, users entering nonexistent ID numbers would see the outline of a postcard, but no actual content. This isn't particularly friendly for our users, who would like to know when they make a mistake and to be given a chance to correct it. Thus, before we retrieve information from the returned row, we make sure that there was a returned row. If not, then we can safely assume that this is because the ID number submitted by the user did not match the id_number column for any row in the database.

Our code accomplishes this checking by using the ``numrows'' method on the statement handle ($sth)--the object which allows us to read the results of our query. If numrows equals 0, we have not received any rows from the server, and we complain to the user that he has entered an ID number which does not match any on our server.

Obviously, to be displayed, the graphic with the specified name must exist in /tmp (or whichever directory you name in the final part of the program) on the server. If the graphic's name is misspelled or if it is placed in the wrong directory on the server, the program will not be able to display it, so take care. (We will take a closer look at this problem next month, when we look at the use of multiple tables for the graphic.)

Finally, show-postcard.pl turns the row into a Web page that can be sent to the user. Indeed, since the ``postcard'' is really a Web page, you could argue that, while I have talked about this project as if it were useful only for sending postcards, you could easily adapt this strategy for creating personalized home pages on your system, with each user getting a different page.

Creating the Postcard

Now that we can retrieve postcards without too much trouble, we have to take care of the final part of this project: allowing users to create postcards using HTML forms.

The basic idea is as follows: The sender enters all of the necessary information into an HTML form. The CGI program receiving the submitted form saves the data to the ``postcards'' table, sends e-mail to the recipient indicating how to retrieve the postcard, and thanks the sender for using our service.

We have already seen how to insert data into the table using an SQL query. All we have to do now is create a CGI program that turns the contents of a form into such a query, and an HTML form that submits its data to our program. You can see an example of such a program, send-postcard.pl, in Listing 2.

Listing 2. Program send-postcard.pl

In many ways, send-postcard.pl does the same thing as show-postcard.pl. It takes variable values from the HTML form and inserts those values into a canned SQL query. That query is then sent to the database server, which processes it--in this case, by inserting a new row into the database.

As you can see from the listing, we first grab the contents of each of the HTML form elements. In this particular version of the program, we do not check the lengths of each of the fields. It would undoubtedly be a good idea to do so in a production version, given that the database has been instructed to accept names and addresses with a certain maximum length.

Next, we create an ID number for the postcard:

my $id_number = time & 0xFFFFF & $$;

Why didn't we take a simple value, such as time (the number of seconds since January 1, 1970) or $$ (the current process ID)? And why do we perform a bitwise ``and'' on these values? Because the ID number must be unique; otherwise the database will not accept the new row. We also want to avoid sequential numbers, so that users will not be able to easily guess the numbers. This is far from random and can be guessed by someone interested in doing so; however, it is better than nothing at all and makes life a bit more interesting.

Finally, we create the entry for this postcard in the table, building up the SQL command little by little:

my $command = "";
$command = "insert into postcards ";
$command .= "  (id_number, sender_name, 
	sender_email, recipient_name, ";
$command .= "   recipient_email, graphic_name,
	postcard_text) ";
$command .= "values ";
$command .= "  ($id_number, \"$sender_name\",
\"$sender_email\", ";
$command .= "   \"$recipient_name\", \"$recipient_email\", ";
$command .= "   \"$graphic_name\", \"$postcard_text\") ";

Notice how we have to surround all but one of the values with quotation marks. This is because they are character values and blobs (as opposed to integers), and thus must be quoted when passed in an SQL query.

Once the SQL query has returned, we know that the postcard has been inserted into the database. Unless, of course, $sth is undefined, in which case we die inelegantly with an error message.

# Make sure that $sth returned reasonably
die "Error with command \"$command\""
	 unless (defined $sth);

Finally, we send e-mail to the recipient indicating that there is a postcard waiting for her, along with the URL for retrieving the postcard. So long as the ID number stored in the database matches the value of $id_number in our program, we should not have any problems. We finish up by thanking the sender for using our system.

Sending the Postcard

Now we come to the part which will enable our users to send postcards to each other: The HTML form from which the information is submitted to the send-postcard.pl program.

This form, as you might expect, is relatively straightforward. It contains five text fields, one for each of the fields we expect to get from the user, as well as a text area into which the user can enter arbitrary text. You can see the page of HTML for yourself in Listing 3.

Listing 3. HTML Form for Submitting Postcard

This system, while a bit crude, does demonstrate how to create a postcard system on your web site with a bit of work. In addition, by taking advantage of the power of SQL and the features of a relational database, we created a relatively robust system without a lot of work and without having to debug a lot of code.

You could easily add another few HTML form elements to postcard.html, making it possible for the sender of a postcard to set the background color, text style and font of a particular postcard. The possibilities are indeed limitless, although you should avoid making such an HTML form look like the cockpit of a jumbo jet.

There are, of course, a number of loose ends with this project. One such problem has to do with the graphics, which we mentioned briefly above. In addition, what happens if the ID number is lost? Currently, there isn't any way for someone to come to our site and retrieve any postcards that they might have sent or received. We will take care of that next month, as we continue to look at and use SQL in our CGI programs.

Reuven M. Lerner is an Internet and Web consultant living in Haifa, Israel, who has been using the Web since early 1993. In his spare time, he cooks, reads, and volunteers with educational projects in his community. You can reach him at reuven@netvision.net.il.