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