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