From 29f6d814a276af83d9fb98a398ac8d22d8b6e9c1 Mon Sep 17 00:00:00 2001 From: fusion32 Date: Thu, 17 Jul 2025 01:02:33 -0300 Subject: impl LOGIN_ACCOUNT to be used with the login server --- sql/schema.sql | 16 ++++-- src/connections.cc | 155 +++++++++++++++++++++++++++++++++++++++++++++++++--- src/database.cc | 39 ++++++++++++- src/querymanager.hh | 12 +++- 4 files changed, 207 insertions(+), 15 deletions(-) diff --git a/sql/schema.sql b/sql/schema.sql index 49d5cb6..5427ea6 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -20,9 +20,9 @@ CREATE TABLE IF NOT EXISTS Accounts ( AccountID INTEGER NOT NULL, Email TEXT NOT NULL COLLATE NOCASE, Auth BLOB NOT NULL, - PremiumEnd INTEGER NOT NULL, - PendingPremiumDays INTEGER NOT NULL, - Deleted INTEGER NOT NULL, + PremiumEnd INTEGER NOT NULL DEFAULT 0, + PendingPremiumDays INTEGER NOT NULL DEFAULT 0, + Deleted INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (AccountID), UNIQUE (Email) ); @@ -264,6 +264,12 @@ CREATE TABLE IF NOT EXISTS OnlineCharacters ( -- REMOVE(fusion): Testing Data. --============================================================================== -INSERT INTO Worlds (Name, Type, RebootTime, Address, Port, MaxPlayers, +INSERT INTO Worlds (WorldID, Name, Type, RebootTime, Address, Port, MaxPlayers, PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) - VALUES ('Zanera', 0, 5, 0x7F000001, 7172, 1000, 100, 300, 100); + VALUES (1, 'Zanera', 0, 5, 0x7F000001, 7172, 1000, 100, 300, 100); + +INSERT INTO Accounts (AccountID, Email, Auth) + VALUES (111111, '@tibia', X'206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); + +INSERT INTO Characters (WorldID, CharacterID, AccountID, Name, Sex) + VALUES (1, 1, 1, 'Player', 1); diff --git a/src/connections.cc b/src/connections.cc index 79a810e..e78c48e 100644 --- a/src/connections.cc +++ b/src/connections.cc @@ -175,7 +175,6 @@ void ReleaseConnection(TConnection *Connection){ DeleteConnectionBuffer(Connection); memset(Connection, 0, sizeof(TConnection)); Connection->State = CONNECTION_FREE; - Connection->Socket = -1; } } @@ -397,6 +396,7 @@ void ExitConnections(void){ } free(g_Connections); + g_Connections = NULL; } } @@ -518,11 +518,151 @@ void ProcessLoginQuery(TConnection *Connection, TReadBuffer *Buffer){ SendQueryStatusOk(Connection); } +// TODO(fusion): This might be replaced with some `LOGIN_WEB` query. void ProcessCheckAccountPasswordQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + char Password[30]; + char IPString[16]; + int AccountID = (int)Buffer->Read32(); + Buffer->ReadString(Password, sizeof(Password)); + Buffer->ReadString(IPString, sizeof(IPString)); + + int IPAddress = 0; + if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){ + SendQueryStatusFailed(Connection); + return; + } + + // TODO(fusion): This query may return errors 1-4 but their meaning is not + // clear, since there is no explicit use of it. + TAccountData Account; + if(!GetAccountData(AccountID, &Account)){ + SendQueryStatusFailed(Connection); + return; + } + + if(Account.AccountID == 0){ + SendQueryStatusError(Connection, 1); + return; + } + + if(!TestPassword(Account.Auth, sizeof(Account.Auth), Password)){ + SendQueryStatusError(Connection, 2); + return; + } + + if(IsAccountBanished(Account.AccountID)){ + SendQueryStatusError(Connection, 3); + return; + } + + if(IsIPBanished(IPAddress)){ + SendQueryStatusError(Connection, 4); + return; + } + + SendQueryStatusOk(Connection); +} + +int LoginAccountTransaction(int AccountID, const char *Password, int IPAddress, + DynamicArray *Characters, int *PremiumDays){ + TransactionScope Tx("LoginAccount"); + if(!Tx.Begin()){ + return -1; + } + + TAccountData Account; + if(!GetAccountData(AccountID, &Account)){ + return -1; + } + + if(Account.AccountID == 0){ + return 1; + } + + if(!TestPassword(Account.Auth, sizeof(Account.Auth), Password)){ + return 2; + } + + if(GetAccountLoginAttempts(Account.AccountID, 5 * 60) > 10){ + return 3; + } + + if(GetIPAddressLoginAttempts(IPAddress, 30 * 60) > 15){ + return 4; + } + + if(IsAccountBanished(Account.AccountID)){ + return 5; + } + + if(IsIPBanished(IPAddress)){ + return 6; + } + + if(!GetCharacterList(Account.AccountID, Characters)){ + return -1; + } + + if(!Tx.Commit()){ + return -1; + } + + *PremiumDays = Account.PremiumDays + Account.PendingPremiumDays; + return 0; +} + +void ProcessLoginAccountQuery(TConnection *Connection, TReadBuffer *Buffer){ + char Password[30]; + char IPString[16]; + int AccountID = (int)Buffer->Read32(); + Buffer->ReadString(Password, sizeof(Password)); + Buffer->ReadString(IPString, sizeof(IPString)); + + int IPAddress = 0; + if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){ + SendQueryStatusFailed(Connection); + return; + } + + int PremiumDays = 0; + DynamicArray Characters; + int Result = LoginAccountTransaction(AccountID, Password, + IPAddress, &Characters, &PremiumDays); + + // NOTE(fusion): Similar to `ProcessLoginGameQuery` except we don't modify + // any tables inside the login transaction. + // TODO(fusion): Maybe have different login attempt tables or types? + InsertLoginAttempt(AccountID, IPAddress, (Result != 0)); + + if(Result == -1){ + SendQueryStatusFailed(Connection); + return; + } + + if(Result != 0){ + SendQueryStatusError(Connection, Result); + return; + } + + TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK); + int NumCharacters = std::min(Characters.Length(), UINT8_MAX); + WriteBuffer.Write8((uint8)NumCharacters); + for(int i = 0; i < NumCharacters; i += 1){ + WriteBuffer.WriteString(Characters[i].Name); + WriteBuffer.WriteString(Characters[i].WorldName); + WriteBuffer.Write32BE((uint32)Characters[i].WorldAddress); + WriteBuffer.Write16((uint16)Characters[i].WorldPort); + } + WriteBuffer.Write16((uint16)PremiumDays); + SendResponse(Connection, &WriteBuffer); } void ProcessLoginAdminQuery(TConnection *Connection, TReadBuffer *Buffer){ + // TODO(fusion): I thought for a second this could be the query used with + // the login server but it doesn't take a password or ip address for basic + // checks. Even if it's used in combination with `CheckAccountPassword`, + // it doesn't make sense to split what should have been a single query which + // is what the new `LoginAccount` query does. SendQueryStatusFailed(Connection); } @@ -571,6 +711,10 @@ static int LoginGameTransaction(int WorldID, int AccountID, const char *Characte return 8; } + if(!TestPassword(Account.Auth, sizeof(Account.Auth), Password)){ + return 6; + } + if(GetAccountLoginAttempts(Account.AccountID, 5 * 60) > 10){ return 7; } @@ -579,10 +723,6 @@ static int LoginGameTransaction(int WorldID, int AccountID, const char *Characte return 9; } - if(!TestPassword(Account.Auth, sizeof(Account.Auth), Password)){ - return 6; - } - if(IsAccountBanished(Account.AccountID)){ return 10; } @@ -614,7 +754,7 @@ static int LoginGameTransaction(int WorldID, int AccountID, const char *Characte return -1; } - if(!Account.Premium && Account.PendingPremiumDays > 0){ + if(Account.PremiumDays == 0 && Account.PendingPremiumDays > 0){ if(!ActivatePendingPremiumDays(Account.AccountID)){ return -1; } @@ -1717,6 +1857,7 @@ void ProcessConnectionQuery(TConnection *Connection){ switch(Query){ case QUERY_CHECK_ACCOUNT_PASSWORD: ProcessCheckAccountPasswordQuery(Connection, &Buffer); break; + case QUERY_LOGIN_ACCOUNT: ProcessLoginAccountQuery(Connection, &Buffer); break; case QUERY_LOGIN_ADMIN: ProcessLoginAdminQuery(Connection, &Buffer); break; case QUERY_LOGIN_GAME: ProcessLoginGameQuery(Connection, &Buffer); break; case QUERY_LOGOUT_GAME: ProcessLogoutGameQuery(Connection, &Buffer); break; diff --git a/src/database.cc b/src/database.cc index 3ba0201..cd32f88 100644 --- a/src/database.cc +++ b/src/database.cc @@ -201,7 +201,7 @@ bool GetWorldConfig(int WorldID, TWorldConfig *WorldConfig){ bool GetAccountData(int AccountID, TAccountData *Account){ ASSERT(Account != NULL); sqlite3_stmt *Stmt = PrepareQuery( - "SELECT AccountID, Email, Auth, (PremiumEnd >= UNIXEPOCH()), PendingPremiumDays" + "SELECT AccountID, Email, Auth, MAX(PremiumEnd - UNIXEPOCH(), 0), PendingPremiumDays" " FROM Accounts WHERE AccountID = ?1"); if(Stmt == NULL){ LOG_ERR("Failed to prepare query"); @@ -227,7 +227,7 @@ bool GetAccountData(int AccountID, TAccountData *Account){ if(sqlite3_column_bytes(Stmt, 2) == sizeof(Account->Auth)){ memcpy(Account->Auth, sqlite3_column_blob(Stmt, 2), sizeof(Account->Auth)); } - Account->Premium = (sqlite3_column_int(Stmt, 3) != 0); + Account->PremiumDays = (sqlite3_column_int(Stmt, 3) + 86399) / 86400; Account->PendingPremiumDays = sqlite3_column_int(Stmt, 4); } @@ -280,6 +280,41 @@ bool ActivatePendingPremiumDays(int AccountID){ return true; } +bool GetCharacterList(int AccountID, DynamicArray *Characters){ + sqlite3_stmt *Stmt = PrepareQuery( + "SELECT C.Name, W.Name, W.IPAddress, W.Port" + " FROM Characters AS C" + " INNER JOIN Worlds AS W ON W.WorldID = C.WorldID" + " WHERE C.AccountID = ?1"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + return false; + } + + if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){ + LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(g_Database)); + return false; + } + + while(sqlite3_step(Stmt) == SQLITE_ROW){ + TCharacterLoginData Character = {}; + StringCopy(Character.Name, sizeof(Character.Name), + (const char*)sqlite3_column_text(Stmt, 0)); + StringCopy(Character.WorldName, sizeof(Character.WorldName), + (const char*)sqlite3_column_text(Stmt, 1)); + Character.WorldAddress = sqlite3_column_int(Stmt, 2); + Character.WorldPort = sqlite3_column_int(Stmt, 3); + Characters->Push(Character); + } + + if(sqlite3_errcode(g_Database) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + return true; +} + int GetCharacterID(int WorldID, const char *CharacterName){ ASSERT(CharacterName != NULL); sqlite3_stmt *Stmt = PrepareQuery( diff --git a/src/querymanager.hh b/src/querymanager.hh index ef37de1..9b358ce 100644 --- a/src/querymanager.hh +++ b/src/querymanager.hh @@ -549,6 +549,7 @@ enum : int { enum : int { QUERY_LOGIN = 0, QUERY_CHECK_ACCOUNT_PASSWORD = 10, + QUERY_LOGIN_ACCOUNT = 11, QUERY_LOGIN_ADMIN = 12, QUERY_LOGIN_GAME = 20, QUERY_LOGOUT_GAME = 21, @@ -639,6 +640,7 @@ void SendQueryStatusError(TConnection *Connection, int ErrorCode); void SendQueryStatusFailed(TConnection *Connection); void ProcessLoginQuery(TConnection *Connection, TReadBuffer *Buffer); void ProcessCheckAccountPasswordQuery(TConnection *Connection, TReadBuffer *Buffer); +void ProcessLoginAccountQuery(TConnection *Connection, TReadBuffer *Buffer); void ProcessLoginAdminQuery(TConnection *Connection, TReadBuffer *Buffer); void ProcessLoginGameQuery(TConnection *Connection, TReadBuffer *Buffer); void ProcessLogoutGameQuery(TConnection *Connection, TReadBuffer *Buffer); @@ -705,7 +707,7 @@ struct TAccountData{ int AccountID; char Email[100]; uint8 Auth[64]; - bool Premium; + int PremiumDays; int PendingPremiumDays; bool Deleted; }; @@ -715,6 +717,13 @@ struct TAccountBuddy{ char Name[30]; }; +struct TCharacterLoginData{ + char Name[30]; + char WorldName[30]; + int WorldAddress; + int WorldPort; +}; + struct TCharacterData{ int WorldID; int CharacterID; @@ -826,6 +835,7 @@ bool GetWorldConfig(int WorldID, TWorldConfig *WorldConfig); bool GetAccountData(int AccountID, TAccountData *Account); int GetAccountOnlineCharacters(int AccountID); bool ActivatePendingPremiumDays(int AccountID); +bool GetCharacterList(int AccountID, DynamicArray *Characters); int GetCharacterID(int WorldID, const char *CharacterName); bool GetCharacterData(const char *CharacterName, TCharacterData *Character); bool GetCharacterRight(int CharacterID, const char *Right); -- cgit v1.2.3