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