C++ DatabasePostgre::PExecute方法代码示例

AccountOpResult AccountMgr::DeleteAccount(uint32 accid)
{
    QueryResult *result = loginDatabase.PQuery("SELECT 1 FROM account WHERE id='%d'", accid);
    if(!result)
        return AOR_NAME_NOT_EXIST;                          // account doesn't exist
    delete result;

    result = CharacterDatabase.PQuery("SELECT guid FROM characters WHERE account='%d'",accid);
    if (result)
    {
        do
        {
            Field *fields = result->Fetch();
            uint32 guidlo = fields[0].GetUInt32();
            uint64 guid = MAKE_NEW_GUID(guidlo, 0, HIGHGUID_PLAYER);

            // kick if player currently
            if(Player* p = ObjectAccessor::FindPlayer(guid))
            {
                WorldSession* s = p->GetSession();
                s->KickPlayer();                            // mark session to remove at next session list update
                s->LogoutPlayer(false);                     // logout player without waiting next session list update
            }

            Player::DeleteFromDB(guid, accid, false);       // no need to update realm characters
        } while (result->NextRow());

        delete result;
    }

    // table realm specific but common for all characters of account for realm
    CharacterDatabase.PExecute("DELETE FROM character_tutorial WHERE account = '%u'",accid);

    loginDatabase.BeginTransaction();

    bool res =
        loginDatabase.PExecute("DELETE FROM account WHERE id='%d'", accid) &&
        loginDatabase.PExecute("DELETE FROM realmcharacters WHERE acctid='%d'", accid);

    loginDatabase.CommitTransaction();

    if(!res)
        return AOR_DB_INTERNAL_ERROR;                       // unexpected error;

    return AOR_OK;
}

int AccountMgr::DeleteAccount(uint32 accid)
{
    QueryResult *result = loginDatabase.PQuery("SELECT 1 FROM account WHERE id='%d'", accid);
    if(!result)
        return 1;                                           // account doesn't exist
    delete result;

    result = CharacterDatabase.PQuery("SELECT guid FROM characters WHERE account='%d'",accid);
    if (result)
    {
        do
        {
            Field *fields = result->Fetch();
            uint32 guidlo = fields[0].GetUInt32();
            uint64 guid = MAKE_GUID(guidlo, HIGHGUID_PLAYER);

            // kick if player currently
            if(Player* p = objmgr.GetPlayer(guid))
            {
                WorldSession* s = p->GetSession();
                s->KickPlayer();                            // mark session to remove at next session list update
                s->LogoutPlayer(false);                     // logout player without waiting next session list update
            }

            Player::DeleteFromDB(guid, accid, false);       // no need to update realm characters
        } while (result->NextRow());

        delete result;
    }

    loginDatabase.BeginTransaction();

    bool res =  loginDatabase.PExecute("DELETE FROM account WHERE id='%d'", accid) &&
        loginDatabase.PExecute("DELETE FROM realmcharacters WHERE acctid='%d'", accid);

    loginDatabase.CommitTransaction();

    if(!res)
        return -1;                                          // unexpected error;

    return 0;
}

int AccountMgr::ChangePassword(uint32 accid, std::string new_passwd)
{
    QueryResult *result = loginDatabase.PQuery("SELECT 1 FROM account WHERE id='%d'", accid);
    if(!result)
        return 1;                                           // account doesn't exist
    delete result;

    loginDatabase.escape_string(new_passwd);
    if(!loginDatabase.PExecute("UPDATE account SET sha_pass_hash=SHA1(CONCAT(UPPER(username),':',UPPER('%s'))) WHERE id='%d'", new_passwd.c_str(), accid))
        return -1;                                          // unexpected error

    return 0;
}

int AccountMgr::ChangeUsername(uint32 accid, std::string new_uname, std::string new_passwd)
{
    QueryResult *result = loginDatabase.PQuery("SELECT 1 FROM `account` WHERE `id`='%d'", accid);
    if(!result)
        return 1;                                           // account doesn't exist
    delete result;

    loginDatabase.escape_string(new_uname);
    loginDatabase.escape_string(new_passwd);
    if(!loginDatabase.PExecute("UPDATE `account` SET `username`='%s',`I`=SHA1(CONCAT(UPPER('%s'),':',UPPER('%s'))) WHERE `id`='%d'", new_uname.c_str(), new_uname.c_str(), new_passwd.c_str(), accid))
        return -1;                                          // unexpected error

    return 0;
}

AccountOpResult AccountMgr::ChangePassword(uint32 accid, std::string new_passwd)
{
    QueryResult *result = loginDatabase.PQuery("SELECT 1 FROM account WHERE id='%d'", accid);
    if(!result)
        return AOR_NAME_NOT_EXIST;                          // account doesn't exist
    delete result;

    if (utf8length(new_passwd) > MAX_ACCOUNT_STR)
        return AOR_PASS_TOO_LONG;

    normilizeString(new_passwd);

    loginDatabase.escape_string(new_passwd);
    if(!loginDatabase.PExecute("UPDATE account SET sha_pass_hash=SHA1(CONCAT(username,':','%s')) WHERE id='%d'", new_passwd.c_str(), accid))
        return AOR_DB_INTERNAL_ERROR;                       // unexpected error

    return AOR_OK;
}

int AccountMgr::CreateAccount(std::string username, std::string password)
{
    if(username.length() > 16)
        return 1;                                           // username's too long

    loginDatabase.escape_string(username);
    QueryResult *result = loginDatabase.PQuery("SELECT 1 FROM `account` WHERE `username`='%s'", username.c_str());
    if(result)
    {
        delete result;
        return 2;                                           // username does already exist
    }

    loginDatabase.escape_string(password);

    if(!loginDatabase.PExecute("INSERT INTO `account`(`username`,`I`,`joindate`) VALUES('%s',SHA1(CONCAT(UPPER('%s'),':',UPPER('%s'))),NOW())", username.c_str(), username.c_str(), password.c_str()))
        return -1;                                          // unexpected error
    loginDatabase.Execute("INSERT INTO `realmcharacters` (`realmid`, `acctid`, `numchars`) SELECT `realmlist`.`id`, `account`.`id`, 0 FROM `account`, `realmlist` WHERE `account`.`id` NOT IN (SELECT `acctid` FROM `realmcharacters`)");

    return 0;                                               // everything's fine
}

/// Make the SRP6 calculation from hash in dB
void AuthSocket::_SetVSFields(std::string rI)
{
    BigNumber I;
    I.SetHexStr(rI.c_str());

    //In case of leading zeroes in the rI hash, restore them
    uint8 mDigest[SHA_DIGEST_LENGTH];
    memset(mDigest,0,SHA_DIGEST_LENGTH);
    if (I.GetNumBytes() <= SHA_DIGEST_LENGTH)
        memcpy(mDigest,I.AsByteArray(),I.GetNumBytes());

    std::reverse(mDigest,mDigest+SHA_DIGEST_LENGTH);

    Sha1Hash sha;
    sha.UpdateData(s.AsByteArray(), s.GetNumBytes());
    sha.UpdateData(mDigest, SHA_DIGEST_LENGTH);
    sha.Finalize();
    BigNumber x;
    x.SetBinary(sha.GetDigest(), sha.GetLength());
    v = g.ModExp(x, N);
    // No SQL injection (username escaped)
    dbRealmServer.PExecute("UPDATE `account` SET `v` = '%s', `s` = '%s' WHERE UPPER(`username`)= UPPER('%s')",v.AsHexStr(),s.AsHexStr(), _safelogin.c_str() );
}

int AccountMgr::CreateAccount(std::string username, std::string password)
{
    if(username.length() > 16)
        return 1;                                           // username's too long

    std::transform( username.begin(), username.end(), username.begin(), ::toupper );

    loginDatabase.escape_string(username);
    QueryResult *result = loginDatabase.PQuery("SELECT 1 FROM account WHERE username='%s'", username.c_str());
    if(result)
    {
        delete result;
        return 2;                                           // username does already exist
    }

    loginDatabase.escape_string(password);

    if(!loginDatabase.PExecute("INSERT INTO account(username,sha_pass_hash,joindate) VALUES('%s',SHA1(CONCAT(UPPER('%s'),':',UPPER('%s'))),NOW())", username.c_str(), username.c_str(), password.c_str()))
        return -1;                                          // unexpected error
    loginDatabase.Execute("INSERT INTO realmcharacters (realmid, acctid, numchars) SELECT realmlist.id, account.id, 0 FROM account, realmlist WHERE account.id NOT IN (SELECT acctid FROM realmcharacters)");

    return 0;                                               // everything's fine
}

AccountOpResult AccountMgr::CreateAccount(std::string username, std::string password)
{
    if(utf8length(username) > MAX_ACCOUNT_STR)
        return AOR_NAME_TOO_LONG;                           // username's too long

    normilizeString(username);
    normilizeString(password);

    loginDatabase.escape_string(username);
    loginDatabase.escape_string(password);

    QueryResult *result = loginDatabase.PQuery("SELECT 1 FROM account WHERE username = '%s'", username.c_str());
    if(result)
    {
        delete result;
        return AOR_NAME_ALREDY_EXIST;                       // username does already exist
    }

    if(!loginDatabase.PExecute("INSERT INTO account(username,sha_pass_hash,joindate) VALUES('%s',SHA1(CONCAT('%s',':','%s')),NOW())", username.c_str(), username.c_str(), password.c_str()))
        return AOR_DB_INTERNAL_ERROR;                       // unexpected error
    loginDatabase.Execute("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");

    return AOR_OK;                                          // everything's fine
}

/// Logon Proof command handler
bool AuthSocket::_HandleLogonProof()
{
    DEBUG_LOG("Entering _HandleLogonProof");
    ///- Read the packet
    if (ibuf.GetLength() < sizeof(sAuthLogonProof_C))
        return false;

    sAuthLogonProof_C lp;
    ibuf.Read((char *)&lp, sizeof(sAuthLogonProof_C));

    ///- Continue the SRP6 calculation based on data received from the client
    BigNumber A;
    A.SetBinary(lp.A, 32);

    Sha1Hash sha;
    sha.UpdateBigNumbers(&A, &B, NULL);
    sha.Finalize();
    BigNumber u;
    u.SetBinary(sha.GetDigest(), 20);
    BigNumber S = (A * (v.ModExp(u, N))).ModExp(b, N);

    uint8 t[32];
    uint8 t1[16];
    uint8 vK[40];
    memcpy(t, S.AsByteArray(), 32);
    for (int i = 0; i < 16; i++)
    {
        t1[i] = t[i*2];
    }
    sha.Initialize();
    sha.UpdateData(t1, 16);
    sha.Finalize();
    for (int i = 0; i < 20; i++)
    {
        vK[i*2] = sha.GetDigest()[i];
    }
    for (int i = 0; i < 16; i++)
    {
        t1[i] = t[i*2+1];
    }
    sha.Initialize();
    sha.UpdateData(t1, 16);
    sha.Finalize();
    for (int i = 0; i < 20; i++)
    {
        vK[i*2+1] = sha.GetDigest()[i];
    }
    K.SetBinary(vK, 40);

    uint8 hash[20];

    sha.Initialize();
    sha.UpdateBigNumbers(&N, NULL);
    sha.Finalize();
    memcpy(hash, sha.GetDigest(), 20);
    sha.Initialize();
    sha.UpdateBigNumbers(&g, NULL);
    sha.Finalize();
    for (int i = 0; i < 20; i++)
    {
        hash[i] ^= sha.GetDigest()[i];
    }
    BigNumber t3;
    t3.SetBinary(hash, 20);

    sha.Initialize();
    sha.UpdateData(_login);
    sha.Finalize();
    BigNumber t4;
    t4.SetBinary(sha.GetDigest(), 20);

    sha.Initialize();
    sha.UpdateBigNumbers(&t3, &t4, &s, &A, &B, &K, NULL);
    sha.Finalize();
    BigNumber M;
    M.SetBinary(sha.GetDigest(), 20);

    ///- Check if SRP6 results match (password is correct), else send an error
    if (!memcmp(M.AsByteArray(), lp.M1, 20))
    {
        sLog.outBasic("User '%s' successfully authenticated", _login.c_str());

        ///- Update the sessionkey, last_ip and last login time in the account table for this account
        // No SQL injection (escaped user name) and IP address as received by socket
        dbRealmServer.PExecute("UPDATE `account` SET `sessionkey` = '%s', `last_ip` = '%s', `last_login` = NOW(), `locale` = '%u' WHERE `username` = '%s'",K.AsHexStr(), GetRemoteAddress().c_str(),  _localization, _safelogin.c_str() );

        ///- Finish SRP6 and send the final result to the client
        sha.Initialize();
        sha.UpdateBigNumbers(&A, &M, &K, NULL);
        sha.Finalize();

        sAuthLogonProof_S proof;
        memcpy(proof.M2, sha.GetDigest(), 20);
        proof.cmd = AUTH_LOGON_PROOF;
        proof.error = 0;
        proof.unk2 = 0;
        proof.unk3 = 0;

        SendBuf((char *)&proof, sizeof(proof));
//.........这里部分代码省略.........