diff options
| -rwxr-xr-x | bin/cleandb.sh | 12 | ||||
| -rwxr-xr-x | bin/deluser.sh | 2 | ||||
| -rwxr-xr-x | bin/indexcontacts.sh | 2 | ||||
| -rwxr-xr-x | bin/moduserprefs.sh | 4 | ||||
| -rwxr-xr-x | bin/updatedb.sh | 15 | ||||
| -rw-r--r-- | installer/test.php | 10 | ||||
| -rw-r--r-- | plugins/squirrelmail_usercopy/squirrelmail_usercopy.php | 7 | ||||
| -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_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 | 103 | ||||
| -rw-r--r-- | program/steps/utils/killcache.inc | 13 | ||||
| -rw-r--r-- | tests/Framework/ImapCache.php | 2 |
17 files changed, 406 insertions, 340 deletions
diff --git a/bin/cleandb.sh b/bin/cleandb.sh index 165d33f38..9edfeec91 100755 --- a/bin/cleandb.sh +++ b/bin/cleandb.sh @@ -48,7 +48,7 @@ $threshold = date('Y-m-d 00:00:00', time() - $days * 86400); foreach (array('contacts','contactgroups','identities') as $table) { - $sqltable = $db->table_name($table); + $sqltable = $db->table_name($table, true); // also delete linked records // could be skipped for databases which respect foreign key constraints @@ -59,10 +59,10 @@ foreach (array('contacts','contactgroups','identities') as $table) { $memberstable = $db->table_name('contactgroupmembers'); $db->query( - "DELETE FROM $memberstable". - " WHERE $pk IN (". - "SELECT $pk FROM $sqltable". - " WHERE del=1 AND changed < ?". + "DELETE FROM " . $db->quote_identifier($memberstable). + " WHERE `$pk` IN (". + "SELECT `$pk` FROM $sqltable". + " WHERE `del` = 1 AND `changed` < ?". ")", $threshold); @@ -70,7 +70,7 @@ foreach (array('contacts','contactgroups','identities') as $table) { } // delete outdated records - $db->query("DELETE FROM $sqltable WHERE del=1 AND changed < ?", $threshold); + $db->query("DELETE FROM $sqltable WHERE `del` = 1 AND `changed` < ?", $threshold); echo $db->affected_rows() . " records deleted from '$table'\n"; } diff --git a/bin/deluser.sh b/bin/deluser.sh index f12ec9032..9504d5b43 100755 --- a/bin/deluser.sh +++ b/bin/deluser.sh @@ -91,7 +91,7 @@ if ($plugin['abort']) { // deleting the user record should be sufficient due to ON DELETE CASCADE foreign key references // but not all database backends actually support this so let's do it by hand foreach (array('identities','contacts','contactgroups','dictionaries','cache','cache_index','cache_messages','cache_thread','searches','users') as $table) { - $db->query('DELETE FROM ' . $db->table_name($table) . ' WHERE user_id=?', $user->ID); + $db->query('DELETE FROM ' . $db->table_name($table, true) . ' WHERE `user_id` = ?', $user->ID); } if ($db->is_error()) { diff --git a/bin/indexcontacts.sh b/bin/indexcontacts.sh index df403807c..9509dc06a 100755 --- a/bin/indexcontacts.sh +++ b/bin/indexcontacts.sh @@ -35,7 +35,7 @@ if (!$db->is_connected() || $db->is_error()) { } // iterate over all users -$sql_result = $db->query("SELECT user_id FROM " . $db->table_name('users') . " ORDER BY user_id"); +$sql_result = $db->query("SELECT `user_id` FROM " . $db->table_name('users', true) . " ORDER BY `user_id`"); while ($sql_result && ($sql_arr = $db->fetch_assoc($sql_result))) { echo "Indexing contacts for user " . $sql_arr['user_id'] . "..."; diff --git a/bin/moduserprefs.sh b/bin/moduserprefs.sh index 8a9725fa4..e892b1f4d 100755 --- a/bin/moduserprefs.sh +++ b/bin/moduserprefs.sh @@ -58,10 +58,10 @@ if (!$db->is_connected() || $db->is_error()) $query = '1=1'; if ($args['user']) - $query = 'user_id=' . intval($args['user']); + $query = '`user_id` = ' . intval($args['user']); // iterate over all users -$sql_result = $db->query("SELECT * FROM " . $db->table_name('users') . " WHERE $query"); +$sql_result = $db->query("SELECT * FROM " . $db->table_name('users', true) . " WHERE $query"); while ($sql_result && ($sql_arr = $db->fetch_assoc($sql_result))) { echo "Updating prefs for user " . $sql_arr['user_id'] . "..."; diff --git a/bin/updatedb.sh b/bin/updatedb.sh index 964bc184c..e9818074d 100755 --- a/bin/updatedb.sh +++ b/bin/updatedb.sh @@ -55,9 +55,9 @@ if (!$DB->is_connected()) { // Read DB schema version from database (if 'system' table exists) if (in_array($DB->table_name('system'), (array)$DB->list_tables())) { - $DB->query("SELECT " . $DB->quote_identifier('value') - ." FROM " . $DB->quote_identifier($DB->table_name('system')) - ." WHERE " . $DB->quote_identifier('name') ." = ?", + $DB->query("SELECT `value`" + ." FROM " . $DB->table_name('system', true) + ." WHERE `name` = ?", $opts['package'] . '-version'); $row = $DB->fetch_array(); @@ -159,17 +159,16 @@ function update_db_schema($package, $version, $file) return; } - $system_table = $DB->quote_identifier($DB->table_name('system')); + $system_table = $DB->table_name('system', true); $DB->query("UPDATE " . $system_table - ." SET " . $DB->quote_identifier('value') . " = ?" - ." WHERE " . $DB->quote_identifier('name') . " = ?", + ." SET `value` = ?" + ." WHERE `name` = ?", $version, $package . '-version'); if (!$DB->is_error() && !$DB->affected_rows()) { $DB->query("INSERT INTO " . $system_table - ." (" . $DB->quote_identifier('name') . ", " . $DB->quote_identifier('value') . ")" - ." VALUES (?, ?)", + ." (`name`, `value`) VALUES (?, ?)", $package . '-version', $version); } diff --git a/installer/test.php b/installer/test.php index 72c7a1f76..988451e9f 100644 --- a/installer/test.php +++ b/installer/test.php @@ -154,7 +154,7 @@ else if ($db_working && $_POST['updatedb']) { // test database if ($db_working) { - $db_read = $DB->query("SELECT count(*) FROM {$RCI->config['db_prefix']}users"); + $db_read = $DB->query("SELECT count(*) FROM " . $DB->quote_identifier($RCI->config['db_prefix'] . 'users')); if ($DB->is_error()) { $RCI->fail('DB Schema', "Database not initialized"); echo '<p><input type="submit" name="initdb" value="Initialize database" /></p>'; @@ -178,17 +178,19 @@ if ($db_working) { if ($db_working) { // write test $insert_id = md5(uniqid()); - $db_write = $DB->query("INSERT INTO {$RCI->config['db_prefix']}session (sess_id, created, ip, vars) VALUES (?, ".$DB->now().", '127.0.0.1', 'foo')", $insert_id); + $db_write = $DB->query("INSERT INTO " . $DB->quote_identifier($RCI->config['db_prefix'] . 'session') + . " (`sess_id`, `created`, `ip`, `vars`) VALUES (?, ".$DB->now().", '127.0.0.1', 'foo')", $insert_id); if ($db_write) { $RCI->pass('DB Write'); - $DB->query("DELETE FROM {$RCI->config['db_prefix']}session WHERE sess_id=?", $insert_id); + $DB->query("DELETE FROM " . $DB->quote_identifier($RCI->config['db_prefix'] . 'session') + . " WHERE `sess_id` = ?", $insert_id); } else { $RCI->fail('DB Write', $RCI->get_error()); } echo '<br />'; - + // check timezone settings $tz_db = 'SELECT ' . $DB->unixtimestamp($DB->now()) . ' AS tz_db'; $tz_db = $DB->query($tz_db); diff --git a/plugins/squirrelmail_usercopy/squirrelmail_usercopy.php b/plugins/squirrelmail_usercopy/squirrelmail_usercopy.php index e882a2f37..7f378678e 100644 --- a/plugins/squirrelmail_usercopy/squirrelmail_usercopy.php +++ b/plugins/squirrelmail_usercopy/squirrelmail_usercopy.php @@ -164,14 +164,16 @@ class squirrelmail_usercopy extends rcube_plugin if ($db_charset) $db->query('SET NAMES '.$db_charset); - $sql_result = $db->query('SELECT * FROM '.$userprefs_table.' WHERE user=?', $uname); // ? is replaced with emailaddress + $sql_result = $db->query('SELECT * FROM ' . $db->quote_identifier($userprefs_table) + .' WHERE `user` = ?', $uname); // ? is replaced with emailaddress while ($sql_array = $db->fetch_assoc($sql_result) ) { // fetch one row from result $this->prefs[$sql_array['prefkey']] = rcube_charset::convert(rtrim($sql_array['prefval']), $db_charset); } /* retrieve address table data */ - $sql_result = $db->query('SELECT * FROM '.$address_table.' WHERE owner=?', $uname); // ? is replaced with emailaddress + $sql_result = $db->query('SELECT * FROM ' . $db->quote_identifier($address_table) + .' WHERE `owner` = ?', $uname); // ? is replaced with emailaddress // parse addres book while ($sql_array = $db->fetch_assoc($sql_result) ) { // fetch one row from result @@ -186,5 +188,4 @@ class squirrelmail_usercopy extends rcube_plugin } } // end if 'sql'-driver } - } 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_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..b63cabece 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)); @@ -347,8 +344,8 @@ class rcube_user $insert_cols[] = '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'); } - } diff --git a/program/steps/utils/killcache.inc b/program/steps/utils/killcache.inc index 1cb99b879..1adaebd8c 100644 --- a/program/steps/utils/killcache.inc +++ b/program/steps/utils/killcache.inc @@ -26,22 +26,27 @@ if (!$RCMAIL->config->get('devel_mode')) { } // @TODO: transaction here (if supported by DB) would be a good thing -$res = $RCMAIL->db->query("DELETE FROM cache"); +$res = $RCMAIL->db->query("DELETE FROM " . $RCMAIL->db->table_name('cache', true)); if ($err = $RCMAIL->db->is_error($res)) { exit($err); } -$res = $RCMAIL->db->query("DELETE FROM cache_messages"); +$res = $RCMAIL->db->query("DELETE FROM " . $RCMAIL->db->table_name('cache_shared', true)); if ($err = $RCMAIL->db->is_error($res)) { exit($err); } -$res = $RCMAIL->db->query("DELETE FROM cache_index"); +$res = $RCMAIL->db->query("DELETE FROM " . $RCMAIL->db->table_name('cache_messages', true)); if ($err = $RCMAIL->db->is_error($res)) { exit($err); } -$res = $RCMAIL->db->query("DELETE FROM cache_thread"); +$res = $RCMAIL->db->query("DELETE FROM " . $RCMAIL->db->table_name('cache_index', true)); +if ($err = $RCMAIL->db->is_error($res)) { + exit($err); +} + +$res = $RCMAIL->db->query("DELETE FROM " . $RCMAIL->db->table_name('cache_thread', true)); if ($err = $RCMAIL->db->is_error($res)) { exit($err); } diff --git a/tests/Framework/ImapCache.php b/tests/Framework/ImapCache.php index 83612c549..6c7f98807 100644 --- a/tests/Framework/ImapCache.php +++ b/tests/Framework/ImapCache.php @@ -13,7 +13,7 @@ class Framework_ImapCache extends PHPUnit_Framework_TestCase */ function test_class() { - $object = new rcube_imap_cache(null, null, null, null); + $object = new rcube_imap_cache(new rcube_db('test'), null, null, null); $this->assertInstanceOf('rcube_imap_cache', $object, "Class constructor"); } |