diff options
| -rw-r--r-- | sql/base/auth_database.sql | 10 | ||||
| -rw-r--r-- | sql/updates/auth/3.3.5/2020_08_03_01_auth.sql | 5 | ||||
| -rw-r--r-- | src/server/database/Database/Implementation/LoginDatabase.cpp | 10 |
3 files changed, 16 insertions, 9 deletions
diff --git a/sql/base/auth_database.sql b/sql/base/auth_database.sql index 798a30deeb8..ffbc5ede914 100644 --- a/sql/base/auth_database.sql +++ b/sql/base/auth_database.sql @@ -27,7 +27,8 @@ CREATE TABLE `account` ( `username` varchar(32) NOT NULL DEFAULT '', `salt` BINARY(32), `verifier` BINARY(32), - `session_key` VARBINARY(64) DEFAULT NULL, + `session_key_auth` BINARY(40) DEFAULT NULL, + `session_key_bnet` VARBINARY(64) DEFAULT NULL, `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', @@ -1968,9 +1969,10 @@ INSERT INTO `updates` VALUES ('2020_05_15_00_auth.sql','765389B45F97A02160A58B373D63166F7F7D4427','ARCHIVED','2020-05-15 08:55:56',0), ('2020_06_15_00_auth.sql','3158036285CC9A4AB7D39063F9687649A21D0A94','ARCHIVED','2020-06-15 07:48:08',0), ('2020_06_20_00_auth.sql','85345FAF20B91DA7B157AE1E17DF5B6446C2E109','ARCHIVED','2020-06-11 10:48:00',0), -('2020_07_15_00_auth.sql','56748440894EA78C3BE72C4A3F2E97E256E6EE40','ARCHIVED','2020-07-15 10:35:41',0), -('2020_08_02_00_auth.sql','B0290F6558C59262D9DDD8071060A8803DD56930','ARCHIVED','2020-08-02 00:00:00',0), -('2020_08_03_00_auth.sql','492CA77C0FAEEEF3E0492121B3A92689373ECFA3','RELEASED','2020-08-03 09:24:47',0); +('2020_07_15_00_auth.sql','56748440894EA78C3BE72C4A3F2E97E256E6EE40','RELEASED','2020-07-15 00:00:00',0), +('2020_08_02_00_auth.sql','B0290F6558C59262D9DDD8071060A8803DD56930','RELEASED','2020-08-02 00:00:00',0), +('2020_08_03_00_auth.sql','492CA77C0FAEEEF3E0492121B3A92689373ECFA3','RELEASED','2020-08-03 00:00:00',0), +('2020_08_03_01_auth.sql','EC1063396CA20A2303D83238470D41EF4439EC72','RELEASED','2020-08-03 00:00:01',0); /*!40000 ALTER TABLE `updates` ENABLE KEYS */; UNLOCK TABLES; diff --git a/sql/updates/auth/3.3.5/2020_08_03_01_auth.sql b/sql/updates/auth/3.3.5/2020_08_03_01_auth.sql new file mode 100644 index 00000000000..f46d259aa4b --- /dev/null +++ b/sql/updates/auth/3.3.5/2020_08_03_01_auth.sql @@ -0,0 +1,5 @@ +-- +ALTER TABLE `account` + DROP COLUMN `session_key`, + ADD COLUMN `session_key_auth` BINARY(40) DEFAULT NULL AFTER `verifier`, + ADD COLUMN `session_key_bnet` VARBINARY(64) DEFAULT NULL AFTER `session_key_auth`; diff --git a/src/server/database/Database/Implementation/LoginDatabase.cpp b/src/server/database/Database/Implementation/LoginDatabase.cpp index 77de11b516a..737d9c4a8f3 100644 --- a/src/server/database/Database/Implementation/LoginDatabase.cpp +++ b/src/server/database/Database/Implementation/LoginDatabase.cpp @@ -37,19 +37,19 @@ void LoginDatabaseConnection::DoPrepareStatements() PrepareStatement(LOGIN_DEL_ACCOUNT_BANNED, "DELETE FROM account_banned WHERE id = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_LOGON, "UPDATE account SET salt = ?, verifier = ?, s = DEFAULT, v = DEFAULT WHERE id = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_LOGON_LEGACY, "UPDATE account SET sha_pass_hash = ? WHERE id = ?", CONNECTION_ASYNC); - PrepareStatement(LOGIN_UPD_LOGONPROOF, "UPDATE account SET session_key = ?, last_ip = ?, last_login = NOW(), locale = ?, failed_logins = 0, os = ? WHERE username = ?", CONNECTION_SYNCH); + PrepareStatement(LOGIN_UPD_LOGONPROOF, "UPDATE account SET session_key_auth = ?, last_ip = ?, last_login = NOW(), locale = ?, failed_logins = 0, os = ? WHERE username = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_SEL_LOGONCHALLENGE, "SELECT a.id, a.username, a.locked, a.lock_country, a.last_ip, a.failed_logins, ab.unbandate > UNIX_TIMESTAMP() OR ab.unbandate = ab.bandate, " "ab.unbandate = ab.bandate, aa.SecurityLevel, a.totp_secret, IF(a.s != DEFAULT(a.s) OR a.v != DEFAULT(a.v) OR a.salt IS NULL OR a.verifier IS NULL, a.sha_pass_hash, NULL), a.salt, a.verifier " "FROM account a LEFT JOIN account_access aa ON a.id = aa.AccountID LEFT JOIN account_banned ab ON ab.id = a.id AND ab.active = 1 WHERE a.username = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_SEL_RECONNECTCHALLENGE, "SELECT a.id, UPPER(a.username), a.locked, a.lock_country, a.last_ip, a.failed_logins, ab.unbandate > UNIX_TIMESTAMP() OR ab.unbandate = ab.bandate, " - "ab.unbandate = ab.bandate, aa.SecurityLevel, a.session_key " - "FROM account a LEFT JOIN account_access aa ON a.id = aa.AccountID LEFT JOIN account_banned ab ON ab.id = a.id AND ab.active = 1 WHERE a.username = ? AND LENGTH(a.session_key) = 40", CONNECTION_ASYNC); + "ab.unbandate = ab.bandate, aa.SecurityLevel, a.session_key_auth " + "FROM account a LEFT JOIN account_access aa ON a.id = aa.AccountID LEFT JOIN account_banned ab ON ab.id = a.id AND ab.active = 1 WHERE a.username = ? AND a.session_key_auth IS NOT NULL", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_FAILEDLOGINS, "UPDATE account SET failed_logins = failed_logins + 1 WHERE username = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_SEL_ACCOUNT_ID_BY_NAME, "SELECT id FROM account WHERE username = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_SEL_ACCOUNT_LIST_BY_NAME, "SELECT id, username FROM account WHERE username = ?", CONNECTION_SYNCH); - PrepareStatement(LOGIN_SEL_ACCOUNT_INFO_BY_NAME, "SELECT a.id, a.session_key, a.last_ip, a.locked, a.lock_country, a.expansion, a.mutetime, a.locale, a.recruiter, a.os, aa.SecurityLevel, " + PrepareStatement(LOGIN_SEL_ACCOUNT_INFO_BY_NAME, "SELECT a.id, a.session_key_auth, a.last_ip, a.locked, a.lock_country, a.expansion, a.mutetime, a.locale, a.recruiter, a.os, aa.SecurityLevel, " "ab.unbandate > UNIX_TIMESTAMP() OR ab.unbandate = ab.bandate, r.id FROM account a LEFT JOIN account_access aa ON a.id = aa.AccountID AND aa.RealmID IN (-1, ?) " - "LEFT JOIN account_banned ab ON a.id = ab.id AND ab.active = 1 LEFT JOIN account r ON a.id = r.recruiter WHERE a.username = ? AND LENGTH(a.session_key) = 40 ORDER BY aa.RealmID DESC LIMIT 1", CONNECTION_ASYNC); + "LEFT JOIN account_banned ab ON a.id = ab.id AND ab.active = 1 LEFT JOIN account r ON a.id = r.recruiter WHERE a.username = ? AND a.session_key_auth IS NOT NULL ORDER BY aa.RealmID DESC LIMIT 1", CONNECTION_ASYNC); PrepareStatement(LOGIN_SEL_ACCOUNT_LIST_BY_EMAIL, "SELECT id, username FROM account WHERE email = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_SEL_REALM_CHARACTER_COUNTS, "SELECT realmid, numchars FROM realmcharacters WHERE acctid = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_SEL_ACCOUNT_BY_IP, "SELECT id, username FROM account WHERE last_ip = ?", CONNECTION_SYNCH); |