Back to course home page

FORDHAM UNIVERSITY
Graduate School of Arts and Sciences

Dept. of Computer and Info. Sciences

CSGA 5030 -- Internet & Web Programming

Fall, 2001
Prof. Robert K. Moniot

Application Notes
for
Perl-DBI with PostgreSQL

These notes are intended to fill the gaps between the material in Chapter Eighteen of CGI Programming 101, which is based on MySQL, and what you need to know in order to use the Perl DBI module for PostgreSQL as it is installed on storm. Fortunately, both Mysql and PostgreSQL conform quite closely to both the SQL database query language standard and the Perl DBI module standard, so the differences from the textbook are minor and have mainly to do with matters, such as access control, that are not discussed in the book.

1. Using PostgreSQL interactively: The command-line interface to PostgreSQL is named psql. For documentation about PostgreSQL and the SQL query language, consult the user's manual at
   http://www.postgresql.org/users-lounge/docs/7.1/user/
   A faster connection to this manual is available if you are logged in on storm and running your browser on it. Open the URL:
   file:/usr/share/doc/postgresql-7.0.3/postgres/index.html

2. Using the Perl DBI module: The DBI module for PostgreSQL is essentially the same as the one for Mysql. You will need to make a few minor changes to the program dbicat.cgi on page 173:
   • The connection to the database must specify Pg instead of mysql, and it requires the use of dbname=name to specify the database name. Thus it will look like

        $dbh = DBI->connect( "dbi:Pg:dbname=products", "webdb", "")
     

     The user name can be any valid PostgreSQL user name. The account webdb was created for use in cases like this where the database is public and shared. (The text's username webserver is not acceptable to PostgreSQL, which limits usernames to 8 characters in length.)

   • In queries, PostgreSQL requires the use of apostrophes rather than quote marks to delimit strings. So the query setup for this program looks like

       $sth = $dbh->prepare("select stocknum,name,price from  items where
      status != 'OUT' order by stocknum")
     

   • When a query is finished, it should be explicitly closed using the finish command, as for instance:

       $sth->finish;
     

   Note: The products database has already been created on storm, containing all the products in the table on page 167 of the text. No authentication is required to access this database, other than to be a valid PostgreSQL user.

3. Setting up your own database: Each student in the class has been given a PostgreSQL account with the same user name as their storm login name. You have been granted permission to create databases.

   Note that in the textbook, the products database is used both for the catalog application, which only reads the database, and for the counter application, which must update a table. This dual use of the products database is not appropriate for a class in which many different users need to create CGIs that modify the database. There is no problem sharing the catalog table since it is read-only. However, for the counter and any other applications involving modifying the database, each student needs a private database.

   You should therefore create a database of your own for this purpose. All database names managed by PostgreSQL must be unique. Therefore, in order to avoid name collisions with other students, give your database the same name as your account. For instance, if your account name is joeuser, then your database should be named joeuser. This creation step needs to be done only once: from the shell prompt, give the command

     $ createdb joeuser
   

   substituting your account name in place of joeuser. (In fact, you need not specify the database name, since the default is to create one with the same name as your user name.) Use this database for all applications that require modifying the contents of the database.

4. Access control: When your PostgreSQL account was created, a password file was set up for you containing the password that your instructor gave you. This password applies only to your private database with the same name as your account name. (Any other database that you may create will be accessible by all PostgreSQL users on the system.) In order to connect to your database, this username and password must be supplied. Note that other users on the system can connect to your database if they know this authentication information. Details on this issue will be given below. For now, we deal with how you can set up your database to contain some tables.

   For example, suppose you want to try the SQL page counter in the text on pp. 174-175. In order for this CGI to work, the counts table must exist prior to running the CGI. You need to create it by hand, using the PostgreSQL interactive front-end psql. The form of the psql command is

   psql [ -u ] database
   where the -u option is needed if the database requires password authentication. For example, here is how it would look as joeuser creates the counts table of his own database.

     $ psql -u joeuser
     Username: joeuser
     Password: 
   
     Welcome to the POSTGRESQL interactive sql monitor:
       Please read the file COPYRIGHT for copyright terms of POSTGRESQL
     [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]
   
        type \? for help on slash commands
        type \q to quit
        type \g or terminate with semicolon to execute query
      You are currently connected to the database: joeuser
   
     joeuser=> create table counts(
             pagename char(80) not null primary key,
             count int not null);
     joeuser=> \q
     $
   

   The connection to this database from the counter CGI could look something like this:

     $dbname  - "joeuser";
     $pg_user = "joeuser";
     $pg_pass = "snorfled";
     $dbh = DBI->connect( "dbi:Pg:dbname=$dbname", $pg_user, $pg_pass)
   

   where in this example, snorfled is joeuser's PostgreSQL password in cleartext.

   For better security, it would be best not to place the account and password information in the CGI script where it is visible to anyone on the system. Instead, place this information in a private file and have the CGI read this information into the variables $pg_user and $pg_pass at run time. A good place for this file is in your cgi-files directory, which should have permission 700, namely owner-accessible only.