[Sommaire] [Imprimer]

   1  <?php
   2  /**
   3   * The Auth_sql class provides a SQL implementation of the Horde
   4   * authentication system.
   5   *
   6   * Required parameters:<pre>
   7   *   'phptype'      The database type (ie. 'pgsql', 'mysql', etc.).</pre>
   8   *
   9   * Optional parameters:<pre>
  10   *   'encryption'             The encryption to use to store the password in
  11   *                            the table (e.g. plain, crypt, md5-hex,
  12   *                            md5-base64, smd5, sha, ssha, aprmd5).
  13   *                            DEFAULT: 'md5-hex'
  14   *   'show_encryption'        Whether or not to prepend the encryption in the
  15   *                            password field.
  16   *                            DEFAULT: 'false'
  17   *   'password_field'         The name of the password field in the auth table.
  18   *                            DEFAULT: 'user_pass'
  19   *   'table'                  The name of the SQL table to use in 'database'.
  20   *                            DEFAULT: 'horde_users'
  21   *   'username_field'         The name of the username field in the auth table.
  22   *                            DEFAULT: 'user_uid'
  23   *   'soft_expiration_field'  The name of the field containing a date after
  24   *                            which the system will request the user change his
  25   *                            or her password.
  26   *                            DEFAULT: none
  27   *   'hard_expiration_field'  The name of the field containing a date after
  28   *                            which the account is no longer valid and the user
  29   *                            will not be able to log in at all.
  30   *                            DEFAULT: none</pre>
  31   *
  32   * Required by some database implementations:<pre>
  33   *   'hostspec'     The hostname of the database server.
  34   *   'protocol'     The communication protocol ('tcp', 'unix', etc.).
  35   *   'database'     The name of the database.
  36   *   'username'     The username with which to connect to the database.
  37   *   'password'     The password associated with 'username'.
  38   *   'options'      Additional options to pass to the database.
  39   *   'port'         The port on which to connect to the database.
  40   *   'tty'          The TTY on which to connect to the database.</pre>
  41   *
  42   * The table structure for the Auth system is in
  43   * scripts/sql/horde_users.sql.
  44   *
  45   * $Horde: framework/Auth/Auth/sql.php,v 1.69.10.19 2006/08/14 02:48:48 chuck Exp $
  46   *
  47   * Copyright 1999-2006 Chuck Hagenbuch <chuck@horde.org>
  48   *
  49   * See the enclosed file COPYING for license information (LGPL). If you
  50   * did not receive this file, see http://www.fsf.org/copyleft/lgpl.html.
  51   *
  52   * @author  Chuck Hagenbuch <chuck@horde.org>
  53   * @since   Horde 1.3
  54   * @package Horde_Auth
  55   */
  56  class Auth_sql extends Auth {
  57  
  58      /**
  59       * An array of capabilities, so that the driver can report which
  60       * operations it supports and which it doesn't.
  61       *
  62       * @var array
  63       */
  64      var $capabilities = array('add'           => true,
  65                                'update'        => true,
  66                                'resetpassword' => true,
  67                                'remove'        => true,
  68                                'list'          => true,
  69                                'transparent'   => false);
  70  
  71      /**
  72       * Handle for the current database connection.
  73       *
  74       * @var DB
  75       */
  76      var $_db;
  77  
  78      /**
  79       * Boolean indicating whether or not we're connected to the SQL server.
  80       *
  81       * @var boolean
  82       */
  83      var $_connected = false;
  84  
  85      /**
  86       * Constructs a new SQL authentication object.
  87       *
  88       * @param array $params  A hash containing connection parameters.
  89       */
  90      function Auth_sql($params = array())
  91      {
  92          $this->_params = $params;
  93      }
  94  
  95      /**
  96       * Find out if a set of login credentials are valid.
  97       *
  98       * @access private
  99       *
 100       * @param string $userId      The userId to check.
 101       * @param array $credentials  The credentials to use.
 102       *
 103       * @return boolean  Whether or not the credentials are valid.
 104       */
 105      function _authenticate($userId, $credentials)
 106      {
 107          /* _connect() will die with Horde::fatal() upon failure. */
 108          $this->_connect();
 109  
 110          /* Build the SQL query. */
 111          $query = sprintf('SELECT * FROM %s WHERE %s = ?',
 112                           $this->_params['table'],
 113                           $this->_params['username_field']);
 114          $values = array($userId);
 115  
 116          Horde::logMessage('SQL Query by Auth_sql::_authenticate(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
 117  
 118          $result = $this->_db->query($query, $values);
 119          if (is_a($result, 'PEAR_Error')) {
 120              $this->_setAuthError(AUTH_REASON_FAILED);
 121              return false;
 122          }
 123  
 124          $row = $result->fetchRow(DB_GETMODE_ASSOC);
 125          if (is_array($row)) {
 126              $result->free();
 127          } else {
 128              $this->_setAuthError(AUTH_REASON_BADLOGIN);
 129              return false;
 130          }
 131  
 132          if (!$this->_comparePasswords($row[$this->_params['password_field']],
 133                                        $credentials['password'])) {
 134              $this->_setAuthError(AUTH_REASON_BADLOGIN);
 135              return false;
 136          }
 137  
 138          $now = time();
 139          if (!empty($this->_params['hard_expiration_field']) &&
 140              !empty($row[$this->_params['hard_expiration_field']]) &&
 141              ($now > $row[$this->_params['hard_expiration_field']])) {
 142              $this->_setAuthError(AUTH_REASON_EXPIRED);
 143              return false;
 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->_authCredentials['changeRequested'] = true;
 150          }
 151  
 152          return true;
 153      }
 154  
 155      /**
 156       * Add a set of authentication credentials.
 157       *
 158       * @param string $userId      The userId to add.
 159       * @param array $credentials  The credentials to add.
 160       *
 161       * @return mixed  True on success or a PEAR_Error object on failure.
 162       */
 163      function addUser($userId, $credentials)
 164      {
 165          $this->_connect();
 166  
 167          /* Build the SQL query. */
 168          $query = sprintf('INSERT INTO %s (%s, %s) VALUES (?, ?)',
 169                           $this->_params['table'],
 170                           $this->_params['username_field'],
 171                           $this->_params['password_field']);
 172          $values = array($userId,
 173                          $this->getCryptedPassword($credentials['password'],
 174                                                    '',
 175                                                    $this->_params['encryption'],
 176                                                    $this->_params['show_encryption']));
 177  
 178          Horde::logMessage('SQL Query by Auth_sql::addUser(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
 179  
 180          $result = $this->_db->query($query, $values);
 181          if (is_a($result, 'PEAR_Error')) {
 182              return $result;
 183          }
 184  
 185          return true;
 186      }
 187  
 188      /**
 189       * Update a set of authentication credentials.
 190       *
 191       * @param string $oldID        The old userId.
 192       * @param string $newID        The new userId.
 193       * @param array  $credentials  The new credentials
 194       *
 195       * @return mixed  True on success or a PEAR_Error object on failure.
 196       */
 197      function updateUser($oldID, $newID, $credentials)
 198      {
 199          /* _connect() will die with Horde::fatal() upon failure. */
 200          $this->_connect();
 201  
 202          /* Build the SQL query. */
 203          $tuple = array();
 204          $tuple[$this->_params['username_field']] = $newID;
 205          $tuple[$this->_params['password_field']] =
 206              $this->getCryptedPassword($credentials['password'],
 207                                        '',
 208                                        $this->_params['encryption'],
 209                                        $this->_params['show_encryption']);
 210  
 211          if (empty($this->_params['soft_expiration_window'])) {
 212              if (!empty($this->_params['soft_expiration_field'])) {
 213                  $tuple[$this->_params['soft_expiration_field']] = null;
 214              }
 215          } else {
 216              $date = time();
 217              $datea = localtime($date, true);
 218              $date = mktime($datea['tm_hour'], $datea['tm_min'],
 219                             $datea['tm_sec'], $datea['tm_mon'] + 1,
 220                             $datea['tm_mday'] + $this->_params['soft_expiration_window'],
 221                             $datea['tm_year']);
 222  
 223              $tuple[$this->_params['soft_expiration_field']] = $date;
 224  
 225              global $notification;
 226              if (!empty($notification)) {
 227                  $notification->push(strftime(_("New password will expire on %x."), $date), 'horde.message');
 228              }
 229  
 230              if (empty($this->_params['hard_expiration_window'])) {
 231                  $tuple[$this->_params['hard_expiration_field']] = null;
 232              } else {
 233                  $datea = localtime($date, true);
 234                  $date = mktime($datea['tm_hour'], $datea['tm_min'],
 235                                 $datea['tm_sec'], $datea['tm_mon'] + 1,
 236                                 $datea['tm_mday'] + $this->_params['soft_expiration_window'],
 237                                 $datea['tm_year']);
 238  
 239                  $tuple[$this->_params['hard_expiration_field']] = $date;
 240              }
 241          }
 242  
 243          require_once 'Horde/SQL.php';
 244          $query = sprintf('UPDATE %s SET %s WHERE %s = ?',
 245                           $this->_params['table'],
 246                           Horde_SQL::updateValues($this->_db, $tuple),
 247                           $this->_params['username_field']);
 248          $values = array($oldID);
 249  
 250          Horde::logMessage('SQL Query by Auth_sql:updateUser(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
 251  
 252          $result = $this->_db->query($query, $values);
 253          if (is_a($result, 'PEAR_Error')) {
 254              Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR);
 255              return $result;
 256          }
 257  
 258          return true;
 259      }
 260  
 261      /**
 262       * Reset a user's password. Used for example when the user does not
 263       * remember the existing password.
 264       *
 265       * @param string $user_id  The user id for which to reset the password.
 266       *
 267       * @return mixed  The new password on success or a PEAR_Error object on
 268       *                failure.
 269       */
 270      function resetPassword($user_id)
 271      {
 272          /* _connect() will die with Horde::fatal() upon failure. */
 273          $this->_connect();
 274  
 275          /* Get a new random password. */
 276          $password = Auth::genRandomPassword();
 277  
 278          /* Build the SQL query. */
 279          $query = sprintf('UPDATE %s SET %s = ? WHERE %s = ?',
 280                           $this->_params['table'],
 281                           $this->_params['password_field'],
 282                           $this->_params['username_field']);
 283          $values = array($this->getCryptedPassword($password,
 284                                                    '',
 285                                                    $this->_params['encryption'],
 286                                                    $this->_params['show_encryption']),
 287                          $user_id);
 288  
 289          Horde::logMessage('SQL Query by Auth_sql::resetPassword(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
 290  
 291          $result = $this->_db->query($query, $values);
 292          if (is_a($result, 'PEAR_Error')) {
 293              return $result;
 294          }
 295  
 296          return $password;
 297      }
 298  
 299      /**
 300       * Delete a set of authentication credentials.
 301       *
 302       * @param string $userId  The userId to delete.
 303       *
 304       * @return boolean        Success or failure.
 305       */
 306      function removeUser($userId)
 307      {
 308          /* _connect() will die with Horde::fatal() upon failure. */
 309          $this->_connect();
 310  
 311          /* Build the SQL query. */
 312          $query = sprintf('DELETE FROM %s WHERE %s = ?',
 313                           $this->_params['table'],
 314                           $this->_params['username_field']);
 315          $values = array($userId);
 316  
 317          Horde::logMessage('SQL Query by Auth_sql::removeUser(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
 318  
 319          $result = $this->_db->query($query, $values);
 320          if (is_a($result, 'PEAR_Error')) {
 321              return $result;
 322          }
 323  
 324          return $this->removeUserData($userId);
 325      }
 326  
 327      /**
 328       * List all users in the system.
 329       *
 330       * @return mixed  The array of userIds, or false on failure/unsupported.
 331       */
 332      function listUsers()
 333      {
 334          /* _connect() will die with Horde::fatal() upon failure. */
 335          $this->_connect();
 336  
 337          /* Build the SQL query. */
 338          $query = sprintf('SELECT %s FROM %s',
 339                           $this->_params['username_field'],
 340                           $this->_params['table']);
 341  
 342          Horde::logMessage('SQL Query by Auth_sql::listUsers(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
 343  
 344          return $this->_db->getCol($query);
 345      }
 346  
 347      /**
 348       * Checks if a userId exists in the sistem.
 349       *
 350       * @return boolean  Whether or not the userId already exists.
 351       */
 352      function exists($userId)
 353      {
 354          /* _connect() will die with Horde::fatal() upon failure. */
 355          $this->_connect();
 356  
 357          /* Build the SQL query. */
 358          $query = sprintf('SELECT %s FROM %s WHERE %s = ?',
 359                           $this->_params['username_field'],
 360                           $this->_params['table'],
 361                           $this->_params['username_field']);
 362          $values = array($userId);
 363  
 364          Horde::logMessage('SQL Query by Auth_sql::exists(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
 365  
 366          return $this->_db->getOne($query, $values);
 367      }
 368  
 369      /**
 370       * Compare an encrypted password to a plaintext string to see if
 371       * they match.
 372       *
 373       * @access private
 374       *
 375       * @param string $encrypted  The crypted password to compare against.
 376       * @param string $plaintext  The plaintext password to verify.
 377       *
 378       * @return boolean  True if matched, false otherwise.
 379       */
 380      function _comparePasswords($encrypted, $plaintext)
 381      {
 382          return $encrypted == $this->getCryptedPassword($plaintext,
 383                                                         $encrypted,
 384                                                         $this->_params['encryption'],
 385                                                         $this->_params['show_encryption']);
 386      }
 387  
 388      /**
 389       * Attempts to open a connection to the SQL server.
 390       *
 391       * @access private
 392       *
 393       * @return mixed  True on success or a PEAR_Error object on failure.
 394       */
 395      function _connect()
 396      {
 397          if ($this->_connected) {
 398              return true;
 399          }
 400  
 401          Horde::assertDriverConfig($this->_params, 'auth', array('phptype'),
 402                                    'authentication SQL');
 403  
 404          if (!isset($this->_params['database'])) {
 405              $this->_params['database'] = '';
 406          }
 407          if (!isset($this->_params['username'])) {
 408              $this->_params['username'] = '';
 409          }
 410          if (!isset($this->_params['password'])) {
 411              $this->_params['password'] = '';
 412          }
 413          if (!isset($this->_params['hostspec'])) {
 414              $this->_params['hostspec'] = '';
 415          }
 416          if (empty($this->_params['encryption'])) {
 417              $this->_params['encryption'] = 'md5-hex';
 418          }
 419          if (!isset($this->_params['show_encryption'])) {
 420              $this->_params['show_encryption'] = false;
 421          }
 422          if (empty($this->_params['table'])) {
 423              $this->_params['table'] = 'horde_users';
 424          }
 425          if (empty($this->_params['username_field'])) {
 426              $this->_params['username_field'] = 'user_uid';
 427          } else {
 428              $this->_params['username_field'] = String::lower($this->_params['username_field']);
 429          }
 430          if (empty($this->_params['password_field'])) {
 431              $this->_params['password_field'] = 'user_pass';
 432          } else {
 433              $this->_params['password_field'] = String::lower($this->_params['password_field']);
 434          }
 435  
 436          /* Connect to the SQL server using the supplied parameters. */
 437          include_once 'DB.php';
 438          $this->_db = &DB::connect($this->_params,
 439                                    array('persistent' => !empty($this->_params['persistent'])));
 440          if (is_a($this->_db, 'PEAR_Error')) {
 441              Horde::fatal(_("Unable to connect to SQL server."), __FILE__, __LINE__);
 442          }
 443  
 444          // Set DB portability options.
 445          switch ($this->_db->phptype) {
 446          case 'mssql':
 447              $this->_db->setOption('portability', DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_ERRORS | DB_PORTABILITY_RTRIM);
 448              break;
 449          default:
 450              $this->_db->setOption('portability', DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_ERRORS);
 451          }
 452  
 453          $this->_connected = true;
 454          return true;
 455      }
 456  
 457  }