Add realm ID for actions based ip logging (#24453)

* Update SQL queries to insert realm id

* Insert the realm id when logging actions based on IP

* Adding the SQL update file

* Update 9999_99_99_99_auth.sql

* Update auth structure

Co-authored-by: Giacomo Pozzoni <giacomopoz@gmail.com>

4 files changed, 39 insertions, 25 deletions

diff --git a/sql/base/auth_database.sql b/sql/base/auth_database.sql
index 77ecd7951c0..b9c7059bef4 100644
--- a/sql/base/auth_database.sql
+++ b/sql/base/auth_database.sql
@@ -341,6 +341,7 @@ CREATE TABLE `logs_ip_actions` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Identifier',
   `account_id` int(10) unsigned NOT NULL COMMENT 'Account ID',
   `character_guid` int(10) unsigned NOT NULL COMMENT 'Character Guid',
+  `realm_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Realm ID',
   `type` tinyint(3) unsigned NOT NULL,
   `ip` varchar(15) NOT NULL DEFAULT '127.0.0.1',
   `systemnote` text COMMENT 'Notes inserted by system',
@@ -1959,7 +1960,8 @@ INSERT INTO `updates` VALUES
 ('2020_02_15_00_auth.sql','9CDBEBC722BEBB7C93091CF16EEB9C922C4116BD','ARCHIVED','2020-02-15 17:36:05',0),
 ('2020_03_16_00_auth.sql','B27E4CE14C713C73216930A7F3F770786D902A3F','ARCHIVED','2020-03-16 08:47:49',0),
 ('2020_04_07_00_auth.sql','6D73A4E1EC5382F10C39F20E2E6E764510A8A5E6','ARCHIVED','2020-04-07 22:23:35',0),
-('2020_04_15_00_auth.sql','031E61B50B03E40406F07C196826DB9016A7203F','ARCHIVED','2020-04-15 11:03:56',0);
+('2020_04_15_00_auth.sql','031E61B50B03E40406F07C196826DB9016A7203F','ARCHIVED','2020-04-15 11:03:56',0),
+('2020_04_18_00_auth.sql','BD962B50760771B60F2785027D6957EEF2009240','ARCHIVED','2020-04-18 14:09:28',0);
 /*!40000 ALTER TABLE `updates` ENABLE KEYS */;
 UNLOCK TABLES;
 
diff --git a/sql/updates/auth/3.3.5/2020_04_18_00_auth.sql b/sql/updates/auth/3.3.5/2020_04_18_00_auth.sql
new file mode 100644
index 00000000000..afa498a0342
--- /dev/null
+++ b/sql/updates/auth/3.3.5/2020_04_18_00_auth.sql
@@ -0,0 +1,2 @@
+--
+ALTER TABLE `logs_ip_actions` ADD COLUMN `realm_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Realm ID' AFTER `character_guid`;
diff --git a/src/server/database/Database/Implementation/LoginDatabase.cpp b/src/server/database/Database/Implementation/LoginDatabase.cpp
index fccd21a83fa..4fe185d64cf 100644
--- a/src/server/database/Database/Implementation/LoginDatabase.cpp
+++ b/src/server/database/Database/Implementation/LoginDatabase.cpp
@@ -100,14 +100,14 @@ void LoginDatabaseConnection::DoPrepareStatements()
     PrepareStatement(LOGIN_DEL_ACCOUNT, "DELETE FROM account WHERE id = ?", CONNECTION_ASYNC);
     PrepareStatement(LOGIN_SEL_AUTOBROADCAST, "SELECT id, weight, text FROM autobroadcast WHERE realmid = ? OR realmid = -1", CONNECTION_SYNCH);
     PrepareStatement(LOGIN_GET_EMAIL_BY_ID, "SELECT email FROM account WHERE id = ?", CONNECTION_SYNCH);
-    // 0: uint32, 1: uint32, 2: uint8, 3: uint32, 4: string // Complete name: "Login_Insert_AccountLoginDeLete_IP_Logging"
-    PrepareStatement(LOGIN_INS_ALDL_IP_LOGGING, "INSERT INTO logs_ip_actions (account_id,character_guid,type,ip,systemnote,unixtime,time) VALUES (?, ?, ?, (SELECT last_ip FROM account WHERE id = ?), ?, unix_timestamp(NOW()), NOW())", CONNECTION_ASYNC);
-    // 0: uint32, 1: uint32, 2: uint8, 3: uint32, 4: string // Complete name: "Login_Insert_FailedAccountLogin_IP_Logging"
-    PrepareStatement(LOGIN_INS_FACL_IP_LOGGING, "INSERT INTO logs_ip_actions (account_id,character_guid,type,ip,systemnote,unixtime,time) VALUES (?, ?, ?, (SELECT last_attempt_ip FROM account WHERE id = ?), ?, unix_timestamp(NOW()), NOW())", CONNECTION_ASYNC);
-    // 0: uint32, 1: uint32, 2: uint8, 3: string, 4: string // Complete name: "Login_Insert_CharacterDelete_IP_Logging"
-    PrepareStatement(LOGIN_INS_CHAR_IP_LOGGING, "INSERT INTO logs_ip_actions (account_id,character_guid,type,ip,systemnote,unixtime,time) VALUES (?, ?, ?, ?, ?, unix_timestamp(NOW()), NOW())", CONNECTION_ASYNC);
-    // 0: uint32, 1: string, 2: string                      // Complete name: "Login_Insert_Failed_Account_Login_due_password_IP_Logging"
-    PrepareStatement(LOGIN_INS_FALP_IP_LOGGING, "INSERT INTO logs_ip_actions (account_id,character_guid,type,ip,systemnote,unixtime,time) VALUES (?, 0, 1, ?, ?, unix_timestamp(NOW()), NOW())", CONNECTION_ASYNC);
+    // 0: uint32, 1: uint32, 2: uint32, 3: uint8, 4: uint32, 5: string // Complete name: "Login_Insert_AccountLoginDeLete_IP_Logging"
+    PrepareStatement(LOGIN_INS_ALDL_IP_LOGGING, "INSERT INTO logs_ip_actions (account_id, character_guid, realm_id, type, ip, systemnote, unixtime, time) VALUES (?, ?, ?, ?, (SELECT last_ip FROM account WHERE id = ?), ?, unix_timestamp(NOW()), NOW())", CONNECTION_ASYNC);
+    // 0: uint32, 1: uint32, 2: uint32, 3: uint8, 4: uint32, 5: string // Complete name: "Login_Insert_FailedAccountLogin_IP_Logging"
+    PrepareStatement(LOGIN_INS_FACL_IP_LOGGING, "INSERT INTO logs_ip_actions (account_id, character_guid, realm_id, type, ip, systemnote, unixtime, time) VALUES (?, ?, ?, ?, (SELECT last_attempt_ip FROM account WHERE id = ?), ?, unix_timestamp(NOW()), NOW())", CONNECTION_ASYNC);
+    // 0: uint32, 1: uint32, 2: uint32, 3: uint8, 4: string, 5: string // Complete name: "Login_Insert_CharacterDelete_IP_Logging"
+    PrepareStatement(LOGIN_INS_CHAR_IP_LOGGING, "INSERT INTO logs_ip_actions (account_id, character_guid, realm_id, type, ip, systemnote, unixtime, time) VALUES (?, ?, ?, ?, ?, ?, unix_timestamp(NOW()), NOW())", CONNECTION_ASYNC);
+    // 0: uint32, 1: uint32, 2: string, 3: string                      // Complete name: "Login_Insert_Failed_Account_Login_due_password_IP_Logging"
+    PrepareStatement(LOGIN_INS_FALP_IP_LOGGING, "INSERT INTO logs_ip_actions (account_id, character_guid, realm_id, type, ip, systemnote, unixtime, time) VALUES (?, 0, ?, 1, ?, ?, unix_timestamp(NOW()), NOW())", CONNECTION_ASYNC);
     PrepareStatement(LOGIN_SEL_ACCOUNT_ACCESS_BY_ID, "SELECT gmlevel, RealmID FROM account_access WHERE id = ? and (RealmID = ? OR RealmID = -1) ORDER BY gmlevel desc", CONNECTION_SYNCH);
 
     PrepareStatement(LOGIN_SEL_RBAC_ACCOUNT_PERMISSIONS, "SELECT permissionId, granted FROM rbac_account_permissions WHERE accountId = ? AND (realmId = ? OR realmId = -1) ORDER BY permissionId, realmId", CONNECTION_BOTH);
diff --git a/src/server/scripts/World/action_ip_logger.cpp b/src/server/scripts/World/action_ip_logger.cpp
index b9c267e85f5..f3b5954efb1 100644
--- a/src/server/scripts/World/action_ip_logger.cpp
+++ b/src/server/scripts/World/action_ip_logger.cpp
@@ -19,6 +19,8 @@
 #include "DatabaseEnv.h"
 #include "Player.h"
 #include "WorldSession.h"
+#include "World.h"
+#include "Realm.h"
 
 enum IPLoggingTypes
 {
@@ -97,6 +99,7 @@ class AccountActionIpLogger : public AccountScript
             // We declare all the required variables
             uint32 playerGuid = accountId;
             ObjectGuid::LowType characterGuid = 0;
+            uint32 realmId = realm.Id.Realm;
             std::string systemNote = "ERROR"; // "ERROR" is a placeholder here. We change it later.
 
             // With this switch, we change systemNote so that we have a more accurate phrasing of what type it is.
@@ -142,9 +145,10 @@ class AccountActionIpLogger : public AccountScript
 
                 stmt->setUInt32(0, playerGuid);
                 stmt->setUInt32(1, characterGuid);
-                stmt->setUInt8(2, aType);
-                stmt->setUInt32(3, playerGuid);
-                stmt->setString(4, systemNote);
+                stmt->setUInt32(2, realmId);
+                stmt->setUInt8(3, aType);
+                stmt->setUInt32(4, playerGuid);
+                stmt->setString(5, systemNote);
                 LoginDatabase.Execute(stmt);
             }
             else // ... but for failed login, we query last_attempt_ip from account table. Which we do with an unique query
@@ -153,9 +157,10 @@ class AccountActionIpLogger : public AccountScript
 
                 stmt->setUInt32(0, playerGuid);
                 stmt->setUInt32(1, characterGuid);
-                stmt->setUInt8(2, aType);
-                stmt->setUInt32(3, playerGuid);
-                stmt->setString(4, systemNote);
+                stmt->setUInt32(2, realmId);
+                stmt->setUInt8(3, aType);
+                stmt->setUInt32(4, playerGuid);
+                stmt->setString(5, systemNote);
                 LoginDatabase.Execute(stmt);
             }
             return;
@@ -202,6 +207,7 @@ class CharacterActionIpLogger : public PlayerScript
             // We declare all the required variables
             uint32 playerGuid = player->GetSession()->GetAccountId();
             ObjectGuid::LowType characterGuid = player->GetGUID().GetCounter();
+            uint32 realmId = realm.Id.Realm;
             const std::string currentIp = player->GetSession()->GetRemoteAddress();
             std::string systemNote = "ERROR"; // "ERROR" is a placeholder here. We change it...
 
@@ -235,9 +241,10 @@ class CharacterActionIpLogger : public PlayerScript
 
             stmt->setUInt32(0, playerGuid);
             stmt->setUInt32(1, characterGuid);
-            stmt->setUInt8(2, aType);
-            stmt->setString(3, currentIp); // We query the ip here.
-            stmt->setString(4, systemNote);
+            stmt->setUInt32(2, realmId);
+            stmt->setUInt8(3, aType);
+            stmt->setString(4, currentIp); // We query the ip here.
+            stmt->setString(5, systemNote);
             // Seeing as the time differences should be minimal, we do not get unixtime and the timestamp right now;
             // Rather, we let it be added with the SQL query.
 
@@ -270,6 +277,7 @@ public:
 
         // We declare all the required variables
         ObjectGuid::LowType characterGuid = guid.GetCounter(); // We have no access to any member function of Player* or WorldSession*. So use old-fashioned way.
+        uint32 realmId = realm.Id.Realm;
         // Query playerGuid/accountId, as we only have characterGuid
         std::string systemNote = "ERROR"; // "ERROR" is a placeholder here. We change it later.
 
@@ -291,17 +299,19 @@ public:
         }
 
         // Once we have done everything, we can insert the new log.
-        LoginDatabasePreparedStatement* stmt2 = LoginDatabase.GetPreparedStatement(LOGIN_INS_ALDL_IP_LOGGING);
+        LoginDatabasePreparedStatement* stmt = LoginDatabase.GetPreparedStatement(LOGIN_INS_ALDL_IP_LOGGING);
+
+        stmt->setUInt32(0, playerGuid);
+        stmt->setUInt32(1, characterGuid);
+        stmt->setUInt32(2, realmId);
+        stmt->setUInt8(3, aType);
+        stmt->setUInt32(4, playerGuid);
+        stmt->setString(5, systemNote);
 
-        stmt2->setUInt32(0, playerGuid);
-        stmt2->setUInt32(1, characterGuid);
-        stmt2->setUInt8(2, aType);
-        stmt2->setUInt32(3, playerGuid);
-        stmt2->setString(4, systemNote);
         // Seeing as the time differences should be minimal, we do not get unixtime and the timestamp right now;
         // Rather, we let it be added with the SQL query.
 
-        LoginDatabase.Execute(stmt2);
+        LoginDatabase.Execute(stmt);
         return;
     }
 };