A Secure online Course Registration System

Group Member:

Steve Liu
Columbia University
New York, NY 10027 USA
sl387@columbia.edu

Abstract

This secure on-line registration system is implemented by integrating Apache httpd server(apache 1.2b10) with Eric Young's SSLeay(a free implementation of Netscape's Secure Socket Layer - the software encryption protocol behind the Netscape Secure Server and the Netscape Navigator Browser. See SSLeay and SSLapps FAQ for details), Netscape Browser and Sybase SQL database, where electronic signatures of instructors, etc. are used to replace the current handwritten approval signatures.

The system based on an Sybase database that checks student authorization for specific courses, tracks the number of credits taken, the number of students enrolled in a course and checks for time conflicts. Instructors will be presented with a list of students that require approval, with the ability to see the courses they have taken so far. The instructor is told by email that students await his/her approval. Instructors should also be able to check enrollment.

The system can be accessed by students and instructors via WWW and used to replace the current registration system. The system have the following functionalities:

For student:

• To check his/her course information
• To register new courses.
• To drop registered courses.

For instructor:

• To list the students information who are in my calss
• To accept/reject a registration request
• To grade a student at the end of the term

The application specifications are following:

1. Intranet level access;
2. Query and transaction combination;
3. Processing on both client and server side;
4. Security: passwords, permission, encryption.

Introduction:

Digital IDs use public key encryption techniques that use two related keys, a public key and a private key.

In public key encryption, the public key is made available to anyone who wants to correspond with the owner of the key pair. The public key can be used to verify a message signed with the private key or encrypt messages that can only be decrypted using the private key. The security of messages encrypted this way relies on the security of the private key, which must be protected against unauthorized use.

In a Digital ID, a key pair is bound to a user's name and other identifying information. When installed in a web browser, a Digital ID functions as electronic credentials that sites can check. This enables Digital IDs to be used in place of password dialogs for information or services that require membership or restrict access to particular users.

See following flow graph:

Tools

Platform

The Design Architechture

 HTTPS                     HTTPS                      DB
Client                     Server                    Server
------                     ------                    ------
|    |                     |    |                    |    |
|    |                     |    |                    |    |
|    | <-CGI-><- Network ->|    | <-CGI-> <-Network->|    |
|    |                     |    |                    |    |
|    |                     |    |                    |    |
|    |                     |    |                    |    |
------                     ------                    ------

Implementation

1. Designed forms needed for interface according to application specification and database designing
2. Designed the needed database which should be consistent with the interface forms

There are four tables in this system. They are following:

CREATE TABLE
STUDENT (SName VARCHAR(26) NOT NULL,
 SID CHAR(9) NOT NULL,
  Passwd VARCHAR(11) NOT NULL,
   Dept CHAR(2),
    Email VARCHAR(30),
     PRIMARY KEY(SID) )

CREATE TABLE
PROFESSOR ( PName VARCHAR(26) NOT NULL,
    PID CHAR(9) NOT NULL,
        Passwd VARCHAR(11) NOT NULL,
	    Dept CHAR(2),
	        Email VARCHAR(30),
		    PRIMARY KEY(PID) )  

CREATE TABLE
COURSE ( CName VARCHAR(30) NOT NULL,
 CNo CHAR(11) NOT NULL,
  Pre_Req VARCHAR(42),
   Pts CHAR(3),
    Time VARCHAR(15),
     Profs CHAR(9),  
      PRIMARY KEY(CNo),
       FOREIGN KEY (Profs) REFERENCES PROFESSOR(PID) )

CREATE TABLE
SCourse ( SID CHAR(9) NOT NULL,
  CNo CHAR(11) NOT NULL,
    CName VARCHAR(30) NOT NULL,
      Pts CHAR(3),
        Grade CHAR(2),
	  PRIMARY KEY(SID, CNo), 
	    FOREIGN KEY (SID) REFERENCES STUDENT(SID))

3. Designed the gateway to connect web application to sybase SQL server
4. Patched SSLeay to apache http server, built the ssl web server wich is running on sherman.cs.columbia.edu:3456.

Related Work:

WDB: A Web interface to SQL database.

A gateway written in Perl which uses Form Definition Files (FDF) to define the tables and fields in a database. FDF's are generated by scripts that come with WDB. FDF's can be customized to use join conditions, hide fields, format output, etc. WDB comes with Perl extensions that support Sybase, Informix, and Mini SQL. Generates HTML query forms on the fly and formats the database results. There's also a version to use with Postgres95, but that's at another web page. The limitation for WDB is that it only provides the query functinality, don't support update capability. The WWW based Course Registration System provides an alternative for connecting a Web server to a Sybase SQL database server.

Reference:

A tutorial of CGI security
CGI Security [NCSA]
CGI Security [Paul Phillips]
Accessing a Database Server via the World Wide Web