diff options
Diffstat (limited to 'program/lib')
| -rw-r--r-- | program/lib/Roundcube/rcube_cache.php | 51 | ||||
| -rw-r--r-- | program/lib/Roundcube/rcube_cache_shared.php | 40 | ||||
| -rw-r--r-- | program/lib/Roundcube/rcube_contacts.php | 160 | ||||
| -rw-r--r-- | program/lib/Roundcube/rcube_db.php | 74 | ||||
| -rw-r--r-- | program/lib/Roundcube/rcube_db_oracle.php | 263 | ||||
| -rw-r--r-- | program/lib/Roundcube/rcube_imap_cache.php | 196 | ||||
| -rw-r--r-- | program/lib/Roundcube/rcube_session.php | 30 | ||||
| -rw-r--r-- | program/lib/Roundcube/rcube_spellchecker.php | 25 | ||||
| -rw-r--r-- | program/lib/Roundcube/rcube_user.php | 105 |
9 files changed, 633 insertions, 311 deletions
diff --git a/program/lib/Roundcube/rcube_cache.php b/program/lib/Roundcube/rcube_cache.php index 0017dcacc..7210ce645 100644 --- a/program/lib/Roundcube/rcube_cache.php +++ b/program/lib/Roundcube/rcube_cache.php @@ -75,7 +75,7 @@ class rcube_cache else { $this->type = 'db'; $this->db = $rcube->get_dbh(); - $this->table = $this->db->table_name('cache'); + $this->table = $this->db->table_name('cache', true); } // convert ttl string to seconds @@ -197,10 +197,10 @@ class rcube_cache { if ($this->type == 'db' && $this->db && $this->ttl) { $this->db->query( - "DELETE FROM ".$this->table. - " WHERE user_id = ?". - " AND cache_key LIKE ?". - " AND expires < " . $this->db->now(), + "DELETE FROM {$this->table}". + " WHERE `user_id` = ?". + " AND `cache_key` LIKE ?". + " AND `expires` < " . $this->db->now(), $this->userid, $this->prefix.'.%'); } @@ -215,7 +215,7 @@ class rcube_cache $rcube = rcube::get_instance(); $db = $rcube->get_dbh(); - $db->query("DELETE FROM " . $db->table_name('cache') . " WHERE expires < " . $db->now()); + $db->query("DELETE FROM " . $db->table_name('cache', true) . " WHERE `expires` < " . $db->now()); } @@ -284,13 +284,12 @@ class rcube_cache } else { $sql_result = $this->db->limitquery( - "SELECT data, cache_key". - " FROM " . $this->table. - " WHERE user_id = ?". - " AND cache_key = ?". + "SELECT `data`, `cache_key`". + " FROM {$this->table}". + " WHERE `user_id` = ? AND `cache_key` = ?". // for better performance we allow more records for one key // get the newer one - " ORDER BY created DESC", + " ORDER BY `created` DESC", 0, 1, $this->userid, $this->prefix.'.'.$key); if ($sql_arr = $this->db->fetch_assoc($sql_result)) { @@ -346,9 +345,8 @@ class rcube_cache // Remove NULL rows (here we don't need to check if the record exist) if ($data == 'N;') { $this->db->query( - "DELETE FROM " . $this->table. - " WHERE user_id = ?". - " AND cache_key = ?", + "DELETE FROM {$this->table}". + " WHERE `user_id` = ? AND `cache_key` = ?", $this->userid, $key); return true; @@ -357,12 +355,12 @@ class rcube_cache // update existing cache record if ($key_exists) { $result = $this->db->query( - "UPDATE " . $this->table. - " SET created = " . $this->db->now(). - ", expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL'). - ", data = ?". - " WHERE user_id = ?". - " AND cache_key = ?", + "UPDATE {$this->table}". + " SET `created` = " . $this->db->now(). + ", `expires` = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL'). + ", `data` = ?". + " WHERE `user_id` = ?". + " AND `cache_key` = ?", $data, $this->userid, $key); } // add new cache record @@ -370,8 +368,8 @@ class rcube_cache // for better performance we allow more records for one key // so, no need to check if record exist (see rcube_cache::read_record()) $result = $this->db->query( - "INSERT INTO " . $this->table. - " (created, expires, user_id, cache_key, data)". + "INSERT INTO {$this->table}". + " (`created`, `expires`, `user_id`, `cache_key`, `data`)". " VALUES (" . $this->db->now() . ", " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') . ", ?, ?, ?)", $this->userid, $key, $data); } @@ -421,20 +419,19 @@ class rcube_cache // Remove all keys (in specified cache) if ($key === null) { - $where = " AND cache_key LIKE " . $this->db->quote($this->prefix.'.%'); + $where = " AND `cache_key` LIKE " . $this->db->quote($this->prefix.'.%'); } // Remove keys by name prefix else if ($prefix_mode) { - $where = " AND cache_key LIKE " . $this->db->quote($this->prefix.'.'.$key.'%'); + $where = " AND `cache_key` LIKE " . $this->db->quote($this->prefix.'.'.$key.'%'); } // Remove one key by name else { - $where = " AND cache_key = " . $this->db->quote($this->prefix.'.'.$key); + $where = " AND `cache_key` = " . $this->db->quote($this->prefix.'.'.$key); } $this->db->query( - "DELETE FROM " . $this->table. - " WHERE user_id = ?" . $where, + "DELETE FROM {$this->table} WHERE `user_id` = ?" . $where, $this->userid); } diff --git a/program/lib/Roundcube/rcube_cache_shared.php b/program/lib/Roundcube/rcube_cache_shared.php index 8f2574046..a2bf09208 100644 --- a/program/lib/Roundcube/rcube_cache_shared.php +++ b/program/lib/Roundcube/rcube_cache_shared.php @@ -72,7 +72,7 @@ class rcube_cache_shared else { $this->type = 'db'; $this->db = $rcube->get_dbh(); - $this->table = $this->db->table_name('cache_shared'); + $this->table = $this->db->table_name('cache_shared', true); } // convert ttl string to seconds @@ -193,9 +193,9 @@ class rcube_cache_shared { if ($this->type == 'db' && $this->db && $this->ttl) { $this->db->query( - "DELETE FROM " . $this->table - . " WHERE cache_key LIKE ?" - . " AND expires < " . $this->db->now(), + "DELETE FROM {$this->table}" + . " WHERE `cache_key` LIKE ?" + . " AND `expires` < " . $this->db->now(), $this->prefix . '.%'); } } @@ -209,7 +209,7 @@ class rcube_cache_shared $rcube = rcube::get_instance(); $db = $rcube->get_dbh(); - $db->query("DELETE FROM " . $db->table_name('cache_shared') . " WHERE expires < " . $db->now()); + $db->query("DELETE FROM " . $db->table_name('cache_shared', true) . " WHERE `expires` < " . $db->now()); } @@ -278,12 +278,12 @@ class rcube_cache_shared } else { $sql_result = $this->db->limitquery( - "SELECT data, cache_key". - " FROM " . $this->table . - " WHERE cache_key = ?". + "SELECT `data`, `cache_key`". + " FROM {$this->table}" . + " WHERE `cache_key` = ?". // for better performance we allow more records for one key // get the newer one - " ORDER BY created DESC", + " ORDER BY `created` DESC", 0, 1, $this->prefix . '.' . $key); if ($sql_arr = $this->db->fetch_assoc($sql_result)) { @@ -331,18 +331,18 @@ class rcube_cache_shared // Remove NULL rows (here we don't need to check if the record exist) if ($data == 'N;') { - $this->db->query("DELETE FROM " . $this->table . " WHERE cache_key = ?", $key); + $this->db->query("DELETE FROM {$this->table} WHERE `cache_key` = ?", $key); return true; } // update existing cache record if ($key_exists) { $result = $this->db->query( - "UPDATE " . $this->table . - " SET created = " . $this->db->now() . - ", expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') . - ", data = ?". - " WHERE cache_key = ?", + "UPDATE {$this->table}" . + " SET `created` = " . $this->db->now() . + ", `expires` = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') . + ", `data` = ?". + " WHERE `cache_key` = ?", $data, $key); } // add new cache record @@ -350,8 +350,8 @@ class rcube_cache_shared // for better performance we allow more records for one key // so, no need to check if record exist (see rcube_cache::read_record()) $result = $this->db->query( - "INSERT INTO ".$this->table. - " (created, expires, cache_key, data)". + "INSERT INTO {$this->table}". + " (`created`, `expires`, `cache_key`, `data`)". " VALUES (".$this->db->now().", " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') . ", ?, ?)", $key, $data); } @@ -401,15 +401,15 @@ class rcube_cache_shared // Remove all keys (in specified cache) if ($key === null) { - $where = " WHERE cache_key LIKE " . $this->db->quote($this->prefix.'.%'); + $where = " WHERE `cache_key` LIKE " . $this->db->quote($this->prefix.'.%'); } // Remove keys by name prefix else if ($prefix_mode) { - $where = " WHERE cache_key LIKE " . $this->db->quote($this->prefix.'.'.$key.'%'); + $where = " WHERE `cache_key` LIKE " . $this->db->quote($this->prefix.'.'.$key.'%'); } // Remove one key by name else { - $where = " WHERE cache_key = " . $this->db->quote($this->prefix.'.'.$key); + $where = " WHERE `cache_key` = " . $this->db->quote($this->prefix.'.'.$key); } $this->db->query("DELETE FROM " . $this->table . $where); diff --git a/program/lib/Roundcube/rcube_contacts.php b/program/lib/Roundcube/rcube_contacts.php index 5e1a40e5b..bd3a3f82b 100644 --- a/program/lib/Roundcube/rcube_contacts.php +++ b/program/lib/Roundcube/rcube_contacts.php @@ -167,11 +167,9 @@ class rcube_contacts extends rcube_addressbook } $sql_result = $this->db->query( - "SELECT * FROM ".$this->db->table_name($this->db_groups). - " WHERE del<>1". - " AND user_id=?". - $sql_filter. - " ORDER BY name", + "SELECT * FROM " . $this->db->table_name($this->db_groups, true) + . " WHERE `del` <> 1 AND `user_id` = ?" . $sql_filter + . " ORDER BY `name`", $this->user_id); while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) { @@ -192,10 +190,8 @@ class rcube_contacts extends rcube_addressbook function get_group($group_id) { $sql_result = $this->db->query( - "SELECT * FROM ".$this->db->table_name($this->db_groups). - " WHERE del<>1". - " AND contactgroup_id=?". - " AND user_id=?", + "SELECT * FROM " . $this->db->table_name($this->db_groups, true) + . " WHERE `del` <> 1 AND `contactgroup_id` = ? AND `user_id` = ?", $group_id, $this->user_id); if ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) { @@ -228,25 +224,25 @@ class rcube_contacts extends rcube_addressbook $length = $subset != 0 ? abs($subset) : $this->page_size; if ($this->group_id) - $join = " LEFT JOIN ".$this->db->table_name($this->db_groupmembers)." AS m". - " ON (m.contact_id = c.".$this->primary_key.")"; + $join = " LEFT JOIN " . $this->db->table_name($this->db_groupmembers, true) . " AS m". + " ON (m.`contact_id` = c.`".$this->primary_key."`)"; $order_col = (in_array($this->sort_col, $this->table_cols) ? $this->sort_col : 'name'); - $order_cols = array('c.'.$order_col); + $order_cols = array("c.`$order_col`"); if ($order_col == 'firstname') - $order_cols[] = 'c.surname'; + $order_cols[] = 'c.`surname`'; else if ($order_col == 'surname') - $order_cols[] = 'c.firstname'; + $order_cols[] = 'c.`firstname`'; if ($order_col != 'name') - $order_cols[] = 'c.name'; - $order_cols[] = 'c.email'; + $order_cols[] = 'c.`name`'; + $order_cols[] = 'c.`email`'; $sql_result = $this->db->limitquery( - "SELECT * FROM ".$this->db->table_name($this->db_name)." AS c" . + "SELECT * FROM " . $this->db->table_name($this->db_name, true) . " AS c" . $join . - " WHERE c.del<>1" . - " AND c.user_id=?" . - ($this->group_id ? " AND m.contactgroup_id=?" : ""). + " WHERE c.`del` <> 1" . + " AND c.`user_id` = ?" . + ($this->group_id ? " AND m.`contactgroup_id` = ?" : ""). ($this->filter ? " AND (".$this->filter.")" : "") . " ORDER BY ". $this->db->concat($order_cols) . " " . $this->sort_order, @@ -442,7 +438,7 @@ class rcube_contacts extends rcube_addressbook // build WHERE clause $ids = $this->db->array2list($ids, 'integer'); - $where = 'c.' . $this->primary_key.' IN ('.$ids.')'; + $where = 'c.`' . $this->primary_key.'` IN ('.$ids.')'; // reset counter unset($this->cache['count']); @@ -486,17 +482,17 @@ class rcube_contacts extends rcube_addressbook private function _count() { if ($this->group_id) - $join = " LEFT JOIN ".$this->db->table_name($this->db_groupmembers)." AS m". - " ON (m.contact_id=c.".$this->primary_key.")"; + $join = " LEFT JOIN " . $this->db->table_name($this->db_groupmembers, true) . " AS m". + " ON (m.`contact_id` = c.`".$this->primary_key."`)"; // count contacts for this user $sql_result = $this->db->query( - "SELECT COUNT(c.contact_id) AS rows". - " FROM ".$this->db->table_name($this->db_name)." AS c". + "SELECT COUNT(c.`contact_id`) AS rows". + " FROM " . $this->db->table_name($this->db_name, true) . " AS c". $join. - " WHERE c.del<>1". - " AND c.user_id=?". - ($this->group_id ? " AND m.contactgroup_id=?" : ""). + " WHERE c.`del` <> 1". + " AND c.`user_id` = ?". + ($this->group_id ? " AND m.`contactgroup_id` = ?" : ""). ($this->filter ? " AND (".$this->filter.")" : ""), $this->user_id, $this->group_id @@ -534,10 +530,10 @@ class rcube_contacts extends rcube_addressbook return $assoc ? $first : $this->result; $this->db->query( - "SELECT * FROM ".$this->db->table_name($this->db_name). - " WHERE contact_id=?". - " AND user_id=?". - " AND del<>1", + "SELECT * FROM " . $this->db->table_name($this->db_name, true). + " WHERE `contact_id` = ?". + " AND `user_id` = ?". + " AND `del` <> 1", $id, $this->user_id ); @@ -566,9 +562,11 @@ class rcube_contacts extends rcube_addressbook return $results; $sql_result = $this->db->query( - "SELECT cgm.contactgroup_id, cg.name FROM " . $this->db->table_name($this->db_groupmembers) . " AS cgm" . - " LEFT JOIN " . $this->db->table_name($this->db_groups) . " AS cg ON (cgm.contactgroup_id = cg.contactgroup_id AND cg.del<>1)" . - " WHERE cgm.contact_id=?", + "SELECT cgm.`contactgroup_id`, cg.`name` " + . " FROM " . $this->db->table_name($this->db_groupmembers, true) . " AS cgm" + . " LEFT JOIN " . $this->db->table_name($this->db_groups, true) . " AS cg" + . " ON (cgm.`contactgroup_id` = cg.`contactgroup_id` AND cg.`del` <> 1)" + . " WHERE cgm.`contact_id` = ?", $id ); while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) { @@ -636,8 +634,8 @@ class rcube_contacts extends rcube_addressbook if (!$existing->count && !empty($a_insert_cols)) { $this->db->query( - "INSERT INTO ".$this->db->table_name($this->db_name). - " (user_id, changed, del, ".join(', ', $a_insert_cols).")". + "INSERT INTO " . $this->db->table_name($this->db_name, true). + " (`user_id`, `changed`, `del`, ".join(', ', $a_insert_cols).")". " VALUES (".intval($this->user_id).", ".$this->db->now().", 0, ".join(', ', $a_insert_values).")" ); @@ -671,11 +669,11 @@ class rcube_contacts extends rcube_addressbook if (!empty($write_sql)) { $this->db->query( - "UPDATE ".$this->db->table_name($this->db_name). - " SET changed=".$this->db->now().", ".join(', ', $write_sql). - " WHERE contact_id=?". - " AND user_id=?". - " AND del<>1", + "UPDATE " . $this->db->table_name($this->db_name, true). + " SET `changed` = ".$this->db->now().", ".join(', ', $write_sql). + " WHERE `contact_id` = ?". + " AND `user_id` = ?". + " AND `del` <> 1", $id, $this->user_id ); @@ -771,10 +769,10 @@ class rcube_contacts extends rcube_addressbook // flag record as deleted (always) $this->db->query( - "UPDATE ".$this->db->table_name($this->db_name). - " SET del=1, changed=".$this->db->now(). - " WHERE user_id=?". - " AND contact_id IN ($ids)", + "UPDATE " . $this->db->table_name($this->db_name, true). + " SET `del` = 1, `changed` = ".$this->db->now(). + " WHERE `user_id` = ?". + " AND `contact_id` IN ($ids)", $this->user_id ); @@ -798,10 +796,10 @@ class rcube_contacts extends rcube_addressbook // clear deleted flag $this->db->query( - "UPDATE ".$this->db->table_name($this->db_name). - " SET del=0, changed=".$this->db->now(). - " WHERE user_id=?". - " AND contact_id IN ($ids)", + "UPDATE " . $this->db->table_name($this->db_name, true). + " SET `del` = 0, `changed` = ".$this->db->now(). + " WHERE `user_id` = ?". + " AND `contact_id` IN ($ids)", $this->user_id ); @@ -822,16 +820,18 @@ class rcube_contacts extends rcube_addressbook { $this->cache = null; - $this->db->query("UPDATE " . $this->db->table_name($this->db_name) - . " SET del = 1, changed = " . $this->db->now() - . " WHERE user_id = ?", $this->user_id); + $now = $this->db->now(); + + $this->db->query("UPDATE " . $this->db->table_name($this->db_name, true) + . " SET `del` = 1, `changed` = $now" + . " WHERE `user_id` = ?", $this->user_id); $count = $this->db->affected_rows(); if ($with_groups) { - $this->db->query("UPDATE " . $this->db->table_name($this->db_groups) - . " SET del = 1, changed = " . $this->db->now() - . " WHERE user_id = ?", $this->user_id); + $this->db->query("UPDATE " . $this->db->table_name($this->db_groups, true) + . " SET `del` = 1, `changed` = $now" + . " WHERE `user_id` = ?", $this->user_id); $count += $this->db->affected_rows(); } @@ -854,13 +854,14 @@ class rcube_contacts extends rcube_addressbook $name = $this->unique_groupname($name); $this->db->query( - "INSERT INTO ".$this->db->table_name($this->db_groups). - " (user_id, changed, name)". + "INSERT INTO " . $this->db->table_name($this->db_groups, true). + " (`user_id`, `changed`, `name`)". " VALUES (".intval($this->user_id).", ".$this->db->now().", ".$this->db->quote($name).")" ); - if ($insert_id = $this->db->insert_id($this->db_groups)) + if ($insert_id = $this->db->insert_id($this->db_groups)) { $result = array('id' => $insert_id, 'name' => $name); + } return $result; } @@ -876,10 +877,10 @@ class rcube_contacts extends rcube_addressbook { // flag group record as deleted $this->db->query( - "UPDATE " . $this->db->table_name($this->db_groups) - . " SET del = 1, changed = " . $this->db->now() - . " WHERE contactgroup_id = ?" - . " AND user_id = ?", + "UPDATE " . $this->db->table_name($this->db_groups, true) + . " SET `del` = 1, `changed` = " . $this->db->now() + . " WHERE `contactgroup_id` = ?" + . " AND `user_id` = ?", $gid, $this->user_id ); @@ -901,10 +902,10 @@ class rcube_contacts extends rcube_addressbook $name = $this->unique_groupname($newname); $sql_result = $this->db->query( - "UPDATE ".$this->db->table_name($this->db_groups). - " SET name=?, changed=".$this->db->now(). - " WHERE contactgroup_id=?". - " AND user_id=?", + "UPDATE " . $this->db->table_name($this->db_groups, true). + " SET `name` = ?, `changed` = ".$this->db->now(). + " WHERE `contactgroup_id` = ?". + " AND `user_id` = ?", $name, $gid, $this->user_id ); @@ -930,9 +931,9 @@ class rcube_contacts extends rcube_addressbook // get existing assignments ... $sql_result = $this->db->query( - "SELECT contact_id FROM ".$this->db->table_name($this->db_groupmembers). - " WHERE contactgroup_id=?". - " AND contact_id IN (".$this->db->array2list($ids, 'integer').")", + "SELECT `contact_id` FROM " . $this->db->table_name($this->db_groupmembers, true). + " WHERE `contactgroup_id` = ?". + " AND `contact_id` IN (".$this->db->array2list($ids, 'integer').")", $group_id ); while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) { @@ -943,8 +944,8 @@ class rcube_contacts extends rcube_addressbook foreach ($ids as $contact_id) { $this->db->query( - "INSERT INTO ".$this->db->table_name($this->db_groupmembers). - " (contactgroup_id, contact_id, created)". + "INSERT INTO " . $this->db->table_name($this->db_groupmembers, true). + " (`contactgroup_id`, `contact_id`, `created`)". " VALUES (?, ?, ".$this->db->now().")", $group_id, $contact_id @@ -976,9 +977,9 @@ class rcube_contacts extends rcube_addressbook $ids = $this->db->array2list($ids, 'integer'); $sql_result = $this->db->query( - "DELETE FROM ".$this->db->table_name($this->db_groupmembers). - " WHERE contactgroup_id=?". - " AND contact_id IN ($ids)", + "DELETE FROM " . $this->db->table_name($this->db_groupmembers, true). + " WHERE `contactgroup_id` = ?". + " AND `contact_id` IN ($ids)", $group_id ); @@ -999,10 +1000,10 @@ class rcube_contacts extends rcube_addressbook do { $sql_result = $this->db->query( - "SELECT 1 FROM ".$this->db->table_name($this->db_groups). - " WHERE del<>1". - " AND user_id=?". - " AND name=?", + "SELECT 1 FROM " . $this->db->table_name($this->db_groups, true). + " WHERE `del` <> 1". + " AND `user_id` = ?". + " AND `name` = ?", $this->user_id, $checkname); @@ -1014,5 +1015,4 @@ class rcube_contacts extends rcube_addressbook return $checkname; } - } diff --git a/program/lib/Roundcube/rcube_db.php b/program/lib/Roundcube/rcube_db.php index 100afd530..b12c99d0e 100644 --- a/program/lib/Roundcube/rcube_db.php +++ b/program/lib/Roundcube/rcube_db.php @@ -50,6 +50,7 @@ class rcube_db ); const DEBUG_LINE_LENGTH = 4096; + const DEFAULT_QUOTE = '`'; /** * Factory, returns driver-specific instance of the class @@ -238,8 +239,12 @@ class rcube_db // Read or write ? $mode = preg_match('/^(select|show|set)/i', $query) ? 'r' : 'w'; + $start = '[' . $this->options['identifier_start'] . self::DEFAULT_QUOTE . ']'; + $end = '[' . $this->options['identifier_end'] . self::DEFAULT_QUOTE . ']'; + $regex = '/(?:^|\s)(from|update|into|join)\s+'.$start.'?([a-z0-9._]+)'.$end.'?\s+/i'; + // find tables involved in this query - if (preg_match_all('/(?:^|\s)(from|update|into|join)\s+'.$this->options['identifier_start'].'?([a-z0-9._]+)'.$this->options['identifier_end'].'?\s+/i', $query, $matches, PREG_SET_ORDER)) { + if (preg_match_all($regex, $query, $matches, PREG_SET_ORDER)) { foreach ($matches as $m) { $table = $m[2]; @@ -408,6 +413,9 @@ class rcube_db $query = $this->set_limit($query, $numrows, $offset); } + // replace self::DEFAULT_QUOTE with driver-specific quoting + $query = $this->query_parse($query); + // Because in Roundcube we mostly use queries that are // executed only once, we will not use prepared queries $pos = 0; @@ -431,6 +439,7 @@ class rcube_db $query = str_replace('??', '?', $query); $query = rtrim($query, " \t\n\r\0\x0B;"); + // log query $this->debug($query); // destroy reference to previous result, required for SQLite driver (#1488874) @@ -450,6 +459,51 @@ class rcube_db } /** + * Parse SQL query and replace identifier quoting + * + * @param string $query SQL query + * + * @return string SQL query + */ + protected function query_parse($query) + { + $start = $this->options['identifier_start']; + $end = $this->options['identifier_end']; + $quote = self::DEFAULT_QUOTE; + + if ($start == $quote) { + return $query; + } + + $pos = 0; + $in = false; + + while ($pos = strpos($query, $quote, $pos)) { + if ($query[$pos+1] == $quote) { // skip escaped quote + $pos += 2; + } + else { + if ($in) { + $q = $end; + $in = false; + } + else { + $q = $start; + $in = true; + } + + $query = substr_replace($query, $q, $pos, 1); + $pos++; + } + } + + // replace escaped quote back to normal, see self::quote() + $query = str_replace($quote.$quote, $quote, $query); + + return $query; + } + + /** * Helper method to handle DB errors. * This by default logs the error but could be overriden by a driver implementation * @@ -677,8 +731,13 @@ class rcube_db 'bool' => PDO::PARAM_BOOL, 'integer' => PDO::PARAM_INT, ); + $type = isset($map[$type]) ? $map[$type] : PDO::PARAM_STR; - return strtr($this->dbh->quote($input, $type), array('?' => '??')); // escape ? + + return strtr($this->dbh->quote($input, $type), + // escape ? and ` + array('?' => '??', self::DEFAULT_QUOTE => self::DEFAULT_QUOTE.self::DEFAULT_QUOTE) + ); } return 'NULL'; @@ -917,15 +976,20 @@ class rcube_db /** * Return correct name for a specific database table * - * @param string $table Table name + * @param string $table Table name + * @param bool $quoted Quote table identifier * * @return string Translated table name */ - public function table_name($table) + public function table_name($table, $quoted = false) { // add prefix to the table name if configured if (($prefix = $this->options['table_prefix']) && strpos($table, $prefix) !== 0) { - return $prefix . $table; + $table = $prefix . $table; + } + + if ($quoted) { + $table = $this->quote_identifier($table); } return $table; diff --git a/program/lib/Roundcube/rcube_db_oracle.php b/program/lib/Roundcube/rcube_db_oracle.php new file mode 100644 index 000000000..ddd351ec3 --- /dev/null +++ b/program/lib/Roundcube/rcube_db_oracle.php @@ -0,0 +1,263 @@ +<?php + +/** + +-----------------------------------------------------------------------+ + | This file is part of the Roundcube Webmail client | + | Copyright (C) 2011-2014, Kolab Systems AG | + | | + | Licensed under the GNU General Public License version 3 or | + | any later version with exceptions for skins & plugins. | + | See the README file for a full license statement. | + | | + | PURPOSE: | + | Database wrapper class that implements PHP PDO functions | + | for Oracle database | + +-----------------------------------------------------------------------+ + | Author: Aleksander Machniak <machniak@kolabsys.com> | + +-----------------------------------------------------------------------+ +*/ + +/** + * Database independent query interface + * This is a wrapper for the PHP PDO + * + * @package Framework + * @subpackage Database + */ +class rcube_db_oracle extends rcube_db +{ + public $db_provider = 'oracle'; + + /** + * Driver-specific configuration of database connection + * + * @param array $dsn DSN for DB connections + * @param PDO $dbh Connection handler + */ + protected function conn_configure($dsn, $dbh) + { + $dbh->query("ALTER SESSION SET nls_date_format = 'YYYY-MM-DD'"); + $dbh->query("ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS'"); + } + + /** + * Get last inserted record ID + * + * @param string $table Table name (to find the incremented sequence) + * + * @return mixed ID or false on failure + */ + public function insert_id($table = null) + { + if (!$this->db_connected || $this->db_mode == 'r' || empty($table)) { + return false; + } + + $sequence = $this->quote_identifier($this->sequence_name($table)); + $result = $dbh->query("SELECT $sequence.currval FROM dual"); + + return $result ? $result->fetchColumn() : false; + } + + /** + * Formats input so it can be safely used in a query + * PDO_OCI does not implement quote() method + * + * @param mixed $input Value to quote + * @param string $type Type of data (integer, bool, ident) + * + * @return string Quoted/converted string for use in query + */ + public function quote($input, $type = null) + { + // handle int directly for better performance + if ($type == 'integer' || $type == 'int') { + return intval($input); + } + + if (is_null($input)) { + return 'NULL'; + } + + if ($type == 'ident') { + return $this->quote_identifier($input); + } + + switch ($type) { + case 'bool': + case 'integer': + return intval($input); + default: + return "'" . strtr($input, array( + '?' => '??', + "'" => "''", + rcube_db::DEFAULT_QUOTE => rcube_db::DEFAULT_QUOTE . rcube_db::DEFAULT_QUOTE + )) . "'"; + } + } + + /** + * Return correct name for a specific database sequence + * + * @param string $table Table name + * + * @return string Translated sequence name + */ + protected function sequence_name($table) + { + // Note: we support only one sequence per table + // Note: The sequence name must be <table_name>_seq + $sequence = $table . '_seq'; + + // modify sequence name if prefix is configured + if ($prefix = $this->options['table_prefix']) { + return $prefix . $sequence; + } + + return $sequence; + } + + /** + * Return SQL statement for case insensitive LIKE + * + * @param string $column Field name + * @param string $value Search value + * + * @return string SQL statement to use in query + */ + public function ilike($column, $value) + { + return 'UPPER(' . $this->quote_identifier($column) . ') LIKE UPPER(' . $this->quote($value) . ')'; + } + + /** + * Return SQL function for current time and date + * + * @param int $interval Optional interval (in seconds) to add/subtract + * + * @return string SQL function to use in query + */ + public function now($interval = 0) + { + if ($interval) { + $interval = intval($interval); + return "current_timestamp + INTERVAL '$interval' SECOND"; + } + + return "current_timestamp"; + } + + /** + * Return SQL statement to convert a field value into a unix timestamp + * + * @param string $field Field name + * + * @return string SQL statement to use in query + * @deprecated + */ + public function unixtimestamp($field) + { + return "(($field - to_date('1970-01-01','YYYY-MM-DD')) * 60 * 60 * 24)"; + } + + /** + * Adds TOP (LIMIT,OFFSET) clause to the query + * + * @param string $query SQL query + * @param int $limit Number of rows + * @param int $offset Offset + * + * @return string SQL query + */ + protected function set_limit($query, $limit = 0, $offset = 0) + { + $limit = intval($limit); + $offset = intval($offset); + $end = $offset + $limit; + + // @TODO: Oracle 12g has better OFFSET support + + $orderby = stristr($query, 'ORDER BY'); + $select = substr($query, 0, stripos($query, 'FROM')); + $offset += 1; + + if ($orderby !== false) { + $query = trim(substr($query, 0, -1 * strlen($orderby))); + } + else { + // it shouldn't happen, paging without sorting has not much sense + // @FIXME: I don't know how to build paging query without ORDER BY + $orderby = "ORDER BY 1"; + } + + $query = preg_replace('/^SELECT\s/i', '', $query); + $query = "$select FROM (SELECT ROW_NUMBER() OVER ($orderby) AS row_number, $query)" + . " WHERE row_number BETWEEN $offset AND $end"; + + return $query; + } + + /** + * Parse SQL file and fix table names according to table prefix + */ + protected function fix_table_names($sql) + { + if (!$this->options['table_prefix']) { + return $sql; + } + + $sql = parent::fix_table_names($sql); + + // replace sequence names, and other Oracle-specific commands + $sql = preg_replace_callback('/((SEQUENCE ["]?)([^" \r\n]+)/', + array($this, 'fix_table_names_callback'), + $sql + ); + + $sql = preg_replace_callback( + '/([ \r\n]+["]?)([^"\' \r\n\.]+)(["]?\.nextval)/', + array($this, 'fix_table_names_seq_callback'), + $sql + ); + + return $sql; + } + + /** + * Preg_replace callback for fix_table_names() + */ + protected function fix_table_names_seq_callback($matches) + { + return $matches[1] . $this->options['table_prefix'] . $matches[2] . $matches[3]; + } + + /** + * Returns PDO DSN string from DSN array + */ + protected function dsn_string($dsn) + { + $params = array(); + $result = 'oci:'; + + if ($dsn['hostspec']) { + $host = $dsn['hostspec']; + if ($dsn['port']) { + $host .= ':' . $dsn['port']; + } + + $dsn['database'] = $host . '/' . $dsn['database']; + } + + if ($dsn['database']) { + $params[] = 'dbname=' . $dsn['database']; + } + + $params['charset'] = 'UTF8'; + + if (!empty($params)) { + $result .= implode(';', $params); + } + + return $result; + } +} diff --git a/program/lib/Roundcube/rcube_imap_cache.php b/program/lib/Roundcube/rcube_imap_cache.php index e49e77803..519132126 100644 --- a/program/lib/Roundcube/rcube_imap_cache.php +++ b/program/lib/Roundcube/rcube_imap_cache.php @@ -125,6 +125,11 @@ class rcube_imap_cache // cache all possible information by default $this->mode = self::MODE_INDEX | self::MODE_MESSAGE; + + // database tables + $this->index_table = $db->table_name('cache_index', true); + $this->thread_table = $db->table_name('cache_thread', true); + $this->messages_table = $db->table_name('cache_messages', true); } @@ -333,11 +338,11 @@ class rcube_imap_cache if ($this->mode & self::MODE_MESSAGE) { // Fetch messages from cache $sql_result = $this->db->query( - "SELECT uid, data, flags" - ." FROM ".$this->db->table_name('cache_messages') - ." WHERE user_id = ?" - ." AND mailbox = ?" - ." AND uid IN (".$this->db->array2list($msgs, 'integer').")", + "SELECT `uid`, `data`, `flags`" + ." FROM {$this->messages_table}" + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?" + ." AND `uid` IN (".$this->db->array2list($msgs, 'integer').")", $this->userid, $mailbox); $msgs = array_flip($msgs); @@ -400,11 +405,11 @@ class rcube_imap_cache if ($this->mode & self::MODE_MESSAGE) { $sql_result = $this->db->query( - "SELECT flags, data" - ." FROM ".$this->db->table_name('cache_messages') - ." WHERE user_id = ?" - ." AND mailbox = ?" - ." AND uid = ?", + "SELECT `flags`, `data`" + ." FROM {$this->messages_table}" + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?" + ." AND `uid` = ?", $this->userid, $mailbox, (int)$uid); if ($sql_arr = $this->db->fetch_assoc($sql_result)) { @@ -480,11 +485,11 @@ class rcube_imap_cache // here will work as select, assume row exist if affected_rows=0) if (!$force) { $res = $this->db->query( - "UPDATE ".$this->db->table_name('cache_messages') - ." SET flags = ?, data = ?, expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') - ." WHERE user_id = ?" - ." AND mailbox = ?" - ." AND uid = ?", + "UPDATE {$this->messages_table}" + ." SET `flags` = ?, `data` = ?, `expires` = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?" + ." AND `uid` = ?", $flags, $msg, $this->userid, $mailbox, (int) $message->uid); if ($this->db->affected_rows($res)) { @@ -496,8 +501,8 @@ class rcube_imap_cache // insert new record $res = $this->db->query( - "INSERT INTO ".$this->db->table_name('cache_messages') - ." (user_id, mailbox, uid, flags, expires, data)" + "INSERT INTO {$this->messages_table}" + ." (`user_id`, `mailbox`, `uid`, `flags`, `expires`, `data`)" ." VALUES (?, ?, ?, ?, ". ($this->ttl ? $this->db->now($this->ttl) : 'NULL') . ", ?)", $this->userid, $mailbox, (int) $message->uid, $flags, $msg); @@ -505,12 +510,12 @@ class rcube_imap_cache // thanks to ignore_key_errors "duplicate row" errors will be ignored if ($force && !$res && !$this->db->is_error($res)) { $this->db->query( - "UPDATE ".$this->db->table_name('cache_messages') - ." SET expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') - .", flags = ?, data = ?" - ." WHERE user_id = ?" - ." AND mailbox = ?" - ." AND uid = ?", + "UPDATE {$this->messages_table}" + ." SET `expires` = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') + .", `flags` = ?, `data` = ?" + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?" + ." AND `uid` = ?", $flags, $msg, $this->userid, $mailbox, (int) $message->uid); } @@ -558,13 +563,13 @@ class rcube_imap_cache } $this->db->query( - "UPDATE ".$this->db->table_name('cache_messages') - ." SET expires = ". ($this->ttl ? $this->db->now($this->ttl) : 'NULL') - .", flags = flags ".($enabled ? "+ $idx" : "- $idx") - ." WHERE user_id = ?" - ." AND mailbox = ?" - .(!empty($uids) ? " AND uid IN (".$this->db->array2list($uids, 'integer').")" : "") - ." AND (flags & $idx) ".($enabled ? "= 0" : "= $idx"), + "UPDATE {$this->messages_table}" + ." SET `expires` = ". ($this->ttl ? $this->db->now($this->ttl) : 'NULL') + .", `flags` = `flags` ".($enabled ? "+ $idx" : "- $idx") + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?" + .(!empty($uids) ? " AND `uid` IN (".$this->db->array2list($uids, 'integer').")" : "") + ." AND (`flags` & $idx) ".($enabled ? "= 0" : "= $idx"), $this->userid, $mailbox); } @@ -583,8 +588,8 @@ class rcube_imap_cache if (!strlen($mailbox)) { $this->db->query( - "DELETE FROM ".$this->db->table_name('cache_messages') - ." WHERE user_id = ?", + "DELETE FROM {$this->messages_table}" + ." WHERE `user_id` = ?", $this->userid); } else { @@ -597,10 +602,10 @@ class rcube_imap_cache } $this->db->query( - "DELETE FROM ".$this->db->table_name('cache_messages') - ." WHERE user_id = ?" - ." AND mailbox = ?" - .($uids !== null ? " AND uid IN (".$this->db->array2list((array)$uids, 'integer').")" : ""), + "DELETE FROM {$this->messages_table}" + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?" + .($uids !== null ? " AND `uid` IN (".$this->db->array2list((array)$uids, 'integer').")" : ""), $this->userid, $mailbox); } } @@ -619,18 +624,18 @@ class rcube_imap_cache // otherwise use 'valid' flag to not loose HIGHESTMODSEQ value if ($remove) { $this->db->query( - "DELETE FROM ".$this->db->table_name('cache_index') - ." WHERE user_id = ?" - .(strlen($mailbox) ? " AND mailbox = ".$this->db->quote($mailbox) : ""), + "DELETE FROM {$this->index_table}" + ." WHERE `user_id` = ?" + .(strlen($mailbox) ? " AND `mailbox` = ".$this->db->quote($mailbox) : ""), $this->userid ); } else { $this->db->query( - "UPDATE ".$this->db->table_name('cache_index') - ." SET valid = 0" - ." WHERE user_id = ?" - .(strlen($mailbox) ? " AND mailbox = ".$this->db->quote($mailbox) : ""), + "UPDATE {$this->index_table}" + ." SET `valid` = 0" + ." WHERE `user_id` = ?" + .(strlen($mailbox) ? " AND `mailbox` = ".$this->db->quote($mailbox) : ""), $this->userid ); } @@ -654,9 +659,9 @@ class rcube_imap_cache function remove_thread($mailbox = null) { $this->db->query( - "DELETE FROM ".$this->db->table_name('cache_thread') - ." WHERE user_id = ?" - .(strlen($mailbox) ? " AND mailbox = ".$this->db->quote($mailbox) : ""), + "DELETE FROM {$this->thread_table}" + ." WHERE `user_id` = ?" + .(strlen($mailbox) ? " AND `mailbox` = ".$this->db->quote($mailbox) : ""), $this->userid ); @@ -692,15 +697,16 @@ class rcube_imap_cache { $rcube = rcube::get_instance(); $db = $rcube->get_dbh(); + $now = $db->now(); - $db->query("DELETE FROM ".$db->table_name('cache_messages') - ." WHERE expires < " . $db->now()); + $db->query("DELETE FROM " . $db->table_name('cache_messages', true) + ." WHERE `expires` < $now"); - $db->query("DELETE FROM ".$db->table_name('cache_index') - ." WHERE expires < " . $db->now()); + $db->query("DELETE FROM " . $db->table_name('cache_index', true) + ." WHERE `expires` < $now"); - $db->query("DELETE FROM ".$db->table_name('cache_thread') - ." WHERE expires < " . $db->now()); + $db->query("DELETE FROM ".$db->table_name('cache_thread', true) + ." WHERE `expires` < $now"); } @@ -711,10 +717,10 @@ class rcube_imap_cache { // Get index from DB $sql_result = $this->db->query( - "SELECT data, valid" - ." FROM ".$this->db->table_name('cache_index') - ." WHERE user_id = ?" - ." AND mailbox = ?", + "SELECT `data`, `valid`" + ." FROM {$this->index_table}" + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?", $this->userid, $mailbox); if ($sql_arr = $this->db->fetch_assoc($sql_result)) { @@ -748,10 +754,10 @@ class rcube_imap_cache { // Get thread from DB $sql_result = $this->db->query( - "SELECT data" - ." FROM ".$this->db->table_name('cache_thread') - ." WHERE user_id = ?" - ." AND mailbox = ?", + "SELECT `data`" + ." FROM {$this->thread_table}" + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?", $this->userid, $mailbox); if ($sql_arr = $this->db->fetch_assoc($sql_result)) { @@ -789,14 +795,16 @@ class rcube_imap_cache (int) $mbox_data['UIDNEXT'], $modseq ? $modseq : $mbox_data['HIGHESTMODSEQ'], ); - $data = implode('@', $data); + + $data = implode('@', $data); + $expires = $this->ttl ? $this->db->now($this->ttl) : 'NULL'; if ($exists) { $res = $this->db->query( - "UPDATE ".$this->db->table_name('cache_index') - ." SET data = ?, valid = 1, expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') - ." WHERE user_id = ?" - ." AND mailbox = ?", + "UPDATE {$this->index_table}" + ." SET `data` = ?, `valid` = 1, `expires` = $expires" + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?", $data, $this->userid, $mailbox); if ($this->db->affected_rows($res)) { @@ -807,19 +815,19 @@ class rcube_imap_cache $this->db->set_option('ignore_key_errors', true); $res = $this->db->query( - "INSERT INTO ".$this->db->table_name('cache_index') - ." (user_id, mailbox, valid, expires, data)" - ." VALUES (?, ?, 1, ". ($this->ttl ? $this->db->now($this->ttl) : 'NULL') .", ?)", + "INSERT INTO {$this->index_table}" + ." (`user_id`, `mailbox`, `valid`, `expires`, `data`)" + ." VALUES (?, ?, 1, $expires, ?)", $this->userid, $mailbox, $data); // race-condition, insert failed so try update (#1489146) // thanks to ignore_key_errors "duplicate row" errors will be ignored if (!$exists && !$res && !$this->db->is_error($res)) { $res = $this->db->query( - "UPDATE ".$this->db->table_name('cache_index') - ." SET data = ?, valid = 1, expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') - ." WHERE user_id = ?" - ." AND mailbox = ?", + "UPDATE {$this->index_table}" + ." SET `data` = ?, `valid` = 1, `expires` = $expires" + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?", $data, $this->userid, $mailbox); } @@ -838,16 +846,16 @@ class rcube_imap_cache (int) $mbox_data['UIDVALIDITY'], (int) $mbox_data['UIDNEXT'], ); - $data = implode('@', $data); - $expires = ($this->ttl ? $this->db->now($this->ttl) : 'NULL'); + $data = implode('@', $data); + $expires = $this->ttl ? $this->db->now($this->ttl) : 'NULL'; if ($exists) { $res = $this->db->query( - "UPDATE ".$this->db->table_name('cache_thread') - ." SET data = ?, expires = $expires" - ." WHERE user_id = ?" - ." AND mailbox = ?", + "UPDATE {$this->thread_table}" + ." SET `data` = ?, `expires` = $expires" + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?", $data, $this->userid, $mailbox); if ($this->db->affected_rows($res)) { @@ -858,8 +866,8 @@ class rcube_imap_cache $this->db->set_option('ignore_key_errors', true); $res = $this->db->query( - "INSERT INTO ".$this->db->table_name('cache_thread') - ." (user_id, mailbox, expires, data)" + "INSERT INTO {$this->thread_table}" + ." (`user_id`, `mailbox`, `expires`, `data`)" ." VALUES (?, ?, $expires, ?)", $this->userid, $mailbox, $data); @@ -867,10 +875,10 @@ class rcube_imap_cache // thanks to ignore_key_errors "duplicate row" errors will be ignored if (!$exists && !$res && !$this->db->is_error($res)) { $this->db->query( - "UPDATE ".$this->db->table_name('cache_thread') - ." SET expires = $expires, data = ?" - ." WHERE user_id = ?" - ." AND mailbox = ?", + "UPDATE {$this->thread_table}" + ." SET `expires` = $expires, `data` = ?" + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?", $data, $this->userid, $mailbox); } @@ -1086,10 +1094,10 @@ class rcube_imap_cache // Get known UIDs if ($this->mode & self::MODE_MESSAGE) { $sql_result = $this->db->query( - "SELECT uid" - ." FROM ".$this->db->table_name('cache_messages') - ." WHERE user_id = ?" - ." AND mailbox = ?", + "SELECT `uid`" + ." FROM {$this->messages_table}" + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?", $this->userid, $mailbox); while ($sql_arr = $this->db->fetch_assoc($sql_result)) { @@ -1125,12 +1133,12 @@ class rcube_imap_cache } $this->db->query( - "UPDATE ".$this->db->table_name('cache_messages') - ." SET flags = ?, expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') - ." WHERE user_id = ?" - ." AND mailbox = ?" - ." AND uid = ?" - ." AND flags <> ?", + "UPDATE {$this->messages_table}" + ." SET `flags` = ?, `expires` = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') + ." WHERE `user_id` = ?" + ." AND `mailbox` = ?" + ." AND `uid` = ?" + ." AND `flags` <> ?", $flags, $this->userid, $mailbox, $uid, $flags); } } diff --git a/program/lib/Roundcube/rcube_session.php b/program/lib/Roundcube/rcube_session.php index 26f78433a..8306a0687 100644 --- a/program/lib/Roundcube/rcube_session.php +++ b/program/lib/Roundcube/rcube_session.php @@ -103,6 +103,8 @@ class rcube_session array($this, 'db_write'), array($this, 'db_destroy'), array($this, 'gc')); + + $this->table_name = $this->db->table_name('session', true); } } @@ -175,9 +177,8 @@ class rcube_session public function db_read($key) { $sql_result = $this->db->query( - "SELECT vars, ip, changed, " . $this->db->now() . " AS ts" - . " FROM " . $this->db->table_name('session') - . " WHERE sess_id = ?", $key); + "SELECT `vars`, `ip`, `changed`, " . $this->db->now() . " AS ts" + . " FROM {$this->table_name} WHERE `sess_id` = ?", $key); if ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) { $this->time_diff = time() - strtotime($sql_arr['ts']); @@ -204,9 +205,8 @@ class rcube_session */ public function db_write($key, $vars) { - $now = $this->db->now(); - $table = $this->db->table_name('session'); - $ts = microtime(true); + $now = $this->db->now(); + $ts = microtime(true); if ($this->nowrite) return true; @@ -227,17 +227,18 @@ class rcube_session $newvars = $this->_fixvars($vars, $oldvars); if ($newvars !== $oldvars) { - $this->db->query("UPDATE $table " - . "SET changed = $now, vars = ? WHERE sess_id = ?", + $this->db->query("UPDATE {$this->table_name} " + . "SET `changed` = $now, `vars` = ? WHERE `sess_id` = ?", base64_encode($newvars), $key); } else if ($ts - $this->changed + $this->time_diff > $this->lifetime / 2) { - $this->db->query("UPDATE $table SET changed = $now" - . " WHERE sess_id = ?", $key); + $this->db->query("UPDATE {$this->table_name} SET `changed` = $now" + . " WHERE `sess_id` = ?", $key); } } else { - $this->db->query("INSERT INTO $table (sess_id, vars, ip, created, changed)" + $this->db->query("INSERT INTO {$this->table_name}" + . " (`sess_id`, `vars`, `ip`, `created`, `changed`)" . " VALUES (?, ?, ?, $now, $now)", $key, base64_encode($vars), (string)$this->ip); } @@ -290,8 +291,7 @@ class rcube_session public function db_destroy($key) { if ($key) { - $this->db->query(sprintf("DELETE FROM %s WHERE sess_id = ?", - $this->db->table_name('session')), $key); + $this->db->query("DELETE FROM {$this->table_name} WHERE `sess_id` = ?", $key); } return true; @@ -407,8 +407,8 @@ class rcube_session if ($this->gc_enabled) { // just delete all expired sessions if ($this->storage == 'db') { - $this->db->query("DELETE FROM " . $this->db->table_name('session') - . " WHERE changed < " . $this->db->now(-$this->gc_enabled)); + $this->db->query("DELETE FROM {$this->table_name}" + . " WHERE `changed` < " . $this->db->now(-$this->gc_enabled)); } foreach ($this->gc_handlers as $fct) { diff --git a/program/lib/Roundcube/rcube_spellchecker.php b/program/lib/Roundcube/rcube_spellchecker.php index 43bab08c4..062780720 100644 --- a/program/lib/Roundcube/rcube_spellchecker.php +++ b/program/lib/Roundcube/rcube_spellchecker.php @@ -360,25 +360,25 @@ class rcube_spellchecker if ($this->have_dict) { if (!empty($this->dict)) { $this->rc->db->query( - "UPDATE ".$this->rc->db->table_name('dictionary') - ." SET data = ?" - ." WHERE user_id " . ($plugin['userid'] ? "= ".$this->rc->db->quote($plugin['userid']) : "IS NULL") - ." AND " . $this->rc->db->quote_identifier('language') . " = ?", + "UPDATE " . $this->rc->db->table_name('dictionary', true) + ." SET `data` = ?" + ." WHERE `user_id` " . ($plugin['userid'] ? "= ".$this->rc->db->quote($plugin['userid']) : "IS NULL") + ." AND `language` = ?", implode(' ', $plugin['dictionary']), $plugin['language']); } // don't store empty dict else { $this->rc->db->query( - "DELETE FROM " . $this->rc->db->table_name('dictionary') - ." WHERE user_id " . ($plugin['userid'] ? "= ".$this->rc->db->quote($plugin['userid']) : "IS NULL") - ." AND " . $this->rc->db->quote_identifier('language') . " = ?", + "DELETE FROM " . $this->rc->db->table_name('dictionary', true) + ." WHERE `user_id` " . ($plugin['userid'] ? "= ".$this->rc->db->quote($plugin['userid']) : "IS NULL") + ." AND `language` = ?", $plugin['language']); } } else if (!empty($this->dict)) { $this->rc->db->query( - "INSERT INTO " .$this->rc->db->table_name('dictionary') - ." (user_id, " . $this->rc->db->quote_identifier('language') . ", data) VALUES (?, ?, ?)", + "INSERT INTO " . $this->rc->db->table_name('dictionary', true) + ." (`user_id`, `language`, `data`) VALUES (?, ?, ?)", $plugin['userid'], $plugin['language'], implode(' ', $plugin['dictionary'])); } } @@ -403,9 +403,9 @@ class rcube_spellchecker if (empty($plugin['abort'])) { $dict = array(); $sql_result = $this->rc->db->query( - "SELECT data FROM ".$this->rc->db->table_name('dictionary') - ." WHERE user_id ". ($plugin['userid'] ? "= ".$this->rc->db->quote($plugin['userid']) : "IS NULL") - ." AND " . $this->rc->db->quote_identifier('language') . " = ?", + "SELECT `data` FROM " . $this->rc->db->table_name('dictionary', true) + ." WHERE `user_id` ". ($plugin['userid'] ? "= ".$this->rc->db->quote($plugin['userid']) : "IS NULL") + ." AND `language` = ?", $plugin['language']); if ($sql_arr = $this->rc->db->fetch_assoc($sql_result)) { @@ -427,5 +427,4 @@ class rcube_spellchecker return $this->dict; } - } diff --git a/program/lib/Roundcube/rcube_user.php b/program/lib/Roundcube/rcube_user.php index 739b6f2a0..b2110df8b 100644 --- a/program/lib/Roundcube/rcube_user.php +++ b/program/lib/Roundcube/rcube_user.php @@ -67,7 +67,8 @@ class rcube_user if ($id && !$sql_arr) { $sql_result = $this->db->query( - "SELECT * FROM ".$this->db->table_name('users')." WHERE user_id = ?", $id); + "SELECT * FROM " . $this->db->table_name('users', true) + . " WHERE `user_id` = ?", $id); $sql_arr = $this->db->fetch_assoc($sql_result); } @@ -189,10 +190,9 @@ class rcube_user $save_prefs = serialize($save_prefs); $this->db->query( - "UPDATE ".$this->db->table_name('users'). - " SET preferences = ?". - ", language = ?". - " WHERE user_id = ?", + "UPDATE ".$this->db->table_name('users', true). + " SET `preferences` = ?, `language` = ?". + " WHERE `user_id` = ?", $save_prefs, $_SESSION['language'], $this->ID); @@ -243,7 +243,7 @@ class rcube_user $id = (int)$id; // cache identities for better performance if (!array_key_exists($id, $this->identities)) { - $result = $this->list_identities($id ? 'AND identity_id = ' . $id : ''); + $result = $this->list_identities($id ? "AND `identity_id` = $id" : ''); $this->identities[$id] = $result[0]; } @@ -264,13 +264,10 @@ class rcube_user $result = array(); $sql_result = $this->db->query( - "SELECT * FROM ".$this->db->table_name('identities'). - " WHERE del <> 1 AND user_id = ?". + "SELECT * FROM ".$this->db->table_name('identities', true). + " WHERE `del` <> 1 AND `user_id` = ?". ($sql_add ? " ".$sql_add : ""). - " ORDER BY ". $this->db->quote_identifier('standard') . " DESC, " - . $this->db->quote_identifier('name') . " ASC, " - . $this->db->quote_identifier('email') . " ASC, " - . $this->db->quote_identifier('identity_id') . " ASC", + " ORDER BY `standard` DESC, `name` ASC, `email` ASC, `identity_id` ASC", $this->ID); while ($sql_arr = $this->db->fetch_assoc($sql_result)) { @@ -311,11 +308,11 @@ class rcube_user $query_params[] = $iid; $query_params[] = $this->ID; - $sql = "UPDATE ".$this->db->table_name('identities'). - " SET changed = ".$this->db->now().", ".join(', ', $query_cols). - " WHERE identity_id = ?". - " AND user_id = ?". - " AND del <> 1"; + $sql = "UPDATE ".$this->db->table_name('identities', true). + " SET `changed` = ".$this->db->now().", ".join(', ', $query_cols). + " WHERE `identity_id` = ?". + " AND `user_id` = ?". + " AND `del` <> 1"; call_user_func_array(array($this->db, 'query'), array_merge(array($sql), $query_params)); @@ -344,11 +341,11 @@ class rcube_user $insert_cols[] = $this->db->quote_identifier($col); $insert_values[] = $value; } - $insert_cols[] = 'user_id'; + $insert_cols[] = $this->db->quote_identifier('user_id'); $insert_values[] = $this->ID; - $sql = "INSERT INTO ".$this->db->table_name('identities'). - " (changed, ".join(', ', $insert_cols).")". + $sql = "INSERT INTO ".$this->db->table_name('identities', true). + " (`changed`, ".join(', ', $insert_cols).")". " VALUES (".$this->db->now().", ".join(', ', array_pad(array(), sizeof($insert_values), '?')).")"; call_user_func_array(array($this->db, 'query'), @@ -372,8 +369,8 @@ class rcube_user return false; $sql_result = $this->db->query( - "SELECT count(*) AS ident_count FROM ".$this->db->table_name('identities'). - " WHERE user_id = ? AND del <> 1", + "SELECT count(*) AS ident_count FROM ".$this->db->table_name('identities', true). + " WHERE `user_id` = ? AND `del` <> 1", $this->ID); $sql_arr = $this->db->fetch_assoc($sql_result); @@ -383,10 +380,10 @@ class rcube_user return -1; $this->db->query( - "UPDATE ".$this->db->table_name('identities'). - " SET del = 1, changed = ".$this->db->now(). - " WHERE user_id = ?". - " AND identity_id = ?", + "UPDATE ".$this->db->table_name('identities', true). + " SET `del` = 1, `changed` = ".$this->db->now(). + " WHERE `user_id` = ?". + " AND `identity_id` = ?", $this->ID, $iid); @@ -405,11 +402,9 @@ class rcube_user { if ($this->ID && $iid) { $this->db->query( - "UPDATE ".$this->db->table_name('identities'). - " SET ".$this->db->quote_identifier('standard')." = '0'". - " WHERE user_id = ?". - " AND identity_id <> ?". - " AND del <> 1", + "UPDATE ".$this->db->table_name('identities', true). + " SET `standard` = '0'". + " WHERE `user_id` = ? AND `identity_id` <> ?", $this->ID, $iid); @@ -425,9 +420,9 @@ class rcube_user { if ($this->ID) { $this->db->query( - "UPDATE ".$this->db->table_name('users'). - " SET last_login = ".$this->db->now(). - " WHERE user_id = ?", + "UPDATE ".$this->db->table_name('users', true). + " SET `last_login` = ".$this->db->now(). + " WHERE `user_id` = ?", $this->ID); } } @@ -456,17 +451,17 @@ class rcube_user $config = rcube::get_instance()->config; // query for matching user name - $sql_result = $dbh->query("SELECT * FROM " . $dbh->table_name('users') - ." WHERE mail_host = ? AND username = ?", $host, $user); + $sql_result = $dbh->query("SELECT * FROM " . $dbh->table_name('users', true) + ." WHERE `mail_host` = ? AND `username` = ?", $host, $user); $sql_arr = $dbh->fetch_assoc($sql_result); // username not found, try aliases from identities if (empty($sql_arr) && $config->get('user_aliases') && strpos($user, '@')) { $sql_result = $dbh->limitquery("SELECT u.*" - ." FROM " . $dbh->table_name('users') . " u" - ." JOIN " . $dbh->table_name('identities') . " i ON (i.user_id = u.user_id)" - ." WHERE email = ? AND del <> 1", 0, 1, $user); + ." FROM " . $dbh->table_name('users', true) . " u" + ." JOIN " . $dbh->table_name('identities', true) . " i ON (i.`user_id` = u.`user_id`)" + ." WHERE `email` = ? AND `del` <> 1", 0, 1, $user); $sql_arr = $dbh->fetch_assoc($sql_result); } @@ -513,8 +508,8 @@ class rcube_user } $dbh->query( - "INSERT INTO ".$dbh->table_name('users'). - " (created, last_login, username, mail_host, language)". + "INSERT INTO ".$dbh->table_name('users', true). + " (`created`, `last_login`, `username`, `mail_host`, `language`)". " VALUES (".$dbh->now().", ".$dbh->now().", ?, ?, ?)", $data['user'], $data['host'], @@ -646,11 +641,10 @@ class rcube_user $result = array(); $sql_result = $this->db->query( - "SELECT search_id AS id, ".$this->db->quote_identifier('name') - ." FROM ".$this->db->table_name('searches') - ." WHERE user_id = ?" - ." AND ".$this->db->quote_identifier('type')." = ?" - ." ORDER BY ".$this->db->quote_identifier('name'), + "SELECT `search_id` AS id, `name`" + ." FROM ".$this->db->table_name('searches', true) + ." WHERE `user_id` = ? AND `type` = ?" + ." ORDER BY `name`", (int) $this->ID, (int) $type); while ($sql_arr = $this->db->fetch_assoc($sql_result)) { @@ -678,12 +672,10 @@ class rcube_user } $sql_result = $this->db->query( - "SELECT ".$this->db->quote_identifier('name') - .", ".$this->db->quote_identifier('data') - .", ".$this->db->quote_identifier('type') - ." FROM ".$this->db->table_name('searches') - ." WHERE user_id = ?" - ." AND search_id = ?", + "SELECT `name`, `data`, `type`" + . " FROM ".$this->db->table_name('searches', true) + . " WHERE `user_id` = ?" + ." AND `search_id` = ?", (int) $this->ID, (int) $id); while ($sql_arr = $this->db->fetch_assoc($sql_result)) { @@ -712,9 +704,9 @@ class rcube_user return false; $this->db->query( - "DELETE FROM ".$this->db->table_name('searches') - ." WHERE user_id = ?" - ." AND search_id = ?", + "DELETE FROM ".$this->db->table_name('searches', true) + ." WHERE `user_id` = ?" + ." AND `search_id` = ?", (int) $this->ID, $sid); return $this->db->affected_rows(); @@ -742,7 +734,7 @@ class rcube_user $insert_cols[] = $this->db->quote_identifier('data'); $insert_values[] = serialize($data['data']); - $sql = "INSERT INTO ".$this->db->table_name('searches') + $sql = "INSERT INTO ".$this->db->table_name('searches', true) ." (".join(', ', $insert_cols).")" ." VALUES (".join(', ', array_pad(array(), sizeof($insert_values), '?')).")"; @@ -751,5 +743,4 @@ class rcube_user return $this->db->insert_id('searches'); } - } |