Web Sessions

With the advent of stored procedures in MySQL 5, it is now possible to hand off all web session managment to the database directly. There are arguments for and against doing this. Against: It will put more load on your database server (ie, your primary database server, since all requests must go against this host, and not your read-only slave host(s)). However, it can be more efficient since you don't have to prepare and execute several distinct queries to perform the same operation.

This was developed against MySQL 5.0.3 on WinXP.

Table Structures

Variables

We need to define a few variables, so we'll make a table to hold simple name = value pairs. 

create table variable (name varchar(255) primary key,
index index_name(name(10)),
value text not null);

insert into variable VALUES ('idle_session_timeout_seconds', '600'), ('session_max_time', '0'), ('session_change_ip_allowed', '1'), ('purge_old_sessions_percent', '10'), ('site_enabled', '1'), ('site_enabled_message', 'Site maintenence crew are working; normal service will return at 2am');

User table

Our user table is going to be huge; number of columns and number of rows. Add columns to store what you want for your users. 

create table users (
  id int unsigned primary key auto_increment,
  email text not null,
  index index_email(email(20)),
  name_first text,
  name_last text,
  password text,
  password_previous text,
  password_change_time datetime,
  last_login datetime,
  last_access datetime,
  disabled bool default 0,
  reset_password bool default 0,
  reset_question text,
  reset_answer text);

Sessions

Our session table is hot. Every web hit will update this table. It can't lock at all. It must be as tiny as possible - for column sizes and number of rows. Any delay in finding the matching cookie and updating its last access time will slow the entire site down. 

create table sessions (
  cookie char(36) primary key,
  user_id int unsigned not null,
  session_start_time datetime not null,
  last_access datetime not null,
  index index_last_access(last_access));

We have this as a char(36) so that all fields have a set size, and thus MySQL deems this as a FIXED table. For MyISAM tables, this can be a speed improvment, since the server can skip rows by a set offset. Thus, each row here will be 36 + 4 + 8 = 48 bytes (plus some for the index, around an extra 8 bytes for the index).

Login history

We'll record an ever growing list of logins and login failures into two separate tables. Over time, we'll dump the login failures reasonably regularly (we probably only want to look at the failures over the last 10 minutes anyway to look for repeated crack attempts). This will get very long, so size is again important. 

create table logins (
  user_id int unsigned not null,
  index index_user_id(user_id),
  login_time datetime not null,
  duration int unsigned,
  address int not null);

create table login_failures (
  email text,
  failure_time datetime not null,
  address int not null);

Note that the login table has a duration; we'll fill this in at the end of the session when we clear it out. We'll build up a usage pattern for each user. Also, we're going to be naive and assume that the ip address doesnt change from the start of the session to the end. This is of course feasable, just I'm not that bothered by it. Checking for this constraint not changing can be done in the login() stored procedure.

Stored Procedures

Now we have our table structures in place, we can create our SQL procedures that will drive the authentication. We want four procedures:

Login

When we have a login event, we want to: 

create procedure login (IN email1 text, IN password1 text, IN address1 int, OUT cookie varchar(36), OUT user_id int unsigned)
language SQL
comment 'Returns a cookie UUID if successful'
NOT DETERMINISTIC
BEGIN
  declare all_done INT(1);
  declare this_user_id int unsigned;
  declare saved_password text;
  declare cur_1 CURSOR FOR SELECT id, password FROM users WHERE email = email1;
  declare cur_2 CURSOR FOR SELECT uuid();
  declare continue handler for not found SET all_done = 1;
  SET all_done = 0;
  OPEN cur_1;
  FETCH cur_1 into this_user_id, saved_password;
  IF all_done != 1 THEN
    IF saved_password = password1 THEN
      OPEN cur_2;
      FETCH cur_2 into cookie;
      SET user_id = this_user_id;
      INSERT INTO logins     (user_id, login_time, address) VALUES (this_user_id, now(), address1);
      INSERT INTO sessions (user_id, session_start_time, last_access, cookie) VALUES (this_user_id, now(), now(), cookie);
      UPDATE users SET last_login = now() where id = this_user_id;
      CLOSE cur_2;
    ELSE
      INSERT INTO login_failures (email, failure_time, address) VALUES (email1, now(), address1);
    END IF;
  END IF;
  CLOSE cur_1;
END ; //

Cookie_session

When a cookie is presented to the web site, we want to: 

create procedure cookie_session (IN cookie1 varchar(36), OUT this_user_id int unsigned)
language SQL
NOT DETERMINISTIC
comment 'Returns the user_id if successful'
BEGIN
  declare all_done, this_do_update INT(1);
  declare cur_1 CURSOR FOR SELECT user_id from sessions WHERE cookie = cookie1 AND unix_timestamp(now()) - unix_timestamp(last_access) < (SELECT value from variable where name = 'idle_session_timeout_seconds') ;
  declare cur_2 CURSOR FOR SELECT rand() * 100 < (SELECT value from variable where name = 'purge_old_sessions_percent') as do_update ;
  declare continue handler for not found SET all_done = 1;
  SET all_done = 0;
  open cur_1;
  FETCH cur_1 INTO this_user_id;
  IF all_done != 1 THEN
    UPDATE sessions SET last_access = now() WHERE cookie = cookie1;
  END IF;
  CLOSE cur_1;
  open cur_2;
  FETCH cur_2 into this_do_update;
  IF this_do_update = 1 THEN
    call clean_sessions(@count);
  END IF;
END ;//

Logout

On logout, we want to update the user's last access time, and then work out how long the session is that we are about to remove, and update one of the login entries that corresponds to this session with this duration. 

create procedure logout (IN cookie1 varchar(36), OUT result int(1))
language SQL
NOT DETERMINISTIC
comment 'Removes the cookie session, updating other tables'
BEGIN
 declare all_done int(1);
 declare this_last_access datetime;
 declare this_duration, this_user_id  int unsigned;
 declare cur_1 CURSOR FOR SELECT last_access, last_access - session_start_time, user_id from sessions where cookie = cookie1;
 declare continue handler for not found SET all_done = 1;
 SET all_done = 0;
 open cur_1;
 FETCH cur_1 into this_last_access, this_duration, this_user_id;
 close cur_1;
 update users set last_access = this_last_access where id = this_user_id and last_access < this_last_access limit 1;
 update logins set duration = this_duration where user_id = this_user_id and duration is null limit 1;
 delete from sessions where cookie = cookie1 limit 1;
END; //

User_change_password

When we change the pasword, we want to ensure that it is not being set the same as the current password, and also record the time it was changed. Also, we want to set the rest_password flag to false, since we have just reset the password. 

create procedure user_change_password (IN user_id1 int unsigned, IN password_new text, OUT result int)
language SQL
comment 'Returns either true or false'
BEGIN
  declare all_done INT(1);
  declare this_password text;
  declare cur_1 CURSOR FOR SELECT password from users where id = user_id1 and password != password_new;
  declare continue handler for not found SET all_done = 1;
  SET all_done = 0;
  SET result = 0;
  OPEN cur_1;
  FETCH cur_1 into this_password;
  IF all_done != 1 THEN
    UPDATE users SET password_previous = this_password, password = password_new, reset_password = 0, password_change_time = now() WHERE id = user_id1;
    SET result = 1;
  END IF;
END ; //

Clean_sessions

We want to clear out the session table, so we:

In this example, I have used a timeout of 600 seconds. However, it is feasable to have a table called, for example, Variables (name text, value text), and insert into Variables VALUES ('Web_Session_Timeout', 600), and use this value instead! 

create procedure clean_sessions (OUT result int unsigned) 
language SQL
comment 'Returns the number of sessions cleaned'
BEGIN
  declare this_cookie varchar(36);
  declare this_user_id, this_duration int unsigned;
  declare this_access_time, this_session_start_time datetime;
  declare all_done int(1);
  declare c1 CURSOR FOR SELECT user_id,cookie,session_start_time,last_access, last_access-session_start_time from sessions WHERE last_access < now()-INTERVAL (select value from variable where name='idle_session_timeout_seconds') SECOND;
  declare continue handler for not found SET all_done = 1;

  OPEN c1;
  SET result = 0;
  SET all_done = 0;
  REPEAT 
    FETCH c1 into this_user_id, this_cookie, this_session_start_time, this_access_time, this_duration;
    IF all_done != 1 THEN
       
      UPDATE users SET last_access = this_access_time WHERE id = this_user_id AND last_access < this_access_time;
      UPDATE logins SET duration = this_duration WHERE user_id = this_user_id AND login_time = this_session_start_time and duration is NULL LIMIT 1;
      DELETE FROM sessions where cookie = this_cookie;
      SET result = result+1;
    END IF;
    UNTIL all_done = 1
  END REPEAT;
  CLOSE c1;
END ; //

Using this from Perl

Pretty easy.

use DBI;
use CGI;
my $dbh = DBI->connect(..);
my $query = CGI->new;
if (defined $query->cookie(-name => session) {
  my $sql = "call cookie_session(?, @user_id); select @user_id as user_id";
  my $sth = $dbh->prepare($sql);
  my $res = $sth->execute($query->cookie(-name => session));
  my $ref = $sth->fetchrow_hashref;
  # If we have a user ID, then this is a valid user session
} elsif (defined $query->param('email')) {
  my $sql = "call login(?, ?, ?, @cookie, @user_id); select @cookie as cookie, @user_id as user_id";
  my $sth = $dbh->prepare($sql);
  my $res = $sth->execute($query->param('email'), $query->param('password'), $query->remote_host);
  my $ref = $sth->fetchrow_hashref;
  my $cookie_val = $ref->{cookie};
  # Now set this in the browser...
}

Get the SQL for this websessions.sql.