Update positioning in contact page
[living-lab-site.git] / application / libraries / Auth / OpenID / SQLStore.php
1 <?php
2
3 /**
4  * SQL-backed OpenID stores.
5  *
6  * PHP versions 4 and 5
7  *
8  * LICENSE: See the COPYING file included in this distribution.
9  *
10  * @package OpenID
11  * @author JanRain, Inc. <openid@janrain.com>
12  * @copyright 2005-2008 Janrain, Inc.
13  * @license http://www.apache.org/licenses/LICENSE-2.0 Apache
14  */
15
16 /**
17  * @access private
18  */
19 require_once 'Auth/OpenID/Interface.php';
20 require_once 'Auth/OpenID/Nonce.php';
21
22 /**
23  * @access private
24  */
25 require_once 'Auth/OpenID.php';
26
27 /**
28  * @access private
29  */
30 require_once 'Auth/OpenID/Nonce.php';
31
32 /**
33  * This is the parent class for the SQL stores, which contains the
34  * logic common to all of the SQL stores.
35  *
36  * The table names used are determined by the class variables
37  * associations_table_name and nonces_table_name.  To change the name
38  * of the tables used, pass new table names into the constructor.
39  *
40  * To create the tables with the proper schema, see the createTables
41  * method.
42  *
43  * This class shouldn't be used directly.  Use one of its subclasses
44  * instead, as those contain the code necessary to use a specific
45  * database.  If you're an OpenID integrator and you'd like to create
46  * an SQL-driven store that wraps an application's database
47  * abstraction, be sure to create a subclass of
48  * {@link Auth_OpenID_DatabaseConnection} that calls the application's
49  * database abstraction calls.  Then, pass an instance of your new
50  * database connection class to your SQLStore subclass constructor.
51  *
52  * All methods other than the constructor and createTables should be
53  * considered implementation details.
54  *
55  * @package OpenID
56  */
57 class Auth_OpenID_SQLStore extends Auth_OpenID_OpenIDStore {
58
59     /**
60      * This creates a new SQLStore instance.  It requires an
61      * established database connection be given to it, and it allows
62      * overriding the default table names.
63      *
64      * @param connection $connection This must be an established
65      * connection to a database of the correct type for the SQLStore
66      * subclass you're using.  This must either be an PEAR DB
67      * connection handle or an instance of a subclass of
68      * Auth_OpenID_DatabaseConnection.
69      *
70      * @param associations_table: This is an optional parameter to
71      * specify the name of the table used for storing associations.
72      * The default value is 'oid_associations'.
73      *
74      * @param nonces_table: This is an optional parameter to specify
75      * the name of the table used for storing nonces.  The default
76      * value is 'oid_nonces'.
77      */
78     function Auth_OpenID_SQLStore($connection,
79                                   $associations_table = null,
80                                   $nonces_table = null)
81     {
82         $this->associations_table_name = "oid_associations";
83         $this->nonces_table_name = "oid_nonces";
84
85         // Check the connection object type to be sure it's a PEAR
86         // database connection.
87         if (!(is_object($connection) &&
88               (is_subclass_of($connection, 'db_common') ||
89                is_subclass_of($connection,
90                               'auth_openid_databaseconnection')))) {
91             trigger_error("Auth_OpenID_SQLStore expected PEAR connection " .
92                           "object (got ".get_class($connection).")",
93                           E_USER_ERROR);
94             return;
95         }
96
97         $this->connection = $connection;
98
99         // Be sure to set the fetch mode so the results are keyed on
100         // column name instead of column index.  This is a PEAR
101         // constant, so only try to use it if PEAR is present.  Note
102         // that Auth_Openid_Databaseconnection instances need not
103         // implement ::setFetchMode for this reason.
104         if (is_subclass_of($this->connection, 'db_common')) {
105             $this->connection->setFetchMode(DB_FETCHMODE_ASSOC);
106         }
107
108         if ($associations_table) {
109             $this->associations_table_name = $associations_table;
110         }
111
112         if ($nonces_table) {
113             $this->nonces_table_name = $nonces_table;
114         }
115
116         $this->max_nonce_age = 6 * 60 * 60;
117
118         // Be sure to run the database queries with auto-commit mode
119         // turned OFF, because we want every function to run in a
120         // transaction, implicitly.  As a rule, methods named with a
121         // leading underscore will NOT control transaction behavior.
122         // Callers of these methods will worry about transactions.
123         $this->connection->autoCommit(false);
124
125         // Create an empty SQL strings array.
126         $this->sql = array();
127
128         // Call this method (which should be overridden by subclasses)
129         // to populate the $this->sql array with SQL strings.
130         $this->setSQL();
131
132         // Verify that all required SQL statements have been set, and
133         // raise an error if any expected SQL strings were either
134         // absent or empty.
135         list($missing, $empty) = $this->_verifySQL();
136
137         if ($missing) {
138             trigger_error("Expected keys in SQL query list: " .
139                           implode(", ", $missing),
140                           E_USER_ERROR);
141             return;
142         }
143
144         if ($empty) {
145             trigger_error("SQL list keys have no SQL strings: " .
146                           implode(", ", $empty),
147                           E_USER_ERROR);
148             return;
149         }
150
151         // Add table names to queries.
152         $this->_fixSQL();
153     }
154
155     function tableExists($table_name)
156     {
157         return !$this->isError(
158                       $this->connection->query(
159                           sprintf("SELECT * FROM %s LIMIT 0",
160                                   $table_name)));
161     }
162
163     /**
164      * Returns true if $value constitutes a database error; returns
165      * false otherwise.
166      */
167     function isError($value)
168     {
169         return PEAR::isError($value);
170     }
171
172     /**
173      * Converts a query result to a boolean.  If the result is a
174      * database error according to $this->isError(), this returns
175      * false; otherwise, this returns true.
176      */
177     function resultToBool($obj)
178     {
179         if ($this->isError($obj)) {
180             return false;
181         } else {
182             return true;
183         }
184     }
185
186     /**
187      * This method should be overridden by subclasses.  This method is
188      * called by the constructor to set values in $this->sql, which is
189      * an array keyed on sql name.
190      */
191     function setSQL()
192     {
193     }
194
195     /**
196      * Resets the store by removing all records from the store's
197      * tables.
198      */
199     function reset()
200     {
201         $this->connection->query(sprintf("DELETE FROM %s",
202                                          $this->associations_table_name));
203
204         $this->connection->query(sprintf("DELETE FROM %s",
205                                          $this->nonces_table_name));
206     }
207
208     /**
209      * @access private
210      */
211     function _verifySQL()
212     {
213         $missing = array();
214         $empty = array();
215
216         $required_sql_keys = array(
217                                    'nonce_table',
218                                    'assoc_table',
219                                    'set_assoc',
220                                    'get_assoc',
221                                    'get_assocs',
222                                    'remove_assoc'
223                                    );
224
225         foreach ($required_sql_keys as $key) {
226             if (!array_key_exists($key, $this->sql)) {
227                 $missing[] = $key;
228             } else if (!$this->sql[$key]) {
229                 $empty[] = $key;
230             }
231         }
232
233         return array($missing, $empty);
234     }
235
236     /**
237      * @access private
238      */
239     function _fixSQL()
240     {
241         $replacements = array(
242                               array(
243                                     'value' => $this->nonces_table_name,
244                                     'keys' => array('nonce_table',
245                                                     'add_nonce',
246                                                     'clean_nonce')
247                                     ),
248                               array(
249                                     'value' => $this->associations_table_name,
250                                     'keys' => array('assoc_table',
251                                                     'set_assoc',
252                                                     'get_assoc',
253                                                     'get_assocs',
254                                                     'remove_assoc',
255                                                     'clean_assoc')
256                                     )
257                               );
258
259         foreach ($replacements as $item) {
260             $value = $item['value'];
261             $keys = $item['keys'];
262
263             foreach ($keys as $k) {
264                 if (is_array($this->sql[$k])) {
265                     foreach ($this->sql[$k] as $part_key => $part_value) {
266                         $this->sql[$k][$part_key] = sprintf($part_value,
267                                                             $value);
268                     }
269                 } else {
270                     $this->sql[$k] = sprintf($this->sql[$k], $value);
271                 }
272             }
273         }
274     }
275
276     function blobDecode($blob)
277     {
278         return $blob;
279     }
280
281     function blobEncode($str)
282     {
283         return $str;
284     }
285
286     function createTables()
287     {
288         $this->connection->autoCommit(true);
289         $n = $this->create_nonce_table();
290         $a = $this->create_assoc_table();
291         $this->connection->autoCommit(false);
292
293         if ($n && $a) {
294             return true;
295         } else {
296             return false;
297         }
298     }
299
300     function create_nonce_table()
301     {
302         if (!$this->tableExists($this->nonces_table_name)) {
303             $r = $this->connection->query($this->sql['nonce_table']);
304             return $this->resultToBool($r);
305         }
306         return true;
307     }
308
309     function create_assoc_table()
310     {
311         if (!$this->tableExists($this->associations_table_name)) {
312             $r = $this->connection->query($this->sql['assoc_table']);
313             return $this->resultToBool($r);
314         }
315         return true;
316     }
317
318     /**
319      * @access private
320      */
321     function _set_assoc($server_url, $handle, $secret, $issued,
322                         $lifetime, $assoc_type)
323     {
324         return $this->connection->query($this->sql['set_assoc'],
325                                         array(
326                                               $server_url,
327                                               $handle,
328                                               $secret,
329                                               $issued,
330                                               $lifetime,
331                                               $assoc_type));
332     }
333
334     function storeAssociation($server_url, $association)
335     {
336         if ($this->resultToBool($this->_set_assoc(
337                                             $server_url,
338                                             $association->handle,
339                                             $this->blobEncode(
340                                                   $association->secret),
341                                             $association->issued,
342                                             $association->lifetime,
343                                             $association->assoc_type
344                                             ))) {
345             $this->connection->commit();
346         } else {
347             $this->connection->rollback();
348         }
349     }
350
351     /**
352      * @access private
353      */
354     function _get_assoc($server_url, $handle)
355     {
356         $result = $this->connection->getRow($this->sql['get_assoc'],
357                                             array($server_url, $handle));
358         if ($this->isError($result)) {
359             return null;
360         } else {
361             return $result;
362         }
363     }
364
365     /**
366      * @access private
367      */
368     function _get_assocs($server_url)
369     {
370         $result = $this->connection->getAll($this->sql['get_assocs'],
371                                             array($server_url));
372
373         if ($this->isError($result)) {
374             return array();
375         } else {
376             return $result;
377         }
378     }
379
380     function removeAssociation($server_url, $handle)
381     {
382         if ($this->_get_assoc($server_url, $handle) == null) {
383             return false;
384         }
385
386         if ($this->resultToBool($this->connection->query(
387                               $this->sql['remove_assoc'],
388                               array($server_url, $handle)))) {
389             $this->connection->commit();
390         } else {
391             $this->connection->rollback();
392         }
393
394         return true;
395     }
396
397     function getAssociation($server_url, $handle = null)
398     {
399         if ($handle !== null) {
400             $assoc = $this->_get_assoc($server_url, $handle);
401
402             $assocs = array();
403             if ($assoc) {
404                 $assocs[] = $assoc;
405             }
406         } else {
407             $assocs = $this->_get_assocs($server_url);
408         }
409
410         if (!$assocs || (count($assocs) == 0)) {
411             return null;
412         } else {
413             $associations = array();
414
415             foreach ($assocs as $assoc_row) {
416                 $assoc = new Auth_OpenID_Association($assoc_row['handle'],
417                                                      $assoc_row['secret'],
418                                                      $assoc_row['issued'],
419                                                      $assoc_row['lifetime'],
420                                                      $assoc_row['assoc_type']);
421
422                 $assoc->secret = $this->blobDecode($assoc->secret);
423
424                 if ($assoc->getExpiresIn() == 0) {
425                     $this->removeAssociation($server_url, $assoc->handle);
426                 } else {
427                     $associations[] = array($assoc->issued, $assoc);
428                 }
429             }
430
431             if ($associations) {
432                 $issued = array();
433                 $assocs = array();
434                 foreach ($associations as $key => $assoc) {
435                     $issued[$key] = $assoc[0];
436                     $assocs[$key] = $assoc[1];
437                 }
438
439                 array_multisort($issued, SORT_DESC, $assocs, SORT_DESC,
440                                 $associations);
441
442                 // return the most recently issued one.
443                 list($issued, $assoc) = $associations[0];
444                 return $assoc;
445             } else {
446                 return null;
447             }
448         }
449     }
450
451     /**
452      * @access private
453      */
454     function _add_nonce($server_url, $timestamp, $salt)
455     {
456         $sql = $this->sql['add_nonce'];
457         $result = $this->connection->query($sql, array($server_url,
458                                                        $timestamp,
459                                                        $salt));
460         if ($this->isError($result)) {
461             $this->connection->rollback();
462         } else {
463             $this->connection->commit();
464         }
465         return $this->resultToBool($result);
466     }
467
468     function useNonce($server_url, $timestamp, $salt)
469     {
470         global $Auth_OpenID_SKEW;
471
472         if ( abs($timestamp - time()) > $Auth_OpenID_SKEW ) {
473             return false;
474         }
475
476         return $this->_add_nonce($server_url, $timestamp, $salt);
477     }
478
479     /**
480      * "Octifies" a binary string by returning a string with escaped
481      * octal bytes.  This is used for preparing binary data for
482      * PostgreSQL BYTEA fields.
483      *
484      * @access private
485      */
486     function _octify($str)
487     {
488         $result = "";
489         for ($i = 0; $i < Auth_OpenID::bytes($str); $i++) {
490             $ch = substr($str, $i, 1);
491             if ($ch == "\\") {
492                 $result .= "\\\\\\\\";
493             } else if (ord($ch) == 0) {
494                 $result .= "\\\\000";
495             } else {
496                 $result .= "\\" . strval(decoct(ord($ch)));
497             }
498         }
499         return $result;
500     }
501
502     /**
503      * "Unoctifies" octal-escaped data from PostgreSQL and returns the
504      * resulting ASCII (possibly binary) string.
505      *
506      * @access private
507      */
508     function _unoctify($str)
509     {
510         $result = "";
511         $i = 0;
512         while ($i < strlen($str)) {
513             $char = $str[$i];
514             if ($char == "\\") {
515                 // Look to see if the next char is a backslash and
516                 // append it.
517                 if ($str[$i + 1] != "\\") {
518                     $octal_digits = substr($str, $i + 1, 3);
519                     $dec = octdec($octal_digits);
520                     $char = chr($dec);
521                     $i += 4;
522                 } else {
523                     $char = "\\";
524                     $i += 2;
525                 }
526             } else {
527                 $i += 1;
528             }
529
530             $result .= $char;
531         }
532
533         return $result;
534     }
535
536     function cleanupNonces()
537     {
538         global $Auth_OpenID_SKEW;
539         $v = time() - $Auth_OpenID_SKEW;
540
541         $this->connection->query($this->sql['clean_nonce'], array($v));
542         $num = $this->connection->affectedRows();
543         $this->connection->commit();
544         return $num;
545     }
546
547     function cleanupAssociations()
548     {
549         $this->connection->query($this->sql['clean_assoc'],
550                                  array(time()));
551         $num = $this->connection->affectedRows();
552         $this->connection->commit();
553         return $num;
554     }
555 }
556
557