1: <?php
  2: /**
  3:  * The Horde_Auth_Sql class provides a SQL implementation of the Horde
  4:  * authentication system.
  5:  *
  6:  * The table structure for the Auth system is in
  7:  * horde/scripts/sql/horde_users.sql.
  8:  *
  9:  * Copyright 1999-2012 Horde LLC (http://www.horde.org/)
 10:  *
 11:  * See the enclosed file COPYING for license information (LGPL). If you did
 12:  * not receive this file, http://www.horde.org/licenses/lgpl21
 13:  *
 14:  * @author   Chuck Hagenbuch <chuck@horde.org>
 15:  * @category Horde
 16:  * @license http://www.horde.org/licenses/lgpl21 LGPL-2.1
 17:  * @package  Auth
 18:  */
 19: class Horde_Auth_Sql extends Horde_Auth_Base
 20: {
 21:     /**
 22:      * An array of capabilities, so that the driver can report which
 23:      * operations it supports and which it doesn't.
 24:      *
 25:      * @var array
 26:      */
 27:     protected $_capabilities = array(
 28:         'add'           => true,
 29:         'list'          => true,
 30:         'remove'        => true,
 31:         'resetpassword' => true,
 32:         'update'        => true,
 33:         'authenticate'  => true,
 34:     );
 35: 
 36:     /**
 37:      * Handle for the current database connection.
 38:      *
 39:      * @var Horde_Db_Adapter
 40:      */
 41:     protected $_db;
 42: 
 43:     /**
 44:      * Constructor
 45:      *
 46:      * @param array $params  Parameters:
 47:      * 'db' - (Horde_Db_Adapter) [REQUIRED] Database object.
 48:      * <pre>
 49:      * 'encryption' - (string) The encryption to use to store the password in
 50:      *                the table (e.g. plain, crypt, md5-hex, md5-base64, smd5,
 51:      *                sha, ssha, aprmd5).
 52:      *                DEFAULT: 'md5-hex'
 53:      * 'hard_expiration_field' - (string) The name of the field containing a
 54:      *                           date after which the account is no longer
 55:      *                           valid and the user will not be able to log in
 56:      *                           at all.
 57:      *                           DEFAULT: none
 58:      * 'password_field' - (string) The name of the password field in the auth
 59:      *                    table.
 60:      *                    DEFAULT: 'user_pass'
 61:      * 'show_encryption' - (boolean) Whether or not to prepend the encryption
 62:      *                     in the password field.
 63:      *                     DEFAULT: false
 64:      * 'soft_expiration_field' - (string) The name of the field containing a
 65:      *                           date after which the system will request the
 66:      *                           user change his or her password.
 67:      *                           DEFAULT: none
 68:      * 'table' - (string) The name of the SQL table to use in 'database'.
 69:      *           DEFAULT: 'horde_users'
 70:      * 'username_field' - (string) The name of the username field in the auth
 71:      *                    table.
 72:      *                    DEFAULT: 'user_uid'
 73:      * </pre>
 74:      *
 75:      * @throws InvalidArgumentException
 76:      */
 77:     public function __construct(array $params = array())
 78:     {
 79:         if (!isset($params['db'])) {
 80:             throw new InvalidArgumentException('Missing db parameter.');
 81:         }
 82:         $this->_db = $params['db'];
 83:         unset($params['db']);
 84: 
 85:         $params = array_merge(array(
 86:             'encryption' => 'md5-hex',
 87:             'password_field' => 'user_pass',
 88:             'show_encryption' => false,
 89:             'table' => 'horde_users',
 90:             'username_field' => 'user_uid',
 91:             'soft_expiration_field' => null,
 92:             'soft_expiration_window' => null,
 93:             'hard_expiration_field' => null,
 94:             'hard_expiration_window' => null
 95:         ), $params);
 96: 
 97:         parent::__construct($params);
 98: 
 99:         /* Only allow limits when there is a storage configured */
100:         if ((empty($params['soft_expiration_field'])) &&
101:             ($params['soft_expiration_window'] > 0)) {
102:             throw new InvalidArgumentException('You cannot set [soft_expiration_window] without [soft_expiration_field].');
103:         }
104: 
105:         if (($params['hard_expiration_field'] == '') &&
106:             ($params['hard_expiration_window'] > 0)) {
107:             throw new InvalidArgumentException('You cannot set [hard_expiration_window] without [hard_expiration_field].');
108:         }
109: 
110:     }
111: 
112:     /**
113:      * Find out if a set of login credentials are valid.
114:      *
115:      * @param string $userId      The userId to check.
116:      * @param array $credentials  The credentials to use.
117:      *
118:      * @throws Horde_Auth_Exception
119:      */
120:     protected function _authenticate($userId, $credentials)
121:     {
122:         /* Build the SQL query. */
123:         $query = sprintf('SELECT * FROM %s WHERE %s = ?',
124:                          $this->_params['table'],
125:                          $this->_params['username_field']);
126:         $values = array($userId);
127: 
128:         try {
129:             $row = $this->_db->selectOne($query, $values);
130:         } catch (Horde_Db_Exception $e) {
131:             throw new Horde_Auth_Exception('', Horde_Auth::REASON_FAILED);
132:         }
133: 
134:         if (!$row ||
135:             !$this->_comparePasswords($row[$this->_params['password_field']], $credentials['password'])) {
136:             throw new Horde_Auth_Exception('', Horde_Auth::REASON_BADLOGIN);
137:         }
138: 
139:         $now = time();
140:         if (!empty($this->_params['hard_expiration_field']) &&
141:             !empty($row[$this->_params['hard_expiration_field']]) &&
142:             ($now > $row[$this->_params['hard_expiration_field']])) {
143:             throw new Horde_Auth_Exception('', Horde_Auth::REASON_EXPIRED);
144:         }
145: 
146:         if (!empty($this->_params['soft_expiration_field']) &&
147:             !empty($row[$this->_params['soft_expiration_field']]) &&
148:             ($now > $row[$this->_params['soft_expiration_field']])) {
149:             $this->setCredential('change', true);
150:             $this->setCredential('expire', $date);
151:         }
152:     }
153: 
154:     /**
155:      * Add a set of authentication credentials.
156:      *
157:      * @param string $userId      The userId to add.
158:      * @param array $credentials  The credentials to add.
159:      *
160:      * @throws Horde_Auth_Exception
161:      */
162:     public function addUser($userId, $credentials)
163:     {
164:         /* Build the SQL query. */
165:         $query = sprintf('INSERT INTO %s (%s, %s',
166:                          $this->_params['table'],
167:                          $this->_params['username_field'],
168:                          $this->_params['password_field']);
169:         $query_values_part = ' VALUES (?, ?';
170:         $values = array($userId,
171:                         Horde_Auth::getCryptedPassword($credentials['password'],
172:                                                   '',
173:                                                   $this->_params['encryption'],
174:                                                   $this->_params['show_encryption']));
175:         if (!empty($this->_params['soft_expiration_field'])) {
176:             $query .= sprintf(', %s', $this->_params['soft_expiration_field']);
177:             $query_values_part .= ', ?';
178:             $values[] = $this->_calc_expiration('soft');
179:         }
180:         if (!empty($this->_params['hard_expiration_field'])) {
181:             $query .= sprintf(', %s', $this->_params['hard_expiration_field']);
182:             $query_values_part .= ', ?';
183:             $values[] = $this->_calc_expiration('hard');
184:         }
185:         $query .= ')' . $query_values_part . ')';
186: 
187:         try {
188:             $this->_db->insert($query, $values);
189:         } catch (Horde_Db_Exception $e) {
190:             throw new Horde_Auth_Exception($e);
191:         }
192:     }
193: 
194:     /**
195:      * Update a set of authentication credentials.
196:      *
197:      * @param string $oldID       The old userId.
198:      * @param string $newID       The new userId.
199:      * @param array $credentials  The new credentials
200:      *
201:      * @throws Horde_Auth_Exception
202:      */
203:     public function updateUser($oldID, $newID, $credentials)
204:     {
205:         $query = sprintf('UPDATE %s SET ', $this->_params['table']);
206:         $values = array();
207: 
208:         /* Build the SQL query. */
209:         $query .= $this->_params['username_field'] . ' = ?';
210:         $values[] = $newID;
211: 
212:         $query .= ', ' . $this->_params['password_field'] . ' = ?';
213:         $values[] = Horde_Auth::getCryptedPassword($credentials['password'], '', $this->_params['encryption'], $this->_params['show_encryption']);
214:         if (!empty($this->_params['soft_expiration_field'])) {
215:                 $query .= ', ' . $this->_params['soft_expiration_field'] . ' = ?';
216:                 $values[] =  $this->_calc_expiration('soft');
217:         }
218:         if (!empty($this->_params['hard_expiration_field'])) {
219:                 $query .= ', ' . $this->_params['hard_expiration_field'] . ' = ?';
220:                 $values[] =  $this->_calc_expiration('hard');
221:         }
222: 
223:         $query .= sprintf(' WHERE %s = ?', $this->_params['username_field']);
224:         $values[] = $oldID;
225: 
226:         try {
227:             $this->_db->update($query, $values);
228:         } catch (Horde_Db_Exception $e) {
229:             throw new Horde_Auth_Exception($e);
230:         }
231:     }
232: 
233:     /**
234:      * Reset a user's password. Used for example when the user does not
235:      * remember the existing password.
236:      *
237:      * @param string $userId  The user id for which to reset the password.
238:      *
239:      * @return string  The new password on success.
240:      * @throws Horde_Auth_Exception
241:      */
242:     public function resetPassword($userId)
243:     {
244:         /* Get a new random password. */
245:         $password = Horde_Auth::genRandomPassword();
246: 
247:         /* Build the SQL query. */
248:         $query = sprintf('UPDATE %s SET %s = ?',
249:                          $this->_params['table'],
250:                          $this->_params['password_field']);
251:         $values = array(Horde_Auth::getCryptedPassword($password,
252:                                                   '',
253:                                                   $this->_params['encryption'],
254:                                                   $this->_params['show_encryption']));
255:         if (!empty($this->_params['soft_expiration_field'])) {
256:                 $query .= ', ' . $this->_params['soft_expiration_field'] . ' = ?';
257:                 $values[] =  $this->_calc_expiration('soft');
258:         }
259:         if (!empty($this->_params['hard_expiration_field'])) {
260:                 $query .= ', ' . $this->_params['hard_expiration_field'] . ' = ?';
261:                 $values[] =  $this->_calc_expiration('hard');
262:         }
263:         $query .= sprintf(' WHERE %s = ?', $this->_params['username_field']);
264:         $values[] = $userId;
265:         try {
266:             $this->_db->update($query, $values);
267:         } catch (Horde_Db_Exception $e) {
268:             throw new Horde_Auth_Exception($e);
269:         }
270: 
271:         return $password;
272:     }
273: 
274:     /**
275:      * Delete a set of authentication credentials.
276:      *
277:      * @param string $userId  The userId to delete.
278:      *
279:      * @throws Horde_Auth_Exception
280:      */
281:     public function removeUser($userId)
282:     {
283:         /* Build the SQL query. */
284:         $query = sprintf('DELETE FROM %s WHERE %s = ?',
285:                          $this->_params['table'],
286:                          $this->_params['username_field']);
287:         $values = array($userId);
288: 
289:         try {
290:             $this->_db->delete($query, $values);
291:         } catch (Horde_Db_Exception $e) {
292:             throw new Horde_Auth_Exception($e);
293:         }
294:     }
295: 
296:     /**
297:      * List all users in the system.
298:      *
299:      * @return array  The array of userIds.
300:      * @throws Horde_Auth_Exception
301:      */
302:     public function listUsers($sort = false)
303:     {
304:         /* Build the SQL query. */
305:         $query = sprintf('SELECT %s FROM %s',
306:                          $this->_params['username_field'],
307:                          $this->_params['table']);
308:         if ($sort) {
309:             $query .= sprintf(' ORDER BY %s ASC',
310:                                $this->_params['username_field']);
311:         }
312:         try {
313:             return $this->_db->selectValues($query);
314:         } catch (Horde_Db_Exception $e) {
315:             throw new Horde_Auth_Exception($e);
316:         }
317:     }
318: 
319:     /**
320:      * Checks if a userId exists in the system.
321:      *
322:      * @return boolean  Whether or not the userId already exists.
323:      */
324:     public function exists($userId)
325:     {
326:         /* Build the SQL query. */
327:         $query = sprintf('SELECT 1 FROM %s WHERE %s = ?',
328:                          $this->_params['table'],
329:                          $this->_params['username_field']);
330:         $values = array($userId);
331: 
332:         try {
333:             return (bool)$this->_db->selectValue($query, $values);
334:         } catch (Horde_Db_Exception $e) {
335:             return false;
336:         }
337:     }
338: 
339:     /**
340:      * Compare an encrypted password to a plaintext string to see if
341:      * they match.
342:      *
343:      * @param string $encrypted  The crypted password to compare against.
344:      * @param string $plaintext  The plaintext password to verify.
345:      *
346:      * @return boolean  True if matched, false otherwise.
347:      */
348:     protected function _comparePasswords($encrypted, $plaintext)
349:     {
350:         return $encrypted == Horde_Auth::getCryptedPassword($plaintext,
351:                                                        $encrypted,
352:                                                        $this->_params['encryption'],
353:                                                        $this->_params['show_encryption']);
354:     }
355: 
356:     /**
357:      * Calculate a timestamp and return it along with the field name
358:      *
359:      * @param string $type The timestamp parameter.
360:      *
361:      * @return integer 'timestamp' intended field value or null
362:      */
363:     private function _calc_expiration($type)
364:     {
365:         if (empty($this->_params[$type . '_expiration_window'])) {
366:             return null;
367:         } else {
368:             $now = new Horde_Date(time());
369:             return $now->add(array('mday' => $this->_params[$type.'_expiration_window']))->timestamp();
370:         }
371:     }
372: }
373: