6 * This contains functions for manipulating user preferences
7 * stored in a database, accessed through the Pear DB layer
8 * or PDO, the latter taking precedence if available.
12 * The preferences table should have three columns:
17 * CREATE TABLE userprefs (user CHAR(128) NOT NULL DEFAULT '',
18 * prefkey CHAR(64) NOT NULL DEFAULT '',
19 * prefval BLOB NOT NULL DEFAULT '',
20 * primary key (user,prefkey));
22 * Configuration of databasename, username and password is done
23 * by using conf.pl or the administrator plugin
25 * Three settings that control PDO behavior can be specified in
26 * config/config_local.php if needed:
27 * boolean $disable_pdo SquirrelMail uses PDO by default to access the
28 * user preferences and address book databases, but
29 * setting this to TRUE will cause SquirrelMail to
30 * fall back to using Pear DB instead.
31 * boolean $pdo_show_sql_errors When database errors are encountered,
32 * setting this to TRUE causes the actual
33 * database error to be displayed, otherwise
34 * generic errors are displayed, preventing
35 * internal database information from being
36 * exposed. This should be enabled only for
38 * string $pdo_identifier_quote_char By default, SquirrelMail will quote
39 * table and field names in database
40 * queries with what it thinks is the
41 * appropriate quote character for the
42 * database type being used (backtick
43 * for MySQL (and thus MariaDB), double
44 * quotes for all others), but you can
45 * override the character used by
46 * putting it here, or tell SquirrelMail
47 * NOT to quote identifiers by setting
50 * @copyright 1999-2017 The SquirrelMail Project Team
51 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
53 * @package squirrelmail
59 if (!defined('SM_PATH')) define('SM_PATH','../');
61 /** Unknown database */
62 define('SMDB_UNKNOWN', 0);
64 define('SMDB_MYSQL', 1);
66 define('SMDB_PGSQL', 2);
69 * Needs either PDO or the DB functions
70 * Don't display errors here. (no code execution in functions/*.php).
71 * will handle error in dbPrefs class.
73 global $use_pdo, $disable_pdo;
74 if (empty($disable_pdo) && class_exists('PDO'))
80 @include_once
('DB.php');
82 global $prefs_are_cached, $prefs_cache;
87 function cachePrefValues($username) {
88 global $prefs_are_cached, $prefs_cache;
90 sqgetGlobalVar('prefs_are_cached', $prefs_are_cached, SQ_SESSION
);
91 if ($prefs_are_cached) {
92 sqgetGlobalVar('prefs_cache', $prefs_cache, SQ_SESSION
);
96 sqsession_unregister('prefs_cache');
97 sqsession_unregister('prefs_are_cached');
100 if(isset($db->error
)) {
101 printf( _("Preference database error (%s). Exiting abnormally"),
106 $db->fillPrefsCache($username);
107 if (isset($db->error
)) {
108 printf( _("Preference database error (%s). Exiting abnormally"),
113 $prefs_are_cached = true;
115 sqsession_register($prefs_cache, 'prefs_cache');
116 sqsession_register($prefs_are_cached, 'prefs_are_cached');
120 * Class used to handle connections to prefs database and operations with preferences
122 * @package squirrelmail
129 * Table used to store preferences
132 var $table = 'userprefs';
135 * Field used to store owner of preference
138 var $user_field = 'user';
141 * Field used to store preference name
144 var $key_field = 'prefkey';
147 * Field used to store preference value
150 var $val_field = 'prefval';
153 * Database connection object
165 * Database type (SMDB_* constants)
166 * Is used in setKey().
169 var $db_type = SMDB_UNKNOWN
;
172 * Character used to quote database table
176 var $identifier_quote_char = '';
179 * Default preferences
182 var $default = Array('theme_default' => 0,
183 'include_self_reply_all' => '0',
184 'do_not_reply_to_self' => '1',
185 'show_html_default' => '0');
188 * Preference owner field size
192 var $user_size = 128;
195 * Preference key field size
202 * Preference value field size
206 var $val_size = 65536;
211 * Constructor (PHP5 style, required in some future version of PHP)
212 * initialize the default preferences array.
215 function __construct() {
216 // Try and read the default preferences file.
217 $default_pref = SM_PATH
. 'config/default_pref';
218 if (@file_exists
($default_pref)) {
219 if ($file = @fopen
($default_pref, 'r')) {
220 while (!feof($file)) {
221 $pref = fgets($file, 1024);
222 $i = strpos($pref, '=');
224 $this->default[trim(substr($pref, 0, $i))] = trim(substr($pref, $i +
1));
233 * Constructor (PHP4 style, kept for compatibility reasons)
234 * initialize the default preferences array.
242 * initialize DB connection object
244 * @return boolean true, if object is initialized
248 global $prefs_dsn, $prefs_table, $use_pdo, $pdo_identifier_quote_char;
249 global $prefs_user_field, $prefs_key_field, $prefs_val_field;
250 global $prefs_user_size, $prefs_key_size, $prefs_val_size;
252 /* test if PDO or Pear DB classes are available and freak out if necessary */
253 if (!$use_pdo && !class_exists('DB')) {
254 // same error also in abook_database.php
255 $error = _("Could not find or include PHP PDO or PEAR database functions required for the database backend.") . "\n";
256 $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";
257 $error .= _("Please contact your system administrator and report this error.");
261 if(isset($this->dbh
)) {
265 if (strpos($prefs_dsn, 'mysql') === 0) {
266 $this->db_type
= SMDB_MYSQL
;
267 } else if (strpos($prefs_dsn, 'pgsql') === 0) {
268 $this->db_type
= SMDB_PGSQL
;
271 // figure out identifier quoting (only used for PDO, though we could change that)
272 if (empty($pdo_identifier_quote_char)) {
273 if ($this->db_type
== SMDB_MYSQL
)
274 $this->identifier_quote_char
= '`';
276 $this->identifier_quote_char
= '"';
277 } else if ($pdo_identifier_quote_char === 'none')
278 $this->identifier_quote_char
= '';
280 $this->identifier_quote_char
= $pdo_identifier_quote_char;
282 if (!empty($prefs_table)) {
283 $this->table
= $prefs_table;
285 if (!empty($prefs_user_field)) {
286 $this->user_field
= $prefs_user_field;
289 // the default user field is "user", which in PostgreSQL
290 // is an identifier and causes errors if not escaped
292 if ($this->db_type
== SMDB_PGSQL
) {
293 $this->user_field
= '"' . $this->user_field
. '"';
296 if (!empty($prefs_key_field)) {
297 $this->key_field
= $prefs_key_field;
299 if (!empty($prefs_val_field)) {
300 $this->val_field
= $prefs_val_field;
302 if (!empty($prefs_user_size)) {
303 $this->user_size
= (int) $prefs_user_size;
305 if (!empty($prefs_key_size)) {
306 $this->key_size
= (int) $prefs_key_size;
308 if (!empty($prefs_val_size)) {
309 $this->val_size
= (int) $prefs_val_size;
312 // connect, create database connection object
315 // parse and convert DSN to PDO style
316 // Pear's full DSN syntax is one of the following:
317 // phptype(dbsyntax)://username:password@protocol+hostspec/database?option=value
318 // phptype(syntax)://user:pass@protocol(proto_opts)/database
320 // $matches will contain:
324 // 4: hostname (and possible port number) OR protocol (and possible protocol options)
325 // 5: database name (and possible options)
326 // 6: port number (moved from match number 4)
327 // 7: options (moved from match number 5)
328 // 8: protocol (instead of hostname)
329 // 9: protocol options (moved from match number 4/8)
330 //TODO: do we care about supporting cases where no password is given? (this is a legal DSN, but causes an error below)
331 if (!preg_match('|^(.+)://(.+):(.+)@(.+)/(.+)$|i', $prefs_dsn, $matches)) {
332 $this->error
= _("Could not parse prefs DSN");
339 if (preg_match('|^(.+):(\d+)$|', $matches[4], $host_port_matches)) {
340 $matches[4] = $host_port_matches[1];
341 $matches[6] = $host_port_matches[2];
343 if (preg_match('|^(.+?)\((.+)\)$|', $matches[4], $protocol_matches)) {
344 $matches[8] = $protocol_matches[1];
345 $matches[9] = $protocol_matches[2];
349 //TODO: currently we just ignore options specified on the end of the DSN
350 if (preg_match('|^(.+?)\?(.+)$|', $matches[5], $database_name_options_matches)) {
351 $matches[5] = $database_name_options_matches[1];
352 $matches[7] = $database_name_options_matches[2];
354 if ($matches[8] === 'unix' && !empty($matches[9]))
355 $pdo_prefs_dsn = $matches[1] . ':unix_socket=' . $matches[9] . ';dbname=' . $matches[5];
357 $pdo_prefs_dsn = $matches[1] . ':host=' . $matches[4] . (!empty($matches[6]) ?
';port=' . $matches[6] : '') . ';dbname=' . $matches[5];
359 $dbh = new PDO($pdo_prefs_dsn, $matches[2], $matches[3]);
360 } catch (Exception
$e) {
361 $this->error
= $e->getMessage();
365 $dbh = DB
::connect($prefs_dsn, true);
367 if(DB
::isError($dbh)) {
368 $this->error
= DB
::errorMessage($dbh);
378 * Function used to handle database connection errors
380 * @param object PEAR Error object
383 function failQuery($res = NULL) {
386 printf(_("Preference database error (%s). Exiting abnormally"),
389 printf(_("Preference database error (%s). Exiting abnormally"),
390 ($use_pdo ?
implode(' - ', $res->errorInfo()) : DB
::errorMessage($res)));
396 * Get user's prefs setting
398 * @param string $user user name
399 * @param string $key preference name
400 * @param mixed $default (since 1.2.5) default value
402 * @return mixed preference value
405 function getKey($user, $key, $default = '') {
408 $temp = array(&$user, &$key);
409 $result = do_hook('get_pref_override', $temp);
410 if (is_null($result)) {
411 cachePrefValues($user);
413 if (isset($prefs_cache[$key])) {
414 $result = $prefs_cache[$key];
416 //FIXME: is there a justification for having two prefs hooks so close? who uses them?
417 $temp = array(&$user, &$key);
418 $result = do_hook('get_pref', $temp);
419 if (is_null($result)) {
420 if (isset($this->default[$key])) {
421 $result = $this->default[$key];
432 * Delete user's prefs setting
434 * @param string $user user name
435 * @param string $key preference name
440 function deleteKey($user, $key) {
441 global $prefs_cache, $use_pdo, $pdo_show_sql_errors;
443 if (!$this->open()) {
447 if (!($sth = $this->dbh
->prepare('DELETE FROM ' . $this->identifier_quote_char
. $this->table
. $this->identifier_quote_char
. ' WHERE ' . $this->identifier_quote_char
. $this->user_field
. $this->identifier_quote_char
. ' = ? AND ' . $this->identifier_quote_char
. $this->key_field
. $this->identifier_quote_char
. ' = ?'))) {
448 if ($pdo_show_sql_errors)
449 $this->error
= implode(' - ', $this->dbh
->errorInfo());
451 $this->error
= _("Could not prepare query");
454 if (!($res = $sth->execute(array($user, $key)))) {
455 if ($pdo_show_sql_errors)
456 $this->error
= implode(' - ', $sth->errorInfo());
458 $this->error
= _("Could not execute query");
462 $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
465 $this->dbh
->quoteString($user),
467 $this->dbh
->quoteString($key));
469 $res = $this->dbh
->simpleQuery($query);
470 if(DB
::isError($res)) {
471 $this->failQuery($res);
475 unset($prefs_cache[$key]);
481 * Set user's preference
483 * @param string $user user name
484 * @param string $key preference name
485 * @param mixed $value preference value
490 function setKey($user, $key, $value) {
491 global $use_pdo, $pdo_show_sql_errors;
492 if (!$this->open()) {
497 * Check if username fits into db field
499 if (strlen($user) > $this->user_size
) {
500 $this->error
= "Oversized username value."
501 ." Your preferences can't be saved."
502 ." See the administrator's manual or contact your system administrator.";
505 * Debugging function. Can be used to log all issues that trigger
506 * oversized field errors. Function should be enabled in all three
507 * strlen checks. See http://www.php.net/error-log
509 // error_log($user.'|'.$key.'|'.$value."\n",3,'/tmp/oversized_log');
512 $this->failQuery(null);
515 * Check if preference key fits into db field
517 if (strlen($key) > $this->key_size
) {
518 $err_msg = "Oversized user's preference key."
519 ." Some preferences were not saved."
520 ." See the administrator's manual or contact your system administrator.";
521 // error is not fatal. Only some preference is not saved.
522 trigger_error($err_msg,E_USER_WARNING
);
526 * Check if preference value fits into db field
528 if (strlen($value) > $this->val_size
) {
529 $err_msg = "Oversized user's preference value."
530 ." Some preferences were not saved."
531 ." See the administrator's manual or contact your system administrator.";
532 // error is not fatal. Only some preference is not saved.
533 trigger_error($err_msg,E_USER_WARNING
);
538 if ($this->db_type
== SMDB_MYSQL
) {
540 if (!($sth = $this->dbh
->prepare('REPLACE INTO ' . $this->identifier_quote_char
. $this->table
. $this->identifier_quote_char
. ' (' . $this->identifier_quote_char
. $this->user_field
. $this->identifier_quote_char
. ', ' . $this->identifier_quote_char
. $this->key_field
. $this->identifier_quote_char
. ', ' . $this->identifier_quote_char
. $this->val_field
. $this->identifier_quote_char
. ') VALUES (?, ?, ?)'))) {
541 if ($pdo_show_sql_errors)
542 $this->error
= implode(' - ', $this->dbh
->errorInfo());
544 $this->error
= _("Could not prepare query");
547 if (!($res = $sth->execute(array($user, $key, $value)))) {
548 if ($pdo_show_sql_errors)
549 $this->error
= implode(' - ', $sth->errorInfo());
551 $this->error
= _("Could not execute query");
555 $query = sprintf("REPLACE INTO %s (%s, %s, %s) ".
556 "VALUES('%s','%s','%s')",
561 $this->dbh
->quoteString($user),
562 $this->dbh
->quoteString($key),
563 $this->dbh
->quoteString($value));
565 $res = $this->dbh
->simpleQuery($query);
566 if(DB
::isError($res)) {
567 $this->failQuery($res);
570 } elseif ($this->db_type
== SMDB_PGSQL
) {
572 if ($this->dbh
->exec('BEGIN TRANSACTION') === FALSE) {
573 if ($pdo_show_sql_errors)
574 $this->error
= implode(' - ', $this->dbh
->errorInfo());
576 $this->error
= _("Could not execute query");
579 if (!($sth = $this->dbh
->prepare('DELETE FROM ' . $this->identifier_quote_char
. $this->table
. $this->identifier_quote_char
. ' WHERE ' . $this->identifier_quote_char
. $this->user_field
. $this->identifier_quote_char
. ' = ? AND ' . $this->identifier_quote_char
. $this->key_field
. $this->identifier_quote_char
. ' = ?'))) {
580 if ($pdo_show_sql_errors)
581 $this->error
= implode(' - ', $this->dbh
->errorInfo());
583 $this->error
= _("Could not prepare query");
586 if (!($res = $sth->execute(array($user, $key)))) {
587 if ($pdo_show_sql_errors)
588 $this->error
= implode(' - ', $sth->errorInfo());
590 $this->error
= _("Could not execute query");
591 $this->dbh
->exec('ROLLBACK TRANSACTION');
594 if (!($sth = $this->dbh
->prepare('INSERT INTO ' . $this->identifier_quote_char
. $this->table
. $this->identifier_quote_char
. ' (' . $this->identifier_quote_char
. $this->user_field
. $this->identifier_quote_char
. ', ' . $this->identifier_quote_char
. $this->key_field
. $this->identifier_quote_char
. ', ' . $this->identifier_quote_char
. $this->val_field
. $this->identifier_quote_char
. ') VALUES (?, ?, ?)'))) {
595 if ($pdo_show_sql_errors)
596 $this->error
= implode(' - ', $this->dbh
->errorInfo());
598 $this->error
= _("Could not prepare query");
601 if (!($res = $sth->execute(array($user, $key, $value)))) {
602 if ($pdo_show_sql_errors)
603 $this->error
= implode(' - ', $sth->errorInfo());
605 $this->error
= _("Could not execute query");
606 $this->dbh
->exec('ROLLBACK TRANSACTION');
609 if ($this->dbh
->exec('COMMIT TRANSACTION') === FALSE) {
610 if ($pdo_show_sql_errors)
611 $this->error
= implode(' - ', $this->dbh
->errorInfo());
613 $this->error
= _("Could not execute query");
617 $this->dbh
->simpleQuery("BEGIN TRANSACTION");
618 $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
621 $this->dbh
->quoteString($user),
623 $this->dbh
->quoteString($key));
624 $res = $this->dbh
->simpleQuery($query);
625 if (DB
::isError($res)) {
626 $this->dbh
->simpleQuery("ROLLBACK TRANSACTION");
627 $this->failQuery($res);
629 $query = sprintf("INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
634 $this->dbh
->quoteString($user),
635 $this->dbh
->quoteString($key),
636 $this->dbh
->quoteString($value));
637 $res = $this->dbh
->simpleQuery($query);
638 if (DB
::isError($res)) {
639 $this->dbh
->simpleQuery("ROLLBACK TRANSACTION");
640 $this->failQuery($res);
642 $this->dbh
->simpleQuery("COMMIT TRANSACTION");
646 if (!($sth = $this->dbh
->prepare('DELETE FROM ' . $this->identifier_quote_char
. $this->table
. $this->identifier_quote_char
. ' WHERE ' . $this->identifier_quote_char
. $this->user_field
. $this->identifier_quote_char
. ' = ? AND ' . $this->identifier_quote_char
. $this->key_field
. $this->identifier_quote_char
. ' = ?'))) {
647 if ($pdo_show_sql_errors)
648 $this->error
= implode(' - ', $this->dbh
->errorInfo());
650 $this->error
= _("Could not prepare query");
653 if (!($res = $sth->execute(array($user, $key)))) {
654 if ($pdo_show_sql_errors)
655 $this->error
= implode(' - ', $sth->errorInfo());
657 $this->error
= _("Could not execute query");
660 if (!($sth = $this->dbh
->prepare('INSERT INTO ' . $this->identifier_quote_char
. $this->table
. $this->identifier_quote_char
. ' (' . $this->identifier_quote_char
. $this->user_field
. $this->identifier_quote_char
. ', ' . $this->identifier_quote_char
. $this->key_field
. $this->identifier_quote_char
. ', ' . $this->identifier_quote_char
. $this->val_field
. $this->identifier_quote_char
. ') VALUES (?, ?, ?)'))) {
661 if ($pdo_show_sql_errors)
662 $this->error
= implode(' - ', $this->dbh
->errorInfo());
664 $this->error
= _("Could not prepare query");
667 if (!($res = $sth->execute(array($user, $key, $value)))) {
668 if ($pdo_show_sql_errors)
669 $this->error
= implode(' - ', $sth->errorInfo());
671 $this->error
= _("Could not execute query");
675 $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
678 $this->dbh
->quoteString($user),
680 $this->dbh
->quoteString($key));
681 $res = $this->dbh
->simpleQuery($query);
682 if (DB
::isError($res)) {
683 $this->failQuery($res);
685 $query = sprintf("INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
690 $this->dbh
->quoteString($user),
691 $this->dbh
->quoteString($key),
692 $this->dbh
->quoteString($value));
693 $res = $this->dbh
->simpleQuery($query);
694 if (DB
::isError($res)) {
695 $this->failQuery($res);
704 * Fill preference cache array
706 * @param string $user user name
711 function fillPrefsCache($user) {
712 global $prefs_cache, $use_pdo, $pdo_show_sql_errors;
714 if (!$this->open()) {
718 $prefs_cache = array();
720 if (!($sth = $this->dbh
->prepare('SELECT ' . $this->identifier_quote_char
. $this->key_field
. $this->identifier_quote_char
. ' AS prefkey, ' . $this->identifier_quote_char
. $this->val_field
. $this->identifier_quote_char
. ' AS prefval FROM ' . $this->identifier_quote_char
. $this->table
. $this->identifier_quote_char
. ' WHERE ' . $this->identifier_quote_char
. $this->user_field
. $this->identifier_quote_char
. ' = ?'))) {
721 if ($pdo_show_sql_errors)
722 $this->error
= implode(' - ', $this->dbh
->errorInfo());
724 $this->error
= _("Could not prepare query");
727 if (!($res = $sth->execute(array($user)))) {
728 if ($pdo_show_sql_errors)
729 $this->error
= implode(' - ', $sth->errorInfo());
731 $this->error
= _("Could not execute query");
735 while ($row = $sth->fetch(PDO
::FETCH_ASSOC
)) {
736 $prefs_cache[$row['prefkey']] = $row['prefval'];
739 $query = sprintf("SELECT %s as prefkey, %s as prefval FROM %s ".
745 $this->dbh
->quoteString($user));
746 $res = $this->dbh
->query($query);
747 if (DB
::isError($res)) {
748 $this->failQuery($res);
751 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC
)) {
752 $prefs_cache[$row['prefkey']] = $row['prefval'];
757 } /* end class dbPrefs */
761 * Returns the value for the requested preference
764 function getPref($data_dir, $username, $pref_name, $default = '') {
766 if(isset($db->error
)) {
767 printf( _("Preference database error (%s). Exiting abnormally"),
772 return $db->getKey($username, $pref_name, $default);
776 * Remove the desired preference setting ($pref_name)
779 function removePref($data_dir, $username, $pref_name) {
782 if(isset($db->error
)) {
786 $db->deleteKey($username, $pref_name);
788 if (isset($prefs_cache[$pref_name])) {
789 unset($prefs_cache[$pref_name]);
792 sqsession_register($prefs_cache , 'prefs_cache');
797 * Sets the desired preference setting ($pref_name) to whatever is in $value
800 function setPref($data_dir, $username, $pref_name, $value) {
803 if (isset($prefs_cache[$pref_name]) && ($prefs_cache[$pref_name] == $value)) {
808 removePref($data_dir, $username, $pref_name);
813 if(isset($db->error
)) {
817 $db->setKey($username, $pref_name, $value);
818 $prefs_cache[$pref_name] = $value;
819 assert_options(ASSERT_ACTIVE
, 1);
820 assert_options(ASSERT_BAIL
, 1);
821 assert ('$value == $prefs_cache[$pref_name]');
822 sqsession_register($prefs_cache , 'prefs_cache');
827 * This checks if the prefs are available
830 function checkForPrefs($data_dir, $username) {
832 if(isset($db->error
)) {
838 * Writes the Signature
841 function setSig($data_dir, $username, $number, $value) {
842 if ($number == "g") {
843 $key = '___signature___';
845 $key = sprintf('___sig%s___', $number);
847 setPref($data_dir, $username, $key, $value);
855 function getSig($data_dir, $username, $number) {
856 if ($number == "g") {
857 $key = '___signature___';
859 $key = sprintf('___sig%d___', $number);
861 return getPref($data_dir, $username, $key);