Added the following patch from the dev@openidenabled.com mailing list:

http://lists.openidenabled.com/pipermail/dev/attachments/20080221/f087874e/attachment.obj

Original Messages:
Ryan Patterson cgamesplay at cgamesplay.com
Thu Feb 21 10:57:56 PST 2008
OpenIDStore for PEAR::MDB2

	"On Thu, Feb 21, 2008 at 10:24 AM, Thomas Harning
	<thomas.harning at trustbearer.com> wrote:
	>  Shortening the URLs to 255 chars has some ugly problems since the spec states
	>  that the max Server URL can technically be up to 2047 bytes (per OpenID 1.1-Appendix D)

	Ah, I glanced at the spec but didn't see anything.

	>  One option for databases that can't have a key large enough is using a prefix-based key...
	>  I know MySQL supports this, but I'm not so sure on others.

	This is what the current SQLStore implementation does. I've modified
	the patch to add specific SQL for mysql backends, but use MDB2's
	generic table creation for other stores. The test cases in the patch
	pass for MDB2 using MySQL, but I'd like to verify that it works with
	PostgreSQL.

	--
	Regards,
	Ryan Patterson <mailto:cgamesplay at cgamesplay.com>"

		Thomas Harning thomas.harning at trustbearer.com
		Thu Feb 21 07:24:02 PST 2008
		OpenIDStore for PEAR::MDB2

			"Ryan Patterson wrote:
			> I've created an Auth_OpenID_OpenIDStore implementation that utilizes
			> PEAR::MDB2 instead of PEAR::DB, called Auth_OpenID_MDB2Store. The
			> advantages to this store are that it supports all database back ends
			> that PEAR::MDB2 supports, and does not require the user to know which
			> database back end is being used when instantiating the store. Because
			> of the complete database abstraction, the entire store is
			> approximately 350 lines shorter than the SQLStore family.
			>
			> As a note to users planning to test this store: the table schema is
			> incompatible with the other SQLStores. Specifically, the length of
			> server URLs has been shortened to 255 characters to allow cross-RDBMS
			> compatibility, and the field types have been changed to NOT NULL. You
			> may safely delete your associations and nonces tables with no
			> consequences.

			Shortening the URLs to 255 chars has some ugly problems since the spec states
			that the max Server URL can technically be up to 2047 bytes (per OpenID 1.1-Appendix D)

			One option for databases that can't have a key large enough is using a prefix-based key...
			I know MySQL supports this, but I'm not so sure on others.

			Another option would be to use a unique value as the primary key and use some ugly
			mechanics so that you can store a 20 byte sha1 in a non-unique index and the server URL
			not in an index.

			The basic select would be SELECT * FROM Sites WHERE URLHash = ? AND URL = ?;

			The URLHash would narrow it down to likely one, where URL would clobber potential
			collisions and keep the data around if needed...

			--
			Thomas Harning @ TrustBearer Labs (http://www.trustbearer.com)
			Secure OpenID: https://openid.trustbearer.com/harningt
			3201 Stellhorn Road 260-399-1656
			Fort Wayne, IN 46815"

				OpenIDStore for PEAR::MDB2
				Ryan Patterson cgamesplay at cgamesplay.com
				Thu Feb 21 07:19:02 PST 2008

					"I've created an Auth_OpenID_OpenIDStore implementation that utilizes
					PEAR::MDB2 instead of PEAR::DB, called Auth_OpenID_MDB2Store. The
					advantages to this store are that it supports all database back ends
					that PEAR::MDB2 supports, and does not require the user to know which
					database back end is being used when instantiating the store. Because
					of the complete database abstraction, the entire store is
					approximately 350 lines shorter than the SQLStore family.

					As a note to users planning to test this store: the table schema is
					incompatible with the other SQLStores. Specifically, the length of
					server URLs has been shortened to 255 characters to allow cross-RDBMS
					compatibility, and the field types have been changed to NOT NULL. You
					may safely delete your associations and nonces tables with no
					consequences.

					A test case has been added that tests the MDB2 store with a MySQL
					backend. I would appreciate it if someone capable could modify the
					test case and test it using PostgreSQL, to ensure that the database
					abstraction I've used is correct.

					--
					Regards,
					Ryan Patterson <mailto:cgamesplay at cgamesplay.com>"

All of the patch's hunks were applied successfully.

2 files changed, 475 insertions, 6 deletions

diff --git a/Auth/OpenID/MDB2Store.php b/Auth/OpenID/MDB2Store.php
new file mode 100644
index 0000000..80024ba
--- /dev/null
+++ b/Auth/OpenID/MDB2Store.php
@@ -0,0 +1,413 @@
+<?php
+
+/**
+ * SQL-backed OpenID stores for use with PEAR::MDB2.
+ *
+ * PHP versions 4 and 5
+ *
+ * LICENSE: See the COPYING file included in this distribution.
+ *
+ * @package OpenID
+ * @author JanRain, Inc. <openid@janrain.com>
+ * @copyright 2005 Janrain, Inc.
+ * @license http://www.gnu.org/copyleft/lesser.html LGPL
+ */
+
+require_once 'MDB2.php';
+
+/**
+ * @access private
+ */
+require_once 'Auth/OpenID/Interface.php';
+
+/**
+ * @access private
+ */
+require_once 'Auth/OpenID.php';
+
+/**
+ * @access private
+ */
+require_once 'Auth/OpenID/Nonce.php';
+
+/**
+ * This store uses a PEAR::MDB2 connection to store persistence
+ * information.
+ *
+ * The table names used are determined by the class variables
+ * associations_table_name and nonces_table_name.  To change the name
+ * of the tables used, pass new table names into the constructor.
+ *
+ * To create the tables with the proper schema, see the createTables
+ * method.
+ *
+ * @package OpenID
+ */
+class Auth_OpenID_MDB2Store extends Auth_OpenID_OpenIDStore {
+    /**
+     * This creates a new MDB2Store instance.  It requires an
+     * established database connection be given to it, and it allows
+     * overriding the default table names.
+     *
+     * @param connection $connection This must be an established
+     * connection to a database of the correct type for the SQLStore
+     * subclass you're using.  This must be a PEAR::MDB2 connection
+     * handle.
+     *
+     * @param associations_table: This is an optional parameter to
+     * specify the name of the table used for storing associations.
+     * The default value is 'oid_associations'.
+     *
+     * @param nonces_table: This is an optional parameter to specify
+     * the name of the table used for storing nonces.  The default
+     * value is 'oid_nonces'.
+     */
+    function Auth_OpenID_MDB2Store($connection,
+                                  $associations_table = null,
+                                  $nonces_table = null)
+    {
+        $this->associations_table_name = "oid_associations";
+        $this->nonces_table_name = "oid_nonces";
+
+        // Check the connection object type to be sure it's a PEAR
+        // database connection.
+        if (!is_object($connection) ||
+            !is_subclass_of($connection, 'mdb2_driver_common')) {
+            trigger_error("Auth_OpenID_MDB2Store expected PEAR connection " .
+                          "object (got ".get_class($connection).")",
+                          E_USER_ERROR);
+            return;
+        }
+
+        $this->connection = $connection;
+
+        // Be sure to set the fetch mode so the results are keyed on
+        // column name instead of column index.
+        $this->connection->setFetchMode(MDB2_FETCHMODE_ASSOC);
+        
+        if (PEAR::isError($this->connection->loadModule('Extended'))) {
+            trigger_error("Unable to load MDB2_Extended module", E_USER_ERROR);
+            return;
+        }
+
+        if ($associations_table) {
+            $this->associations_table_name = $associations_table;
+        }
+
+        if ($nonces_table) {
+            $this->nonces_table_name = $nonces_table;
+        }
+
+        $this->max_nonce_age = 6 * 60 * 60;
+    }
+
+    function tableExists($table_name)
+    {
+        return !PEAR::isError($this->connection->query(
+                                  sprintf("SELECT * FROM %s LIMIT 0",
+                                          $table_name)));
+    }
+
+    function createTables()
+    {
+        $n = $this->create_nonce_table();
+        $a = $this->create_assoc_table();
+
+        if (!$n || !$a) {
+            return false;
+        }
+        return true;
+    }
+
+    function create_nonce_table()
+    {
+        if (!$this->tableExists($this->nonces_table_name)) {
+            switch ($this->connection->phptype) {
+                case "mysql":
+                case "mysqli":
+                    // Custom SQL for MySQL to use InnoDB and variable-
+                    // length keys
+                    $r = $this->connection->exec(
+                        sprintf("CREATE TABLE %s (\n".
+                                "  server_url VARCHAR(2047) NOT NULL DEFAULT '',\n".
+                                "  timestamp INTEGER NOT NULL,\n".
+                                "  salt CHAR(40) NOT NULL,\n".
+                                "  UNIQUE (server_url(255), timestamp, salt)\n".
+                                ") TYPE=InnoDB",
+                                $this->nonces_table_name));
+                    if (PEAR::isError($r)) {
+                        return false;
+                    }
+                    break;
+                default:
+                    if (PEAR::isError(
+                        $this->connection->loadModule('Manager'))) {
+                        return false;
+                    }
+                    $fields = array(
+                        "server_url" => array(
+                            "type" => "text",
+                            "length" => 2047,
+                            "notnull" => true
+                        ),
+                        "timestamp" => array(
+                            "type" => "integer",
+                            "notnull" => true
+                        ),
+                        "salt" => array(
+                            "type" => "text",
+                            "length" => 40,
+                            "fixed" => true,
+                            "notnull" => true
+                        )
+                    );
+                    $constraint = array(
+                        "unique" => 1,
+                        "fields" => array(
+                            "server_url" => true,
+                            "timestamp" => true,
+                            "salt" => true
+                        )
+                    );
+                    
+                    $r = $this->connection->createTable($this->nonces_table_name,
+                                                        $fields);
+                    if (PEAR::isError($r)) {
+                        return false;
+                    }
+                    
+                    $r = $this->connection->createConstraint(
+                        $this->nonces_table_name,
+                        $this->nonces_table_name . "_constraint",
+                        $constraint);
+                    if (PEAR::isError($r)) {
+                        return false;
+                    }
+                    break;
+            }
+        }
+        return true;
+    }
+
+    function create_assoc_table()
+    {
+        if (!$this->tableExists($this->associations_table_name)) {
+            switch ($this->connection->phptype) {
+                case "mysql":
+                case "mysqli":
+                    // Custom SQL for MySQL to use InnoDB and variable-
+                    // length keys
+                    $r = $this->connection->exec(
+                        sprintf("CREATE TABLE %s(\n".
+                                "  server_url VARCHAR(2047) NOT NULL DEFAULT '',\n".
+                                "  handle VARCHAR(255) NOT NULL,\n".
+                                "  secret BLOB NOT NULL,\n".
+                                "  issued INTEGER NOT NULL,\n".
+                                "  lifetime INTEGER NOT NULL,\n".
+                                "  assoc_type VARCHAR(64) NOT NULL,\n".
+                                "  PRIMARY KEY (server_url(255), handle)\n".
+                                ") TYPE=InnoDB",
+                            $this->associations_table_name));
+                    if (PEAR::isError($r)) {
+                        return false;
+                    }
+                    break;
+                default:
+                    if (PEAR::isError(
+                        $this->connection->loadModule('Manager'))) {
+                        return false;
+                    }
+                    $fields = array(
+                        "server_url" => array(
+                            "type" => "text",
+                            "length" => 2047,
+                            "notnull" => true
+                        ),
+                        "handle" => array(
+                            "type" => "text",
+                            "length" => 255,
+                            "notnull" => true
+                        ),
+                        "secret" => array(
+                            "type" => "blob",
+                            "length" => "255",
+                            "notnull" => true
+                        ),
+                        "issued" => array(
+                            "type" => "integer",
+                            "notnull" => true
+                        ),
+                        "lifetime" => array(
+                            "type" => "integer",
+                            "notnull" => true
+                        ),
+                        "assoc_type" => array(
+                            "type" => "text",
+                            "length" => 64,
+                            "notnull" => true
+                        )
+                    );
+                    $options = array(
+                        "primary" => array(
+                            "server_url" => true,
+                            "handle" => true
+                        )
+                    );
+                    
+                    $r = $this->connection->createTable(
+                        $this->associations_table_name,
+                        $fields,
+                        $options);
+                    if (PEAR::isError($r)) {
+                        return false;
+                    }
+                    break;
+            }
+        }
+        return true;
+    }
+
+    function storeAssociation($server_url, $association)
+    {
+        $fields = array(
+            "server_url" => array(
+                "value" => $server_url,
+                "key" => true
+            ),
+            "handle" => array(
+                "value" => $association->handle,
+                "key" => true
+            ),
+            "secret" => array(
+                "value" => $association->secret,
+                "type" => "blob"
+            ),
+            "issued" => array(
+                "value" => $association->issued
+            ),
+            "lifetime" => array(
+                "value" => $association->lifetime
+            ),
+            "assoc_type" => array(
+                "value" => $association->assoc_type
+            )
+        );
+        
+        return !PEAR::isError($this->connection->replace(
+                                  $this->associations_table_name,
+                                  $fields));
+    }
+
+    function cleanupNonces()
+    {
+        global $Auth_OpenID_SKEW;
+        $v = time() - $Auth_OpenID_SKEW;
+
+        return $this->connection->exec(
+            sprintf("DELETE FROM %s WHERE timestamp < %d",
+                    $this->nonces_table_name, $v));
+    }
+
+    function cleanupAssociations()
+    {
+        return $this->connection->exec(
+            sprintf("DELETE FROM %s WHERE issued + lifetime < %d",
+                    $this->associations_table_name, time()));
+    }
+
+    function getAssociation($server_url, $handle = null)
+    {
+        $sql = "";
+        $params = null;
+        $types = array(
+                       "text",
+                       "blob",
+                       "integer",
+                       "integer",
+                       "text"
+                       );
+        if ($handle !== null) {
+            $sql = sprintf("SELECT handle, secret, issued, lifetime, assoc_type " .
+                           "FROM %s WHERE server_url = ? AND handle = ?",
+                           $this->associations_table_name);
+            $params = array($server_url, $handle);
+        } else {
+            $sql = sprintf("SELECT handle, secret, issued, lifetime, assoc_type " .
+                           "FROM %s WHERE server_url = ? ORDER BY issued DESC",
+                           $this->associations_table_name);
+            $params = array($server_url);
+        }
+        
+        $assoc = $this->connection->getRow($sql, $types, $params);
+
+        if (!$assoc || PEAR::isError($assoc)) {
+            return null;
+        } else {
+            $association = new Auth_OpenID_Association($assoc['handle'],
+                                                       stream_get_contents(
+                                                           $assoc['secret']),
+                                                       $assoc['issued'],
+                                                       $assoc['lifetime'],
+                                                       $assoc['assoc_type']);
+            fclose($assoc['secret']);
+            return $association;
+        }
+    }
+
+    function removeAssociation($server_url, $handle)
+    {
+        $r = $this->connection->execParam(
+            sprintf("DELETE FROM %s WHERE server_url = ? AND handle = ?",
+                    $this->associations_table_name),
+            array($server_url, $handle));
+        
+        if (PEAR::isError($r) || $r == 0) {
+            return false;
+        }
+        return true;
+    }
+
+    function useNonce($server_url, $timestamp, $salt)
+    {
+        global $Auth_OpenID_SKEW;
+
+        if (abs($timestamp - time()) > $Auth_OpenID_SKEW ) {
+            return false;
+        }
+        
+        $fields = array(
+                        "timestamp" => $timestamp,
+                        "salt" => $salt
+                        );
+        
+        if (!empty($server_url)) {
+            $fields["server_url"] = $server_url;
+        }
+        
+        $r = $this->connection->autoExecute(
+            $this->nonces_table_name,
+            $fields,
+            MDB2_AUTOQUERY_INSERT);
+        
+        if (PEAR::isError($r)) {
+            return false;
+        }
+        return true;
+    }
+
+    /**
+     * Resets the store by removing all records from the store's
+     * tables.
+     */
+    function reset()
+    {
+        $this->connection->query(sprintf("DELETE FROM %s",
+                                         $this->associations_table_name));
+
+        $this->connection->query(sprintf("DELETE FROM %s",
+                                         $this->nonces_table_name));
+    }
+
+}
+
+?>
diff --git a/Tests/Auth/OpenID/StoreTest.php b/Tests/Auth/OpenID/StoreTest.php
index b51b961..5e8c8d4 100644
--- a/Tests/Auth/OpenID/StoreTest.php
+++ b/Tests/Auth/OpenID/StoreTest.php
@@ -440,14 +440,14 @@ class Tests_Auth_OpenID_Included_StoreTest extends Tests_Auth_OpenID_Store {
         // because we can't run the test.
         if (!(extension_loaded('pgsql') ||
               @dl('pgsql.so') ||
-              @dl('php_pgsql.dll'))) {
+              @dl('php_pgsql.dll')) ||
+            !(@include_once 'DB.php')) {
             print "(not testing PostGreSQL store)";
             $this->pass();
             return;
         }
 
         require_once 'Auth/OpenID/PostgreSQLStore.php';
-        require_once 'DB.php';
 
         global $_Auth_OpenID_db_test_host;
 
@@ -560,14 +560,14 @@ class Tests_Auth_OpenID_Included_StoreTest extends Tests_Auth_OpenID_Store {
         // because we can't run the test.
         if (!(extension_loaded('sqlite') ||
               @dl('sqlite.so') ||
-              @dl('php_sqlite.dll'))) {
+              @dl('php_sqlite.dll')) ||
+            !(@include_once 'DB.php')) {
             print "(not testing SQLite store)";
             $this->pass();
             return;
         }
 
         require_once 'Auth/OpenID/SQLiteStore.php';
-        require_once 'DB.php';
 
         $temp_dir = _Auth_OpenID_mkdtemp();
 
@@ -604,14 +604,14 @@ class Tests_Auth_OpenID_Included_StoreTest extends Tests_Auth_OpenID_Store {
         // If the mysql extension isn't loaded or loadable, succeed
         // because we can't run the test.
         if (!(extension_loaded('mysql') ||
-              @dl('mysql.' . PHP_SHLIB_SUFFIX))) {
+              @dl('mysql.' . PHP_SHLIB_SUFFIX)) ||
+            !(@include_once 'DB.php')) {
             print "(not testing MySQL store)";
             $this->pass();
             return;
         }
 
         require_once 'Auth/OpenID/MySQLStore.php';
-        require_once 'DB.php';
 
         global $_Auth_OpenID_db_test_host;
 
@@ -651,6 +651,62 @@ class Tests_Auth_OpenID_Included_StoreTest extends Tests_Auth_OpenID_Store {
 
         $db->query("DROP DATABASE $temp_db_name");
     }
+
+    function test_mdb2store()
+    {
+        // The MDB2 test can use any database engine. MySQL is chosen
+        // arbitrarily.
+        if (!(extension_loaded('mysql') ||
+              @dl('mysql.' . PHP_SHLIB_SUFFIX)) ||
+            !(@include_once 'MDB2.php')) {
+            print "(not testing MDB2 store)";
+            $this->pass();
+            return;
+        }
+
+        require_once 'Auth/OpenID/MDB2Store.php';
+
+        global $_Auth_OpenID_db_test_host;
+
+        $dsn = array(
+                     'phptype'  => 'mysql',
+                     'username' => 'openid_test',
+                     'password' => '',
+                     'hostspec' => $_Auth_OpenID_db_test_host
+                     );
+
+        $db =& MDB2::connect($dsn);
+
+        if (PEAR::isError($db)) {
+            print "MySQL database connection failed: " .
+                $db->getMessage();
+            $this->pass();
+            return;
+        }
+
+        $temp_db_name = _Auth_OpenID_getTmpDbName();
+
+        $result = $db->query("CREATE DATABASE $temp_db_name");
+
+        if (PEAR::isError($result)) {
+            $this->pass("Error creating MySQL temporary database: " .
+                        $result->getMessage());
+            return;
+        }
+
+        $db->query("USE $temp_db_name");
+
+        $store =& new Auth_OpenID_MDB2Store($db);
+        if (!$store->createTables()) {
+            $this->fail("Failed to create tables");
+            return;
+        }
+        $this->_testStore($store);
+        $this->_testNonce($store);
+        $this->_testNonceCleanup($store);
+
+        $db->query("DROP DATABASE $temp_db_name");
+    }
 }
 
 /**