Add error handling to abook db lookups
[squirrelmail.git] / functions / abook_database.php
CommitLineData
9b9474d6 1<?php
4b4abf93 2
35586184 3/**
4 * abook_database.php
5 *
46956d06 6 * Supported database schema
7 * <pre>
8 * owner varchar(128) NOT NULL
9 * nickname varchar(16) NOT NULL
10 * firstname varchar(128)
11 * lastname varchar(128)
12 * email varchar(128) NOT NULL
13 * label varchar(255)
14 * PRIMARY KEY (owner,nickname)
15 * </pre>
16 *
4b5049de 17 * @copyright &copy; 1999-2007 The SquirrelMail Project Team
4b4abf93 18 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
a9d318b0 19 * @version $Id$
d6c32258 20 * @package squirrelmail
a9d318b0 21 * @subpackage addressbook
35586184 22 */
d6c32258 23
6d429ce6 24/**
25 * Needs the DB functions
26 * Don't display errors here. Error will be set in class constructor function.
27 */
28@include_once('DB.php');
d6c32258 29
30/**
0541c5ae 31 * Address book in a database backend
32 *
33 * Backend for personal/shared address book stored in a database,
34 * accessed using the DB-classes in PEAR.
35 *
36 * IMPORTANT: The PEAR modules must be in the include path
37 * for this class to work.
38 *
39 * An array with the following elements must be passed to
40 * the class constructor (elements marked ? are optional):
41 * <pre>
42 * dsn => database DNS (see PEAR for syntax)
43 * table => table to store addresses in (must exist)
44 * owner => current user (owner of address data)
45 * ? name => name of address book
46 * ? writeable => set writeable flag (true/false)
47 * ? listing => enable/disable listing
48 * </pre>
49 * The table used should have the following columns:
50 * owner, nickname, firstname, lastname, email, label
51 * The pair (owner,nickname) should be unique (primary key).
52 *
53 * NOTE. This class should not be used directly. Use the
54 * "AddressBook" class instead.
d6c32258 55 * @package squirrelmail
0541c5ae 56 * @subpackage addressbook
d6c32258 57 */
9b9474d6 58class abook_database extends addressbook_backend {
0541c5ae 59 /**
60 * Backend type
61 * @var string
62 */
9b9474d6 63 var $btype = 'local';
0541c5ae 64 /**
65 * Backend name
66 * @var string
67 */
9b9474d6 68 var $bname = 'database';
62f7daa5 69
0541c5ae 70 /**
71 * Data Source Name (connection description)
72 * @var string
73 */
9b9474d6 74 var $dsn = '';
0541c5ae 75 /**
76 * Table that stores addresses
77 * @var string
78 */
9b9474d6 79 var $table = '';
0541c5ae 80 /**
81 * Owner name
82 *
83 * Limits list of database entries visible to end user
84 * @var string
85 */
9b9474d6 86 var $owner = '';
0541c5ae 87 /**
88 * Database Handle
89 * @var resource
90 */
9b9474d6 91 var $dbh = false;
0541c5ae 92 /**
93 * Enable/disable writing into address book
94 * @var bool
95 */
9b9474d6 96 var $writeable = true;
0541c5ae 97 /**
98 * Enable/disable address book listing
99 * @var bool
100 */
101 var $listing = true;
62f7daa5 102
9b9474d6 103 /* ========================== Private ======================= */
62f7daa5 104
0541c5ae 105 /**
106 * Constructor
107 * @param array $param address book backend options
108 */
9b9474d6 109 function abook_database($param) {
232fb714 110 $this->sname = _("Personal Address Book");
62f7daa5 111
6d429ce6 112 /* test if Pear DB class is available and freak out if it is not */
113 if (! class_exists('DB')) {
114 // same error also in db_prefs.php
35235328 115 $error = _("Could not include PEAR database functions required for the database backend.") . "\n";
6d429ce6 116 $error .= sprintf(_("Is PEAR installed, and is the include path set correctly to find %s?"),
35235328 117 'DB.php') . "\n";
6d429ce6 118 $error .= _("Please contact your system administrator and report this error.");
119 return $this->set_error($error);
120 }
121
9b9474d6 122 if (is_array($param)) {
62f7daa5 123 if (empty($param['dsn']) ||
124 empty($param['table']) ||
9b9474d6 125 empty($param['owner'])) {
126 return $this->set_error('Invalid parameters');
127 }
62f7daa5 128
91be2362 129 $this->dsn = $param['dsn'];
130 $this->table = $param['table'];
131 $this->owner = $param['owner'];
62f7daa5 132
9b9474d6 133 if (!empty($param['name'])) {
91be2362 134 $this->sname = $param['name'];
9b9474d6 135 }
7902aca2 136
9b9474d6 137 if (isset($param['writeable'])) {
91be2362 138 $this->writeable = $param['writeable'];
9b9474d6 139 }
7902aca2 140
30e9932c 141 if (isset($param['listing'])) {
142 $this->listing = $param['listing'];
143 }
144
7902aca2 145 $this->open(true);
9b9474d6 146 }
147 else {
91be2362 148 return $this->set_error('Invalid argument to constructor');
9b9474d6 149 }
150 }
62f7daa5 151
152
0541c5ae 153 /**
e50f5ac2 154 * Open the database.
0541c5ae 155 * @param bool $new new connection if it is true
156 * @return bool
157 */
9b9474d6 158 function open($new = false) {
159 $this->error = '';
62f7daa5 160
9b9474d6 161 /* Return true is file is open and $new is unset */
162 if ($this->dbh && !$new) {
7902aca2 163 return true;
9b9474d6 164 }
62f7daa5 165
9b9474d6 166 /* Close old file, if any */
167 if ($this->dbh) {
168 $this->close();
169 }
62f7daa5 170
9b9474d6 171 $dbh = DB::connect($this->dsn, true);
62f7daa5 172
286fe80b 173 if (DB::isError($dbh)) {
701c9c6b 174 return $this->set_error(sprintf(_("Database error: %s"),
7902aca2 175 DB::errorMessage($dbh)));
9b9474d6 176 }
62f7daa5 177
9b9474d6 178 $this->dbh = $dbh;
46956d06 179
180 /**
181 * field names are lowercased.
182 * We use unquoted identifiers and they use upper case in Oracle
183 */
184 $this->dbh->setOption('portability', DB_PORTABILITY_LOWERCASE);
185
9b9474d6 186 return true;
187 }
7902aca2 188
0541c5ae 189 /**
190 * Close the file and forget the filehandle
191 */
9b9474d6 192 function close() {
193 $this->dbh->disconnect();
194 $this->dbh = false;
195 }
7902aca2 196
503c7650 197 /**
198 * Determine internal database field name given one of
199 * the SquirrelMail SM_ABOOK_FIELD_* constants
200 *
201 * @param integer $field The SM_ABOOK_FIELD_* contant to look up
202 *
203 * @return string The desired field name, or the string "ERROR"
204 * if the $field is not understood (the caller
205 * is responsible for handing errors)
206 *
207 */
208 function get_field_name($field) {
209 switch ($field) {
210 case SM_ABOOK_FIELD_NICKNAME:
211 return 'nickname';
212 case SM_ABOOK_FIELD_FIRSTNAME:
213 return 'firstname';
214 case SM_ABOOK_FIELD_LASTNAME:
215 return 'lastname';
216 case SM_ABOOK_FIELD_EMAIL:
217 return 'email';
218 case SM_ABOOK_FIELD_LABEL:
219 return 'label';
220 default:
221 return 'ERROR';
222 }
223 }
224
9b9474d6 225 /* ========================== Public ======================== */
62f7daa5 226
0541c5ae 227 /**
228 * Search the database
46956d06 229 *
230 * Backend supports only * and ? wildcards. Complex eregs are not supported.
231 * Search is case insensitive.
0541c5ae 232 * @param string $expr search expression
46956d06 233 * @return array search results. boolean false on error
0541c5ae 234 */
aa8c4265 235 function search($expr) {
9b9474d6 236 $ret = array();
237 if(!$this->open()) {
7902aca2 238 return false;
9b9474d6 239 }
30e9932c 240
9b9474d6 241 /* To be replaced by advanded search expression parsing */
242 if (is_array($expr)) {
243 return;
244 }
245
327e2d96 246 // don't allow wide search when listing is disabled.
247 if ($expr=='*' && ! $this->listing)
248 return array();
249
46956d06 250 /* lowercase expression in order to make it case insensitive */
251 $expr = strtolower($expr);
252
253 /* escape SQL wildcards */
254 $expr = str_replace('_', '\\_', $expr);
255 $expr = str_replace('%', '\\%', $expr);
256
257 /* Convert wildcards to SQL syntax */
9b9474d6 258 $expr = str_replace('?', '_', $expr);
259 $expr = str_replace('*', '%', $expr);
260 $expr = $this->dbh->quoteString($expr);
261 $expr = "%$expr%";
262
46956d06 263 /* create escape expression */
264 $escape = 'ESCAPE \'' . $this->dbh->quoteString('\\') . '\'';
265
9b9474d6 266 $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND " .
46956d06 267 "(LOWER(firstname) LIKE '%s' %s OR LOWER(lastname) LIKE '%s' %s)",
268 $this->table, $this->owner, $expr, $escape, $expr, $escape);
269
9b9474d6 270 $res = $this->dbh->query($query);
271
272 if (DB::isError($res)) {
701c9c6b 273 return $this->set_error(sprintf(_("Database error: %s"),
7902aca2 274 DB::errorMessage($res)));
9b9474d6 275 }
7902aca2 276
9b9474d6 277 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
91be2362 278 array_push($ret, array('nickname' => $row['nickname'],
35235328 279 'name' => $this->fullname($row['firstname'], $row['lastname']),
91be2362 280 'firstname' => $row['firstname'],
281 'lastname' => $row['lastname'],
282 'email' => $row['email'],
283 'label' => $row['label'],
284 'backend' => $this->bnum,
285 'source' => &$this->sname));
9b9474d6 286 }
287 return $ret;
288 }
62f7daa5 289
0541c5ae 290 /**
503c7650 291 * Lookup an address by the indicated field.
292 *
293 * @param string $value The value to look up
294 * @param integer $field The field to look in, should be one
295 * of the SM_ABOOK_FIELD_* constants
296 * defined in include/constants.php
297 * (OPTIONAL; defaults to nickname field)
bf55ebab 298 * NOTE: uniqueness is only guaranteed
299 * when the nickname field is used here;
300 * otherwise, the first matching address
301 * is returned.
503c7650 302 *
303 * @return array Array with lookup results when the value
304 * was found, an empty array if the value was
305 * not found.
306 *
0541c5ae 307 */
503c7650 308 function lookup($value, $field=SM_ABOOK_FIELD_NICKNAME) {
309 if (empty($value)) {
7902aca2 310 return array();
9b9474d6 311 }
62f7daa5 312
503c7650 313 $value = strtolower($value);
7902aca2 314
9b9474d6 315 if (!$this->open()) {
7902aca2 316 return false;
9b9474d6 317 }
62f7daa5 318
26bd3897 319 $db_field = $this->get_field_name($field);
320 if ($db_field == 'ERROR') {
321 return $this->set_error(sprintf(_("Unknown field name: %s"), $field));
322 }
323
503c7650 324 $query = sprintf("SELECT * FROM %s WHERE owner = '%s' AND LOWER(%s) = '%s'",
26bd3897 325 $this->table, $this->owner, $db_field,
503c7650 326 $this->dbh->quoteString($value));
7902aca2 327
9b9474d6 328 $res = $this->dbh->query($query);
7902aca2 329
9b9474d6 330 if (DB::isError($res)) {
701c9c6b 331 return $this->set_error(sprintf(_("Database error: %s"),
7902aca2 332 DB::errorMessage($res)));
9b9474d6 333 }
7902aca2 334
9b9474d6 335 if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
91be2362 336 return array('nickname' => $row['nickname'],
35235328 337 'name' => $this->fullname($row['firstname'], $row['lastname']),
91be2362 338 'firstname' => $row['firstname'],
339 'lastname' => $row['lastname'],
340 'email' => $row['email'],
341 'label' => $row['label'],
342 'backend' => $this->bnum,
343 'source' => &$this->sname);
9b9474d6 344 }
345 return array();
346 }
347
0541c5ae 348 /**
e50f5ac2 349 * List all addresses
0541c5ae 350 * @return array search results
351 */
aa8c4265 352 function list_addr() {
9b9474d6 353 $ret = array();
354 if (!$this->open()) {
7902aca2 355 return false;
9b9474d6 356 }
62f7daa5 357
91e0dccc 358 if(isset($this->listing) && !$this->listing) {
359 return array();
360 }
30e9932c 361
7902aca2 362
9b9474d6 363 $query = sprintf("SELECT * FROM %s WHERE owner='%s'",
364 $this->table, $this->owner);
7902aca2 365
9b9474d6 366 $res = $this->dbh->query($query);
62f7daa5 367
9b9474d6 368 if (DB::isError($res)) {
701c9c6b 369 return $this->set_error(sprintf(_("Database error: %s"),
7902aca2 370 DB::errorMessage($res)));
9b9474d6 371 }
7902aca2 372
9b9474d6 373 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
91be2362 374 array_push($ret, array('nickname' => $row['nickname'],
35235328 375 'name' => $this->fullname($row['firstname'], $row['lastname']),
91be2362 376 'firstname' => $row['firstname'],
377 'lastname' => $row['lastname'],
378 'email' => $row['email'],
379 'label' => $row['label'],
380 'backend' => $this->bnum,
381 'source' => &$this->sname));
9b9474d6 382 }
383 return $ret;
384 }
7902aca2 385
0541c5ae 386 /**
387 * Add address
388 * @param array $userdata added data
389 * @return bool
390 */
9b9474d6 391 function add($userdata) {
392 if (!$this->writeable) {
35235328 393 return $this->set_error(_("Address book is read-only"));
9b9474d6 394 }
7902aca2 395
9b9474d6 396 if (!$this->open()) {
7902aca2 397 return false;
9b9474d6 398 }
62f7daa5 399
9b9474d6 400 /* See if user exist already */
401 $ret = $this->lookup($userdata['nickname']);
402 if (!empty($ret)) {
2706a0b1 403 return $this->set_error(sprintf(_("User \"%s\" already exists"),$ret['nickname']));
9b9474d6 404 }
405
406 /* Create query */
407 $query = sprintf("INSERT INTO %s (owner, nickname, firstname, " .
408 "lastname, email, label) VALUES('%s','%s','%s'," .
409 "'%s','%s','%s')",
410 $this->table, $this->owner,
411 $this->dbh->quoteString($userdata['nickname']),
62f7daa5 412 $this->dbh->quoteString($userdata['firstname']),
8419c13b 413 $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')),
62f7daa5 414 $this->dbh->quoteString($userdata['email']),
8419c13b 415 $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')) );
9b9474d6 416
417 /* Do the insert */
7902aca2 418 $r = $this->dbh->simpleQuery($query);
46956d06 419
420 /* Check for errors */
421 if (DB::isError($r)) {
422 return $this->set_error(sprintf(_("Database error: %s"),
423 DB::errorMessage($r)));
9b9474d6 424 }
46956d06 425 return true;
9b9474d6 426 }
7902aca2 427
0541c5ae 428 /**
eee5aa69 429 * Deletes address book entries
430 * @param array $alias aliases that have to be deleted. numerical
431 * array with nickname values
0541c5ae 432 * @return bool
433 */
9b9474d6 434 function remove($alias) {
435 if (!$this->writeable) {
35235328 436 return $this->set_error(_("Address book is read-only"));
9b9474d6 437 }
7902aca2 438
9b9474d6 439 if (!$this->open()) {
7902aca2 440 return false;
9b9474d6 441 }
62f7daa5 442
9b9474d6 443 /* Create query */
444 $query = sprintf("DELETE FROM %s WHERE owner='%s' AND (",
445 $this->table, $this->owner);
7902aca2 446
9b9474d6 447 $sepstr = '';
448 while (list($undef, $nickname) = each($alias)) {
16a973d7 449 $query .= sprintf("%s nickname='%s' ", $sepstr,
7902aca2 450 $this->dbh->quoteString($nickname));
91be2362 451 $sepstr = 'OR';
9b9474d6 452 }
453 $query .= ')';
7902aca2 454
9b9474d6 455 /* Delete entry */
456 $r = $this->dbh->simpleQuery($query);
7902aca2 457
46956d06 458 /* Check for errors */
459 if (DB::isError($r)) {
460 return $this->set_error(sprintf(_("Database error: %s"),
461 DB::errorMessage($r)));
462 }
463 return true;
9b9474d6 464 }
7902aca2 465
0541c5ae 466 /**
467 * Modify address
468 * @param string $alias modified alias
469 * @param array $userdata new data
470 * @return bool
471 */
9b9474d6 472 function modify($alias, $userdata) {
473 if (!$this->writeable) {
35235328 474 return $this->set_error(_("Address book is read-only"));
9b9474d6 475 }
7902aca2 476
9b9474d6 477 if (!$this->open()) {
7902aca2 478 return false;
9b9474d6 479 }
62f7daa5 480
9b9474d6 481 /* See if user exist */
482 $ret = $this->lookup($alias);
483 if (empty($ret)) {
2706a0b1 484 return $this->set_error(sprintf(_("User \"%s\" does not exist"),$alias));
9b9474d6 485 }
486
46956d06 487 /* make sure that new nickname is not used */
488 if (strtolower($alias) != strtolower($userdata['nickname'])) {
489 /* same check as in add() */
490 $ret = $this->lookup($userdata['nickname']);
491 if (!empty($ret)) {
492 $error = sprintf(_("User '%s' already exist."), $ret['nickname']);
493 return $this->set_error($error);
494 }
495 }
496
9b9474d6 497 /* Create query */
498 $query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ".
499 "lastname='%s', email='%s', label='%s' ".
500 "WHERE owner='%s' AND nickname='%s'",
62f7daa5 501 $this->table,
9b9474d6 502 $this->dbh->quoteString($userdata['nickname']),
62f7daa5 503 $this->dbh->quoteString($userdata['firstname']),
8419c13b 504 $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')),
62f7daa5 505 $this->dbh->quoteString($userdata['email']),
8419c13b 506 $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')),
9b9474d6 507 $this->owner,
508 $this->dbh->quoteString($alias) );
509
510 /* Do the insert */
511 $r = $this->dbh->simpleQuery($query);
7902aca2 512
46956d06 513 /* Check for errors */
514 if (DB::isError($r)) {
515 return $this->set_error(sprintf(_("Database error: %s"),
516 DB::errorMessage($r)));
517 }
518 return true;
9b9474d6 519 }
520} /* End of class abook_database */
7902aca2 521
c9fcea56 522// vim: et ts=4