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.

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.

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:

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.

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.

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.

Comment viewing options

Badly need your help. Keep on going and the chances are you will stumble on something, perhaps when you are least expecting it. I have never heard of anyone stumbling on something sitting down.
I am from Moldova and too poorly know English, give please true I wrote the following sentence: "Dht is a trigger chemotherapy certainly like head but with greater negah for the damage metzorah."