Happy New Year
[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 *
c4faef33 17 * @copyright 1999-2020 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/**
058b79d2 25 * Needs either PDO or the DB functions
6d429ce6 26 * Don't display errors here. Error will be set in class constructor function.
27 */
058b79d2 28global $use_pdo, $disable_pdo;
29if (empty($disable_pdo) && class_exists('PDO'))
30 $use_pdo = TRUE;
31else
32 $use_pdo = FALSE;
33
34if (!$use_pdo)
35 @include_once('DB.php');
d6c32258 36
37/**
0541c5ae 38 * Address book in a database backend
39 *
40 * Backend for personal/shared address book stored in a database,
058b79d2 41 * accessed using the DB-classes in PEAR or PDO, the latter taking
42 * precedence if available..
0541c5ae 43 *
058b79d2 44 * IMPORTANT: If PDO is not available (it should be installed by
45 * default since PHP 5.1), then the PEAR modules must
46 * be in the include path for this class to work.
0541c5ae 47 *
48 * An array with the following elements must be passed to
49 * the class constructor (elements marked ? are optional):
50 * <pre>
058b79d2 51 * dsn => database DNS (see PEAR for syntax, but more or
52 * less it is: mysql://user:pass@hostname/dbname)
0541c5ae 53 * table => table to store addresses in (must exist)
54 * owner => current user (owner of address data)
55 * ? name => name of address book
56 * ? writeable => set writeable flag (true/false)
57 * ? listing => enable/disable listing
58 * </pre>
59 * The table used should have the following columns:
60 * owner, nickname, firstname, lastname, email, label
61 * The pair (owner,nickname) should be unique (primary key).
62 *
63 * NOTE. This class should not be used directly. Use the
64 * "AddressBook" class instead.
058b79d2 65 *
66 * Three settings that control PDO behavior can be specified in
67 * config/config_local.php if needed:
68 * boolean $disable_pdo SquirrelMail uses PDO by default to access the
69 * user preferences and address book databases, but
70 * setting this to TRUE will cause SquirrelMail to
71 * fall back to using Pear DB instead.
72 * boolean $pdo_show_sql_errors When database errors are encountered,
73 * setting this to TRUE causes the actual
74 * database error to be displayed, otherwise
75 * generic errors are displayed, preventing
76 * internal database information from being
77 * exposed. This should be enabled only for
78 * debugging purposes.
79 * string $pdo_identifier_quote_char By default, SquirrelMail will quote
80 * table and field names in database
81 * queries with what it thinks is the
82 * appropriate quote character for the
83 * database type being used (backtick
84 * for MySQL (and thus MariaDB), double
85 * quotes for all others), but you can
86 * override the character used by
87 * putting it here, or tell SquirrelMail
88 * NOT to quote identifiers by setting
89 * this to "none"
90 *
d6c32258 91 * @package squirrelmail
0541c5ae 92 * @subpackage addressbook
d6c32258 93 */
9b9474d6 94class abook_database extends addressbook_backend {
0541c5ae 95 /**
96 * Backend type
97 * @var string
98 */
9b9474d6 99 var $btype = 'local';
0541c5ae 100 /**
101 * Backend name
102 * @var string
103 */
9b9474d6 104 var $bname = 'database';
62f7daa5 105
0541c5ae 106 /**
107 * Data Source Name (connection description)
108 * @var string
109 */
9b9474d6 110 var $dsn = '';
058b79d2 111
112 /**
113 * Character used to quote database table
114 * and field names
115 * @var string
116 */
117 var $identifier_quote_char = '';
118
0541c5ae 119 /**
120 * Table that stores addresses
121 * @var string
122 */
9b9474d6 123 var $table = '';
0541c5ae 124 /**
125 * Owner name
126 *
127 * Limits list of database entries visible to end user
128 * @var string
129 */
9b9474d6 130 var $owner = '';
0541c5ae 131 /**
132 * Database Handle
133 * @var resource
134 */
9b9474d6 135 var $dbh = false;
0541c5ae 136 /**
137 * Enable/disable writing into address book
138 * @var bool
139 */
9b9474d6 140 var $writeable = true;
0541c5ae 141 /**
142 * Enable/disable address book listing
143 * @var bool
144 */
145 var $listing = true;
62f7daa5 146
9b9474d6 147 /* ========================== Private ======================= */
62f7daa5 148
0541c5ae 149 /**
acb4fcd7 150 * Constructor (PHP5 style, required in some future version of PHP)
0541c5ae 151 * @param array $param address book backend options
152 */
acb4fcd7 153 function __construct($param) {
232fb714 154 $this->sname = _("Personal Address Book");
62f7daa5 155
058b79d2 156 /* test if PDO or Pear DB classes are available and freak out if necessary */
157 global $use_pdo;
158 if (!$use_pdo && !class_exists('DB')) {
6d429ce6 159 // same error also in db_prefs.php
058b79d2 160 $error = _("Could not find or include PHP PDO or PEAR database functions required for the database backend.") . "\n";
161 $error .= sprintf(_("PDO should come preinstalled with PHP version 5.1 or higher. Otherwise, is PEAR installed, and is the include path set correctly to find %s?"), 'DB.php') . "\n";
6d429ce6 162 $error .= _("Please contact your system administrator and report this error.");
163 return $this->set_error($error);
164 }
165
9b9474d6 166 if (is_array($param)) {
62f7daa5 167 if (empty($param['dsn']) ||
168 empty($param['table']) ||
9b9474d6 169 empty($param['owner'])) {
170 return $this->set_error('Invalid parameters');
171 }
62f7daa5 172
91be2362 173 $this->dsn = $param['dsn'];
174 $this->table = $param['table'];
175 $this->owner = $param['owner'];
62f7daa5 176
9b9474d6 177 if (!empty($param['name'])) {
91be2362 178 $this->sname = $param['name'];
9b9474d6 179 }
7902aca2 180
9b9474d6 181 if (isset($param['writeable'])) {
91be2362 182 $this->writeable = $param['writeable'];
9b9474d6 183 }
7902aca2 184
30e9932c 185 if (isset($param['listing'])) {
186 $this->listing = $param['listing'];
187 }
188
058b79d2 189 // figure out identifier quoting (only used for PDO, though we could change that)
190 global $pdo_identifier_quote_char;
191 if (empty($pdo_identifier_quote_char)) {
192 if (strpos($this->dsn, 'mysql') === 0)
193 $this->identifier_quote_char = '`';
194 else
195 $this->identifier_quote_char = '"';
196 } else if ($pdo_identifier_quote_char === 'none')
197 $this->identifier_quote_char = '';
198 else
199 $this->identifier_quote_char = $pdo_identifier_quote_char;
200
201
7902aca2 202 $this->open(true);
9b9474d6 203 }
204 else {
91be2362 205 return $this->set_error('Invalid argument to constructor');
9b9474d6 206 }
207 }
62f7daa5 208
acb4fcd7 209 /**
210 * Constructor (PHP4 style, kept for compatibility reasons)
211 * @param array $param address book backend options
212 */
213 function abook_database($param) {
214 return self::__construct($param);
215 }
62f7daa5 216
0541c5ae 217 /**
e50f5ac2 218 * Open the database.
0541c5ae 219 * @param bool $new new connection if it is true
220 * @return bool
221 */
9b9474d6 222 function open($new = false) {
058b79d2 223 global $use_pdo;
9b9474d6 224 $this->error = '';
62f7daa5 225
9b9474d6 226 /* Return true is file is open and $new is unset */
227 if ($this->dbh && !$new) {
7902aca2 228 return true;
9b9474d6 229 }
62f7daa5 230
9b9474d6 231 /* Close old file, if any */
232 if ($this->dbh) {
233 $this->close();
234 }
62f7daa5 235
058b79d2 236 if ($use_pdo) {
237 // parse and convert DSN to PDO style
466abb9c 238 // Pear's full DSN syntax is one of the following:
239 // phptype(dbsyntax)://username:password@protocol+hostspec/database?option=value
240 // phptype(syntax)://user:pass@protocol(proto_opts)/database
241 //
058b79d2 242 // $matches will contain:
243 // 1: database type
244 // 2: username
245 // 3: password
466abb9c 246 // 4: hostname (and possible port number) OR protocol (and possible protocol options)
247 // 5: database name (and possible options)
248 // 6: port number (moved from match number 4)
249 // 7: options (moved from match number 5)
250 // 8: protocol (instead of hostname)
251 // 9: protocol options (moved from match number 4/8)
252//TODO: do we care about supporting cases where no password is given? (this is a legal DSN, but causes an error below)
058b79d2 253 if (!preg_match('|^(.+)://(.+):(.+)@(.+)/(.+)$|i', $this->dsn, $matches)) {
254 return $this->set_error(_("Could not parse prefs DSN"));
255 }
466abb9c 256 $matches[6] = NULL;
257 $matches[7] = NULL;
258 $matches[8] = NULL;
259 $matches[9] = NULL;
058b79d2 260 if (preg_match('|^(.+):(\d+)$|', $matches[4], $host_port_matches)) {
261 $matches[4] = $host_port_matches[1];
262 $matches[6] = $host_port_matches[2];
466abb9c 263 }
264 if (preg_match('|^(.+?)\((.+)\)$|', $matches[4], $protocol_matches)) {
265 $matches[8] = $protocol_matches[1];
266 $matches[9] = $protocol_matches[2];
267 $matches[4] = NULL;
058b79d2 268 $matches[6] = NULL;
466abb9c 269 }
270//TODO: currently we just ignore options specified on the end of the DSN
271 if (preg_match('|^(.+?)\?(.+)$|', $matches[5], $database_name_options_matches)) {
272 $matches[5] = $database_name_options_matches[1];
273 $matches[7] = $database_name_options_matches[2];
274 }
275 if ($matches[8] === 'unix' && !empty($matches[9]))
276 $pdo_prefs_dsn = $matches[1] . ':unix_socket=' . $matches[9] . ';dbname=' . $matches[5];
277 else
278 $pdo_prefs_dsn = $matches[1] . ':host=' . $matches[4] . (!empty($matches[6]) ? ';port=' . $matches[6] : '') . ';dbname=' . $matches[5];
058b79d2 279 try {
280 $dbh = new PDO($pdo_prefs_dsn, $matches[2], $matches[3]);
281 } catch (Exception $e) {
282 return $this->set_error(sprintf(_("Database error: %s"), $e->getMessage()));
283 }
62f7daa5 284
058b79d2 285 $dbh->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
62f7daa5 286
058b79d2 287 } else {
288 $dbh = DB::connect($this->dsn, true);
289
290 if (DB::isError($dbh)) {
291 return $this->set_error(sprintf(_("Database error: %s"),
292 DB::errorMessage($dbh)));
293 }
46956d06 294
058b79d2 295 /**
296 * field names are lowercased.
297 * We use unquoted identifiers and they use upper case in Oracle
298 */
299 $dbh->setOption('portability', DB_PORTABILITY_LOWERCASE);
300 }
46956d06 301
058b79d2 302 $this->dbh = $dbh;
9b9474d6 303 return true;
304 }
7902aca2 305
0541c5ae 306 /**
307 * Close the file and forget the filehandle
308 */
9b9474d6 309 function close() {
058b79d2 310 global $use_pdo;
311 if ($use_pdo) {
312 $this->dbh = NULL;
313 } else {
314 $this->dbh->disconnect();
315 $this->dbh = false;
316 }
9b9474d6 317 }
7902aca2 318
503c7650 319 /**
320 * Determine internal database field name given one of
321 * the SquirrelMail SM_ABOOK_FIELD_* constants
322 *
323 * @param integer $field The SM_ABOOK_FIELD_* contant to look up
324 *
325 * @return string The desired field name, or the string "ERROR"
326 * if the $field is not understood (the caller
327 * is responsible for handing errors)
328 *
329 */
330 function get_field_name($field) {
331 switch ($field) {
332 case SM_ABOOK_FIELD_NICKNAME:
333 return 'nickname';
334 case SM_ABOOK_FIELD_FIRSTNAME:
335 return 'firstname';
336 case SM_ABOOK_FIELD_LASTNAME:
337 return 'lastname';
338 case SM_ABOOK_FIELD_EMAIL:
339 return 'email';
340 case SM_ABOOK_FIELD_LABEL:
341 return 'label';
342 default:
343 return 'ERROR';
344 }
345 }
346
9b9474d6 347 /* ========================== Public ======================== */
62f7daa5 348
0541c5ae 349 /**
350 * Search the database
46956d06 351 *
352 * Backend supports only * and ? wildcards. Complex eregs are not supported.
353 * Search is case insensitive.
0541c5ae 354 * @param string $expr search expression
46956d06 355 * @return array search results. boolean false on error
0541c5ae 356 */
aa8c4265 357 function search($expr) {
9b9474d6 358 $ret = array();
359 if(!$this->open()) {
7902aca2 360 return false;
9b9474d6 361 }
30e9932c 362
9b9474d6 363 /* To be replaced by advanded search expression parsing */
364 if (is_array($expr)) {
365 return;
366 }
367
327e2d96 368 // don't allow wide search when listing is disabled.
369 if ($expr=='*' && ! $this->listing)
370 return array();
371
46956d06 372 /* lowercase expression in order to make it case insensitive */
373 $expr = strtolower($expr);
374
375 /* escape SQL wildcards */
376 $expr = str_replace('_', '\\_', $expr);
377 $expr = str_replace('%', '\\%', $expr);
378
379 /* Convert wildcards to SQL syntax */
9b9474d6 380 $expr = str_replace('?', '_', $expr);
381 $expr = str_replace('*', '%', $expr);
058b79d2 382
9b9474d6 383 $expr = "%$expr%";
384
058b79d2 385 global $use_pdo, $pdo_show_sql_errors;
386 if ($use_pdo) {
387 if (!($sth = $this->dbh->prepare('SELECT * FROM ' . $this->identifier_quote_char . $this->table . $this->identifier_quote_char . ' WHERE ' . $this->identifier_quote_char . 'owner' . $this->identifier_quote_char . ' = ? AND (LOWER(' . $this->identifier_quote_char . 'firstname' . $this->identifier_quote_char . ') LIKE ? ESCAPE ? OR LOWER(' . $this->identifier_quote_char . 'lastname' . $this->identifier_quote_char . ') LIKE ? ESCAPE ? OR LOWER(' . $this->identifier_quote_char . 'email' . $this->identifier_quote_char . ') LIKE ? ESCAPE ? OR LOWER(' . $this->identifier_quote_char . 'nickname' . $this->identifier_quote_char . ') LIKE ? ESCAPE ?)'))) {
388 if ($pdo_show_sql_errors)
389 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $this->dbh->errorInfo())));
390 else
391 return $this->set_error(sprintf(_("Database error: %s"), _("Could not prepare query")));
392 }
393 if (!($res = $sth->execute(array($this->owner, $expr, '\\', $expr, '\\', $expr, '\\', $expr, '\\')))) {
394 if ($pdo_show_sql_errors)
395 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $sth->errorInfo())));
396 else
397 return $this->set_error(sprintf(_("Database error: %s"), _("Could not execute query")));
398 }
399
400 while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
401 array_push($ret, array('nickname' => $row['nickname'],
402 'name' => $this->fullname($row['firstname'], $row['lastname']),
403 'firstname' => $row['firstname'],
404 'lastname' => $row['lastname'],
405 'email' => $row['email'],
406 'label' => $row['label'],
407 'backend' => $this->bnum,
408 'source' => &$this->sname));
409 }
410
411 } else {
412 $expr = $this->dbh->quoteString($expr);
7902aca2 413
058b79d2 414 /* create escape expression */
415 $escape = 'ESCAPE \'' . $this->dbh->quoteString('\\') . '\'';
416
417 $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND " .
418 "(LOWER(firstname) LIKE '%s' %s " .
419 "OR LOWER(lastname) LIKE '%s' %s " .
420 "OR LOWER(email) LIKE '%s' %s " .
421 "OR LOWER(nickname) LIKE '%s' %s)",
422 $this->table, $this->owner, $expr, $escape, $expr, $escape,
423 $expr, $escape, $expr, $escape);
424 $res = $this->dbh->query($query);
425
426 if (DB::isError($res)) {
427 return $this->set_error(sprintf(_("Database error: %s"),
428 DB::errorMessage($res)));
429 }
430
431 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
432 array_push($ret, array('nickname' => $row['nickname'],
433 'name' => $this->fullname($row['firstname'], $row['lastname']),
434 'firstname' => $row['firstname'],
435 'lastname' => $row['lastname'],
436 'email' => $row['email'],
437 'label' => $row['label'],
438 'backend' => $this->bnum,
439 'source' => &$this->sname));
440 }
9b9474d6 441 }
442 return $ret;
443 }
62f7daa5 444
0541c5ae 445 /**
503c7650 446 * Lookup an address by the indicated field.
447 *
448 * @param string $value The value to look up
449 * @param integer $field The field to look in, should be one
450 * of the SM_ABOOK_FIELD_* constants
451 * defined in include/constants.php
452 * (OPTIONAL; defaults to nickname field)
bf55ebab 453 * NOTE: uniqueness is only guaranteed
454 * when the nickname field is used here;
455 * otherwise, the first matching address
456 * is returned.
503c7650 457 *
458 * @return array Array with lookup results when the value
459 * was found, an empty array if the value was
460 * not found.
461 *
0541c5ae 462 */
503c7650 463 function lookup($value, $field=SM_ABOOK_FIELD_NICKNAME) {
464 if (empty($value)) {
7902aca2 465 return array();
9b9474d6 466 }
62f7daa5 467
503c7650 468 $value = strtolower($value);
7902aca2 469
9b9474d6 470 if (!$this->open()) {
7902aca2 471 return false;
9b9474d6 472 }
62f7daa5 473
26bd3897 474 $db_field = $this->get_field_name($field);
475 if ($db_field == 'ERROR') {
476 return $this->set_error(sprintf(_("Unknown field name: %s"), $field));
477 }
478
058b79d2 479 global $use_pdo, $pdo_show_sql_errors;
480 if ($use_pdo) {
481 if (!($sth = $this->dbh->prepare('SELECT * FROM ' . $this->identifier_quote_char . $this->table . $this->identifier_quote_char . ' WHERE ' . $this->identifier_quote_char . 'owner' . $this->identifier_quote_char . ' = ? AND LOWER(' . $this->identifier_quote_char . $db_field . $this->identifier_quote_char . ') = ?'))) {
482 if ($pdo_show_sql_errors)
483 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $this->dbh->errorInfo())));
484 else
485 return $this->set_error(sprintf(_("Database error: %s"), _("Could not prepare query")));
486 }
487 if (!($res = $sth->execute(array($this->owner, $value)))) {
488 if ($pdo_show_sql_errors)
489 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $sth->errorInfo())));
490 else
491 return $this->set_error(sprintf(_("Database error: %s"), _("Could not execute query")));
492 }
7902aca2 493
058b79d2 494 if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
495 return array('nickname' => $row['nickname'],
496 'name' => $this->fullname($row['firstname'], $row['lastname']),
497 'firstname' => $row['firstname'],
498 'lastname' => $row['lastname'],
499 'email' => $row['email'],
500 'label' => $row['label'],
501 'backend' => $this->bnum,
502 'source' => &$this->sname);
503 }
7902aca2 504
058b79d2 505 } else {
506 $query = sprintf("SELECT * FROM %s WHERE owner = '%s' AND LOWER(%s) = '%s'",
507 $this->table, $this->owner, $db_field,
508 $this->dbh->quoteString($value));
509
510 $res = $this->dbh->query($query);
7902aca2 511
058b79d2 512 if (DB::isError($res)) {
513 return $this->set_error(sprintf(_("Database error: %s"),
514 DB::errorMessage($res)));
515 }
516
517 if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
518 return array('nickname' => $row['nickname'],
519 'name' => $this->fullname($row['firstname'], $row['lastname']),
520 'firstname' => $row['firstname'],
521 'lastname' => $row['lastname'],
522 'email' => $row['email'],
523 'label' => $row['label'],
524 'backend' => $this->bnum,
525 'source' => &$this->sname);
526 }
9b9474d6 527 }
058b79d2 528
9b9474d6 529 return array();
530 }
531
0541c5ae 532 /**
e50f5ac2 533 * List all addresses
0541c5ae 534 * @return array search results
535 */
aa8c4265 536 function list_addr() {
9b9474d6 537 $ret = array();
538 if (!$this->open()) {
7902aca2 539 return false;
9b9474d6 540 }
62f7daa5 541
91e0dccc 542 if(isset($this->listing) && !$this->listing) {
543 return array();
544 }
30e9932c 545
7902aca2 546
058b79d2 547 global $use_pdo, $pdo_show_sql_errors;
548 if ($use_pdo) {
549 if (!($sth = $this->dbh->prepare('SELECT * FROM ' . $this->identifier_quote_char . $this->table . $this->identifier_quote_char . ' WHERE ' . $this->identifier_quote_char . 'owner' . $this->identifier_quote_char . ' = ?'))) {
550 if ($pdo_show_sql_errors)
551 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $this->dbh->errorInfo())));
552 else
553 return $this->set_error(sprintf(_("Database error: %s"), _("Could not prepare query")));
554 }
555 if (!($res = $sth->execute(array($this->owner)))) {
556 if ($pdo_show_sql_errors)
557 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $sth->errorInfo())));
558 else
559 return $this->set_error(sprintf(_("Database error: %s"), _("Could not execute query")));
560 }
7902aca2 561
058b79d2 562 while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
563 array_push($ret, array('nickname' => $row['nickname'],
564 'name' => $this->fullname($row['firstname'], $row['lastname']),
565 'firstname' => $row['firstname'],
566 'lastname' => $row['lastname'],
567 'email' => $row['email'],
568 'label' => $row['label'],
569 'backend' => $this->bnum,
570 'source' => &$this->sname));
571 }
572 } else {
573 $query = sprintf("SELECT * FROM %s WHERE owner='%s'",
574 $this->table, $this->owner);
62f7daa5 575
058b79d2 576 $res = $this->dbh->query($query);
577
578 if (DB::isError($res)) {
579 return $this->set_error(sprintf(_("Database error: %s"),
580 DB::errorMessage($res)));
581 }
7902aca2 582
058b79d2 583 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
584 array_push($ret, array('nickname' => $row['nickname'],
585 'name' => $this->fullname($row['firstname'], $row['lastname']),
586 'firstname' => $row['firstname'],
587 'lastname' => $row['lastname'],
588 'email' => $row['email'],
589 'label' => $row['label'],
590 'backend' => $this->bnum,
591 'source' => &$this->sname));
592 }
9b9474d6 593 }
058b79d2 594
9b9474d6 595 return $ret;
596 }
7902aca2 597
0541c5ae 598 /**
599 * Add address
600 * @param array $userdata added data
601 * @return bool
602 */
9b9474d6 603 function add($userdata) {
604 if (!$this->writeable) {
35235328 605 return $this->set_error(_("Address book is read-only"));
9b9474d6 606 }
7902aca2 607
9b9474d6 608 if (!$this->open()) {
7902aca2 609 return false;
9b9474d6 610 }
62f7daa5 611
058b79d2 612 // NB: if you want to check for some unwanted characters
613 // or other problems, do so here like this:
614 // TODO: Should pull all validation code out into a separate function
615 //if (strpos($userdata['nickname'], ' ')) {
616 // return $this->set_error(_("Nickname contains illegal characters"));
617 //}
618
9b9474d6 619 /* See if user exist already */
620 $ret = $this->lookup($userdata['nickname']);
621 if (!empty($ret)) {
058b79d2 622 return $this->set_error(sprintf(_("User \"%s\" already exists"), $ret['nickname']));
623 }
624
625 global $use_pdo, $pdo_show_sql_errors;
626 if ($use_pdo) {
627 if (!($sth = $this->dbh->prepare('INSERT INTO ' . $this->identifier_quote_char . $this->table . $this->identifier_quote_char . ' (' . $this->identifier_quote_char . 'owner' . $this->identifier_quote_char . ', ' . $this->identifier_quote_char . 'nickname' . $this->identifier_quote_char . ', ' . $this->identifier_quote_char . 'firstname' . $this->identifier_quote_char . ', ' . $this->identifier_quote_char . 'lastname' . $this->identifier_quote_char . ', ' . $this->identifier_quote_char . 'email' . $this->identifier_quote_char . ', ' . $this->identifier_quote_char . 'label' . $this->identifier_quote_char . ') VALUES (?, ?, ?, ?, ?, ?)'))) {
628 if ($pdo_show_sql_errors)
629 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $this->dbh->errorInfo())));
630 else
631 return $this->set_error(sprintf(_("Database error: %s"), _("Could not prepare query")));
632 }
633 if (!($res = $sth->execute(array($this->owner, $userdata['nickname'], $userdata['firstname'], (!empty($userdata['lastname']) ? $userdata['lastname'] : ''), $userdata['email'], (!empty($userdata['label']) ? $userdata['label'] : ''))))) {
634 if ($pdo_show_sql_errors)
635 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $sth->errorInfo())));
636 else
637 return $this->set_error(sprintf(_("Database error: %s"), _("Could not execute query")));
638 }
639 } else {
640 /* Create query */
641 $query = sprintf("INSERT INTO %s (owner, nickname, firstname, " .
642 "lastname, email, label) VALUES('%s','%s','%s'," .
643 "'%s','%s','%s')",
644 $this->table, $this->owner,
645 $this->dbh->quoteString($userdata['nickname']),
646 $this->dbh->quoteString($userdata['firstname']),
647 $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')),
648 $this->dbh->quoteString($userdata['email']),
649 $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')) );
650
651 /* Do the insert */
652 $r = $this->dbh->simpleQuery($query);
653
654 /* Check for errors */
655 if (DB::isError($r)) {
656 return $this->set_error(sprintf(_("Database error: %s"),
657 DB::errorMessage($r)));
658 }
9b9474d6 659 }
660
46956d06 661 return true;
9b9474d6 662 }
7902aca2 663
0541c5ae 664 /**
eee5aa69 665 * Deletes address book entries
666 * @param array $alias aliases that have to be deleted. numerical
667 * array with nickname values
0541c5ae 668 * @return bool
669 */
9b9474d6 670 function remove($alias) {
671 if (!$this->writeable) {
35235328 672 return $this->set_error(_("Address book is read-only"));
9b9474d6 673 }
7902aca2 674
9b9474d6 675 if (!$this->open()) {
7902aca2 676 return false;
9b9474d6 677 }
62f7daa5 678
058b79d2 679 global $use_pdo, $pdo_show_sql_errors;
680 if ($use_pdo) {
681 $sepstr = '';
682 $where_clause = '';
683 $where_clause_args = array();
684 while (list($undef, $nickname) = each($alias)) {
685 $where_clause .= $sepstr . $this->identifier_quote_char . 'nickname' . $this->identifier_quote_char . ' = ?';
686 $where_clause_args[] = $nickname;
687 $sepstr = ' OR ';
688 }
689 if (!($sth = $this->dbh->prepare('DELETE FROM ' . $this->identifier_quote_char . $this->table . $this->identifier_quote_char . ' WHERE ' . $this->identifier_quote_char . 'owner' . $this->identifier_quote_char . ' = ? AND (' . $where_clause . ')'))) {
690 if ($pdo_show_sql_errors)
691 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $this->dbh->errorInfo())));
692 else
693 return $this->set_error(sprintf(_("Database error: %s"), _("Could not prepare query")));
694 }
b07dbd13 695 array_unshift($where_clause_args, $this->owner);
696 if (!($res = $sth->execute($where_clause_args))) {
058b79d2 697 if ($pdo_show_sql_errors)
698 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $sth->errorInfo())));
699 else
700 return $this->set_error(sprintf(_("Database error: %s"), _("Could not execute query")));
701 }
702 } else {
703 /* Create query */
704 $query = sprintf("DELETE FROM %s WHERE owner='%s' AND (",
705 $this->table, $this->owner);
706
707 $sepstr = '';
708 while (list($undef, $nickname) = each($alias)) {
709 $query .= sprintf("%s nickname='%s' ", $sepstr,
710 $this->dbh->quoteString($nickname));
711 $sepstr = 'OR';
712 }
713 $query .= ')';
7902aca2 714
058b79d2 715 /* Delete entry */
716 $r = $this->dbh->simpleQuery($query);
7902aca2 717
058b79d2 718 /* Check for errors */
719 if (DB::isError($r)) {
720 return $this->set_error(sprintf(_("Database error: %s"),
721 DB::errorMessage($r)));
722 }
46956d06 723 }
058b79d2 724
46956d06 725 return true;
9b9474d6 726 }
7902aca2 727
0541c5ae 728 /**
729 * Modify address
730 * @param string $alias modified alias
731 * @param array $userdata new data
732 * @return bool
733 */
9b9474d6 734 function modify($alias, $userdata) {
735 if (!$this->writeable) {
35235328 736 return $this->set_error(_("Address book is read-only"));
9b9474d6 737 }
7902aca2 738
9b9474d6 739 if (!$this->open()) {
7902aca2 740 return false;
9b9474d6 741 }
62f7daa5 742
058b79d2 743 // NB: if you want to check for some unwanted characters
744 // or other problems, do so here like this:
745 // TODO: Should pull all validation code out into a separate function
746 //if (strpos($userdata['nickname'], ' ')) {
747 // return $this->set_error(_("Nickname contains illegal characters"));
748 //}
749
9b9474d6 750 /* See if user exist */
751 $ret = $this->lookup($alias);
752 if (empty($ret)) {
2706a0b1 753 return $this->set_error(sprintf(_("User \"%s\" does not exist"),$alias));
9b9474d6 754 }
755
46956d06 756 /* make sure that new nickname is not used */
757 if (strtolower($alias) != strtolower($userdata['nickname'])) {
758 /* same check as in add() */
759 $ret = $this->lookup($userdata['nickname']);
760 if (!empty($ret)) {
761 $error = sprintf(_("User '%s' already exist."), $ret['nickname']);
762 return $this->set_error($error);
763 }
764 }
765
058b79d2 766 global $use_pdo, $pdo_show_sql_errors;
767 if ($use_pdo) {
768 if (!($sth = $this->dbh->prepare('UPDATE ' . $this->identifier_quote_char . $this->table . $this->identifier_quote_char . ' SET ' . $this->identifier_quote_char . 'nickname' . $this->identifier_quote_char . ' = ?, ' . $this->identifier_quote_char . 'firstname' . $this->identifier_quote_char . ' = ?, ' . $this->identifier_quote_char . 'lastname' . $this->identifier_quote_char . ' = ?, ' . $this->identifier_quote_char . 'email' . $this->identifier_quote_char . ' = ?, ' . $this->identifier_quote_char . 'label' . $this->identifier_quote_char . ' = ? WHERE ' . $this->identifier_quote_char . 'owner' . $this->identifier_quote_char . ' = ? AND ' . $this->identifier_quote_char . 'nickname' . $this->identifier_quote_char . ' = ?'))) {
769 if ($pdo_show_sql_errors)
770 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $this->dbh->errorInfo())));
771 else
772 return $this->set_error(sprintf(_("Database error: %s"), _("Could not prepare query")));
773 }
774 if (!($res = $sth->execute(array($userdata['nickname'], $userdata['firstname'], (!empty($userdata['lastname']) ? $userdata['lastname'] : ''), $userdata['email'], (!empty($userdata['label']) ? $userdata['label'] : ''), $this->owner, $alias)))) {
775 if ($pdo_show_sql_errors)
776 return $this->set_error(sprintf(_("Database error: %s"), implode(' - ', $sth->errorInfo())));
777 else
778 return $this->set_error(sprintf(_("Database error: %s"), _("Could not execute query")));
779 }
780 } else {
781 /* Create query */
782 $query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ".
783 "lastname='%s', email='%s', label='%s' ".
784 "WHERE owner='%s' AND nickname='%s'",
785 $this->table,
786 $this->dbh->quoteString($userdata['nickname']),
787 $this->dbh->quoteString($userdata['firstname']),
788 $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')),
789 $this->dbh->quoteString($userdata['email']),
790 $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')),
791 $this->owner,
792 $this->dbh->quoteString($alias) );
793
794 /* Do the insert */
795 $r = $this->dbh->simpleQuery($query);
796
797 /* Check for errors */
798 if (DB::isError($r)) {
799 return $this->set_error(sprintf(_("Database error: %s"),
800 DB::errorMessage($r)));
801 }
46956d06 802 }
058b79d2 803
46956d06 804 return true;
9b9474d6 805 }
806} /* End of class abook_database */
7902aca2 807
c9fcea56 808// vim: et ts=4