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 */
19require_once 'Auth/OpenID/Interface.php';
20require_once 'Auth/OpenID/Nonce.php';
21
22/**
23 * @access private
24 */
25require_once 'Auth/OpenID.php';
26
27/**
28 * @access private
29 */
30require_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 */
57class 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
558