Make cache keys case-sensitive on MySQL/MSSQL (#6942)

11 files changed, 38 insertions, 15 deletions

diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index 0ac0a30a9..e9d6d934d 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -1,13 +1,13 @@
 CREATE TABLE [dbo].[cache] (

 	[user_id] [int] NOT NULL ,

-	[cache_key] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[cache_key] [varchar] (128) COLLATE Latin1_General_CS_AS NOT NULL ,

 	[expires] [datetime] NULL ,

 	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 

 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 GO

 

 CREATE TABLE [dbo].[cache_shared] (

-	[cache_key] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[cache_key] [varchar] (255) COLLATE Latin1_General_CS_AS NOT NULL ,

 	[expires] [datetime] NULL ,

 	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 

 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

@@ -15,7 +15,7 @@ GO
 

 CREATE TABLE [dbo].[cache_index] (

 	[user_id] [int] NOT NULL ,

-	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CS_AS NOT NULL ,

 	[expires] [datetime] NULL ,

 	[valid] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,

 	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 

@@ -24,7 +24,7 @@ GO
 

 CREATE TABLE [dbo].[cache_thread] (

 	[user_id] [int] NOT NULL ,

-	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CS_AS NOT NULL ,

 	[expires] [datetime] NULL ,

 	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 

 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

@@ -32,7 +32,7 @@ GO
 

 CREATE TABLE [dbo].[cache_messages] (

 	[user_id] [int] NOT NULL ,

-	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CS_AS NOT NULL ,

 	[uid] [int] NOT NULL ,

 	[expires] [datetime] NULL ,

 	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL ,

@@ -96,7 +96,7 @@ GO
 

 CREATE TABLE [dbo].[users] (

 	[user_id] [int] IDENTITY (1, 1) NOT NULL ,

-	[username] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[username] [varchar] (128) COLLATE Latin1_General_CS_AS NOT NULL ,

 	[mail_host] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

 	[created] [datetime] NOT NULL ,

 	[last_login] [datetime] NULL ,

@@ -422,6 +422,6 @@ CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
     WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)

 GO

 

-INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2018122300')

+INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2019092900')

 GO

 
\ No newline at end of file
diff --git a/SQL/mssql/2019092900.sql b/SQL/mssql/2019092900.sql
new file mode 100644
index 000000000..bebf01c95
--- /dev/null
+++ b/SQL/mssql/2019092900.sql
@@ -0,0 +1,18 @@
+ALTER TABLE [dbo].[cache] ALTER COLUMN

+	[cache_key] [varchar] (128) COLLATE Latin1_General_CS_AS NOT NULL

+GO

+ALTER TABLE [dbo].[cache_shared] ALTER COLUMN

+	[cache_key] [varchar] (255) COLLATE Latin1_General_CS_AS NOT NULL

+GO

+ALTER TABLE [dbo].[cache_index] ALTER COLUMN

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CS_AS NOT NULL

+GO

+ALTER TABLE [dbo].[cache_messages] ALTER COLUMN

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CS_AS NOT NULL

+GO

+ALTER TABLE [dbo].[cache_thread] ALTER COLUMN

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CS_AS NOT NULL

+GO

+ALTER TABLE [dbo].[users] ALTER COLUMN

+	[username] [varchar] (128) COLLATE Latin1_General_CS_AS NOT NULL

+GO

diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 2346e767b..fee1b6d0e 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -36,7 +36,7 @@ CREATE TABLE `users` (
 
 CREATE TABLE `cache` (
  `user_id` int(10) UNSIGNED NOT NULL,
- `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
+ `cache_key` varchar(128) BINARY NOT NULL,
  `expires` datetime DEFAULT NULL,
  `data` longtext NOT NULL,
  PRIMARY KEY (`user_id`, `cache_key`),
@@ -49,7 +49,7 @@ CREATE TABLE `cache` (
 -- Table structure for table `cache_shared`
 
 CREATE TABLE `cache_shared` (
- `cache_key` varchar(255) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
+ `cache_key` varchar(255) BINARY NOT NULL,
  `expires` datetime DEFAULT NULL,
  `data` longtext NOT NULL,
  PRIMARY KEY (`cache_key`),
@@ -211,7 +211,7 @@ CREATE TABLE `filestore` (
  CONSTRAINT `user_id_fk_filestore` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE `uniqueness` (`user_id`, `context`, `filename`)
-);
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
 -- Table structure for table `system`
 
@@ -223,4 +223,4 @@ CREATE TABLE `system` (
 
 /*!40014 SET FOREIGN_KEY_CHECKS=1 */;
 
-INSERT INTO `system` (`name`, `value`) VALUES ('roundcube-version', '2018122300');
+INSERT INTO `system` (`name`, `value`) VALUES ('roundcube-version', '2019092900');
diff --git a/SQL/mysql/2018021600.sql b/SQL/mysql/2018021600.sql
index 10098511c..ed72c0e34 100644
--- a/SQL/mysql/2018021600.sql
+++ b/SQL/mysql/2018021600.sql
@@ -8,4 +8,4 @@ CREATE TABLE `filestore` (
     CONSTRAINT `user_id_fk_filestore` FOREIGN KEY (`user_id`)
         REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
     UNIQUE `uniqueness` (`user_id`, `filename`)
-);
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
diff --git a/SQL/mysql/2019092900.sql b/SQL/mysql/2019092900.sql
new file mode 100644
index 000000000..204b15997
--- /dev/null
+++ b/SQL/mysql/2019092900.sql
@@ -0,0 +1,2 @@
+ALTER TABLE `cache` CHANGE `cache_key` `cache_key` varchar(128) BINARY NOT NULL;
+ALTER TABLE `cache_shared` CHANGE `cache_key` `cache_key` varchar(255) BINARY NOT NULL;
diff --git a/SQL/oracle.initial.sql b/SQL/oracle.initial.sql
index ed4897983..fb821ea03 100644
--- a/SQL/oracle.initial.sql
+++ b/SQL/oracle.initial.sql
@@ -238,4 +238,4 @@ CREATE TABLE "system" (
     "value" long
 );
 
-INSERT INTO "system" ("name", "value") VALUES ('roundcube-version', '2018122300');
+INSERT INTO "system" ("name", "value") VALUES ('roundcube-version', '2019092900');
diff --git a/SQL/oracle/2019092900.sql b/SQL/oracle/2019092900.sql
new file mode 100644
index 000000000..fe6741a02
--- /dev/null
+++ b/SQL/oracle/2019092900.sql
@@ -0,0 +1 @@
+-- empty
\ No newline at end of file
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index 34b7b2225..8d8957cb9 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -314,4 +314,4 @@ CREATE TABLE "system" (
     value text
 );
 
-INSERT INTO "system" (name, value) VALUES ('roundcube-version', '2018122300');
+INSERT INTO "system" (name, value) VALUES ('roundcube-version', '2019092900');
diff --git a/SQL/postgres/2019092900.sql b/SQL/postgres/2019092900.sql
new file mode 100644
index 000000000..fe6741a02
--- /dev/null
+++ b/SQL/postgres/2019092900.sql
@@ -0,0 +1 @@
+-- empty
\ No newline at end of file
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index 2ce55faf8..f62a89412 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -215,4 +215,4 @@ CREATE TABLE system (
   value text NOT NULL
 );
 
-INSERT INTO system (name, value) VALUES ('roundcube-version', '2018122300');
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2019092900');
diff --git a/SQL/sqlite/2019092900.sql b/SQL/sqlite/2019092900.sql
new file mode 100644
index 000000000..fe6741a02
--- /dev/null
+++ b/SQL/sqlite/2019092900.sql
@@ -0,0 +1 @@
+-- empty
\ No newline at end of file