Ferreteria/sql/user session

About

This table was originally an inextricable part of VbzCart, but it is now mostly handled through the standalone user-session library. Only ID_Cart and ID_Order have been added for VbzCart.

Rules

• A new session should be created whenever the "same" client doesn't remember the session cookie (could be a different computer on the same IP address), or if the session has timed out.

Relations

• Each user_client has one or more user_sessions

Fields

• Stash: a place to put serialized application data (maybe there should be a convention about prefixing key-names with the app's name)
• WhenClosed: if not NULL, then this session should not be reused. If this session is accessed after the time of WhenExpires, code should manually set WhenClosed to NOW().

Future

The ID_Cart and ID_Order fields should probably be stored in Stash instead of having dedicated fields. Then this table could be used by other apps without alteration.

Security

I briefly toyed with the idea of only allowing the session to be set via http query within the secure area once and thereafter requiring it to come from a cookie, but anyone familiar with wget could spoof a cookie pretty easily, so this didn't seem worth the effort.

The real security comes from requiring that client's fingerprint (browser + IP address) "match" those in the session record for the session token (which the client must also give) before authorizing renewal of a session for that client. The session token is essentially a "shared secret" generated by the server.

However, once a user has created a login, it seems reasonable to allow them to reuse sessions where the browser ID or IP address (or at least to retain their login). Tentatively, this should be a user option that is off (more secure) by default.

History

• 2009-06-18 design started - first draft, not sure concept is right
• 2009-07-10 each session ties to a cart, not vice-versa; Token is now a random string
• 2011-02-07 fixing bug where user gets the same cart again after placing an order:
  • ID_Cart is now cleared when cart is converted to an order, so that same cart won't get reloaded
  • ID_Order field so we can still pull up the order when ID_Cart is cleared
  • Remember, this is the active cart. After cart is converted to an order, it is no longer active.
• 2011-03-27 decided that clearing ID_Cart is a bad idea, because:
  • user can't reload the receipt page
  • we can't detect if the user has already sent in an order, so we can't ask if they want to add to it or modify it
• 2013-09-26 added ID_User -- this is how we know if the user is logged in.
• 2013-11-09
  • renamed from shop_session to user_session
  • moved ID_User above ID_Cart, since that and ID_Order are now "additions" to the base class as defined in user-session.php
• 2016-12-23 added Stash BLOB field for storing arbitrary session values (messages, display preferences, maybe eventually Cart ID)
• 2017-01-26 moved from VbzCart to Ferreteria
  • changed from MyISAM to InnoDB
  • ID_Cart and ID_Order removed
  • ID_User renamed to ID_Acct
  • made WhenCreated NOT NULL
• 2017-02-10 Had to rethink design a tiny bit.
  • Someone might log into different user accounts in the same Session.
  • We wouldn't want Session preferences to be forgotten after logout.
  • ...or even after logging in, if the user Account profile doesn't override.
  • If we have multiple clients with identical browsers behind a NAT wall, keeping each Session active becomes more critical.

  ...so really, we should only ever start a new session if the browser loses the cookie -- but might be a new client, so we don't want to close any existing Sessions. We also don't want to close them when the user logs out. So basically, we can never close them, unless we want to automatically expire them when too much time passes since last use.

  Therefore: Removing WhenClosed, adding WhenUsed.

  User logins/logouts should be tracked in the EventPlex.

SQL

CREATE TABLE `user_session` (
  `ID`          INT          NOT NULL AUTO_INCREMENT,
  `ID_Client`   INT          NOT NULL COMMENT "user_client.ID",
  `ID_Acct`     INT      DEFAULT NULL COMMENT "ID of currently logged-in user account; NULL if not logged in",
  `Token`       VARCHAR(31)  NOT NULL COMMENT "session identifier : random string passed as cookie",
  `Stash`       BLOB     DEFAULT NULL COMMENT "other values associated with the session (PHP serialized)",
  `WhenCreated` DATETIME     NOT NULL COMMENT "when session was created",
  `WhenUsed`    DATETIME DEFAULT NULL COMMENT "when the session was last accessed",
  `WhenExpires` DATETIME DEFAULT NULL COMMENT "when session expired or is due to expire",
  PRIMARY KEY(`ID`),
  UNIQUE KEY(`Token`)
 ) ENGINE = InnoDB;