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

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
}

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 Challenge command handler
bool AuthSocket::_HandleLogonChallenge()
{
    DEBUG_LOG("Entering _HandleLogonChallenge");
    if (ibuf.GetLength() < sizeof(sAuthLogonChallenge_C))
        return false;

    ///- Read the first 4 bytes (header) to get the length of the remaining of the packet
    std::vector<uint8> buf;
    buf.resize(4);

    ibuf.Read((char *)&buf[0], 4);
    uint16 remaining = ((sAuthLogonChallenge_C *)&buf[0])->size;
    DEBUG_LOG("[AuthChallenge] got header, body is %#04x bytes", remaining);

    if ((remaining < sizeof(sAuthLogonChallenge_C) - buf.size()) || (ibuf.GetLength() < remaining))
        return false;

    //No big fear of memory outage (size is int16, i.e. < 65536)
    buf.resize(remaining + buf.size() + 1);
    buf[buf.size() - 1] = 0;
    sAuthLogonChallenge_C *ch = (sAuthLogonChallenge_C*)&buf[0];

    ///- Read the remaining of the packet
    ibuf.Read((char *)&buf[4], remaining);
    DEBUG_LOG("[AuthChallenge] got full packet, %#04x bytes", ch->size);
    DEBUG_LOG("[AuthChallenge] name(%d): '%s'", ch->I_len, ch->I);

    ByteBuffer pkt;

    _login = (const char*)ch->I;

    //Escape the user login to avoid further SQL injection
    //Memory will be freed on AuthSocket object destruction
    _safelogin=_login;
    dbRealmServer.escape_string(_safelogin);

    ///- Check if the client has one of the expected version numbers
    bool valid_version=false;
    int accepted_versions[]=EXPECTED_MANGOS_CLIENT_BUILD;
    for(int i=0;accepted_versions[i];i++)
        if(ch->build==accepted_versions[i])
    {
        valid_version=true;
        break;
    }

    /// <ul><li> if this is a valid version
    if(valid_version)
    {
        pkt << (uint8) AUTH_LOGON_CHALLENGE;
        pkt << (uint8) 0x00;

        ///- Verify that this IP is not in the ip_banned table
        // No SQL injection possible (paste the IP address as passed by the socket)
        dbRealmServer.Execute("DELETE FROM `ip_banned` WHERE `unbandate`<=UNIX_TIMESTAMP() AND `unbandate`<>`bandate`");
        QueryResult *result = dbRealmServer.PQuery(  "SELECT * FROM `ip_banned` WHERE `ip` = '%s'",GetRemoteAddress().c_str());
        if(result)
        {
            pkt << (uint8)REALM_AUTH_ACCOUNT_BANNED;
            sLog.outBasic("[AuthChallenge] Banned ip %s tries to login!",GetRemoteAddress().c_str ());
            delete result;
        }
        else
        {
            ///- Get the account details from the account table
            // No SQL injection (escaped user name)

            result = dbRealmServer.PQuery("SELECT `I`,`id`,`locked`,`last_ip`,`gmlevel` FROM `account` WHERE `username` = '%s'",_safelogin.c_str ());
            if( result )
            {
                ///- If the IP is 'locked', check that the player comes indeed from the correct IP address
                bool locked = false;
                if((*result)[2].GetUInt8() == 1)            // if ip is locked
                {
                    DEBUG_LOG("[AuthChallenge] Account '%s' is locked to IP - '%s'", _login.c_str(), (*result)[3].GetString());
                    DEBUG_LOG("[AuthChallenge] Player address is '%s'", GetRemoteAddress().c_str());
                    if ( strcmp((*result)[3].GetString(),GetRemoteAddress().c_str()) )
                    {
                        DEBUG_LOG("[AuthChallenge] Account IP differs");
                        pkt << (uint8) REALM_AUTH_ACCOUNT_FREEZED;
                        locked=true;
                    }
                    else
                    {
                        DEBUG_LOG("[AuthChallenge] Account IP matches");
                    }
                }
                else
                {
                    DEBUG_LOG("[AuthChallenge] Account '%s' is not locked to ip", _login.c_str());
                }

                if (!locked)
                {
                    //set expired bans to inactive
                    dbRealmServer.Execute("UPDATE `account_banned` SET `active` = 0 WHERE `unbandate`<=UNIX_TIMESTAMP() AND `unbandate`<>`bandate`");
                    ///- If the account is banned, reject the logon attempt
                    QueryResult *banresult = dbRealmServer.PQuery("SELECT `bandate`,`unbandate` FROM `account_banned` WHERE `id` = %u AND `active` = 1", (*result)[1].GetUInt32());
                    if(banresult)
//.........这里部分代码省略.........