[3.3.5] Core/Authserver: TOTP rewrite: (PR #23633)

- Proper management commands (.account 2fa)
- Secrets can now be encrypted (set TOTPTokenSecret in .conf)
- Secret now stored in binary
- Argon2 and AES primitives
- Base32/64 support

(cherry picked from commit 4211645834c467a03c60248e80818d3607be9ea7)

4 files changed, 377 insertions, 1 deletions

diff --git a/sql/base/auth_database.sql b/sql/base/auth_database.sql
index be13b856ea3..a4524580141 100644
--- a/sql/base/auth_database.sql
+++ b/sql/base/auth_database.sql
@@ -32,7 +32,7 @@ CREATE TABLE `account` (
   `sha_pass_hash` varchar(40) NOT NULL DEFAULT '',
   `v` varchar(64) NOT NULL DEFAULT 'dummy value, use `verifier` instead',
   `s` varchar(64) NOT NULL DEFAULT 'dummy value, use `salt` instead',
-  `token_key` varchar(100) NOT NULL DEFAULT '',
+  `totp_secret` varbinary(128) DEFAULT NULL,
   `email` varchar(255) NOT NULL DEFAULT '',
   `reg_mail` varchar(255) NOT NULL DEFAULT '',
   `joindate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
@@ -1454,6 +1454,9 @@ INSERT INTO `rbac_linked_permissions` VALUES
 (199,223),
 (199,225),
 (199,263),
+(199,378),
+(199,379),
+(199,380),
 (199,496),
 (199,507),
 (199,525),
@@ -1710,6 +1713,10 @@ INSERT INTO `rbac_permissions` VALUES
 (375,'Command: gm list'),
 (376,'Command: gm visible'),
 (377,'Command: go'),
+(378,'Command: account 2fa'),
+(379,'Command: account 2fa setup'),
+(380,'Command: account 2fa remove'),
+(381,'Command: account set 2fa'),
 (387,'Command: gobject'),
 (388,'Command: gobject activate'),
 (389,'Command: gobject add'),
@@ -2238,6 +2245,29 @@ INSERT INTO `realmlist` VALUES
 UNLOCK TABLES;
 
 --
+-- Table structure for table `secret_digest`
+--
+
+DROP TABLE IF EXISTS `secret_digest`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `secret_digest` (
+  `id` int(10) unsigned not null,
+  `digest` varchar(100) not null,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping data for table `secret_digest`
+--
+
+LOCK TABLES `secret_digest` WRITE;
+/*!40000 ALTER TABLE `secret_digest` DISABLE KEYS */;
+/*!40000 ALTER TABLE `secret_digest` ENABLE KEYS */;
+UNLOCK TABLES;
+
+--
 -- Table structure for table `updates`
 --
 
@@ -2414,6 +2444,8 @@ INSERT INTO `updates` VALUES
 ('2019_07_16_00_auth.sql','36CB53A9EBD64BFDCF7030083E36E534F1753773','ARCHIVED','2019-07-16 00:00:00',0),
 ('2019_07_17_00_auth.sql','4F983F039904894ACC483BE885676C5F0A18F06B','ARCHIVED','2019-07-17 00:00:00',0),
 ('2019_07_26_00_auth.sql','DC9D0651602AE78B1243B40555A1A7B8447D01B2','ARCHIVED','2019-07-26 18:21:34',0),
+('2019_08_10_00_auth.sql','E936802893474BB9B459D01BB5F181F54EDF0653','ARCHIVED','2019-08-10 00:00:00',0),
+('2019_08_10_01_auth.sql','C58357260F0C70DA226A71F7E05DE2C49AAEFD74','ARCHIVED','2019-08-10 00:00:00',0),
 ('2019_08_11_00_auth.sql','04DCC2ABDA15BC7C015E8BFEA383C62A362B166F','ARCHIVED','2019-08-11 10:56:39',0),
 ('2019_08_18_00_auth.sql','0479A04B669A67D2E5A498CFB91507E742EFB34F','ARCHIVED','2019-08-17 11:51:02',0),
 ('2019_10_27_00_auth.sql','C943A651B5C9AC51BB7DF69821886F4B59F57153','ARCHIVED','2019-10-27 13:06:06',0),
diff --git a/sql/updates/auth/master/2019_08_10_00_auth.sql b/sql/updates/auth/master/2019_08_10_00_auth.sql
new file mode 100644
index 00000000000..6af00e84c4b
--- /dev/null
+++ b/sql/updates/auth/master/2019_08_10_00_auth.sql
@@ -0,0 +1,13 @@
+-- 
+DELETE FROM `rbac_permissions` WHERE `id` BETWEEN 378 AND 381;
+INSERT INTO `rbac_permissions` (`id`,`name`) VALUES
+(378, 'Command: account 2fa'),
+(379, 'Command: account 2fa setup'),
+(380, 'Command: account 2fa remove'),
+(381, 'Command: account set 2fa');
+
+DELETE FROM `rbac_linked_permissions` WHERE `linkedId` BETWEEN 378 AND 381;
+INSERT INTO `rbac_linked_permissions` (`id`,`linkedId`) VALUES
+(199, 378),
+(199, 379),
+(199, 380);
diff --git a/sql/updates/auth/master/2019_08_10_01_auth.sql b/sql/updates/auth/master/2019_08_10_01_auth.sql
new file mode 100644
index 00000000000..fd0e0c8ae0c
--- /dev/null
+++ b/sql/updates/auth/master/2019_08_10_01_auth.sql
@@ -0,0 +1,295 @@
+-- 
+
+START TRANSACTION; -- we're messing with the accounts table here, let's play it safe
+
+DROP TABLE IF EXISTS `secret_digest`;
+CREATE TABLE `secret_digest` (
+  `id` int(10) unsigned not null,
+  `digest` varchar(100) not null,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB;
+
+-- ============================================== --
+-- BASE32 CONVERSION STARTS HERE                  --
+-- (there is another banner like this at the end, --
+--  so you know how far down you need to skip)    --
+-- ============================================== --
+
+CREATE TEMPORARY TABLE `_temp_base32_lookup1`
+(
+    `c` char(1) not null,
+    `v` tinyint unsigned not null,
+    primary key (`c`)
+);
+INSERT INTO `_temp_base32_lookup1` (`c`,`v`) VALUES
+('A',00),('B',01),('C',02),('D',03),('E',04),('F',05),('G',06),('H',07),
+('I',08),('J',09),('K',10),('L',11),('M',12),('N',13),('O',14),('P',15),
+('Q',16),('R',17),('S',18),('T',19),('U',20),('V',21),('W',22),('X',23),
+('Y',24),('Z',25),('2',26),('3',27),('4',28),('5',29),('6',30),('7',31);
+
+CREATE TEMPORARY TABLE `_temp_base32_lookup2` LIKE `_temp_base32_lookup1`;
+INSERT INTO `_temp_base32_lookup2` SELECT * FROM `_temp_base32_lookup1`;
+
+CREATE TEMPORARY TABLE `_temp_base32_lookup3` LIKE `_temp_base32_lookup1`;
+INSERT INTO `_temp_base32_lookup3` SELECT * FROM `_temp_base32_lookup1`;
+
+CREATE TEMPORARY TABLE `_temp_base32_lookup4` LIKE `_temp_base32_lookup1`;
+INSERT INTO `_temp_base32_lookup4` SELECT * FROM `_temp_base32_lookup1`;
+
+CREATE TEMPORARY TABLE `_temp_base32_lookup5` LIKE `_temp_base32_lookup1`;
+INSERT INTO `_temp_base32_lookup5` SELECT * FROM `_temp_base32_lookup1`;
+
+CREATE TEMPORARY TABLE `_temp_base32_lookup6` LIKE `_temp_base32_lookup1`;
+INSERT INTO `_temp_base32_lookup6` SELECT * FROM `_temp_base32_lookup1`;
+
+CREATE TEMPORARY TABLE `_temp_base32_lookup7` LIKE `_temp_base32_lookup1`;
+INSERT INTO `_temp_base32_lookup7` SELECT * FROM `_temp_base32_lookup1`;
+
+CREATE TEMPORARY TABLE `_temp_base32_lookup8` LIKE `_temp_base32_lookup1`;
+INSERT INTO `_temp_base32_lookup8` SELECT * FROM `_temp_base32_lookup1`;
+
+CREATE TEMPORARY TABLE `_temp_totp_conversion`
+(
+    `original_key` varchar(100) not null default '',
+    `remaining_key` varchar(100) not null default '',
+    `totp_secret` varbinary(128) default null,
+    primary key(`original_key`),
+    index (`remaining_key`)
+);
+
+INSERT INTO `_temp_totp_conversion` (`original_key`) SELECT DISTINCT `token_key` FROM `account`;
+UPDATE `_temp_totp_conversion` SET `remaining_key`=TRIM(TRAILING '=' FROM `original_key`),`totp_secret`='' WHERE `original_key`!='';
+
+-- 8 base32 chars = 5 bytes
+-- ...so after 12 iterations we're done
+-- mysql doesn't let us do loops, so we have to do this manually (....mysql)
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- iteration 2
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- iteration 3
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- iteration 4
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- iteration 5
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- iteration 6
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- iteration 7
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- iteration 8
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- iteration 9
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- iteration 10
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- iteration 11
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- iteration 12
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+LEFT JOIN `_temp_base32_lookup8` look8 ON look8.`c`=SUBSTR(`remaining_key`,8,1)
+SET `remaining_key`=SUBSTR(`remaining_key`,9),`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 35) | (look2.`v` << 30) | (look3.`v` << 25) | (look4.`v` << 20) | (look5.`v` << 15) | (look6.`v` << 10) | (look7.`v` << 5) | (look8.`v`)),10,16),10,'0')))
+WHERE LENGTH(`remaining_key`) >= 8;
+
+-- ok, now the only things left are trailing partial bytes
+-- if the trailing block had 1 byte , we have xxxxx xxx00                               (strlen = 2)
+-- if the trailing block had 2 bytes, we have xxxxx xxxyy yyyyy y0000                   (strlen = 4)
+-- if the trailing block had 3 bytes, we have xxxxx xxxyy yyyyy yzzzz zzzz0             (strlen = 5)
+-- if the trailing block had 4 bytes, we have xxxxx xxxyy yyyyy yzzzz zzzzw wwwww ww000 (strlen = 7)
+
+-- 1 byte case
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+SET `remaining_key`='',`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 3) | (look2.`v` >> 2)),10,16),2,'0')))
+WHERE LENGTH(`remaining_key`)=2;
+-- 2 byte case
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+SET `remaining_key`='',`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 11) | (look2.`v` << 6) | (look3.`v` << 1) | (look4.`v` >> 4)),10,16),4,'0')))
+WHERE LENGTH(`remaining_key`)=4;
+-- 3 byte case
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+SET `remaining_key`='',`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 19) | (look2.`v` << 14) | (look3.`v` << 9) | (look4.`v` << 4) | (look5.`v` >> 1)),10,16),6,'0')))
+WHERE LENGTH(`remaining_key`)=5;
+-- 4 byte case
+UPDATE `_temp_totp_conversion`
+LEFT JOIN `_temp_base32_lookup1` look1 ON look1.`c`=SUBSTR(`remaining_key`,1,1)
+LEFT JOIN `_temp_base32_lookup2` look2 ON look2.`c`=SUBSTR(`remaining_key`,2,1)
+LEFT JOIN `_temp_base32_lookup3` look3 ON look3.`c`=SUBSTR(`remaining_key`,3,1)
+LEFT JOIN `_temp_base32_lookup4` look4 ON look4.`c`=SUBSTR(`remaining_key`,4,1)
+LEFT JOIN `_temp_base32_lookup5` look5 ON look5.`c`=SUBSTR(`remaining_key`,5,1)
+LEFT JOIN `_temp_base32_lookup6` look6 ON look6.`c`=SUBSTR(`remaining_key`,6,1)
+LEFT JOIN `_temp_base32_lookup7` look7 ON look7.`c`=SUBSTR(`remaining_key`,7,1)
+SET `remaining_key`='',`totp_secret`=CONCAT(`totp_secret`,
+    UNHEX(LPAD(CONV(((look1.`v` << 27) | (look2.`v` << 22) | (look3.`v` << 17) | (look4.`v` << 12) | (look5.`v` << 7) | (look6.`v` << 2) | (look7.`v` >> 3)),10,16),8,'0')))
+WHERE LENGTH(`remaining_key`)=7;
+
+-- assert that we actually converted everything properly
+SET @mode := @@session.sql_mode;
+SET SESSION sql_mode='STRICT_TRANS_TABLES';
+CREATE TEMPORARY TABLE `_temp_assert_check` (`v` char(1) not null);
+INSERT INTO `_temp_assert_check` SELECT CONV(MAX(LENGTH(`remaining_key`)+1),10,2) FROM `_temp_totp_conversion`;
+SET SESSION sql_mode=@mode;
+
+-- =================================================== --
+-- BASE32 CONVERSION ENDS HERE                         --
+-- (this is the other banner i promised you, so you    --
+--  can stop skipping the unnecessarily complex stuff) --
+-- =================================================== --
+
+ALTER TABLE `account` ADD COLUMN `totp_secret` VARBINARY(128) DEFAULT NULL AFTER `s`;
+UPDATE `account` a LEFT JOIN `_temp_totp_conversion` c ON a.`token_key`=c.`original_key` SET a.`totp_secret`=c.`totp_secret`;
+ALTER TABLE `account` DROP COLUMN `token_key`;
+
+COMMIT; -- safety gloves off
diff --git a/sql/updates/world/master/2021_12_18_25_world_2019_08_10_01_world.sql b/sql/updates/world/master/2021_12_18_25_world_2019_08_10_01_world.sql
new file mode 100644
index 00000000000..5ce0c1829fd
--- /dev/null
+++ b/sql/updates/world/master/2021_12_18_25_world_2019_08_10_01_world.sql
@@ -0,0 +1,36 @@
+-- 
+DELETE FROM `command` WHERE `name` LIKE 'account 2fa%';
+DELETE FROM `command` WHERE `name`='account set 2fa';
+INSERT INTO `command` (`name`, `permission`, `help`) VALUES
+('account 2fa', 378, 'Syntax: .account 2fa <setup/remove>'),
+('account 2fa setup', 379, 'Syntax: .account 2fa setup
+
+Sets up two-factor authentication for this account.'),
+('account 2fa remove', 380, 'Syntax: .account 2fa remove <token>
+
+Disables two-factor authentication for this account, if enabled.'),
+('account set 2fa', 381, 'Syntax: .account set 2fa <account> <secret/off>
+
+Provide a base32 encoded secret to setup two-factor authentication for the account.
+Specify \'off\' to disable two-factor authentication for the account.');
+
+
+
+DELETE FROM `trinity_string` WHERE `entry` BETWEEN 88 AND 95;
+DELETE FROM `trinity_string` WHERE `entry` BETWEEN 188 AND 190;
+INSERT INTO `trinity_string` (`entry`,`content_default`) VALUES
+(88, "Two-factor authentication commands are not properly setup."),
+(89, "Two-factor authentication is already enabled for this account."),
+(90, "Invalid two-factor authentication token specified."),
+(91, "In order to complete setup, you'll need to set up the device you'll be using as your second factor.
+
+Your 2FA key: %s
+
+Once you have set up your device, confirm by running .account 2fa setup <token> with the generated token."),
+(92, "Two-factor authentication has been successfully set up."),
+(93, "Two-factor authentication is not enabled for this account."),
+(94, "To remove two-factor authentication, please specify a fresh two-factor token from your authentication device."),
+(95, "Two-factor authentication has been successfully disabled."),
+(188, "The provided two-factor authentication secret is too long."),
+(189, "The provided two-factor authentication secret is not valid."),
+(190, "Successfully enabled two-factor authentication for '%s' with the specified secret.");