source: documentation/trunk/packages/dokuwiki-2011-05-25a/inc/auth/mysql.class.php@ 25027

Last change on this file since 25027 was 25027, checked in by jmt12, 12 years ago

Adding the packages directory, and within it a configured version of dokuwiki all ready to run
File size: 31.8 KB
Line 
1<?php
2/**
3 * MySQLP authentication backend
4 *
5 * @license GPL 2 (http://www.gnu.org/licenses/gpl.html)
6 * @author Andreas Gohr <[email protected]>
7 * @author Chris Smith <[email protected]>
8 * @author Matthias Grimm <[email protected]>
9*/
10
11class 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 <[email protected]>
27 */
28 function auth_mysql() {
29 global $conf;
30 $this->cnf = $conf['auth']['mysql'];
31
32 if (method_exists($this, 'auth_basic'))
33 parent::auth_basic();
34
35 if(!function_exists('mysql_connect')) {
36 if ($this->cnf['debug'])
37 msg("MySQL err: PHP MySQL extension not found.",-1,__LINE__,__FILE__);
38 $this->success = false;
39 return;
40 }
41
42 // default to UTF-8, you rarely want something else
43 if(!isset($this->cnf['charset'])) $this->cnf['charset'] = 'utf8';
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 !isset($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 <[email protected]>
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 <[email protected]>
122 * @author Matthias Grimm <[email protected]>
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 <[email protected]>
156 * @author Matthias Grimm <[email protected]>
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 occurred 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 <[email protected]>
185 * @author Chris Smith <[email protected]>
186 * @author Matthias Grimm <[email protected]>
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 <[email protected]>
233 * @author Matthias Grimm <[email protected]>
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($user, $group, 1)) == false)
254 $rc = false;
255
256 foreach($grpdel as $group)
257 if (($this->_delUserFromGroup($user, $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 <[email protected]>
277 * @author Matthias Grimm <[email protected]>
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 <[email protected]>
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 <[email protected]>
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 <[email protected]>
365 */
366 function joinGroup($user, $group) {
367 $rc = false;
368
369 if ($this->_openDB()) {
370 $this->_lockTables("WRITE");
371 $rc = $this->_addUserToGroup($user, $group);
372 $this->_unlockTables();
373 $this->_closeDB();
374 }
375 return $rc;
376 }
377
378 /**
379 * Remove user from a group [public function]
380 *
381 * @param $user user that leaves a group
382 * @param $group group to leave
383 * @return bool
384 *
385 * @author Matthias Grimm <[email protected]>
386 */
387 function leaveGroup($user, $group) {
388 $rc = false;
389
390 if ($this->_openDB()) {
391 $this->_lockTables("WRITE");
392 $uid = $this->_getUserID($user);
393 $rc = $this->_delUserFromGroup($user, $group);
394 $this->_unlockTables();
395 $this->_closeDB();
396 }
397 return $rc;
398 }
399
400 /**
401 * MySQL is case-insensitive
402 */
403 function isCaseSensitive(){
404 return false;
405 }
406
407 /**
408 * Adds a user to a group.
409 *
410 * If $force is set to '1' non existing groups would be created.
411 *
412 * The database connection must already be established. Otherwise
413 * this function does nothing and returns 'false'. It is strongly
414 * recommended to call this function only after all participating
415 * tables (group and usergroup) have been locked.
416 *
417 * @param $user user to add to a group
418 * @param $group name of the group
419 * @param $force '1' create missing groups
420 * @return bool 'true' on success, 'false' on error
421 *
422 * @author Matthias Grimm <[email protected]>
423 */
424 function _addUserToGroup($user, $group, $force=0) {
425 $newgroup = 0;
426
427 if (($this->dbcon) && ($user)) {
428 $gid = $this->_getGroupID($group);
429 if (!$gid) {
430 if ($force) { // create missing groups
431 $sql = str_replace('%{group}',$this->_escape($group),$this->cnf['addGroup']);
432 $gid = $this->_modifyDB($sql);
433 $newgroup = 1; // group newly created
434 }
435 if (!$gid) return false; // group didn't exist and can't be created
436 }
437
438 $sql = $this->cnf['addUserGroup'];
439 if(strpos($sql,'%{uid}') !== false){
440 $uid = $this->_getUserID($user);
441 $sql = str_replace('%{uid}', $this->_escape($uid),$sql);
442 }
443 $sql = str_replace('%{user}', $this->_escape($user),$sql);
444 $sql = str_replace('%{gid}', $this->_escape($gid),$sql);
445 $sql = str_replace('%{group}',$this->_escape($group),$sql);
446 if ($this->_modifyDB($sql) !== false) return true;
447
448 if ($newgroup) { // remove previously created group on error
449 $sql = str_replace('%{gid}', $this->_escape($gid),$this->cnf['delGroup']);
450 $sql = str_replace('%{group}',$this->_escape($group),$sql);
451 $this->_modifyDB($sql);
452 }
453 }
454 return false;
455 }
456
457 /**
458 * Remove user from a group
459 *
460 * @param $user user that leaves a group
461 * @param $group group to leave
462 * @return bool true on success, false on error
463 *
464 * @author Matthias Grimm <[email protected]>
465 */
466 function _delUserFromGroup($user, $group) {
467 $rc = false;
468
469
470 if (($this->dbcon) && ($user)) {
471 $sql = $this->cnf['delUserGroup'];
472 if(strpos($sql,'%{uid}') !== false){
473 $uid = $this->_getUserID($user);
474 $sql = str_replace('%{uid}', $this->_escape($uid),$sql);
475 }
476 $gid = $this->_getGroupID($group);
477 if ($gid) {
478 $sql = str_replace('%{user}', $this->_escape($user),$sql);
479 $sql = str_replace('%{gid}', $this->_escape($gid),$sql);
480 $sql = str_replace('%{group}',$this->_escape($group),$sql);
481 $rc = $this->_modifyDB($sql) == 0 ? true : false;
482 }
483 }
484 return $rc;
485 }
486
487 /**
488 * Retrieves a list of groups the user is a member off.
489 *
490 * The database connection must already be established
491 * for this function to work. Otherwise it will return
492 * 'false'.
493 *
494 * @param $user user whose groups should be listed
495 * @return bool false on error
496 * @return array array containing all groups on success
497 *
498 * @author Matthias Grimm <[email protected]>
499 */
500 function _getGroups($user) {
501 $groups = array();
502
503 if($this->dbcon) {
504 $sql = str_replace('%{user}',$this->_escape($user),$this->cnf['getGroups']);
505 $result = $this->_queryDB($sql);
506
507 if($result !== false && count($result)) {
508 foreach($result as $row)
509 $groups[] = $row['group'];
510 }
511 return $groups;
512 }
513 return false;
514 }
515
516 /**
517 * Retrieves the user id of a given user name
518 *
519 * The database connection must already be established
520 * for this function to work. Otherwise it will return
521 * 'false'.
522 *
523 * @param $user user whose id is desired
524 * @return user id
525 *
526 * @author Matthias Grimm <[email protected]>
527 */
528 function _getUserID($user) {
529 if($this->dbcon) {
530 $sql = str_replace('%{user}',$this->_escape($user),$this->cnf['getUserID']);
531 $result = $this->_queryDB($sql);
532 return $result === false ? false : $result[0]['id'];
533 }
534 return false;
535 }
536
537 /**
538 * Adds a new User to the database.
539 *
540 * The database connection must already be established
541 * for this function to work. Otherwise it will return
542 * 'false'.
543 *
544 * @param $user login of the user
545 * @param $pwd encrypted password
546 * @param $name full name of the user
547 * @param $mail email address
548 * @param $grps array of groups the user should become member of
549 * @return bool
550 *
551 * @author Andreas Gohr <[email protected]>
552 * @author Chris Smith <[email protected]>
553 * @author Matthias Grimm <[email protected]>
554 */
555 function _addUser($user,$pwd,$name,$mail,$grps){
556 if($this->dbcon && is_array($grps)) {
557 $sql = str_replace('%{user}', $this->_escape($user),$this->cnf['addUser']);
558 $sql = str_replace('%{pass}', $this->_escape($pwd),$sql);
559 $sql = str_replace('%{name}', $this->_escape($name),$sql);
560 $sql = str_replace('%{email}',$this->_escape($mail),$sql);
561 $uid = $this->_modifyDB($sql);
562
563 if ($uid) {
564 foreach($grps as $group) {
565 $gid = $this->_addUserToGroup($user, $group, 1);
566 if ($gid === false) break;
567 }
568
569 if ($gid) return true;
570 else {
571 /* remove the new user and all group relations if a group can't
572 * be assigned. Newly created groups will remain in the database
573 * and won't be removed. This might create orphaned groups but
574 * is not a big issue so we ignore this problem here.
575 */
576 $this->_delUser($user);
577 if ($this->cnf['debug'])
578 msg ("MySQL err: Adding user '$user' to group '$group' failed.",-1,__LINE__,__FILE__);
579 }
580 }
581 }
582 return false;
583 }
584
585 /**
586 * Deletes a given user and all his group references.
587 *
588 * The database connection must already be established
589 * for this function to work. Otherwise it will return
590 * 'false'.
591 *
592 * @param $user user whose id is desired
593 * @return bool
594 *
595 * @author Matthias Grimm <[email protected]>
596 */
597 function _delUser($user) {
598 if($this->dbcon) {
599 $uid = $this->_getUserID($user);
600 if ($uid) {
601 $sql = str_replace('%{uid}',$this->_escape($uid),$this->cnf['delUserRefs']);
602 $this->_modifyDB($sql);
603 $sql = str_replace('%{uid}',$this->_escape($uid),$this->cnf['delUser']);
604 $sql = str_replace('%{user}', $this->_escape($user),$sql);
605 $this->_modifyDB($sql);
606 return true;
607 }
608 }
609 return false;
610 }
611
612 /**
613 * getUserInfo
614 *
615 * Gets the data for a specific user The database connection
616 * must already be established for this function to work.
617 * Otherwise it will return 'false'.
618 *
619 * @param $user user's nick to get data for
620 * @return bool false on error
621 * @return array user info on success
622 *
623 * @author Matthias Grimm <[email protected]>
624 */
625 function _getUserInfo($user){
626 $sql = str_replace('%{user}',$this->_escape($user),$this->cnf['getUserInfo']);
627 $result = $this->_queryDB($sql);
628 if($result !== false && count($result)) {
629 $info = $result[0];
630 $info['grps'] = $this->_getGroups($user);
631 return $info;
632 }
633 return false;
634 }
635
636 /**
637 * Updates the user info in the database
638 *
639 * Update a user data structure in the database according changes
640 * given in an array. The user name can only be changes if it didn't
641 * exists already. If the new user name exists the update procedure
642 * will be aborted. The database keeps unchanged.
643 *
644 * The database connection has already to be established for this
645 * function to work. Otherwise it will return 'false'.
646 *
647 * The password will be crypted if necessary.
648 *
649 * @param $changes array of items to change as pairs of item and value
650 * @param $uid user id of dataset to change, must be unique in DB
651 * @return true on success or false on error
652 *
653 * @author Matthias Grimm <[email protected]>
654 */
655 function _updateUserInfo($changes, $uid) {
656 $sql = $this->cnf['updateUser']." ";
657 $cnt = 0;
658 $err = 0;
659
660 if($this->dbcon) {
661 foreach ($changes as $item => $value) {
662 if ($item == 'user') {
663 if (($this->_getUserID($changes['user']))) {
664 $err = 1; /* new username already exists */
665 break; /* abort update */
666 }
667 if ($cnt++ > 0) $sql .= ", ";
668 $sql .= str_replace('%{user}',$value,$this->cnf['UpdateLogin']);
669 } else if ($item == 'name') {
670 if ($cnt++ > 0) $sql .= ", ";
671 $sql .= str_replace('%{name}',$value,$this->cnf['UpdateName']);
672 } else if ($item == 'pass') {
673 if (!$this->cnf['forwardClearPass'])
674 $value = auth_cryptPassword($value);
675 if ($cnt++ > 0) $sql .= ", ";
676 $sql .= str_replace('%{pass}',$value,$this->cnf['UpdatePass']);
677 } else if ($item == 'mail') {
678 if ($cnt++ > 0) $sql .= ", ";
679 $sql .= str_replace('%{email}',$value,$this->cnf['UpdateEmail']);
680 }
681 }
682
683 if ($err == 0) {
684 if ($cnt > 0) {
685 $sql .= " ".str_replace('%{uid}', $uid, $this->cnf['UpdateTarget']);
686 if(get_class($this) == 'auth_mysql') $sql .= " LIMIT 1"; //some PgSQL inheritance comp.
687 $this->_modifyDB($sql);
688 }
689 return true;
690 }
691 }
692 return false;
693 }
694
695 /**
696 * Retrieves the group id of a given group name
697 *
698 * The database connection must already be established
699 * for this function to work. Otherwise it will return
700 * 'false'.
701 *
702 * @param $group group name which id is desired
703 * @return group id
704 *
705 * @author Matthias Grimm <[email protected]>
706 */
707 function _getGroupID($group) {
708 if($this->dbcon) {
709 $sql = str_replace('%{group}',$this->_escape($group),$this->cnf['getGroupID']);
710 $result = $this->_queryDB($sql);
711 return $result === false ? false : $result[0]['id'];
712 }
713 return false;
714 }
715
716 /**
717 * Opens a connection to a database and saves the handle for further
718 * usage in the object. The successful call to this functions is
719 * essential for most functions in this object.
720 *
721 * @return bool
722 *
723 * @author Matthias Grimm <[email protected]>
724 */
725 function _openDB() {
726 if (!$this->dbcon) {
727 $con = @mysql_connect ($this->cnf['server'], $this->cnf['user'], $this->cnf['password']);
728 if ($con) {
729 if ((mysql_select_db($this->cnf['database'], $con))) {
730 if ((preg_match("/^(\d+)\.(\d+)\.(\d+).*/", mysql_get_server_info ($con), $result)) == 1) {
731 $this->dbver = $result[1];
732 $this->dbrev = $result[2];
733 $this->dbsub = $result[3];
734 }
735 $this->dbcon = $con;
736 if(!empty($this->cnf['charset'])){
737 mysql_query('SET CHARACTER SET "' . $this->cnf['charset'] . '"', $con);
738 }
739 return true; // connection and database successfully opened
740 } else {
741 mysql_close ($con);
742 if ($this->cnf['debug'])
743 msg("MySQL err: No access to database {$this->cnf['database']}.",-1,__LINE__,__FILE__);
744 }
745 } else if ($this->cnf['debug'])
746 msg ("MySQL err: Connection to {$this->cnf['user']}@{$this->cnf['server']} not possible.",
747 -1,__LINE__,__FILE__);
748
749 return false; // connection failed
750 }
751 return true; // connection already open
752 }
753
754 /**
755 * Closes a database connection.
756 *
757 * @author Matthias Grimm <[email protected]>
758 */
759 function _closeDB() {
760 if ($this->dbcon) {
761 mysql_close ($this->dbcon);
762 $this->dbcon = 0;
763 }
764 }
765
766 /**
767 * Sends a SQL query to the database and transforms the result into
768 * an associative array.
769 *
770 * This function is only able to handle queries that returns a
771 * table such as SELECT.
772 *
773 * @param $query SQL string that contains the query
774 * @return array with the result table
775 *
776 * @author Matthias Grimm <[email protected]>
777 */
778 function _queryDB($query) {
779 if($this->cnf['debug'] >= 2){
780 msg('MySQL query: '.hsc($query),0,__LINE__,__FILE__);
781 }
782
783 $resultarray = array();
784 if ($this->dbcon) {
785 $result = @mysql_query($query,$this->dbcon);
786 if ($result) {
787 while (($t = mysql_fetch_assoc($result)) !== false)
788 $resultarray[]=$t;
789 mysql_free_result ($result);
790 return $resultarray;
791 }
792 if ($this->cnf['debug'])
793 msg('MySQL err: '.mysql_error($this->dbcon),-1,__LINE__,__FILE__);
794 }
795 return false;
796 }
797
798 /**
799 * Sends a SQL query to the database
800 *
801 * This function is only able to handle queries that returns
802 * either nothing or an id value such as INPUT, DELETE, UPDATE, etc.
803 *
804 * @param $query SQL string that contains the query
805 * @return insert id or 0, false on error
806 *
807 * @author Matthias Grimm <[email protected]>
808 */
809 function _modifyDB($query) {
810 if ($this->dbcon) {
811 $result = @mysql_query($query,$this->dbcon);
812 if ($result) {
813 $rc = mysql_insert_id($this->dbcon); //give back ID on insert
814 if ($rc !== false) return $rc;
815 }
816 if ($this->cnf['debug'])
817 msg('MySQL err: '.mysql_error($this->dbcon),-1,__LINE__,__FILE__);
818 }
819 return false;
820 }
821
822 /**
823 * Locked a list of tables for exclusive access so that modifications
824 * to the database can't be disturbed by other threads. The list
825 * could be set with $conf['auth']['mysql']['TablesToLock'] = array()
826 *
827 * If aliases for tables are used in SQL statements, also this aliases
828 * must be locked. For eg. you use a table 'user' and the alias 'u' in
829 * some sql queries, the array must looks like this (order is important):
830 * array("user", "user AS u");
831 *
832 * MySQL V3 is not able to handle transactions with COMMIT/ROLLBACK
833 * so that this functionality is simulated by this function. Nevertheless
834 * it is not as powerful as transactions, it is a good compromise in safty.
835 *
836 * @param $mode could be 'READ' or 'WRITE'
837 *
838 * @author Matthias Grimm <[email protected]>
839 */
840 function _lockTables($mode) {
841 if ($this->dbcon) {
842 if (is_array($this->cnf['TablesToLock']) && !empty($this->cnf['TablesToLock'])) {
843 if ($mode == "READ" || $mode == "WRITE") {
844 $sql = "LOCK TABLES ";
845 $cnt = 0;
846 foreach ($this->cnf['TablesToLock'] as $table) {
847 if ($cnt++ != 0) $sql .= ", ";
848 $sql .= "$table $mode";
849 }
850 $this->_modifyDB($sql);
851 return true;
852 }
853 }
854 }
855 return false;
856 }
857
858 /**
859 * Unlock locked tables. All existing locks of this thread will be
860 * abrogated.
861 *
862 * @author Matthias Grimm <[email protected]>
863 */
864 function _unlockTables() {
865 if ($this->dbcon) {
866 $this->_modifyDB("UNLOCK TABLES");
867 return true;
868 }
869 return false;
870 }
871
872 /**
873 * Transforms the filter settings in an filter string for a SQL database
874 * The database connection must already be established, otherwise the
875 * original SQL string without filter criteria will be returned.
876 *
877 * @param $sql SQL string to which the $filter criteria should be added
878 * @param $filter array of filter criteria as pairs of item and pattern
879 * @return SQL string with attached $filter criteria on success
880 * @return the original SQL string on error.
881 *
882 * @author Matthias Grimm <[email protected]>
883 */
884 function _createSQLFilter($sql, $filter) {
885 $SQLfilter = "";
886 $cnt = 0;
887
888 if ($this->dbcon) {
889 foreach ($filter as $item => $pattern) {
890 $tmp = '%'.$this->_escape($pattern).'%';
891 if ($item == 'user') {
892 if ($cnt++ > 0) $SQLfilter .= " AND ";
893 $SQLfilter .= str_replace('%{user}',$tmp,$this->cnf['FilterLogin']);
894 } else if ($item == 'name') {
895 if ($cnt++ > 0) $SQLfilter .= " AND ";
896 $SQLfilter .= str_replace('%{name}',$tmp,$this->cnf['FilterName']);
897 } else if ($item == 'mail') {
898 if ($cnt++ > 0) $SQLfilter .= " AND ";
899 $SQLfilter .= str_replace('%{email}',$tmp,$this->cnf['FilterEmail']);
900 } else if ($item == 'grps') {
901 if ($cnt++ > 0) $SQLfilter .= " AND ";
902 $SQLfilter .= str_replace('%{group}',$tmp,$this->cnf['FilterGroup']);
903 }
904 }
905
906 // we have to check SQLfilter here and must not use $cnt because if
907 // any of cnf['Filter????'] is not defined, a malformed SQL string
908 // would be generated.
909
910 if (strlen($SQLfilter)) {
911 $glue = strpos(strtolower($sql),"where") ? " AND " : " WHERE ";
912 $sql = $sql.$glue.$SQLfilter;
913 }
914 }
915
916 return $sql;
917 }
918
919 /**
920 * Escape a string for insertion into the database
921 *
922 * @author Andreas Gohr <[email protected]>
923 * @param string $string The string to escape
924 * @param boolean $like Escape wildcard chars as well?
925 */
926 function _escape($string,$like=false){
927 if($this->dbcon){
928 $string = mysql_real_escape_string($string, $this->dbcon);
929 }else{
930 $string = addslashes($string);
931 }
932 if($like){
933 $string = addcslashes($string,'%_');
934 }
935 return $string;
936 }
937}
938
939//Setup VIM: ex: et ts=2 :
Note: See TracBrowser for help on using the repository browser.