"""
CGI session handling in MySQL

Christoph Haas <email@christoph-haas.de>

Version: 1.0
Date: 15.10.2005

License: Creative Commons
"""

# Possible todos:
# - make it a proper package
# - allow other backends (mysql/pgsql/sqlite)
# - create the needed database and tables automatically
# - tidy up the code (no more than 80 chars per line)
# - use another "unique" session-id calculation than random()
# - pass the database arguments differently than hardwiring them
# - check if the cookie was accepted by the user

import cgi, Cookie, os, sys, cgitb
import sha, random
import datetime
import MySQLdb

# Enable CGI debugging only when necessary!
# It may display secret information in case of errors!
cgitb.enable()

# MySQL access data
db_host="localhost"
db_user="admin"
db_passwd="secret"
db_database="sessions"
db_create="""
   CREATE TABLE `sessions` (
      `sessionid` varchar(40) NOT NULL default '',
      `time` timestamp(14) NOT NULL,
      `ipaddress` varchar(15) NOT NULL default '',
      `username` varchar(80) default NULL,
      UNIQUE KEY `sessionid` (`sessionid`)
      ) TYPE=MyISAM;

   CREATE TABLE `accounts` (
      `username` VARCHAR( 20 ) NOT NULL ,
      `password` VARCHAR( 20 ) NOT NULL ,
      UNIQUE KEY `username` (`username`)
      ) TYPE=MYISAM;
"""

# Create the web interface
class CGI:
   def __init__(self, authrequired=False):
      sys.stderr = sys.stdout   # send errors and tracebacks to the browser window
      self.authrequired = authrequired
      self.doctype = """<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">"""
      self.formfields = cgi.FieldStorage()

      if os.environ.has_key('REMOTE_ADDR'):
         self.client_ip = os.environ['REMOTE_ADDR']
      else:
         self.client_ip = '0.0.0.0'   # when being run from the shell

      if os.environ.has_key('SCRIPT_NAME'):
         self.script_name = os.environ['SCRIPT_NAME']
      else:
         self.script_name = '-'   # when being run from the shell

      self.logout_url = self.script_name + "?logout=1"
      self.session_maxage = datetime.timedelta(hours=1) # how long before a cookie-based session becomes invalid
      self.cookie = None   # session cookie (not "None" if it should be sent to the client)
      self.sessionid = None
      self.user_logged_in = None   # currently logged in user (None=not logged in)
      self.db = MyDatabase()

   def start(self):
      #
      # Maintain a cookie to authenticate the user if authentication is required
      # Check if we can retrieve a valid session for this user
      #
      if (self.get_cookie_session() is False) and (self.authrequired is True):
         # Create a new cookie
         self.cookie = Cookie.SimpleCookie()   # new browser cookie
         self.sessionid = self.generate_session_id()   # create a new random sessionid
         self.cookie['sessionid'] = self.sessionid

      # Check if the login form has been submitted
      if 'submit' in self.formfields:
         # Submit-button of login form has been pressed

         # Are the login fields present in the form?
         if 'username' in self.formfields and 'password' in self.formfields:
            # Check if the login data are correct
            if self.login(username=self.formfields['username'].value, password=self.formfields['password'].value):
               # Tell the current session that the user is logged in now
               self.user_logged_in = self.formfields['username'].value   # currently logged in user

      # Check if the logout button has been pressed (parameter is logout=1)
      if 'logout' in self.formfields:
         self.logout()

      # Print the CGI header and HTML header
      print "Content-type: text/html; charset=utf-8"
      if self.cookie:
         # Send cookie
         print self.cookie
      print
      print self.doctype
      print """
         <html>
         <head>
            <title>My application</title>
         </head>
         <body>
            <h1>This is a test CGI dealing with cookie sessions</h1>
            """

      # Print a logout link if the user is logged in
      if self.user_logged_in:
         print """<a href="%s">Logout</a>""" % (self.logout_url)

      # Send a login form if this a new session
      if (self.user_logged_in == None) and self.authrequired:
         print """
         <h2>Please login first...</h2>
         <form method="post" action="%(cginame)s">
         <fieldset>
         <legend>Login</legend>
         <table>
         <tr><td>Username:</td><td><input type="text" name="username" /></td></tr>
         <tr><td>Password:</td><td><input type="password" name="password" /></td></tr>
         </table>
         <input type="submit" name="submit" value="Login" />
         </fieldset>
         </form>
         """ % { 'cginame':self.script_name }

         # Tell the user if the last login attempt failed
         if "submit" in self.formfields:
            print "<b>Login failed. Please try again.</b>"

         self.end()

         # Interrupt the CGI here so that just the login is printed
         sys.exit(0)

   def end(self):
      # Main content ends here
      print "  </body>"
      print "</html>"

      # Save the session information back to the database
      if self.authrequired:
         self.save_session(self.sessionid)   # save

   # Generate a session id (SHA digest from random value)
   def generate_session_id(self):
      return sha.new(str(random.random())).hexdigest()

   # Load session information from MySQL database
   def load_session(self, sessionid, ipaddress):
      dbcursor = self.db.cursor()
      dbcursor.execute("select * from sessions where sessionid=%s and ipaddress=%s", (sessionid, ipaddress) )
      entry = dbcursor.fetchone()   # becomes None if nothing was fetched
      return entry

   # Save session information to MySQL database
   def save_session(self, sessionid):
      dbcursor = self.db.cursor()
      dbcursor.execute("""replace into sessions SET sessionid=%s,time=now(),ipaddress=%s,username=%s""",
         (sessionid, self.client_ip, self.user_logged_in))

   # Remove a session from the MySQL database
   def del_session(self, sessionid):
      dbcursor = self.db.cursor()
      dbcursor.execute("""delete from sessions where sessionid=%s and ipaddress=%s""", (sessionid, self.client_ip) )

   # Check if the username and password match
   def login(self, username, password):
      dbcursor = self.db.cursor()
      dbcursor.execute("select count(*) from accounts where username=%s and password=%s", (username, password) )
      entry = dbcursor.fetchone()   # becomes None if nothing was fetched
      if entry['count(*)'] >= 1:   # actually there should be no more than one user
         return True
      return False

   # Logout of mentors.debian.net (=clear the username from the session information) (why ?)
   def logout(self):
      if self.user_logged_in:
         self.user_logged_in = None
         self.del_session(self.sessionid)
      else:
         pass
         # Strange... logging out without being logged in?

   # Get a session based on a cookie stored at the user's browser.
   # Return 'False' if we failed to get valid session.
   def get_cookie_session(self):
      # See if the client sent a cookie
      if not os.environ.has_key("HTTP_COOKIE"):   # no cookie received from browser?
         # No cookie received :(
         return False

      received_cookie = Cookie.SimpleCookie(os.environ['HTTP_COOKIE'])

      # Does the cookie contain a 'sessionid' variable?
      if not received_cookie.has_key('sessionid'):
         # Cookie received does not contain sessionid. :(
         return False

      self.sessionid = received_cookie['sessionid'].value

      # Try to load the session data from the database
      dbentry = self.load_session(sessionid=self.sessionid, ipaddress=self.client_ip)

      # Found a database entry for this sessionid?
      if dbentry == None:
         # Database contained no entry for this sessionid yet :(
         return False

      # Does the IP address match?
      if dbentry['ipaddress'] != self.client_ip:
         # IP address mismatch :(
         # Client's IP is: self.client_ip
         # Database's IP is: dbentry['ipaddress']

         # Removing invalid session from database
         self.del_session(self.sessionid)
         return False

      # Session row from database loaded (in dbentry)
      # Maximal session age: self.session_maxage
      # Last access time according to database: dbentry['time']
      # Now: datetime.datetime.now())
      #
      # Calculate how old the session is
      session_age = datetime.datetime.now() - dbentry['time']

      # Is the session too old?
      if session_age > self.session_maxage:
         # Session is too old :(
         self.cookie = Cookie.SimpleCookie()   # new browser cookie
         # Removing expired session from database
         self.del_session(self.sessionid)
         return False

      # Session is still fresh enough. Bingo! User is now logged in.
      self.user_logged_in = dbentry['username']

      return True

#----------------------------------------

class MyDatabase:
   def __init__(self):

      # Create the database if it did not exist yet
      # TODO: error handling, perhaps database auto-creation
      self.dbhandle = self.connect()

   # Open the database
   def connect(self):
      return MySQLdb.connect(
         host=db_host,
         user=db_user,
         passwd=db_passwd,
         db=db_database
         )

   def cursor(self):
      return self.dbhandle.cursor(MySQLdb.cursors.DictCursor)

# vim:set shiftwidth=3 expandtab smarttab autoindent:

