[Sommaire] [Imprimer]

   1  <?php
   2  /**
   3   * MySQLP authentication backend
   4   *
   5   * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
   6   * @author     Andreas Gohr <andi@splitbrain.org>
   7   * @author     Chris Smith <chris@jalakai.co.uk>
   8   * @author     Matthias Grimm <matthias.grimmm@sourceforge.net>
   9  */
  10  
  11  define('DOKU_AUTH', dirname(__FILE__));
  12  require_once (DOKU_AUTH.'/basic.class.php');
  13  
  14  class auth_mysql extends auth_basic {
  15  
  16      var $dbcon        = 0;
  17      var $dbver        = 0;    // database version
  18      var $dbrev        = 0;    // database revision
  19      var $dbsub        = 0;    // database subrevision
  20      var $cnf          = null;
  21      var $defaultgroup = "";
  22  
  23      /**
  24       * Constructor
  25       *
  26       * checks if the mysql interface is available, otherwise it will
  27       * set the variable $success of the basis class to FALSE
  28       *
  29       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
  30       */
  31      function auth_mysql() {
  32        global $conf;
  33        $this->cnf          = $conf['auth']['mysql'];
  34  
  35        if (method_exists($this, 'auth_basic'))
  36          parent::auth_basic();
  37  
  38        if(!function_exists('mysql_connect')) {
  39          if ($this->cnf['debug'])
  40            msg("MySQL err: PHP MySQL extension not found.",-1,__LINE__,__FILE__);
  41          $this->success = false;
  42          return;
  43        }
  44  
  45        $this->defaultgroup = $conf['defaultgroup'];
  46  
  47        // set capabilities based upon config strings set
  48        if (empty($this->cnf['server']) || empty($this->cnf['user']) ||
  49            empty($this->cnf['password']) || empty($this->cnf['database'])){
  50          if ($this->cnf['debug'])
  51            msg("MySQL err: insufficient configuration.",-1,__LINE__,__FILE__);
  52          $this->success = false;
  53          return;
  54        }
  55  
  56        $this->cando['addUser']      = $this->_chkcnf(array('getUserInfo',
  57                                                            'getGroups',
  58                                                            'addUser',
  59                                                            'getUserID',
  60                                                            'getGroupID',
  61                                                            'addGroup',
  62                                                            'addUserGroup'),true);
  63        $this->cando['delUser']      = $this->_chkcnf(array('getUserID',
  64                                                            'delUser',
  65                                                            'delUserRefs'),true);
  66        $this->cando['modLogin']     = $this->_chkcnf(array('getUserID',
  67                                                            'updateUser',
  68                                                            'UpdateTarget'),true);
  69        $this->cando['modPass']      = $this->cando['modLogin'];
  70        $this->cando['modName']      = $this->cando['modLogin'];
  71        $this->cando['modMail']      = $this->cando['modLogin'];
  72        $this->cando['modGroups']    = $this->_chkcnf(array('getUserID',
  73                                                            'getGroups',
  74                                                            'getGroupID',
  75                                                            'addGroup',
  76                                                            'addUserGroup',
  77                                                            'delGroup',
  78                                                            'getGroupID',
  79                                                            'delUserGroup'),true);
  80        /* getGroups is not yet supported
  81        $this->cando['getGroups']    = $this->_chkcnf(array('getGroups',
  82                                                            'getGroupID'),false); */
  83        $this->cando['getUsers']     = $this->_chkcnf(array('getUsers',
  84                                                            'getUserInfo',
  85                                                            'getGroups'),false);
  86        $this->cando['getUserCount'] = $this->_chkcnf(array('getUsers'),false);
  87      }
  88  
  89      /**
  90       * Check if the given config strings are set
  91       *
  92       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
  93       * @return  bool
  94       */
  95      function _chkcnf($keys, $wop=false){
  96        foreach ($keys as $key){
  97          if (empty($this->cnf[$key])) return false;
  98        }
  99  
 100        /* write operation and lock array filled with tables names? */
 101        if ($wop && (!is_array($this->cnf['TablesToLock']) ||
 102                     !count($this->cnf['TablesToLock']))){
 103          return false;
 104        }
 105  
 106        return true;
 107      }
 108  
 109      /**
 110       * Checks if the given user exists and the given plaintext password
 111       * is correct. Furtheron it might be checked wether the user is
 112       * member of the right group
 113       *
 114       * Depending on which SQL string is defined in the config, password
 115       * checking is done here (getpass) or by the database (passcheck)
 116       *
 117       * @param  $user  user who would like access
 118       * @param  $pass  user's clear text password to check
 119       * @return bool
 120       *
 121       * @author  Andreas Gohr <andi@splitbrain.org>
 122       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 123       */
 124      function checkPass($user,$pass){
 125        $rc  = false;
 126  
 127        if($this->_openDB()) {
 128          $sql    = str_replace('%{user}',$this->_escape($user),$this->cnf['checkPass']);
 129          $sql    = str_replace('%{pass}',$this->_escape($pass),$sql);
 130          $sql    = str_replace('%{dgroup}',$this->_escape($this->defaultgroup),$sql);
 131          $result = $this->_queryDB($sql);
 132  
 133          if($result !== false && count($result) == 1) {
 134            if($this->cnf['forwardClearPass'] == 1)
 135              $rc = true;
 136            else
 137              $rc = auth_verifyPassword($pass,$result[0]['pass']);
 138          }
 139          $this->_closeDB();
 140        }
 141        return $rc;
 142      }
 143  
 144      /**
 145       * [public function]
 146       *
 147       * Returns info about the given user needs to contain
 148       * at least these fields:
 149       *   name  string  full name of the user
 150       *   mail  string  email addres of the user
 151       *   grps  array   list of groups the user is in
 152       *
 153       * @param $user   user's nick to get data for
 154       *
 155       * @author  Andreas Gohr <andi@splitbrain.org>
 156       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 157       */
 158      function getUserData($user){
 159        if($this->_openDB()) {
 160          $this->_lockTables("READ");
 161          $info = $this->_getUserInfo($user);
 162          $this->_unlockTables();
 163          $this->_closeDB();
 164        } else
 165          $info = false;
 166        return $info;
 167      }
 168  
 169      /**
 170       * [public function]
 171       *
 172       * Create a new User. Returns false if the user already exists,
 173       * null when an error occured and true if everything went well.
 174       *
 175       * The new user will be added to the default group by this
 176       * function if grps are not specified (default behaviour).
 177       *
 178       * @param $user  nick of the user
 179       * @param $pwd   clear text password
 180       * @param $name  full name of the user
 181       * @param $mail  email address
 182       * @param $grps  array of groups the user should become member of
 183       *
 184       * @author  Andreas Gohr <andi@splitbrain.org>
 185       * @author  Chris Smith <chris@jalakai.co.uk>
 186       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 187       */
 188      function createUser($user,$pwd,$name,$mail,$grps=null){
 189        if($this->_openDB()) {
 190          if (($info = $this->_getUserInfo($user)) !== false)
 191            return false;  // user already exists
 192  
 193          // set defaultgroup if no groups were given
 194          if ($grps == null)
 195            $grps = array($this->defaultgroup);
 196  
 197          $this->_lockTables("WRITE");
 198          $pwd = $this->cnf['forwardClearPass'] ? $pwd : auth_cryptPassword($pwd);
 199          $rc = $this->_addUser($user,$pwd,$name,$mail,$grps);
 200          $this->_unlockTables();
 201          $this->_closeDB();
 202          if ($rc) return true;
 203        }
 204        return null;  // return error
 205      }
 206  
 207      /**
 208       * Modify user data [public function]
 209       *
 210       * An existing user dataset will be modified. Changes are given in an array.
 211       *
 212       * The dataset update will be rejected if the user name should be changed
 213       * to an already existing one.
 214       *
 215       * The password must be provides unencrypted. Pasword cryption is done
 216       * automatically if configured.
 217       *
 218       * If one or more groups could't be updated, an error would be set. In
 219       * this case the dataset might already be changed and we can't rollback
 220       * the changes. Transactions would be really usefull here.
 221       *
 222       * modifyUser() may be called without SQL statements defined that are
 223       * needed to change group membership (for example if only the user profile
 224       * should be modified). In this case we asure that we don't touch groups
 225       * even $changes['grps'] is set by mistake.
 226       *
 227       * @param   $user     nick of the user to be changed
 228       * @param   $changes  array of field/value pairs to be changed (password
 229       *                    will be clear text)
 230       * @return  bool      true on success, false on error
 231       *
 232       * @author  Chris Smith <chris@jalakai.co.uk>
 233       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 234       */
 235      function modifyUser($user, $changes) {
 236        $rc = false;
 237  
 238        if (!is_array($changes) || !count($changes))
 239          return true;  // nothing to change
 240  
 241        if($this->_openDB()) {
 242          $this->_lockTables("WRITE");
 243  
 244          if (($uid = $this->_getUserID($user))) {
 245            $rc = $this->_updateUserInfo($changes, $uid);
 246  
 247            if ($rc && isset($changes['grps']) && $this->cando['modGroups']) {
 248              $groups = $this->_getGroups($user);
 249              $grpadd = array_diff($changes['grps'], $groups);
 250              $grpdel = array_diff($groups, $changes['grps']);
 251  
 252              foreach($grpadd as $group)
 253                if (($this->_addUserToGroup($uid, $group, 1)) == false)
 254                  $rc = false;
 255  
 256              foreach($grpdel as $group)
 257                if (($this->_delUserFromGroup($uid, $group)) == false)
 258                  $rc = false;
 259            }
 260          }
 261  
 262          $this->_unlockTables();
 263          $this->_closeDB();
 264        }
 265        return $rc;
 266      }
 267  
 268      /**
 269       * [public function]
 270       *
 271       * Remove one or more users from the list of registered users
 272       *
 273       * @param   array  $users   array of users to be deleted
 274       * @return  int             the number of users deleted
 275       *
 276       * @author  Christopher Smith <chris@jalakai.co.uk>
 277       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 278       */
 279      function deleteUsers($users) {
 280        $count = 0;
 281  
 282        if($this->_openDB()) {
 283          if (is_array($users) && count($users)) {
 284            $this->_lockTables("WRITE");
 285            foreach ($users as $user) {
 286              if ($this->_delUser($user))
 287                $count++;
 288            }
 289            $this->_unlockTables();
 290          }
 291          $this->_closeDB();
 292        }
 293        return $count;
 294      }
 295  
 296      /**
 297       * [public function]
 298       *
 299       * Counts users which meet certain $filter criteria.
 300       *
 301       * @param  array  $filter  filter criteria in item/pattern pairs
 302       * @return count of found users.
 303       *
 304       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 305       */
 306      function getUserCount($filter=array()) {
 307        $rc = 0;
 308  
 309        if($this->_openDB()) {
 310          $sql = $this->_createSQLFilter($this->cnf['getUsers'], $filter);
 311  
 312          if ($this->dbver >= 4) {
 313            $sql = substr($sql, 6);  /* remove 'SELECT' or 'select' */
 314            $sql = "SELECT SQL_CALC_FOUND_ROWS".$sql." LIMIT 1";
 315            $this->_queryDB($sql);
 316            $result = $this->_queryDB("SELECT FOUND_ROWS()");
 317            $rc = $result[0]['FOUND_ROWS()'];
 318          } else if (($result = $this->_queryDB($sql)))
 319            $rc = count($result);
 320  
 321          $this->_closeDB();
 322        }
 323        return $rc;
 324      }
 325  
 326      /**
 327       * Bulk retrieval of user data. [public function]
 328       *
 329       * @param   first     index of first user to be returned
 330       * @param   limit     max number of users to be returned
 331       * @param   filter    array of field/pattern pairs
 332       * @return  array of userinfo (refer getUserData for internal userinfo details)
 333       *
 334       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 335       */
 336      function retrieveUsers($first=0,$limit=10,$filter=array()) {
 337        $out   = array();
 338  
 339        if($this->_openDB()) {
 340          $this->_lockTables("READ");
 341          $sql  = $this->_createSQLFilter($this->cnf['getUsers'], $filter);
 342          $sql .= " ".$this->cnf['SortOrder']." LIMIT $first, $limit";
 343          $result = $this->_queryDB($sql);
 344  
 345          if (!empty($result)) {
 346            foreach ($result as $user)
 347              if (($info = $this->_getUserInfo($user['user'])))
 348                $out[$user['user']] = $info;
 349          }
 350  
 351          $this->_unlockTables();
 352          $this->_closeDB();
 353        }
 354        return $out;
 355      }
 356  
 357      /**
 358       * Give user membership of a group [public function]
 359       *
 360       * @param   $user
 361       * @param   $group
 362       * @return  bool    true on success, false on error
 363       *
 364       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 365       */
 366      function joinGroup($user, $group) {
 367        $rc = false;
 368  
 369        if ($this->_openDB()) {
 370          $this->_lockTables("WRITE");
 371          $uid = $this->_getUserID($user);
 372          $rc  = $this->_addUserToGroup($uid, $group);
 373          $this->_unlockTables();
 374          $this->_closeDB();
 375        }
 376        return $rc;
 377      }
 378  
 379      /**
 380       * Remove user from a group [public function]
 381       *
 382       * @param   $user    user that leaves a group
 383       * @param   $group   group to leave
 384       * @return  bool
 385       *
 386       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 387       */
 388      function leaveGroup($user, $group) {
 389        $rc = false;
 390  
 391        if ($this->_openDB()) {
 392          $this->_lockTables("WRITE");
 393          $uid = $this->_getUserID($user);
 394          $rc  = $this->_delUserFromGroup($uid, $group);
 395          $this->_unlockTables();
 396          $this->_closeDB();
 397        }
 398        return $rc;
 399      }
 400  
 401      /**
 402       * Adds a user to a group.
 403       *
 404       * If $force is set to '1' non existing groups would be created.
 405       *
 406       * The database connection must already be established. Otherwise
 407       * this function does nothing and returns 'false'. It is strongly
 408       * recommended to call this function only after all participating
 409       * tables (group and usergroup) have been locked.
 410       *
 411       * @param   $uid     user id to add to a group
 412       * @param   $group   name of the group
 413       * @param   $force   '1' create missing groups
 414       * @return  bool     'true' on success, 'false' on error
 415       *
 416       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 417       */
 418      function _addUserToGroup($uid, $group, $force=0) {
 419        $newgroup = 0;
 420  
 421        if (($this->dbcon) && ($uid)) {
 422          $gid = $this->_getGroupID($group);
 423          if (!$gid) {
 424            if ($force) {  // create missing groups
 425              $sql = str_replace('%{group}',$this->_escape($group),$this->cnf['addGroup']);
 426              $gid = $this->_modifyDB($sql);
 427              $newgroup = 1;  // group newly created
 428            }
 429            if (!$gid) return false; // group didn't exist and can't be created
 430          }
 431  
 432          $sql = str_replace('%{uid}',  $this->_escape($uid),$this->cnf['addUserGroup']);
 433          $sql = str_replace('%{user}', $this->_escape($user),$sql);
 434          $sql = str_replace('%{gid}',  $this->_escape($gid),$sql);
 435          $sql = str_replace('%{group}',$this->_escape($group),$sql);
 436          if ($this->_modifyDB($sql) !== false) return true;
 437  
 438          if ($newgroup) { // remove previously created group on error
 439            $sql = str_replace('%{gid}',  $this->_escape($gid),$this->cnf['delGroup']);
 440            $sql = str_replace('%{group}',$this->_escape($group),$sql);
 441            $this->_modifyDB($sql);
 442          }
 443        }
 444        return false;
 445      }
 446  
 447      /**
 448       * Remove user from a group
 449       *
 450       * @param   $uid     user id that leaves a group
 451       * @param   $group   group to leave
 452       * @return  bool     true on success, false on error
 453       *
 454       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 455       */
 456      function _delUserFromGroup($uid, $group) {
 457        $rc = false;
 458  
 459        if (($this->dbcon) && ($uid)) {
 460          $gid = $this->_getGroupID($group);
 461          if ($gid) {
 462            $sql = str_replace('%{uid}',  $this->_escape($uid),$this->cnf['delUserGroup']);
 463            $sql = str_replace('%{user}', $this->_escape($user),$sql);
 464            $sql = str_replace('%{gid}',  $this->_escape($gid),$sql);
 465            $sql = str_replace('%{group}',$this->_escape($group),$sql);
 466            $rc  = $this->_modifyDB($sql) == 0 ? true : false;
 467          }
 468        }
 469        return $rc;
 470      }
 471  
 472      /**
 473       * Retrieves a list of groups the user is a member off.
 474       *
 475       * The database connection must already be established
 476       * for this function to work. Otherwise it will return
 477       * 'false'.
 478       *
 479       * @param  $user  user whose groups should be listed
 480       * @return bool   false on error
 481       * @return array  array containing all groups on success
 482       *
 483       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 484       */
 485      function _getGroups($user) {
 486        $groups = array();
 487  
 488        if($this->dbcon) {
 489          $sql = str_replace('%{user}',$this->_escape($user),$this->cnf['getGroups']);
 490          $result = $this->_queryDB($sql);
 491  
 492          if(count($result)) {
 493            foreach($result as $row)
 494              $groups[] = $row['group'];
 495          }
 496          return $groups;
 497        }
 498        return false;
 499      }
 500  
 501      /**
 502       * Retrieves the user id of a given user name
 503       *
 504       * The database connection must already be established
 505       * for this function to work. Otherwise it will return
 506       * 'false'.
 507       *
 508       * @param  $user   user whose id is desired
 509       * @return user id
 510       *
 511       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 512       */
 513      function _getUserID($user) {
 514        if($this->dbcon) {
 515          $sql = str_replace('%{user}',$this->_escape($user),$this->cnf['getUserID']);
 516          $result = $this->_queryDB($sql);
 517          return $result === false ? false : $result[0]['id'];
 518        }
 519        return false;
 520      }
 521  
 522      /**
 523       * Adds a new User to the database.
 524       *
 525       * The database connection must already be established
 526       * for this function to work. Otherwise it will return
 527       * 'false'.
 528       *
 529       * @param  $user  login of the user
 530       * @param  $pwd   encrypted password
 531       * @param  $name  full name of the user
 532       * @param  $mail  email address
 533       * @param  $grps  array of groups the user should become member of
 534       * @return bool
 535       *
 536       * @author  Andreas Gohr <andi@splitbrain.org>
 537       * @author  Chris Smith <chris@jalakai.co.uk>
 538       * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 539       */
 540      function _addUser($user,$pwd,$name,$mail,$grps){
 541        if($this->dbcon && is_array($grps)) {
 542          $sql = str_replace('%{user}', $this->_escape($user),$this->cnf['addUser']);
 543          $sql = str_replace('%{pass}', $this->_escape($pwd),$sql);
 544          $sql = str_replace('%{name}', $this->_escape($name),$sql);
 545          $sql = str_replace('%{email}',$this->_escape($mail),$sql);
 546          $uid = $this->_modifyDB($sql);
 547  
 548          if ($uid) {
 549            foreach($grps as $group) {
 550              $gid = $this->_addUserToGroup($uid, $group, 1);
 551              if ($gid === false) break;
 552            }
 553  
 554            if ($gid) return true;
 555            else {
 556              /* remove the new user and all group relations if a group can't
 557               * be assigned. Newly created groups will remain in the database
 558               * and won't be removed. This might create orphaned groups but
 559               * is not a big issue so we ignore this problem here.
 560               */
 561              $this->_delUser($user);
 562              if ($this->cnf['debug'])
 563                msg ("MySQL err: Adding user '$user' to group '$group' failed.",-1,__LINE__,__FILE__);
 564            }
 565          }
 566        }
 567        return false;
 568      }
 569  
 570      /**
 571       * Deletes a given user and all his group references.
 572       *
 573       * The database connection must already be established
 574       * for this function to work. Otherwise it will return
 575       * 'false'.
 576       *
 577       * @param  $user   user whose id is desired
 578       * @return bool
 579       *
 580       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 581       */
 582      function _delUser($user) {
 583        if($this->dbcon) {
 584          $uid = $this->_getUserID($user);
 585          if ($uid) {
 586            $sql = str_replace('%{uid}',$this->_escape($uid),$this->cnf['delUserRefs']);
 587            $this->_modifyDB($sql);
 588            $sql = str_replace('%{uid}',$this->_escape($uid),$this->cnf['delUser']);
 589            $sql = str_replace('%{user}',  $this->_escape($user),$sql);
 590            $this->_modifyDB($sql);
 591            return true;
 592          }
 593        }
 594        return false;
 595      }
 596  
 597      /**
 598       * getUserInfo
 599       *
 600       * Gets the data for a specific user The database connection
 601       * must already be established for this function to work.
 602       * Otherwise it will return 'false'.
 603       *
 604       * @param  $user  user's nick to get data for
 605       * @return bool   false on error
 606       * @return array  user info on success
 607       *
 608       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 609       */
 610      function _getUserInfo($user){
 611        $sql = str_replace('%{user}',$this->_escape($user),$this->cnf['getUserInfo']);
 612        $result = $this->_queryDB($sql);
 613        if(count($result)) {
 614          $info = $result[0];
 615          $info['grps'] = $this->_getGroups($user);
 616          return $info;
 617        }
 618        return false;
 619      }
 620  
 621      /**
 622       * Updates the user info in the database
 623       *
 624       * Update a user data structure in the database according changes
 625       * given in an array. The user name can only be changes if it didn't
 626       * exists already. If the new user name exists the update procedure
 627       * will be aborted. The database keeps unchanged.
 628       *
 629       * The database connection has already to be established for this
 630       * function to work. Otherwise it will return 'false'.
 631       *
 632       * The password will be crypted if necessary.
 633       *
 634       * @param  $changes  array of items to change as pairs of item and value
 635       * @param  $uid      user id of dataset to change, must be unique in DB
 636       * @return true on success or false on error
 637       *
 638       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 639       */
 640      function _updateUserInfo($changes, $uid) {
 641        $sql  = $this->cnf['updateUser']." ";
 642        $cnt = 0;
 643        $err = 0;
 644  
 645        if($this->dbcon) {
 646          foreach ($changes as $item => $value) {
 647            if ($item == 'user') {
 648              if (($this->_getUserID($changes['user']))) {
 649                $err = 1; /* new username already exists */
 650                break;    /* abort update */
 651              }
 652              if ($cnt++ > 0) $sql .= ", ";
 653              $sql .= str_replace('%{user}',$value,$this->cnf['UpdateLogin']);
 654            } else if ($item == 'name') {
 655              if ($cnt++ > 0) $sql .= ", ";
 656              $sql .= str_replace('%{name}',$value,$this->cnf['UpdateName']);
 657            } else if ($item == 'pass') {
 658              if (!$this->cnf['forwardClearPass'])
 659                $value = auth_cryptPassword($value);
 660              if ($cnt++ > 0) $sql .= ", ";
 661              $sql .= str_replace('%{pass}',$value,$this->cnf['UpdatePass']);
 662            } else if ($item == 'mail') {
 663              if ($cnt++ > 0) $sql .= ", ";
 664              $sql .= str_replace('%{email}',$value,$this->cnf['UpdateEmail']);
 665            }
 666          }
 667  
 668          if ($err == 0) {
 669            if ($cnt > 0) {
 670              $sql .= " ".str_replace('%{uid}', $uid, $this->cnf['UpdateTarget']);
 671              if(get_class($this) == 'auth_mysql') $sql .= " LIMIT 1"; //some PgSQL inheritance comp.
 672              $this->_modifyDB($sql);
 673            }
 674            return true;
 675          }
 676        }
 677        return false;
 678      }
 679  
 680      /**
 681       * Retrieves the group id of a given group name
 682       *
 683       * The database connection must already be established
 684       * for this function to work. Otherwise it will return
 685       * 'false'.
 686       *
 687       * @param  $group   group name which id is desired
 688       * @return group id
 689       *
 690       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 691       */
 692      function _getGroupID($group) {
 693        if($this->dbcon) {
 694          $sql = str_replace('%{group}',$this->_escape($group),$this->cnf['getGroupID']);
 695          $result = $this->_queryDB($sql);
 696          return $result === false ? false : $result[0]['id'];
 697        }
 698        return false;
 699      }
 700  
 701      /**
 702       * Opens a connection to a database and saves the handle for further
 703       * usage in the object. The successful call to this functions is
 704       * essential for most functions in this object.
 705       *
 706       * @return bool
 707       *
 708       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 709       */
 710      function _openDB() {
 711        if (!$this->dbcon) {
 712          $con = @mysql_connect ($this->cnf['server'], $this->cnf['user'], $this->cnf['password']);
 713          if ($con) {
 714            if ((mysql_select_db($this->cnf['database'], $con))) {
 715              if ((preg_match("/^(\d+)\.(\d+)\.(\d+).*/", mysql_get_server_info ($con), $result)) == 1) {
 716                $this->dbver = $result[1];
 717                $this->dbrev = $result[2];
 718                $this->dbsub = $result[3];
 719              }
 720              $this->dbcon = $con;
 721              return true;   // connection and database successfully opened
 722            } else {
 723              mysql_close ($con);
 724              if ($this->cnf['debug'])
 725                msg("MySQL err: No access to database {$this->cnf['database']}.",-1,__LINE__,__FILE__);
 726            }
 727          } else if ($this->cnf['debug'])
 728            msg ("MySQL err: Connection to {$this->cnf['user']}@{$this->cnf['server']} not possible.",
 729                 -1,__LINE__,__FILE__);
 730  
 731          return false;  // connection failed
 732        }
 733        return true;  // connection already open
 734      }
 735  
 736      /**
 737       * Closes a database connection.
 738       *
 739       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 740       */
 741      function _closeDB() {
 742        if ($this->dbcon) {
 743          mysql_close ($this->dbcon);
 744          $this->dbcon = 0;
 745        }
 746      }
 747  
 748      /**
 749       * Sends a SQL query to the database and transforms the result into
 750       * an associative array.
 751       *
 752       * This function is only able to handle queries that returns a
 753       * table such as SELECT.
 754       *
 755       * @param $query  SQL string that contains the query
 756       * @return array with the result table
 757       *
 758       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 759       */
 760      function _queryDB($query) {
 761        $resultarray = array();
 762        if ($this->dbcon) {
 763          $result = @mysql_query($query,$this->dbcon);
 764          if ($result) {
 765            while (($t = mysql_fetch_assoc($result)) !== false)
 766              $resultarray[]=$t;
 767            mysql_free_result ($result);
 768            return $resultarray;
 769          }
 770          if ($this->cnf['debug'])
 771            msg('MySQL err: '.mysql_error($this->dbcon),-1,__LINE__,__FILE__);
 772        }
 773        return false;
 774      }
 775  
 776      /**
 777       * Sends a SQL query to the database
 778       *
 779       * This function is only able to handle queries that returns
 780       * either nothing or an id value such as INPUT, DELETE, UPDATE, etc.
 781       *
 782       * @param $query  SQL string that contains the query
 783       * @return insert id or 0, false on error
 784       *
 785       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 786       */
 787      function _modifyDB($query) {
 788        if ($this->dbcon) {
 789          $result = @mysql_query($query,$this->dbcon);
 790          if ($result) {
 791            $rc = mysql_insert_id($this->dbcon); //give back ID on insert
 792            if ($rc !== false) return $rc;
 793          }
 794          if ($this->cnf['debug'])
 795            msg('MySQL err: '.mysql_error($this->dbcon),-1,__LINE__,__FILE__);
 796        }
 797        return false;
 798      }
 799  
 800      /**
 801       * Locked a list of tables for exclusive access so that modifications
 802       * to the database can't be disturbed by other threads. The list
 803       * could be set with $conf['auth']['mysql']['TablesToLock'] = array()
 804       *
 805       * If aliases for tables are used in SQL statements, also this aliases
 806       * must be locked. For eg. you use a table 'user' and the alias 'u' in
 807       * some sql queries, the array must looks like this (order is important):
 808       *   array("user", "user AS u");
 809       *
 810       * MySQL V3 is not able to handle transactions with COMMIT/ROLLBACK
 811       * so that this functionality is simulated by this function. Nevertheless
 812       * it is not as powerful as transactions, it is a good compromise in safty.
 813       *
 814       * @param $mode  could be 'READ' or 'WRITE'
 815       *
 816       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 817       */
 818      function _lockTables($mode) {
 819        if ($this->dbcon) {
 820          if (is_array($this->cnf['TablesToLock']) && !empty($this->cnf['TablesToLock'])) {
 821            if ($mode == "READ" || $mode == "WRITE") {
 822              $sql = "LOCK TABLES ";
 823              $cnt = 0;
 824              foreach ($this->cnf['TablesToLock'] as $table) {
 825                if ($cnt++ != 0) $sql .= ", ";
 826                $sql .= "$table $mode";
 827              }
 828              $this->_modifyDB($sql);
 829              return true;
 830            }
 831          }
 832        }
 833        return false;
 834      }
 835  
 836      /**
 837       * Unlock locked tables. All existing locks of this thread will be
 838       * abrogated.
 839       *
 840       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 841       */
 842      function _unlockTables() {
 843        if ($this->dbcon) {
 844          $this->_modifyDB("UNLOCK TABLES");
 845          return true;
 846        }
 847        return false;
 848      }
 849  
 850      /**
 851       * Transforms the filter settings in an filter string for a SQL database
 852       * The database connection must already be established, otherwise the
 853       * original SQL string without filter criteria will be returned.
 854       *
 855       * @param  $sql     SQL string to which the $filter criteria should be added
 856       * @param  $filter  array of filter criteria as pairs of item and pattern
 857       * @return SQL string with attached $filter criteria on success
 858       * @return the original SQL string on error.
 859       *
 860       * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
 861       */
 862      function _createSQLFilter($sql, $filter) {
 863        $SQLfilter = "";
 864        $cnt = 0;
 865  
 866        if ($this->dbcon) {
 867          foreach ($filter as $item => $pattern) {
 868            $tmp = '%'.$this->_escape($pattern).'%';
 869            if ($item == 'user') {
 870              if ($cnt++ > 0) $SQLfilter .= " AND ";
 871              $SQLfilter .= str_replace('%{user}',$tmp,$this->cnf['FilterLogin']);
 872            } else if ($item == 'name') {
 873              if ($cnt++ > 0) $SQLfilter .= " AND ";
 874              $SQLfilter .= str_replace('%{name}',$tmp,$this->cnf['FilterName']);
 875            } else if ($item == 'mail') {
 876              if ($cnt++ > 0) $SQLfilter .= " AND ";
 877              $SQLfilter .= str_replace('%{email}',$tmp,$this->cnf['FilterEmail']);
 878            } else if ($item == 'grps') {
 879              if ($cnt++ > 0) $SQLfilter .= " AND ";
 880              $SQLfilter .= str_replace('%{group}',$tmp,$this->cnf['FilterGroup']);
 881            }
 882          }
 883  
 884          // we have to check SQLfilter here and must not use $cnt because if
 885          // any of cnf['Filter????'] is not defined, a malformed SQL string
 886          // would be generated.
 887  
 888          if (strlen($SQLfilter)) {
 889            $glue = strpos(strtolower($sql),"where") ? " AND " : " WHERE ";
 890            $sql = $sql.$glue.$SQLfilter;
 891          }
 892        }
 893  
 894        return $sql;
 895      }
 896  
 897      /**
 898       * Escape a string for insertion into the database
 899       *
 900       * @author Andreas Gohr <andi@splitbrain.org>
 901       * @param  string  $string The string to escape
 902       * @param  boolean $like   Escape wildcard chars as well?
 903       */
 904      function _escape($string,$like=false){
 905        if($this->dbcon){
 906          $string = mysql_real_escape_string($string, $this->dbcon);
 907        }else{
 908          $string = addslashes($string);
 909        }
 910        if($like){
 911          $string = addcslashes($string,'%_');
 912        }
 913        return $string;
 914      }
 915  }
 916  
 917  //Setup VIM: ex: et ts=2 enc=utf-8 :