Store session in database.

Use https://github.com/j2L4e/PHP-PDO-MySQL-Session-Handler to store
session data in our existing MySQL database when MEMCACHE is unset.

We must take care to initialise Propel first, then the session and
finally Auth0.

diff --git a/lib/SessionHandler.php b/lib/SessionHandler.php
new file mode 100644 (file)
index 0000000..d3bc008
--- /dev/null
+++ b/lib/SessionHandler.php
@@ -0,0 +1,168 @@
+<?php namespace JL;
+
+/**
+ * A PHP session handler using PDO to keep session data within a MySQL database
+ *
+ * @author  Jan Lohage <info@j2l4e.de>
+ * @link    https://github.com/j2L4e/PHP-PDO-MySQL-Session-Handler
+ *
+ *
+ * Based on PHP-MySQL-Session-Handler (uses mysqli)
+ *
+ * @author  Manuel Reinhard <manu@sprain.ch>
+ * @link    https://github.com/sprain/PHP-MySQL-Session-Handler
+ */
+
+use PDO;
+use SessionHandlerInterface;
+
+class SessionHandler implements SessionHandlerInterface
+{
+  /**
+   * a PDO connection resource
+   * @var resource
+   */
+  protected $dbh;
+
+
+  /**
+   * the name of the DB table which handles the sessions
+   * @var string
+   */
+  protected $dbTable;
+
+
+  /**
+   * Set db data if no connection is being injected
+   * @param  string $dbHost
+   * @param  string $dbUser
+   * @param  string $dbPassword
+   * @param  string $dbDatabase
+   * @param  string $dbCharset optional, default 'utf8'
+   */
+  public function setDbDetails($dbHost, $dbUser, $dbPassword, $dbDatabase, $dbCharset = 'utf8') {
+
+    //create db connection
+    $this->dbh = new PDO("mysql:" .
+      "host={$dbHost};" .
+      "dbname={$dbDatabase};" .
+      "charset={$dbCharset}",
+      $dbUser,
+      $dbPassword,
+      array(
+        PDO::ATTR_EMULATE_PREPARES => false,
+        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION //USE ERRMODE_SILENT FOR PRODUCTION!
+      )
+    );
+  }//function
+
+
+  /**
+   * Inject PDO from outside
+   * @param object $dbh expects PDO object
+   */
+  public function setPDO($dbh) {
+    $this->dbh = $dbh;
+  }
+
+
+  /**
+   * Set MySQL table to work with
+   * @param string $dbTable
+   */
+  public function setDbTable($dbTable) {
+    $this->dbTable = $dbTable;
+  }
+
+
+  /**
+   * Open the session
+   * @return bool
+   */
+  public function open($save_path, $session_name) {
+    //delete old session handlers
+    $limit = time() - (3600 * 24);
+    $stmt = $this->dbh->prepare("DELETE FROM {$this->dbTable} WHERE timestamp < :limit");
+    $ret = $stmt->execute(array(':limit' => $limit));
+
+    return $ret;
+  }
+
+  /**
+   * Close the session
+   * @return bool
+   */
+  public function close() {
+    $this->dbh = null;
+  }
+
+  /**
+   * Read the session
+   * @param int session id
+   * @return string string of the sessoin
+   */
+  public function read($id) {
+    $stmt = $this->dbh->prepare("SELECT * FROM {$this->dbTable} WHERE id=:id");
+    $stmt->execute(array(':id' => $id));
+
+    $session = $stmt->fetch(PDO::FETCH_ASSOC);
+
+    if ($session) {
+      $ret = $session['data'];
+    } else {
+      $ret = false;
+    }
+
+    return $ret;
+  }
+
+
+  /**
+   * Write the session
+   * @param int session id
+   * @param string data of the session
+   */
+  public function write($id, $data) {
+    $stmt = $this->dbh->prepare("REPLACE INTO {$this->dbTable} (id,data,timestamp) VALUES (:id,:data,:timestamp)");
+    $ret = $stmt->execute(
+      array(':id' => $id,
+        ':data' => $data,
+        'timestamp' => time()
+      ));
+
+    return $ret;
+  }
+
+  /**
+   * Destroy the session
+   * @param int session id
+   * @return bool
+   */
+  public function destroy($id) {
+    $stmt = $this->dbh->prepare("DELETE FROM {$this->dbTable} WHERE id=:id");
+    $ret = $stmt->execute(array(
+      ':id' => $id
+    ));
+
+    return $ret;
+  }
+
+
+  /**
+   * Garbage Collector
+   * @param int life time (sec.)
+   * @return bool
+   * @see session.gc_divisor      100
+   * @see session.gc_maxlifetime 1440
+   * @see session.gc_probability    1
+   * @usage execution rate 1/100
+   *        (session.gc_probability/session.gc_divisor)
+   */
+  public function gc($max) {
+    $stmt = $this->dbh->prepare("DELETE FROM {$this->dbTable} WHERE timestamp < :limit");
+    $ret = $stmt->execute(array(':limit' => time() - intval($max)));
+
+    return $ret;
+  }
+
+}//class

diff --git a/lib/auth0.php b/lib/auth0.php
index 96412f2..46b5514 100644 (file)
--- a/lib/auth0.php
+++ b/lib/auth0.php
@@ -16,6 +16,21 @@
     ini_set("session.save_handler", "memcached");
     ini_set("session.save_path", getenv("MEMCACHED"));
   }
+  else {
+    include_once(join(DIRECTORY_SEPARATOR, array($lib_root, "SessionHandler.php")));
+    $session_handler = new \JL\SessionHandler();
+    $session_handler->setPDO(Propel::getConnection());
+    $session_handler->setDbTable('Session');
+    session_set_save_handler(
+      array($session_handler, 'open'),
+      array($session_handler, 'close'),
+      array($session_handler, 'read'),
+      array($session_handler, 'write'),
+      array($session_handler, 'destroy'),
+      array($session_handler, 'gc')
+    );
+    register_shutdown_function('session_write_close');
+  }
 
   $auth0 = new Auth0([
     'domain' => $AUTH0_DOMAIN,

diff --git a/lib/header.php b/lib/header.php
index 928b741..a2432d3 100644 (file)
--- a/lib/header.php
+++ b/lib/header.php
@@ -12,7 +12,7 @@
 <body>
 
 <p id="header">
-<?php if ($username) { ?>
+<?php if (isset($username)) { ?>
 <a href="/city">Cities</a>
 /
 <a href="/area">Areas</a>

diff --git a/propel/schema.xml b/propel/schema.xml
index 3ab248b..e0c3aff 100644 (file)
--- a/propel/schema.xml
+++ b/propel/schema.xml
@@ -167,4 +167,15 @@
       <reference local="driver_id" foreign="id"/>
     </foreign-key>
   </table>
+
+  <!-- Session state -->
+  <table name="Session" phpName="Session" baseClass="ReadifoodObject">
+    <vendor type="mysql">
+      <parameter name="Engine" value="InnoDB"/>
+      <parameter name="Charset" value="utf8"/>
+    </vendor>
+    <column name="id" type="varchar" primaryKey="true" required="true"/>
+    <column name="data" type="longvarchar" required="true"/>
+    <column name="timestamp" type="integer" required="true"/>
+  </table>
 </database>

diff --git a/www/index.php b/www/index.php
index d88b321..d65e7c7 100644 (file)
--- a/www/index.php
+++ b/www/index.php
@@ -34,6 +34,21 @@
   include_once(join(DIRECTORY_SEPARATOR, array($lib_root, "functions.php")));
   include_once(join(DIRECTORY_SEPARATOR, array($lib_root, "session.php")));
 
+  if (isset($_GET['code'])) {
+    try {
+      $auth0->getUser();
+      header("Location: https://" . $_SERVER['HTTP_HOST'] . "/");
+      exit;
+    }
+    catch (\Exception $e) {
+      include_once(join(DIRECTORY_SEPARATOR, array($lib_root, "header.php")));
+      echo "<h1 class=\"printonly\">$charity</h1>\n";
+      echo "<h1>Not logged in!</h1>\n";
+      echo "<p>There was an authentication error.</p>\n";
+      include_once(join(DIRECTORY_SEPARATOR, array($lib_root, "footer.php")));
+    }
+  }
+
   $auth0_user = $auth0->getUser();
   if (isset($auth0_user)) $username = $auth0_user["sub"];
   else $username = null;

diff --git a/www/login.php b/www/login.php
deleted file mode 100644 (file)
index a9f9d22802021ce2673a84450a7bee5020e5e102..0000000000000000000000000000000000000000
--- a/www/login.php
+++ /dev/null
@@ -1,18 +0,0 @@
-<?php
-
-  $root = join(DIRECTORY_SEPARATOR, array($_SERVER['DOCUMENT_ROOT'], ".."));
-  $lib_root = join(DIRECTORY_SEPARATOR, array($root, "lib"));
-  require_once(join(DIRECTORY_SEPARATOR, array($root, "vendor", "autoload.php")));
-  include_once(join(DIRECTORY_SEPARATOR, array($lib_root, "auth0.php")));
-
-  if (isset($_GET['code'])) {
-    try {
-      $auth0->getUser();
-      header("Location: https://" . $_SERVER['HTTP_HOST'] . "/");
-      exit;
-    }
-    catch (\Exception $e) {
-    }
-  }
-
-?>

diff --git a/www/login.php b/www/login.php
new file mode 120000 (symlink)
index 0000000000000000000000000000000000000000..0012f7d2344777b9c670a71722eb0930093ce57f
--- /dev/null
+++ b/www/login.php
@@ -0,0 +1 @@
+index.php
\ No newline at end of file