diff options
8 files changed, 152 insertions, 16 deletions
diff --git a/data/sql/base/db_auth/account.sql b/data/sql/base/db_auth/account.sql index d2ad2b7904..6461db4018 100644 --- a/data/sql/base/db_auth/account.sql +++ b/data/sql/base/db_auth/account.sql @@ -20,6 +20,7 @@ CREATE TABLE `account` `last_ip` varchar(15) NOT NULL DEFAULT '127.0.0.1', `failed_logins` int(10) unsigned NOT NULL DEFAULT '0', `locked` tinyint(3) unsigned NOT NULL DEFAULT '0', + `lock_country` varchar(2) NOT NULL DEFAULT '00', `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `online` tinyint(3) unsigned NOT NULL DEFAULT '0', `expansion` tinyint(3) unsigned NOT NULL DEFAULT '0', @@ -37,16 +38,16 @@ CREATE TABLE `account` LOCK TABLES `account` WRITE; /*!40000 ALTER TABLE `account` DISABLE KEYS */; INSERT INTO `account` VALUES -(1,'test1','047ce22643f9b0bd6baeb18d51bf1075a4d43fc6','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0), -(2,'test2','10eb1ff16cf5380147e8281cd8080a210ecb3c53','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0), -(3,'test3','e546bbf9ca93ae5291f0b441bb9ea2fa0c466176','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0), -(4,'test4','61015d83b456a9c6a7defdff07f55265f24097af','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0), -(5,'test5','dddeac4ffe5f286ec57b7a1ed63bf3a859debe1e','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0), -(6,'test6','f1f94cdffd83c8c4182d66689077f92c807ab579','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0), -(7,'test7','6fcd35c35b127be1d9ca040b2b478eb366506ce2','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0), -(8,'test8','484332ccb02e284e4e0a04573c3fa417f4745fdf','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0), -(9,'test9','4fce15ed251721f02754d5381ae9d0137b6a6a30','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0), -(10,'test10','b22d249228e84ab493b39a2bd765bee9b7c0b350','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0); +(1,'test1','047ce22643f9b0bd6baeb18d51bf1075a4d43fc6','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'00','0000-00-00 00:00:00',0,2,0,'','',0,'',0), +(2,'test2','10eb1ff16cf5380147e8281cd8080a210ecb3c53','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'00','0000-00-00 00:00:00',0,2,0,'','',0,'',0), +(3,'test3','e546bbf9ca93ae5291f0b441bb9ea2fa0c466176','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'00','0000-00-00 00:00:00',0,2,0,'','',0,'',0), +(4,'test4','61015d83b456a9c6a7defdff07f55265f24097af','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'00','0000-00-00 00:00:00',0,2,0,'','',0,'',0), +(5,'test5','dddeac4ffe5f286ec57b7a1ed63bf3a859debe1e','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'00','0000-00-00 00:00:00',0,2,0,'','',0,'',0), +(6,'test6','f1f94cdffd83c8c4182d66689077f92c807ab579','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'00','0000-00-00 00:00:00',0,2,0,'','',0,'',0), +(7,'test7','6fcd35c35b127be1d9ca040b2b478eb366506ce2','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'00','0000-00-00 00:00:00',0,2,0,'','',0,'',0), +(8,'test8','484332ccb02e284e4e0a04573c3fa417f4745fdf','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'00','0000-00-00 00:00:00',0,2,0,'','',0,'',0), +(9,'test9','4fce15ed251721f02754d5381ae9d0137b6a6a30','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'00','0000-00-00 00:00:00',0,2,0,'','',0,'',0), +(10,'test10','b22d249228e84ab493b39a2bd765bee9b7c0b350','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'00','0000-00-00 00:00:00',0,2,0,'','',0,'',0); /*!40000 ALTER TABLE `account` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; diff --git a/data/sql/updates/pending_db_auth/rev_1498796201292521600.sql b/data/sql/updates/pending_db_auth/rev_1498796201292521600.sql new file mode 100644 index 0000000000..a57efdf16b --- /dev/null +++ b/data/sql/updates/pending_db_auth/rev_1498796201292521600.sql @@ -0,0 +1,23 @@ +INSERT INTO version_db_auth (`sql_rev`) VALUES ('1498796201292521600'); + +ALTER TABLE `account` ADD COLUMN `lock_country` VARCHAR(2) NOT NULL DEFAULT '00' AFTER `locked`; + +DROP TABLE IF EXISTS `ip2nation`; +CREATE TABLE `ip2nation` ( + `ip` int(11) unsigned NOT NULL default '0', + `country`char(2) NOT NULL default '', + KEY `ip` (`ip`) +)ENGINE=InnoDB DEFAULT CHARSET=utf8; + +DROP TABLE IF EXISTS `ip2nationCountries`; +CREATE TABLE `ip2nationCountries` ( + `code` varchar(4) NOT NULL default '', + `iso_code_2` varchar(2) NOT NULL default '', + `iso_code_3` varchar(3) default '', + `iso_country` varchar(255) NOT NULL default '', + `country` varchar(255) NOT NULL default '', + `lat` float NOT NULL default '0', + `lon` float NOT NULL default '0', + PRIMARY KEY (`code`), + KEY `code` (`code`) +)ENGINE=InnoDB DEFAULT CHARSET=utf8; diff --git a/data/sql/updates/pending_db_world/rev_1498796247453520600.sql b/data/sql/updates/pending_db_world/rev_1498796247453520600.sql new file mode 100644 index 0000000000..aecf0fd2d9 --- /dev/null +++ b/data/sql/updates/pending_db_world/rev_1498796247453520600.sql @@ -0,0 +1,9 @@ +INSERT INTO version_db_world (`sql_rev`) VALUES ('1498796247453520600'); + +DROP TABLE IF EXISTS ip2nation; +DROP TABLE IF EXISTS ip2nationCountries; + +DELETE FROM `command` WHERE `name` in ('account lock', 'account lock ip', 'account lock country'); +INSERT INTO `command` (`name`,`security`,`help`) VALUES +('account lock ip', 0, 'Syntax: .account lock ip [on|off]\nAllow login from account only from current used IP or remove this requirement.'), +('account lock country', 0, 'Syntax: .account lock country [on|off]\nAllow login from account only from current used Country or remove this requirement.'); diff --git a/modules/worldengine/nucleus/src/Database/Implementation/LoginDatabase.cpp b/modules/worldengine/nucleus/src/Database/Implementation/LoginDatabase.cpp index 216875bb7a..76d7fdc196 100644 --- a/modules/worldengine/nucleus/src/Database/Implementation/LoginDatabase.cpp +++ b/modules/worldengine/nucleus/src/Database/Implementation/LoginDatabase.cpp @@ -28,6 +28,7 @@ void LoginDatabaseConnection::DoPrepareStatements() PrepareStatement(LOGIN_UPD_VS, "UPDATE account SET v = ?, s = ? WHERE username = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_LOGONPROOF, "UPDATE account SET sessionkey = ?, last_ip = ?, last_login = NOW(), locale = ?, failed_logins = 0, os = ? WHERE username = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_SEL_LOGONCHALLENGE, "SELECT a.sha_pass_hash, a.id, a.locked, a.last_ip, aa.gmlevel, a.v, a.s FROM account a LEFT JOIN account_access aa ON (a.id = aa.id) WHERE a.username = ?", CONNECTION_SYNCH); + PrepareStatement(LOGIN_SEL_LOGON_COUNTRY, "SELECT country FROM ip2nation WHERE ip < ? ORDER BY ip DESC LIMIT 0,1", CONNECTION_SYNCH); PrepareStatement(LOGIN_UPD_FAILEDLOGINS, "UPDATE account SET failed_logins = failed_logins + 1 WHERE username = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_SEL_FAILEDLOGINS, "SELECT id, failed_logins FROM account WHERE username = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_SEL_ACCOUNT_ID_BY_NAME, "SELECT id FROM account WHERE username = ?", CONNECTION_SYNCH); @@ -49,6 +50,7 @@ void LoginDatabaseConnection::DoPrepareStatements() PrepareStatement(LOGIN_INS_REALM_CHARACTERS_INIT, "INSERT INTO realmcharacters (realmid, acctid, numchars) SELECT realmlist.id, account.id, 0 FROM realmlist, account LEFT JOIN realmcharacters ON acctid=account.id WHERE acctid IS NULL", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_EXPANSION, "UPDATE account SET expansion = ? WHERE id = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_ACCOUNT_LOCK, "UPDATE account SET locked = ? WHERE id = ?", CONNECTION_ASYNC); + PrepareStatement(LOGIN_UPD_ACCOUNT_LOCK_CONTRY, "UPDATE account SET lock_country = ? WHERE id = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_USERNAME, "UPDATE account SET v = 0, s = 0, username = ?, sha_pass_hash = ? WHERE id = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_PASSWORD, "UPDATE account SET v = 0, s = 0, sha_pass_hash = ? WHERE id = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_MUTE_TIME, "UPDATE account SET mutetime = ? , mutereason = ? , muteby = ? WHERE id = ?", CONNECTION_ASYNC); @@ -76,5 +78,6 @@ void LoginDatabaseConnection::DoPrepareStatements() PrepareStatement(LOGIN_SEL_ACCOUNT_WHOIS, "SELECT username, email, last_ip FROM account WHERE id = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_SEL_REALMLIST_SECURITY_LEVEL, "SELECT allowedSecurityLevel from realmlist WHERE id = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_DEL_ACCOUNT, "DELETE FROM account WHERE id = ?", CONNECTION_ASYNC); + PrepareStatement(LOGIN_SEL_IP2NATION_COUNTRY, "SELECT c.country FROM ip2nationCountries c, ip2nation i WHERE i.ip < ? AND c.code = i.country ORDER BY i.ip DESC LIMIT 0,1", CONNECTION_SYNCH); PrepareStatement(LOGIN_SEL_AUTOBROADCAST, "SELECT id, weight, text FROM autobroadcast WHERE realmid = ? OR realmid = -1", CONNECTION_SYNCH); } diff --git a/modules/worldengine/nucleus/src/Database/Implementation/LoginDatabase.h b/modules/worldengine/nucleus/src/Database/Implementation/LoginDatabase.h index 904369fbb4..d745d44c8f 100644 --- a/modules/worldengine/nucleus/src/Database/Implementation/LoginDatabase.h +++ b/modules/worldengine/nucleus/src/Database/Implementation/LoginDatabase.h @@ -46,6 +46,7 @@ enum LoginDatabaseStatements LOGIN_UPD_VS, LOGIN_UPD_LOGONPROOF, LOGIN_SEL_LOGONCHALLENGE, + LOGIN_SEL_LOGON_COUNTRY, LOGIN_UPD_FAILEDLOGINS, LOGIN_SEL_FAILEDLOGINS, LOGIN_SEL_ACCOUNT_ID_BY_NAME, @@ -69,6 +70,7 @@ enum LoginDatabaseStatements LOGIN_INS_REALM_CHARACTERS_INIT, LOGIN_UPD_EXPANSION, LOGIN_UPD_ACCOUNT_LOCK, + LOGIN_UPD_ACCOUNT_LOCK_CONTRY, LOGIN_UPD_USERNAME, LOGIN_UPD_PASSWORD, LOGIN_UPD_MUTE_TIME, @@ -96,6 +98,7 @@ enum LoginDatabaseStatements LOGIN_SEL_ACCOUNT_WHOIS, LOGIN_SEL_REALMLIST_SECURITY_LEVEL, LOGIN_DEL_ACCOUNT, + LOGIN_SEL_IP2NATION_COUNTRY, LOGIN_SEL_AUTOBROADCAST, MAX_LOGINDATABASE_STATEMENTS diff --git a/src/authserver/Server/AuthSocket.cpp b/src/authserver/Server/AuthSocket.cpp index 81a065e461..0df9deb6c9 100644 --- a/src/authserver/Server/AuthSocket.cpp +++ b/src/authserver/Server/AuthSocket.cpp @@ -426,7 +426,7 @@ bool AuthSocket::_HandleLogonChallenge() sLog->outDebug(LOG_FILTER_NETWORKIO, "[AuthChallenge] Player address is '%s'", ip_address.c_str()); #endif - if (strcmp(fields[3].GetCString(), ip_address.c_str()) != 0) + if (strcmp(fields[4].GetCString(), ip_address.c_str()) != 0) { #if defined(ENABLE_EXTRAS) && defined(ENABLE_EXTRA_LOGS) sLog->outDebug(LOG_FILTER_NETWORKIO, "[AuthChallenge] Account IP differs"); @@ -440,9 +440,37 @@ bool AuthSocket::_HandleLogonChallenge() #endif } else + { #if defined(ENABLE_EXTRAS) && defined(ENABLE_EXTRA_LOGS) sLog->outDebug(LOG_FILTER_NETWORKIO, "[AuthChallenge] Account '%s' is not locked to ip", _login.c_str()); #endif + std::string accountCountry = fields[3].GetString(); + if (accountCountry.empty() || accountCountry == "00") + sLog->outDebug(LOG_FILTER_NETWORKIO, "[AuthChallenge] Account '%s' is not locked to country", _login.c_str()); + else if (!accountCountry.empty()) + { + uint32 ip = inet_addr(ip_address.c_str()); + EndianConvertReverse(ip); + + stmt = LoginDatabase.GetPreparedStatement(LOGIN_SEL_LOGON_COUNTRY); + stmt->setUInt32(0, ip); + if (PreparedQueryResult sessionCountryQuery = LoginDatabase.Query(stmt)) + { + std::string loginCountry = (*sessionCountryQuery)[0].GetString(); + sLog->outDebug(LOG_FILTER_NETWORKIO, "[AuthChallenge] Account '%s' is locked to country: '%s' Player country is '%s'", _login.c_str(), accountCountry.c_str(), loginCountry.c_str()); + if (loginCountry != accountCountry) + { + sLog->outDebug(LOG_FILTER_NETWORKIO, "[AuthChallenge] Account country differs."); + pkt << uint8(WOW_FAIL_UNLOCKABLE_LOCK); + locked = true; + } + else + sLog->outDebug(LOG_FILTER_NETWORKIO, "[AuthChallenge] Account country matches"); + } + else + sLog->outDebug(LOG_FILTER_NETWORKIO, "[AuthChallenge] IP2NATION Table empty"); + } + } if (!locked) { @@ -476,8 +504,8 @@ bool AuthSocket::_HandleLogonChallenge() std::string rI = fields[0].GetString(); // Don't calculate (v, s) if there are already some in the database - std::string databaseV = fields[5].GetString(); - std::string databaseS = fields[6].GetString(); + std::string databaseV = fields[6].GetString(); + std::string databaseS = fields[7].GetString(); #if defined(ENABLE_EXTRAS) && defined(ENABLE_EXTRA_LOGS) sLog->outDebug(LOG_FILTER_NETWORKIO, "database authentication values: v='%s' s='%s'", databaseV.c_str(), databaseS.c_str()); @@ -536,7 +564,7 @@ bool AuthSocket::_HandleLogonChallenge() if (securityFlags & 0x04) // Security token input pkt << uint8(1); - uint8 secLevel = fields[4].GetUInt8(); + uint8 secLevel = fields[5].GetUInt8(); _accountSecurityLevel = secLevel <= SEC_ADMINISTRATOR ? AccountTypes(secLevel) : SEC_ADMINISTRATOR; _localizationName.resize(4); diff --git a/src/scripts/Commands/cs_account.cpp b/src/scripts/Commands/cs_account.cpp index eb1750d9e1..4b6ba7d79f 100644 --- a/src/scripts/Commands/cs_account.cpp +++ b/src/scripts/Commands/cs_account.cpp @@ -30,13 +30,18 @@ public: { "gmlevel", SEC_CONSOLE, true, &HandleAccountSetGmLevelCommand, "" }, { "password", SEC_CONSOLE, true, &HandleAccountSetPasswordCommand, "" } }; + static std::vector<ChatCommand> accountLockCommandTable + { + { "country", SEC_PLAYER, true, &HandleAccountLockCountryCommand, "" }, + { "ip", SEC_PLAYER, true, &HandleAccountLockIpCommand, "" } + }; static std::vector<ChatCommand> accountCommandTable = { { "addon", SEC_MODERATOR, false, &HandleAccountAddonCommand, "" }, { "create", SEC_CONSOLE, true, &HandleAccountCreateCommand, "" }, { "delete", SEC_CONSOLE, true, &HandleAccountDeleteCommand, "" }, { "onlinelist", SEC_CONSOLE, true, &HandleAccountOnlineListCommand, "" }, - { "lock", SEC_PLAYER, false, &HandleAccountLockCommand, "" }, + { "lock", SEC_PLAYER, false, nullptr, "", accountLockCommandTable }, { "set", SEC_ADMINISTRATOR, true, nullptr, "", accountSetCommandTable }, { "password", SEC_PLAYER, false, &HandleAccountPasswordCommand, "" }, { "", SEC_PLAYER, false, &HandleAccountCommand, "" } @@ -230,7 +235,59 @@ public: return true; } - static bool HandleAccountLockCommand(ChatHandler* handler, char const* args) + static bool HandleAccountLockCountryCommand(ChatHandler* handler, char const* args) + { + if (!*args) + { + handler->SendSysMessage(LANG_USE_BOL); + handler->SetSentErrorMessage(true); + return false; + } + + std::string param = (char*)args; + + if (!param.empty()) + { + if (param == "on") + { + PreparedStatement* stmt = LoginDatabase.GetPreparedStatement(LOGIN_SEL_LOGON_COUNTRY); + uint32 ip = inet_addr(handler->GetSession()->GetRemoteAddress().c_str()); + EndianConvertReverse(ip); + stmt->setUInt32(0, ip); + PreparedQueryResult result = LoginDatabase.Query(stmt); + if (result) + { + Field* fields = result->Fetch(); + std::string country = fields[0].GetString(); + stmt = LoginDatabase.GetPreparedStatement(LOGIN_UPD_ACCOUNT_LOCK_CONTRY); + stmt->setString(0, country); + stmt->setUInt32(1, handler->GetSession()->GetAccountId()); + LoginDatabase.Execute(stmt); + handler->PSendSysMessage(LANG_COMMAND_ACCLOCKLOCKED); + } + else + { + handler->PSendSysMessage("[IP2NATION] Table empty"); + ;//sLog->outDebug(LOG_FILTER_NETWORKIO, "[IP2NATION] Table empty"); + } + } + else if (param == "off") + { + PreparedStatement* stmt = LoginDatabase.GetPreparedStatement(LOGIN_UPD_ACCOUNT_LOCK_CONTRY); + stmt->setString(0, "00"); + stmt->setUInt32(1, handler->GetSession()->GetAccountId()); + LoginDatabase.Execute(stmt); + handler->PSendSysMessage(LANG_COMMAND_ACCLOCKUNLOCKED); + } + return true; + } + + handler->SendSysMessage(LANG_USE_BOL); + handler->SetSentErrorMessage(true); + return false; + } + + static bool HandleAccountLockIpCommand(ChatHandler* handler, char const* args) { if (!*args) { diff --git a/src/scripts/Commands/cs_misc.cpp b/src/scripts/Commands/cs_misc.cpp index 3ad187cac5..1af226c38e 100644 --- a/src/scripts/Commands/cs_misc.cpp +++ b/src/scripts/Commands/cs_misc.cpp @@ -1887,7 +1887,19 @@ public: #if TRINITY_ENDIAN == BIGENDIAN EndianConvertReverse(ip); #endif + PreparedStatement* stmt = LoginDatabase.GetPreparedStatement(LOGIN_SEL_IP2NATION_COUNTRY); + stmt->setUInt32(0, ip); + + PreparedQueryResult result2 = WorldDatabase.Query(stmt); + + if (result2) + { + Field* fields2 = result2->Fetch(); + lastIp.append(" ("); + lastIp.append(fields2[0].GetString()); + lastIp.append(")"); + } } else { |