Use consistent column/table quoting in sql queries

1 files changed, 102 insertions, 94 deletions

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);
                 }
             }