Ferreteria/sql/user session: Difference between revisions

From Woozle Writes Code
Jump to navigation Jump to search
(corrected table name in SQL)
(Stash field)
Line 5: Line 5:
* '''Relations''':
* '''Relations''':
** Each {{vbzcart|table|user_client}} has one or more {{vbzcart|table|user_session}}s
** Each {{vbzcart|table|user_client}} has one or more {{vbzcart|table|user_session}}s
** Each {{vbzcart|table|user_session}} has one or more {{vbzcart|table|shop_cart}}s
** Each {{vbzcart|table|user_session}} has zero or one {{vbzcart|table|shop_cart}}s
*** A single session may discard one cart and start a new one.
*** A single session may discard one cart and start a new one.
*** Sessions only use carts they create, never reusing one created by another session.
*** Sessions only use carts they create, never reusing one created by another session (but see Security, below)
*** Each session knows only the cart it is currently using.
*** Each session knows only the cart it is currently using.
* '''Usage''':
* '''Usage''':
** For now, we will be treating each {{vbzcart|table|user_client}} as having its own session which never expires. Having a separate class for the session, though, lets us decouple these things later on if we want to.
** For now, we will be treating each {{vbzcart|table|user_client}} as having its own session which never expires. Having a separate class for the session, though, lets us decouple these things later on if we want to.
** There should eventually be an "empty cart" button; if the user has not logged in, then that button should start a new session rather than clearing the cart for the current session.
** There should eventually be an "empty cart" button; if the user has not logged in, then that button should start a new session rather than clearing the cart for the current session.
* '''Fields''':
===Fields===
** '''WhenClosed''': if not NULL, then this session should not be reused (might be a different user returning to their cart, or might be the same user -- if no login, we have no way of telling, so take safest choice). If this session is accessed after the time of WhenExpires, code should manually set WhenClosed to NOW().
* '''Stash''': a place to put serialized application data (maybe there should be a convention about prefixing key-names with the app's name)
* '''Future''': The ID_Cart and ID_Order fields should probably be handled by a separate session_data table which allows the application to add arbitrary data to the session.
* '''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===
===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.
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 "[[wikipedia:Shared secret|shared secret]]" generated by the server.
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 "[[wikipedia:Shared secret|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===
===History===
* '''2009-06-18''' design started - first draft, not sure concept is right
* '''2009-06-18''' design started - first draft, not sure concept is right
Line 33: Line 37:
** renamed from '''shop_session''' to '''user_session'''
** 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
** 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)
==SQL==
==SQL==
<mysql>CREATE TABLE `user_session` (
<mysql>CREATE TABLE `user_session` (
   `ID`         INT    NOT NULL AUTO_INCREMENT,
   `ID`       INT    NOT NULL AUTO_INCREMENT,
   `ID_Client`   INT    NOT NULL COMMENT "user_client.ID",
   `ID_Client` INT    NOT NULL COMMENT "user_client.ID",
   `ID_User`     INT DEFAULT NULL COMMENT "ID of logged-in user, if any",
   `ID_User`   INT DEFAULT NULL COMMENT "ID of logged-in user, if any",
   `ID_Cart`     INT DEFAULT NULL COMMENT "shop_cart.ID currently active for this session",
   `ID_Cart`   INT DEFAULT NULL COMMENT "shop_cart.ID currently active for this session",
   `ID_Order`   INT DEFAULT NULL COMMENT "order ID to which cart was converted (if any)",
   `ID_Order` INT DEFAULT NULL COMMENT "order ID to which cart was converted (if any)",
   `Token`       VARCHAR(31)      COMMENT "session identifier passed as cookie = random string",
   `Token`     VARCHAR(31)      COMMENT "session identifier passed as cookie = random string",
   `WhenCreated` DATETIME         COMMENT "when session was created",
  `Stash`    BLOB DEFAULT NULL COMMENT "other values associated with the session (PHP serialized)",
   `WhenExpires` DATETIME         COMMENT "when session was due to expire",
   `WhenCreated` DATETIME       COMMENT "when session was created",
   `WhenClosed`  DATETIME         COMMENT "when the session was closed",
   `WhenExpires` DATETIME       COMMENT "when session was due to expire",
   `WhenClosed`  DATETIME       COMMENT "when the session was closed",
   PRIMARY KEY(`ID`),
   PRIMARY KEY(`ID`),
   UNIQUE KEY(`Token`)
   UNIQUE KEY(`Token`)
  ) ENGINE = MYISAM;</mysql>
  ) ENGINE = MYISAM;</mysql>

Revision as of 12:37, 23 December 2016

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 Template:Vbzcart has one or more Template:Vbzcarts
    • Each Template:Vbzcart has zero or one Template:Vbzcarts
      • A single session may discard one cart and start a new one.
      • Sessions only use carts they create, never reusing one created by another session (but see Security, below)
      • Each session knows only the cart it is currently using.
  • Usage:
    • For now, we will be treating each Template:Vbzcart as having its own session which never expires. Having a separate class for the session, though, lets us decouple these things later on if we want to.
    • There should eventually be an "empty cart" button; if the user has not logged in, then that button should start a new session rather than clearing the cart for the current session.

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)

SQL

<mysql>CREATE TABLE `user_session` ( 

 `ID`        INT     NOT NULL AUTO_INCREMENT,
 `ID_Client` INT     NOT NULL COMMENT "user_client.ID",
 `ID_User`   INT DEFAULT NULL COMMENT "ID of logged-in user, if any",
 `ID_Cart`   INT DEFAULT NULL COMMENT "shop_cart.ID currently active for this session",
 `ID_Order`  INT DEFAULT NULL COMMENT "order ID to which cart was converted (if any)",
 `Token`     VARCHAR(31)      COMMENT "session identifier passed as cookie = random string",
 `Stash`    BLOB DEFAULT NULL COMMENT "other values associated with the session (PHP serialized)",
 `WhenCreated` DATETIME       COMMENT "when session was created",
 `WhenExpires` DATETIME       COMMENT "when session was due to expire",
 `WhenClosed`  DATETIME       COMMENT "when the session was closed",
 PRIMARY KEY(`ID`),
 UNIQUE KEY(`Token`)
) ENGINE = MYISAM;</mysql>
Retrieved from "https://wooz.dev/mw/index.php?title=Ferreteria/sql/user_session&oldid=1810"