4 * SQL-backed OpenID stores.
8 * LICENSE: See the COPYING file included in this distribution.
11 * @author JanRain, Inc. <openid@janrain.com>
12 * @copyright 2005-2008 Janrain, Inc.
13 * @license http://www.apache.org/licenses/LICENSE-2.0 Apache
17 * Require the PEAR DB module because we'll need it for the SQL-based
18 * stores implemented here. We silence any errors from the inclusion
19 * because it might not be present, and a user of the SQL stores may
20 * supply an Auth_OpenID_DatabaseConnection instance that implements
23 global $__Auth_OpenID_PEAR_AVAILABLE;
24 $__Auth_OpenID_PEAR_AVAILABLE = @include_once 'DB.php';
29 require_once 'Auth/OpenID/Interface.php';
30 require_once 'Auth/OpenID/Nonce.php';
35 require_once 'Auth/OpenID.php';
40 require_once 'Auth/OpenID/Nonce.php';
43 * This is the parent class for the SQL stores, which contains the
44 * logic common to all of the SQL stores.
46 * The table names used are determined by the class variables
47 * associations_table_name and nonces_table_name. To change the name
48 * of the tables used, pass new table names into the constructor.
50 * To create the tables with the proper schema, see the createTables
53 * This class shouldn't be used directly. Use one of its subclasses
54 * instead, as those contain the code necessary to use a specific
55 * database. If you're an OpenID integrator and you'd like to create
56 * an SQL-driven store that wraps an application's database
57 * abstraction, be sure to create a subclass of
58 * {@link Auth_OpenID_DatabaseConnection} that calls the application's
59 * database abstraction calls. Then, pass an instance of your new
60 * database connection class to your SQLStore subclass constructor.
62 * All methods other than the constructor and createTables should be
63 * considered implementation details.
67 class Auth_OpenID_SQLStore extends Auth_OpenID_OpenIDStore {
70 * This creates a new SQLStore instance. It requires an
71 * established database connection be given to it, and it allows
72 * overriding the default table names.
74 * @param connection $connection This must be an established
75 * connection to a database of the correct type for the SQLStore
76 * subclass you're using. This must either be an PEAR DB
77 * connection handle or an instance of a subclass of
78 * Auth_OpenID_DatabaseConnection.
80 * @param associations_table: This is an optional parameter to
81 * specify the name of the table used for storing associations.
82 * The default value is 'oid_associations'.
84 * @param nonces_table: This is an optional parameter to specify
85 * the name of the table used for storing nonces. The default
86 * value is 'oid_nonces'.
88 function Auth_OpenID_SQLStore($connection,
89 $associations_table = null,
92 global $__Auth_OpenID_PEAR_AVAILABLE;
94 $this->associations_table_name = "oid_associations";
95 $this->nonces_table_name = "oid_nonces";
97 // Check the connection object type to be sure it's a PEAR
98 // database connection.
99 if (!(is_object($connection) &&
100 (is_subclass_of($connection, 'db_common') ||
101 is_subclass_of($connection,
102 'auth_openid_databaseconnection')))) {
103 trigger_error("Auth_OpenID_SQLStore expected PEAR connection " .
104 "object (got ".get_class($connection).")",
109 $this->connection = $connection;
111 // Be sure to set the fetch mode so the results are keyed on
112 // column name instead of column index. This is a PEAR
113 // constant, so only try to use it if PEAR is present. Note
114 // that Auth_Openid_Databaseconnection instances need not
115 // implement ::setFetchMode for this reason.
116 if ($__Auth_OpenID_PEAR_AVAILABLE) {
117 $this->connection->setFetchMode(DB_FETCHMODE_ASSOC);
120 if ($associations_table) {
121 $this->associations_table_name = $associations_table;
125 $this->nonces_table_name = $nonces_table;
128 $this->max_nonce_age = 6 * 60 * 60;
130 // Be sure to run the database queries with auto-commit mode
131 // turned OFF, because we want every function to run in a
132 // transaction, implicitly. As a rule, methods named with a
133 // leading underscore will NOT control transaction behavior.
134 // Callers of these methods will worry about transactions.
135 $this->connection->autoCommit(false);
137 // Create an empty SQL strings array.
138 $this->sql = array();
140 // Call this method (which should be overridden by subclasses)
141 // to populate the $this->sql array with SQL strings.
144 // Verify that all required SQL statements have been set, and
145 // raise an error if any expected SQL strings were either
147 list($missing, $empty) = $this->_verifySQL();
150 trigger_error("Expected keys in SQL query list: " .
151 implode(", ", $missing),
157 trigger_error("SQL list keys have no SQL strings: " .
158 implode(", ", $empty),
163 // Add table names to queries.
167 function tableExists($table_name)
169 return !$this->isError(
170 $this->connection->query(
171 sprintf("SELECT * FROM %s LIMIT 0",
176 * Returns true if $value constitutes a database error; returns
179 function isError($value)
181 return PEAR::isError($value);
185 * Converts a query result to a boolean. If the result is a
186 * database error according to $this->isError(), this returns
187 * false; otherwise, this returns true.
189 function resultToBool($obj)
191 if ($this->isError($obj)) {
199 * This method should be overridden by subclasses. This method is
200 * called by the constructor to set values in $this->sql, which is
201 * an array keyed on sql name.
208 * Resets the store by removing all records from the store's
213 $this->connection->query(sprintf("DELETE FROM %s",
214 $this->associations_table_name));
216 $this->connection->query(sprintf("DELETE FROM %s",
217 $this->nonces_table_name));
223 function _verifySQL()
228 $required_sql_keys = array(
237 foreach ($required_sql_keys as $key) {
238 if (!array_key_exists($key, $this->sql)) {
240 } else if (!$this->sql[$key]) {
245 return array($missing, $empty);
253 $replacements = array(
255 'value' => $this->nonces_table_name,
256 'keys' => array('nonce_table',
261 'value' => $this->associations_table_name,
262 'keys' => array('assoc_table',
271 foreach ($replacements as $item) {
272 $value = $item['value'];
273 $keys = $item['keys'];
275 foreach ($keys as $k) {
276 if (is_array($this->sql[$k])) {
277 foreach ($this->sql[$k] as $part_key => $part_value) {
278 $this->sql[$k][$part_key] = sprintf($part_value,
282 $this->sql[$k] = sprintf($this->sql[$k], $value);
288 function blobDecode($blob)
293 function blobEncode($str)
298 function createTables()
300 $this->connection->autoCommit(true);
301 $n = $this->create_nonce_table();
302 $a = $this->create_assoc_table();
303 $this->connection->autoCommit(false);
312 function create_nonce_table()
314 if (!$this->tableExists($this->nonces_table_name)) {
315 $r = $this->connection->query($this->sql['nonce_table']);
316 return $this->resultToBool($r);
321 function create_assoc_table()
323 if (!$this->tableExists($this->associations_table_name)) {
324 $r = $this->connection->query($this->sql['assoc_table']);
325 return $this->resultToBool($r);
333 function _set_assoc($server_url, $handle, $secret, $issued,
334 $lifetime, $assoc_type)
336 return $this->connection->query($this->sql['set_assoc'],
346 function storeAssociation($server_url, $association)
348 if ($this->resultToBool($this->_set_assoc(
350 $association->handle,
352 $association->secret),
353 $association->issued,
354 $association->lifetime,
355 $association->assoc_type
357 $this->connection->commit();
359 $this->connection->rollback();
366 function _get_assoc($server_url, $handle)
368 $result = $this->connection->getRow($this->sql['get_assoc'],
369 array($server_url, $handle));
370 if ($this->isError($result)) {
380 function _get_assocs($server_url)
382 $result = $this->connection->getAll($this->sql['get_assocs'],
385 if ($this->isError($result)) {
392 function removeAssociation($server_url, $handle)
394 if ($this->_get_assoc($server_url, $handle) == null) {
398 if ($this->resultToBool($this->connection->query(
399 $this->sql['remove_assoc'],
400 array($server_url, $handle)))) {
401 $this->connection->commit();
403 $this->connection->rollback();
409 function getAssociation($server_url, $handle = null)
411 if ($handle !== null) {
412 $assoc = $this->_get_assoc($server_url, $handle);
419 $assocs = $this->_get_assocs($server_url);
422 if (!$assocs || (count($assocs) == 0)) {
425 $associations = array();
427 foreach ($assocs as $assoc_row) {
428 $assoc = new Auth_OpenID_Association($assoc_row['handle'],
429 $assoc_row['secret'],
430 $assoc_row['issued'],
431 $assoc_row['lifetime'],
432 $assoc_row['assoc_type']);
434 $assoc->secret = $this->blobDecode($assoc->secret);
436 if ($assoc->getExpiresIn() == 0) {
437 $this->removeAssociation($server_url, $assoc->handle);
439 $associations[] = array($assoc->issued, $assoc);
446 foreach ($associations as $key => $assoc) {
447 $issued[$key] = $assoc[0];
448 $assocs[$key] = $assoc[1];
451 array_multisort($issued, SORT_DESC, $assocs, SORT_DESC,
454 // return the most recently issued one.
455 list($issued, $assoc) = $associations[0];
466 function _add_nonce($server_url, $timestamp, $salt)
468 $sql = $this->sql['add_nonce'];
469 $result = $this->connection->query($sql, array($server_url,
472 if ($this->isError($result)) {
473 $this->connection->rollback();
475 $this->connection->commit();
477 return $this->resultToBool($result);
480 function useNonce($server_url, $timestamp, $salt)
482 global $Auth_OpenID_SKEW;
484 if ( abs($timestamp - time()) > $Auth_OpenID_SKEW ) {
488 return $this->_add_nonce($server_url, $timestamp, $salt);
492 * "Octifies" a binary string by returning a string with escaped
493 * octal bytes. This is used for preparing binary data for
494 * PostgreSQL BYTEA fields.
498 function _octify($str)
501 for ($i = 0; $i < Auth_OpenID::bytes($str); $i++) {
502 $ch = substr($str, $i, 1);
504 $result .= "\\\\\\\\";
505 } else if (ord($ch) == 0) {
506 $result .= "\\\\000";
508 $result .= "\\" . strval(decoct(ord($ch)));
515 * "Unoctifies" octal-escaped data from PostgreSQL and returns the
516 * resulting ASCII (possibly binary) string.
520 function _unoctify($str)
524 while ($i < strlen($str)) {
527 // Look to see if the next char is a backslash and
529 if ($str[$i + 1] != "\\") {
530 $octal_digits = substr($str, $i + 1, 3);
531 $dec = octdec($octal_digits);
548 function cleanupNonces()
550 global $Auth_OpenID_SKEW;
551 $v = time() - $Auth_OpenID_SKEW;
553 $this->connection->query($this->sql['clean_nonce'], array($v));
554 $num = $this->connection->affectedRows();
555 $this->connection->commit();
559 function cleanupAssociations()
561 $this->connection->query($this->sql['clean_assoc'],
563 $num = $this->connection->affectedRows();
564 $this->connection->commit();