Perl CGI & Databases

Perl and Database Connectivity

Web based applications often need to access information that is stored in some kind of a database, the information needed includes everything from user account information to product lists. Although it is possible to create simple "flat-file" databases using the Perl I/O functions, there are numerous advantages to using a real database system to store data. In this section we will explore the Win32:ODBC Perl module which provides access to nearly any database stored on a Windows based PC from a Perl program. Although we will focus on the interface between Perl and a MS Access database, the concepts extend to many other operating system/database system combinations.

We will examine the steps necessary to develop and install a sample Perl CGI program that accesses user account information that is stored in a MS Access Database. Although we develop a working CGI program that uses a database to store information, a complete discussion of database connectivity, SQL, and Perl ODBC module are beyond the scope of this course. Links are provided to sources of more complete information on these topics.

MS Access

Microsoft Access is a fairly simple database system intended to support small databases for individual use, or use by small workgroups. Although Access is not the best choice for supporting the needs of a large web-based application, we use it because it's easy to get started (you don't need to be a database expert to do simple things) and because we all already have it. There are many other commercial database systems that are more powerful and would generally be used to support real web-based applications (for example, Oracle is used by many large web-sites).

In Access you can create a database that includes multiple tables. Each table is composed of a list of fields, each field has a name, type and value (there are many other attributes that can be associated with fields as well). The fields correspond to the columns in a table.

The rows in a table correspond to records, so the number of rows in a table corresponds to the number of records. (The number of columns in a table is the number of fields.).

Login Account Database

For our sample CGI system we need a single table that holds user ids and passwords, we will use this database to verify logins that are sent by a browser when a user fills in a login form and presses submit. The Perl CGI program will compare the name and password entered by the user against the records (rows) in the database to see if the user account exists and the password is correct.

Our database needs a single table that contains all the user-id/passwrod records. To create this table you should start up Access and create a new database (I named my database "eiwdb"). When you create a new database you need to tell Access where the file should exist - make sure you remember where you told Access to put the file. At this point your Access window should look like this:

Now we need to create the table that will hold the userids and passwords. Press on "Create Table in Design view", this will bring up a screen that allows you to enter the names (and types) of the individual fields (columns) you want in the table. For this sample, I've named the fields "Name" and "Password". The data type for each of these fields is the default type "text" - this means that the fields can hold any kind of textual data, there are other field type for numbers, dates, etc. At this point the Access window looks like this:

Each table in an Access Database needs to include a primary key, which is a field whose value will be unique for each record. For our example the field named "Name" will be unique for each record (each user will need to have a different userid). To tell Access to use the field named "Name" as the primary key, you right-click on the Name field and select "Primary Key". If you don't select a primary key Access will automatically create a new field that will become the primary key (this is also fine for our application).

Now close the table you have just created, when you do this Access will ask you if you want to save changes and will allow you to specify the name of the table - your table should be named "password". Your Access window should now look like this:

You can now add some records to your database. For the sample application we are developing (a simple login/user account creation CGI) we will be creating records in the database from Perl (when a user creates a new account we will add the name and password to the database), but for now you might want to add a few records while in Access. Doubleclick on the password table icon to open the table and begin entering values. The table will appear in a form just like a spreadsheet, and you can type in values directly. Here is what my table looks like after adding 2 user accounts: one for "Bush" and one for "Gore":

Now the database we need is complete, you can close the Access program. In a real web application there are other fields you may want to put in a table that holds user account information, for example you might want to store a session-key in the database, or include personal information like the full name, address, credit card numbers, etc. A real application would also typically require other tables in the database, for example an online store might have a table that holds product information (fields like product name, product number, price, the name of a file that holds a picture, etc).

ODBC

To access your database from a Perl program you use the Win32::ODBC Perl module. Before looking at the mechanics of how to so this from Perl, we should talk about what ODBC is and why it's used.

ODBC stands for "Open Database Connectivity". This is a Microsoft technology that provides access to many relational database systems with one single programming interface. This simplifies the writing of programs that use relational databases, since you don't need to worry about the specifics of which database is used (Access vs. Orcale vs. whatever...), the program is the same no matter what database system is used. To use ODBC you register your specific database with the ODBC manager - this associates a name (called a "Data Source Name") with the specific database you have on your PC. Any program that wants to access your database needs to know the "Data Source Name", but nothing else (the program doesn't need to know if the database is actually part of an Oracle database or a Microsoft Access database).

When we use the Win32::ODBC Perl module to access our password database we will need to first register the database with the ODBC manager installed on the PC. If you don't have an ODBC manager installed on your PC you will need to get one (from Microsoft). The ODBC manager is available via the Control Panel - you should bring up the control panel and double click on "ODBC Data Sources (32bit)". You should see something like this:

To register your Access database with the manager you should select the "User DSN" tab and press the "Add" button. This should give you a list of data source drivers you can choose from, you want to select "Microsoft Access Driver".

Now press "Finish" and you should get a dialog that asks for a Data Source Name and Description. You must provide a name, this name will be used by your Perl program to identify the database. You also need to press the "Select" button to select the MS Access file you just created. Here is what my dialog looks like after I've typed in a name and description and selected the Access database file:

Close the dialog by pressing "OK" and your new DSN should now show up in the list of User Data Sources in the ODBC control panel:

Now any program that uses the ODBC interface can access your new database using the DSN you specified. The next step is to create a Perl program that uses Win32::ODBC to access the database.

Perl and Win32::ODBC

Win32::ODBC is the name of a Perl module that provides connectivity to ODBC databases. To tell Perl about a module that you want to use in your Perl program you use the use command like this (at the top of your perl program):


# Tell perl we are going to need the Win32::ODBC module

use Win32::ODBC;

The Win32::ODBC module is based on a Perl object interface, we have not talked about Perl objects at all. The general idea is just like C++: there are objects that have methods and data associated with them, and to use an object you need to know how to create the object, and what methods and data members are available. You can get complete information about the Perl Win32::ODBC module at http://www.roth.net/perl/odbc/, we will just cover what we need for our sample application.

Creating a Win32:ODBC object requires that you provide the "DSN" that you created when you registered your Access database file with the ODBC control panel. In my example I used the name "eiw", and here is the Perl code that would create an ODBC object that will be used to access this database:

use Win32::ODBC;

# Create a database object and make sure the database was found

$db = new Win32::ODBC("eiw");

if (! $db) {
   printf("Error - the eiw database could not be found\n");
   ...
}

The Perl variable $db created above corresponds to a Win32::ODBC object that can now be used to access the database. To call a method associated with this object we use a syntax similar to the C++ method calling syntax, although we need to treat the $db variable as an object reference. The following code calls the TableList() method - this method returns an array, each element in the array is the name of a table in the database (there can be many tables in one database):


use Win32::ODBC;
# Create a database object and make sure the database was found
if (! $db = new Win32::ODBC("eiw")) {
   printf("Error - the eiw database could not be found\n");
   ...
}

# get list of table names and print them out

@tables = $db->TableList();

printf("I found these tables: %s\n",join(" ",@tables));

NOTE: Since we already know the name of the table in our database we don't actually need to call TableList(), this is just an example of the syntax used when calling a object method in Perl.

The methods use to extract information from the database first require that we establish the subset of the data we wish to deal with. For our application we can simply select the table "password", in general we can select specific fields in a table (and much more complex subsets involving multiple tables). Before we can show how to tell the database obejct what table we want to deal with, we need to talk about the language used to communicate with the database: SQL.

SQL

SQL is "Structured Query Language", a popular language used to communicate with database systems. SQL is quite complex, although the subset of this language we need to know to use databases in our CGI programs is very simple. For the sample CGI program we are developing the operations we need are to extract name/password pairs from the database and to add new name,password pairs to the database. We will look at the commands to accomplish each of these tasks, for a complete reference to SQL you should look at the following:

SQL `SELECT` command

The SELECT SQL command establishes the subset of the database that we want to deal with. For our application we would like to access the fields "Name" and "Password" from the table named "password", here is the SELECT command that will do this:

SELECT Name, Password FROM password

The general form of the SELECT command looks like this:

SELECT column1, column2, ..., columnN FROM table1, table2, ..., tableN [WHERE clause]

As you can see, any number of fields (columns) can be selected from and number of tables. The [WHERE clause] is optional and can be used to specify a subset of rows in the table. For example, if you had a product list in a database you could create a SELECT command that would select only those rows that had a field named Price with a value less than $100. We don't need the WHERE for our simple application.

To send the database our SELECT command that extracts all the names and passwords from the password table, we use the Sql method of our database object:

use Win32::ODBC;
# Create a database object and make sure the database was found
if (! $db = new Win32::ODBC("eiw")) {
   printf("Error - the eiw database could not be found\n");
   ...
}

# Tell the database we want all the names and passwords in the 
#    password table

$db->Sql("SELECT Name, Password FROM password");

Once you have sent the SELECT command to the database, you can extract the resulting rows one at a time by using the FetchRow method. This tells the database to grab the next row of the selected database, the method returns TRUE as long as there are rows remaining (false means the end of the table has been reached). To get the data in to perl variables, we use the Data method, there are other Win32:ODBC database object methods that will allow us to get at the data in other ways, this is just the simplest.

The following Perl code will print out all the names and passwords in the password table, note that we now do some error checking when sending the SELECT command to the database:

use Win32::ODBC;
# Create a database object and make sure the database was found
if (! $db = new Win32::ODBC("eiw")) {
   printf("Error - the eiw database could not be found\n");
   ...
}

# Tell the database we want all the names and passwords in the 
#    password table

if ($db->Sql("SELECT Name, Password FROM password")){
   printf("Error reading from database\n");
   ...
}

# loop through all the rows in the database
while ($db->FetchRow()) {

   # for each row (record) grab the name and password
   # we know the order - it matches our SELECT command!
   ($n,$p)=$db->Data();

   # print out the name and password
   printf("Name is $n\n");
   printf("Password is $p\n");
}

# Close the connection 
$db->Close();

SQL `INSERT` command

Our login handling program will also allow users to create accounts - this means we need to add a new row (record) to the password database. We use the SQL INSERT command to accomplish this, here is the general syntax for the INSERT SQL command:

INSERT INTO table (column1, column2, ... columnN) VALUES (value1, value2, ..., valueN)

column1, column2, are the field names and value1, value2, are the field values. For field values that are numbers you can use the value directly, for field values that are text strings (like names and passwords) you should put each field value inside single quotes.

Here is the perl code that will add a new name,password pair to the database. This code assumes the name to be added is in the perl variable $n and the password is in $p:

$db->Sql("INSERT INTO password (Name, Password) VALUES ('$n', '$p');");

The above command used the SQL INSERT command to add a new row into the password table, the field named Name gets the value $n and the field named Password gets the value $p. Note that the Sql method returns TRUE if there is some kind of error, so we would always check for errors like this:

if ($db->Sql("INSERT INTO password (Name, Password) VALUES ('$name', '$pw');")) {
    printf("Error adding to database\n");
    ...
}

Sample Login/Account Creation CGI

We now have enough Win32:ODBC code to support our simple login/account creation application. Before we look at the entire system, we will create some perl subroutines that do most of the database work. This allows the rest of the Perl code to operate using simepl perl variables, and makes it much easier to write (and read) the code that deals with the specifics of our application.

Here are two Perl subroutines that can handle most of the database operations we need. Both of these subroutines must be passed a Win32::ODBC database object reference, so the database must be opened before calling the subroutines. The subroutine named read_passwords reads all the name,password pairs from the database and returns these as an associative array (index by name):

# This subroutine grabs all the names and passwords
# from the data source passed in (already opened).
# This assumes that there are fields Name and Password 
# in the database table "password"!!!
# An associative array indexed by Name is returned.
#

sub read_passwords {
    my($db) = $_[0];      # database object must be passed in
    my(%accounts);
    my($n,$p);

    # select the name,password fields from the table password

    if ($db->Sql("SELECT Name, Password FROM password")){
	fatal_error("Error reading database");
    }


    # loop through all the rows in the database
    while ($db->FetchRow()) {
	# for each row (record) grab the name and password
	($n,$p)=$db->Data();
	# and save in the associative array 
	$accounts{$n} = $p;
    }
    # return the associative array that holds all the name/password pairs.
    return(%accounts);
}

You may notice that this code uses the fatal_error subroutine. This subroutine prints an error message as HTML and then exits. The next subroutine takes care of adding a new name,password to the database. This subroutine expects to get a database object reference, a name and a password as parameters:

# This subroutine adds a new name and password
# to the database.
# Parameters: database object (already open)
#             name and password

sub new_password {

   # get the parameters passed in and put in to perl variables
   my($db,$name,$pw) = @_;

   # use the SQL INSERT command to add a new row to the password table
   if ($db->Sql("INSERT INTO password (Name, Password) VALUES ('$name', '$pw');")) {
	fatal_error("Error writing database");
    }
}

HTML Form

Below is the HTML form that will be used with our simple application. There are 2 different forms, one for returning users (they supply a name and password only), and one use by new users to create an account. When a submission is made to our CGI, the name/value pair of the submit button is used to distinguish between the 2 different forms. Both submit buttons are named "operation", the value of the login button is "Login" and the value of the new account button is "Create". Our CGI first checks the value of the field named "operation" to decide whether the user is logging in or creating a new account.

<TABLE VALIGN=TOP> <TR VALIGN=TOP><TD ALIGN=CENTER> <FORM ACTION=login.cgi> <TABLE CELLSPACING=0 CELLPADDING=5 BORDER=0 BGCOLOR=#e0e0e0> <TR><TH COLSPAN=2 ALIGN=CENTER>Returning Users Login Form</TH></TR> <TR><TD ALIGN=RIGHT>ID:</TD> <TD ALIGN=LEFT> <INPUT TYPE=TEXT NAME=name></TD> </TR> <TR><TD ALIGN=RIGHT>Password:</TD> <TD ALIGN=LEFT><INPUT TYPE=password NAME=pw></TD> </TR> <TR> <TD COLSPAN=2 ALIGN=CENTER> <INPUT TYPE=submit Name=operation value="Login"> </TD> </TR> </TABLE> </FORM> </TR> </TD> <TD WIDTH=100> </TD> <TD> <FORM ACTION=login.cgi> <TABLE CELLSPACING=0 CELLPADDING=5 BORDER=0 BGCOLOR=#e0e000> <TR><TH COLSPAN=2 ALIGN=CENTER>Need an account? <BR>Fill in this form and press "Create"</TH></TR> <TR> <TD ALIGN=RIGHT>Full Name:</TD> <TD ALIGN=LEFT> <INPUT TYPE=TEXT Name=fullname></TD> </TR> <TR> <TD ALIGN=RIGHT>ID:</TD> <TD ALIGN=LEFT> <INPUT TYPE=TEXT NAME=name></TD> </TR> <TR> <TD ALIGN=RIGHT>Password:</TD> <TD ALIGN=LEFT> <INPUT TYPE=password NAME=pw></TD> </TR> <TR> <TD ALIGN=RIGHT>Credit Card Number:</TD> <TD ALIGN=LEFT> <INPUT TYPE=text Name=cc></TD> </TR> <TR> <TD COLSPAN=2 ALIGN=CENTER> <INPUT TYPE=submit Name=operation value="Create"> </TD> </TR> </TABLE> </FORM> </TD></TR></TABLE>

Returning Users Login Form

ID:

Password:

Need an account?
Fill in this form and press "Create"

Full Name:

ID:

Password:

Credit Card Number:

The "new account" form includes fields for the user's full name and credit card number - these aren't used by this sample system (they are just there to give you an idea of how this might be used in a real application).

The complete CGI program is shown below - this program first connects to the database (using the DSN "eiw") and grabs all the name,password pairs. Next the program determines whether this is a login request or a request to create a new account, and handles both cases. Logins that succeed (the name and password match a record in the database) result in sending back an html file that contains a message indicating the login was a success. After a successful account creation, the original login form is sent back.

#!/perl/bin/perl
#
# Login handler that uses an ODBC data source to store login ids and
# passwords. This CGI program handles user logins (verifies them)
# and also can be used to create new accounts (will add a new record
# to the database). 
#
#
# Form Fields expected:
#
#   operation:  should be "Login" for a login or "Create" to create a
#               new account.
# 
#   name:  user name (user id)
#   pw:    user password
#
# if operation is not specified, this CGI will send back and error 
#  message.
#
# if operation eq "Login" and the username/password are found in the database,
#   this program will send back an HTML document
#   congratulating the user on his/her succesful login. If this was
#   not just a sample program, you would probably want to do something
#   like create a session key, store is as a cookie on the browser and
#   in the database, and also send back the first screen of the web site.
#
#
# if (operation eq "create") this program will attempt to create a new record
# in the database.
#

use CGI ':standard';
use Win32::ODBC;

# Set the name of the data source
$DSN = "eiw";
# try to open the data source
if (!($db = new Win32::ODBC($DSN))){
    fatal_error("Error connecting to the database\n");
}

http_header();

# Now read in all the names and passwords
%accts = read_passwords($db);

if (param('operation') eq "Login") {
    # this is a login request
    $name=param("name");
    $pw = param("pw");

    # make sure something was entered!
    if ((! $name) || (! $pw)) {
	fatal_error("You must supply a name and password!");
    }

    # check to see if the name matches a name in the database
    if (! $accts{$name} ) {
	fatal_error("Invalid User Name");
    }

    # Make sure the password matches
    if ($accts{$name} ne $pw) {
	fatal_error("Invalid Password");
    }

    # sucessful login
    send_file("perldb/congrats.html");
    exit;
} elsif ( param('operation') eq "Create" ) {
    # Create new account request
    $name=param("name");
    $pw = param("pw");

    # make sure something was entered!
    if ((! $name) || (! $pw)) {
	fatal_error("You must supply a name and password!");
    }

    # see if the name is already used
    if ($accts{$name}) {
	fatal_error("Name already in use");
    }

    # add the name/password to the database
    
    new_password($db,$name,$pw);
    send_file("perldb/login.html");
    exit;
}


fatal_error("Unknown operation");


#=======================================
# Database Access Subroutines



# This subroutine grabs all the names and passwords
# from the data source passed in (already opened).
# This assumes that there are fields Name and Password 
# in the database table "password"!!!
#
# An associative array indexed by Name is returned.
#

sub read_passwords {
    my($db) = $_[0];
    my(%accounts);
    my($n,$p);

    if ($db->Sql("SELECT Name, Password FROM password")){
	fatal_error("Error reading database");
    }
    # loop through all the rows in the database
    while ($db->FetchRow()) {
	# for each row (record) grab the name and password
	($n,$p)=$db->Data();
	# and save in the associative array 
	$accounts{$n} = $p;
    }
    return(%accounts);
}


# This subroutine adds a new name and password
# to the database.
#

sub new_password {
    my($db,$name,$pw) = @_;

    if ($db->Sql("INSERT INTO password (Name, Password) VALUES ('$name', '$pw');")) {
	fatal_error("Error writing database");
    }
}


# --------------------------------------------
# http header generation
# this subroutine should be called before the perl CGI program
# prints anything!
# The extra logic in this subroutine just makes sure that the 
# header is never sent twice
#
# example usage:
#  http_header();

sub http_header {
    if (! $http_header ) {
	print "Content-type: text/html\r\n\r\n";
	$http_header=1;
    }
}

# --------------------------------------------
# fatal_error generates an HTML error message and quits.
# This subroutine should only be called when a fatal
# error condition occurs, something like the query didn't
# include expected fields. 
# Any parameters to this subroutine are treated as strings that
# should be sent back as part of the error message.
#
# a back button is created that the user can press to go back to
# the refering page (typically the form that was sent here) if the
# environment variable REFERER is found (otherwise no back button
# is created).
# 
# Example usage:
#   fatal_error("You must fill in all fields in the form!\n");

sub fatal_error {

    # send the HTTP header 
    http_header();

    # and make this a real HTML document
    print "<HEAD><TITLE>Error!</TITLE></HEAD>\n";
    print "<BODY><HTML>\n";

    # send a generic error message back to the browser
    print "<H2>ERROR - YOUR QUERY COULD NOT BE PROCESSED</H2>\n";

    my($line);

    # now send back any parameters passed to this subroutine
    print @_;

    # now create the back button
    my($referer) = $ENV{'HTTP_REFERER'};
    if ($referer) {
	print "<P>\n";
	print "Press <A HREF=$referer>here</A> to return\n";
    }
    # and close out the HTML document
    print "</HTML></BODY>\n";

    # terminate this program
    exit;
}


# --------------------------------------------
#
# send_file will read in a file and send the contents of the
# file to the browser. This is useful when part of the HTML
# you want to generate is always the same - just put it in
# a file instead of coding it in print statements
#
# example usage:
#   send_file("header.html");

sub send_file {
    my($filename) = $_[0];

    # attempt to open the file
    if (! open(F,$filename) ) {
	# Can't open the file - this is a fatal error!
	fatal_error("Can't find the file $filename\n");
    }

    # the file is open - read everything and send to
    # the browser
    my(@lines) = <F>;    # reads the whole file
    print @lines;           # sends all the lines to the browser
    close(F);               # close the file
}