functions/abook_database.php

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