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-2019 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 (PHP5 style, required in some future version of PHP)
 151      * @param array $param address book backend options
 152      */
 153     function __construct($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      * 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     }
 216
 217     /**
 218      * Open the database.
 219      * @param bool $new new connection if it is true
 220      * @return bool
 221      */
 222     function open($new = false) {
 223         global $use_pdo;
 224         $this->error = '';
 225
 226         /* Return true is file is open and $new is unset */
 227         if ($this->dbh && !$new) {
 228             return true;
 229         }
 230
 231         /* Close old file, if any */
 232         if ($this->dbh) {
 233             $this->close();
 234         }
 235
 236         if ($use_pdo) {
 237             // parse and convert DSN to PDO style
 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             //
 242             // $matches will contain:
 243             // 1: database type
 244             // 2: username
 245             // 3: password
 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)
 253             if (!preg_match('|^(.+)://(.+):(.+)@(.+)/(.+)$|i', $this->dsn, $matches)) {
 254                 return $this->set_error(_("Could not parse prefs DSN"));
 255             }
 256             $matches[6] = NULL;
 257             $matches[7] = NULL;
 258             $matches[8] = NULL;
 259             $matches[9] = NULL;
 260             if (preg_match('|^(.+):(\d+)$|', $matches[4], $host_port_matches)) {
 261                 $matches[4] = $host_port_matches[1];
 262                 $matches[6] = $host_port_matches[2];
 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;
 268                 $matches[6] = NULL;
 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];
 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             }
 284
 285             $dbh->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
 286
 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             }
 294
 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         }
 301
 302         $this->dbh = $dbh;
 303         return true;
 304     }
 305
 306     /**
 307      * Close the file and forget the filehandle
 308      */
 309     function close() {
 310         global $use_pdo;
 311         if ($use_pdo) {
 312             $this->dbh = NULL;
 313         } else {
 314             $this->dbh->disconnect();
 315             $this->dbh = false;
 316         }
 317     }
 318
 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
 347     /* ========================== Public ======================== */
 348
 349     /**
 350      * Search the database
 351      *
 352      * Backend supports only * and ? wildcards. Complex eregs are not supported.
 353      * Search is case insensitive.
 354      * @param string $expr search expression
 355      * @return array search results. boolean false on error
 356      */
 357     function search($expr) {
 358         $ret = array();
 359         if(!$this->open()) {
 360             return false;
 361         }
 362
 363         /* To be replaced by advanded search expression parsing */
 364         if (is_array($expr)) {
 365             return;
 366         }
 367
 368         // don't allow wide search when listing is disabled.
 369         if ($expr=='*' && ! $this->listing)
 370             return array();
 371
 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  */
 380         $expr = str_replace('?', '_', $expr);
 381         $expr = str_replace('*', '%', $expr);
 382
 383         $expr = "%$expr%";
 384
 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);
 413
 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             }
 441         }
 442         return $ret;
 443     }
 444
 445     /**
 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)
 453      *                       NOTE: uniqueness is only guaranteed
 454      *                       when the nickname field is used here;
 455      *                       otherwise, the first matching address
 456      *                       is returned.
 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      *
 462      */
 463     function lookup($value, $field=SM_ABOOK_FIELD_NICKNAME) {
 464         if (empty($value)) {
 465             return array();
 466         }
 467
 468         $value = strtolower($value);
 469
 470         if (!$this->open()) {
 471             return false;
 472         }
 473
 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
 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             }
 493
 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             }
 504
 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);
 511
 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             }
 527         }
 528
 529         return array();
 530     }
 531
 532     /**
 533      * List all addresses
 534      * @return array search results
 535      */
 536     function list_addr() {
 537         $ret = array();
 538         if (!$this->open()) {
 539             return false;
 540         }
 541
 542         if(isset($this->listing) && !$this->listing) {
 543             return array();
 544         }
 545
 546
 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             }
 561
 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);
 575
 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             }
 582
 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             }
 593         }
 594
 595         return $ret;
 596     }
 597
 598     /**
 599      * Add address
 600      * @param array $userdata added data
 601      * @return bool
 602      */
 603     function add($userdata) {
 604         if (!$this->writeable) {
 605             return $this->set_error(_("Address book is read-only"));
 606         }
 607
 608         if (!$this->open()) {
 609             return false;
 610         }
 611
 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
 619         /* See if user exist already */
 620         $ret = $this->lookup($userdata['nickname']);
 621         if (!empty($ret)) {
 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             }
 659         }
 660
 661          return true;
 662     }
 663
 664     /**
 665      * Deletes address book entries
 666      * @param array $alias aliases that have to be deleted. numerical
 667      *  array with nickname values
 668      * @return bool
 669      */
 670     function remove($alias) {
 671         if (!$this->writeable) {
 672             return $this->set_error(_("Address book is read-only"));
 673         }
 674
 675         if (!$this->open()) {
 676             return false;
 677         }
 678
 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             }
 695             array_unshift($where_clause_args, $this->owner);
 696             if (!($res = $sth->execute($where_clause_args))) {
 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 .= ')';
 714
 715             /* Delete entry */
 716             $r = $this->dbh->simpleQuery($query);
 717
 718             /* Check for errors */
 719             if (DB::isError($r)) {
 720                 return $this->set_error(sprintf(_("Database error: %s"),
 721                                                 DB::errorMessage($r)));
 722             }
 723         }
 724
 725         return true;
 726     }
 727
 728     /**
 729      * Modify address
 730      * @param string $alias modified alias
 731      * @param array $userdata new data
 732      * @return bool
 733      */
 734     function modify($alias, $userdata) {
 735         if (!$this->writeable) {
 736             return $this->set_error(_("Address book is read-only"));
 737         }
 738
 739         if (!$this->open()) {
 740             return false;
 741         }
 742
 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
 750          /* See if user exist */
 751         $ret = $this->lookup($alias);
 752         if (empty($ret)) {
 753             return $this->set_error(sprintf(_("User \"%s\" does not exist"),$alias));
 754         }
 755
 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
 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             }
 802         }
 803
 804         return true;
 805     }
 806 } /* End of class abook_database */
 807
 808 // vim: et ts=4