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:
558: