Esempio n. 1
0
bool Servatrice_DatabaseInterface::activateUser(const QString &userName, const QString &token)
{
    if (!checkSql())
        return false;

    QSqlQuery *activateQuery = prepareQuery("select name from {prefix}_users where active=0 and name=:username and token=:token");

    activateQuery->bindValue(":username", userName);
    activateQuery->bindValue(":token", token);
    if (!execSqlQuery(activateQuery)) {
        qDebug() << "Account activation failed: SQL error." << activateQuery->lastError()<< " sql: " << activateQuery->lastQuery();
        return false;
    }

    if (activateQuery->next()) {
        const QString name = activateQuery->value(0).toString();
        // redundant check
        if(name == userName)
        {

            QSqlQuery *query = prepareQuery("update {prefix}_users set active=1 where name = :userName");
            query->bindValue(":userName", userName);

            if (!execSqlQuery(query)) {
                qDebug() << "Failed to activate user: "******" sql: " << query->lastQuery();
                return false;
            }

            return true;
        }
    }
    return false;
}
Esempio n. 2
0
bool Servatrice_DatabaseInterface::checkUserIsIpBanned(const QString &ipAddress, QString &banReason, int &banSecondsRemaining)
{
    QSqlQuery *ipBanQuery = prepareQuery(
            "select"
                    " timestampdiff(second, now(), date_add(b.time_from, interval b.minutes minute)),"
                    " b.minutes <=> 0,"
                    " b.visible_reason"
                    " from {prefix}_bans b"
                    " where"
                    " b.time_from = (select max(c.time_from)"
                    " from {prefix}_bans c"
                    " where c.ip_address = :address)"
                    " and b.ip_address = :address2");

    ipBanQuery->bindValue(":address", ipAddress);
    ipBanQuery->bindValue(":address2", ipAddress);
    if (!execSqlQuery(ipBanQuery)) {
        qDebug() << "IP ban check failed: SQL error." << ipBanQuery->lastError();
        return false;
    }

    if (ipBanQuery->next()) {
        const int secondsLeft = ipBanQuery->value(0).toInt();
        const bool permanentBan = ipBanQuery->value(1).toInt();
        if ((secondsLeft > 0) || permanentBan) {
            banReason = ipBanQuery->value(2).toString();
            banSecondsRemaining = permanentBan ? 0 : secondsLeft;
            qDebug() << "User is banned by address" << ipAddress;
            return true;
        }
    }
    return false;
}
Esempio n. 3
0
QList<ServerInfo_Ban> Servatrice_DatabaseInterface::getUserBanHistory(const QString userName)
{
    QList<ServerInfo_Ban> results;
    ServerInfo_Ban banDetails;

    if (!checkSql())
        return results;

    QSqlQuery *query = prepareQuery("SELECT A.id_admin, A.time_from, A.minutes, A.reason, A.visible_reason, B.name AS name_admin FROM {prefix}_bans A LEFT JOIN {prefix}_users B ON A.id_admin=B.id WHERE A.user_name = :user_name");
    query->bindValue(":user_name", userName);

    if (!execSqlQuery(query)) {
        qDebug("Failed to collect ban history information: SQL Error");
        return results;
    }

    while (query->next()){
        banDetails.set_admin_id(QString(query->value(0).toString()).toStdString());
        banDetails.set_admin_name(QString(query->value(5).toString()).toStdString());
        banDetails.set_ban_time(QString(query->value(1).toString()).toStdString());
        banDetails.set_ban_length(QString(query->value(2).toString()).toStdString());
        banDetails.set_ban_reason(QString(query->value(3).toString()).toStdString());
        banDetails.set_visible_reason(QString(query->value(4).toString()).toStdString());
        results << banDetails;
    }

    return results;
}
Esempio n. 4
0
bool Servatrice_DatabaseInterface::registerUser(const QString &userName, const QString &realName, ServerInfo_User_Gender const &gender, const QString &password, const QString &emailAddress, const QString &country, QString &token, bool active)
{
    if (!checkSql())
        return false;

    QString passwordSha512 = PasswordHasher::computeHash(password, PasswordHasher::generateRandomSalt());
    token = active ? QString() : PasswordHasher::generateActivationToken();

    QSqlQuery *query = prepareQuery("insert into {prefix}_users "
            "(name, realname, gender, password_sha512, email, country, registrationDate, active, token) "
            "values "
            "(:userName, :realName, :gender, :password_sha512, :email, :country, UTC_TIMESTAMP(), :active, :token)");
    query->bindValue(":userName", userName);
    query->bindValue(":realName", realName);
    query->bindValue(":gender", getGenderChar(gender));
    query->bindValue(":password_sha512", passwordSha512);
    query->bindValue(":email", emailAddress);
    query->bindValue(":country", country);
    query->bindValue(":active", active ? 1 : 0);
    query->bindValue(":token", token);

    if (!execSqlQuery(query)) {
        qDebug() << "Failed to insert user: "******" sql: " << query->lastQuery();
        return false;
    }

    return true;
}
Esempio n. 5
0
QList<ServerInfo_Warning> Servatrice_DatabaseInterface::getUserWarnHistory(const QString userName)
{
    QList<ServerInfo_Warning> results;
    ServerInfo_Warning warnDetails;

    if (!checkSql())
        return results;

    int userID = getUserIdInDB(userName);
    QSqlQuery *query = prepareQuery("SELECT user_name, mod_name, reason, time_of FROM {prefix}_warnings WHERE user_id = :user_id");
    query->bindValue(":user_id", userID);

    if (!execSqlQuery(query)) {
        qDebug("Failed to collect warning history information: SQL Error");
        return results;
    }

    while (query->next()){
        warnDetails.set_user_name(QString(query->value(0).toString()).toStdString());
        warnDetails.set_admin_name(QString(query->value(1).toString()).toStdString());
        warnDetails.set_reason(QString(query->value(2).toString()).toStdString());
        warnDetails.set_time_of(QString(query->value(3).toString()).toStdString());
        results << warnDetails;
    }

    return results;
}
Esempio n. 6
0
void Servatrice::statusUpdate()
{
	const int uc = getUsersCount(); // for correct mutex locking order
	const int gc = getGamesCount();
	
	uptime += statusUpdateClock->interval() / 1000;
	
	txBytesMutex.lock();
	quint64 tx = txBytes;
	txBytes = 0;
	txBytesMutex.unlock();
	rxBytesMutex.lock();
	quint64 rx = rxBytes;
	rxBytes = 0;
	rxBytesMutex.unlock();
	
	QMutexLocker locker(&dbMutex);
	checkSql();
	
	QSqlQuery query;
	query.prepare("insert into " + dbPrefix + "_uptime (id_server, timest, uptime, users_count, games_count, tx_bytes, rx_bytes) values(:id, NOW(), :uptime, :users_count, :games_count, :tx, :rx)");
	query.bindValue(":id", serverId);
	query.bindValue(":uptime", uptime);
	query.bindValue(":users_count", uc);
	query.bindValue(":games_count", gc);
	query.bindValue(":tx", tx);
	query.bindValue(":rx", rx);
	execSqlQuery(query);
}
Esempio n. 7
0
void Servatrice_DatabaseInterface::clearSessionTables()
{
    lockSessionTables();
    QSqlQuery *query = prepareQuery("update {prefix}_sessions set end_time=now() where end_time is null and id_server = :id_server");
    query->bindValue(":id_server", server->getServerID());
    execSqlQuery(query);
    unlockSessionTables();
}
Esempio n. 8
0
void Servatrice_DatabaseInterface::endSession(qint64 sessionId)
{
    if (server->getAuthenticationMethod() == Servatrice::AuthenticationNone)
        return;

    if (!checkSql())
        return;

    QSqlQuery *query = prepareQuery("lock tables {prefix}_sessions write");
    execSqlQuery(query);

    query = prepareQuery("update {prefix}_sessions set end_time=NOW() where id = :id_session");
    query->bindValue(":id_session", sessionId);
    execSqlQuery(query);

    query = prepareQuery("unlock tables");
    execSqlQuery(query);
}
Esempio n. 9
0
bool Servatrice_DatabaseInterface::userSessionExists(const QString &userName)
{
    // Call only after lockSessionTables().

    QSqlQuery *query = prepareQuery("select 1 from {prefix}_sessions where user_name = :user_name and id_server = :id_server and end_time is null");
    query->bindValue(":id_server", server->getServerID());
    query->bindValue(":user_name", userName);
    execSqlQuery(query);
    return query->next();
}
Esempio n. 10
0
int Servatrice_DatabaseInterface::getNextReplayId()
{
    if (!checkSql())
        return -1;

    QSqlQuery *query = prepareQuery("insert into {prefix}_replays (id_game) values (NULL)");
    execSqlQuery(query);

    return query->lastInsertId().toInt();
}
Esempio n. 11
0
void Servatrice::endSession(int sessionId)
{
	QMutexLocker locker(&dbMutex);
	checkSql();
	
	QSqlQuery query;
	query.prepare("update " + dbPrefix + "_sessions set end_time=NOW() where id = :id_session");
	query.bindValue(":id_session", sessionId);
	execSqlQuery(query);
}
Esempio n. 12
0
void Servatrice_DatabaseInterface::updateUsersLastLoginData(const QString &userName, const QString &clientVersion) {

    if (!checkSql())
        return;

    int usersID;

    QSqlQuery *query = prepareQuery("select id from {prefix}_users where name = :user_name");
    query->bindValue(":user_name", userName);
    if (!execSqlQuery(query)) {
        qDebug("Failed to locate user id when updating users last login data: SQL Error");
        return;
    }

    if (query->next()) {
        usersID = query->value(0).toInt();
    }

    if (usersID) {
        int userCount;
        query = prepareQuery("select count(id) from {prefix}_user_analytics where id = :user_id");
        query->bindValue(":user_id", usersID);
        if (!execSqlQuery(query))
            return;

        if (query->next()) {
            userCount = query->value(0).toInt();
        }

        if (!userCount) {
            query = prepareQuery("insert into {prefix}_user_analytics (id,client_ver,last_login) values (:user_id,:client_ver,NOW())");
            query->bindValue(":user_id", usersID);
            query->bindValue(":client_ver", clientVersion);
            execSqlQuery(query);
        } else {
            query = prepareQuery("update {prefix}_user_analytics set last_login = NOW(), client_ver = :client_ver where id = :user_id");
            query->bindValue(":client_ver", clientVersion);
            query->bindValue(":user_id", usersID);
            execSqlQuery(query);
        }
    }
}
int Servatrice_DatabaseInterface::getNextReplayId()
{
	if (!checkSql())
		return -1;
	
	QSqlQuery query(sqlDatabase);
	query.prepare("insert into " + server->getDbPrefix() + "_replays () values ()");
	execSqlQuery(query);
	
	return query.lastInsertId().toInt();
}
Esempio n. 14
0
void Servatrice_DatabaseInterface::updateUsersClientID(const QString &userName, const QString &userClientID)
{

    if (!checkSql())
        return;

    QSqlQuery *query = prepareQuery("update {prefix}_users set clientid = :clientid where name = :username");
    query->bindValue(":clientid", userClientID);
    query->bindValue(":username", userName);
    execSqlQuery(query);

}
Esempio n. 15
0
int Servatrice_DatabaseInterface::getUserIdInDB(const QString &name)
{
    if (server->getAuthenticationMethod() == Servatrice::AuthenticationSql) {
        QSqlQuery *query = prepareQuery("select id from {prefix}_users where name = :name and active = 1");
        query->bindValue(":name", name);
        if (!execSqlQuery(query))
            return -1;
        if (!query->next())
            return -1;
        return query->value(0).toInt();
    }
    return -1;
}
Esempio n. 16
0
int Servatrice_DatabaseInterface::getNextGameId()
{
    if (!sqlDatabase.isValid())
        return server->getNextLocalGameId();

    if (!checkSql())
        return -1;

    QSqlQuery *query = prepareQuery("insert into {prefix}_games (time_started) values (now())");
    execSqlQuery(query);

    return query->lastInsertId().toInt();
}
Esempio n. 17
0
int Servatrice::startSession(const QString &userName, const QString &address)
{
	QMutexLocker locker(&dbMutex);
	checkSql();
	
	QSqlQuery query;
	query.prepare("insert into " + dbPrefix + "_sessions (user_name, ip_address, start_time) values(:user_name, :ip_address, NOW())");
	query.bindValue(":user_name", userName);
	query.bindValue(":ip_address", address);
	if (execSqlQuery(query))
		return query.lastInsertId().toInt();
	return -1;
}
Esempio n. 18
0
bool Servatrice_DatabaseInterface::userExists(const QString &user)
{
    if (server->getAuthenticationMethod() == Servatrice::AuthenticationSql) {
        checkSql();

        QSqlQuery *query = prepareQuery("select 1 from {prefix}_users where name = :name");
        query->bindValue(":name", user);
        if (!execSqlQuery(query))
            return false;
        return query->next();
    }
    return false;
}
Esempio n. 19
0
AuthenticationResult Servatrice_DatabaseInterface::checkUserPassword(Server_ProtocolHandler *handler, const QString &user, const QString &password, const QString &clientId, QString &reasonStr, int &banSecondsLeft)
{
    switch (server->getAuthenticationMethod()) {
    case Servatrice::AuthenticationNone: return UnknownUser;
    case Servatrice::AuthenticationPassword: {
        QString configPassword = settingsCache->value("authentication/password").toString();
        if (configPassword == password)
            return PasswordRight;

        return NotLoggedIn;
    }
    case Servatrice::AuthenticationSql: {
        if (!checkSql())
            return UnknownUser;

        if (!usernameIsValid(user, reasonStr))
            return UsernameInvalid;

        if (checkUserIsBanned(handler->getAddress(), user, clientId, reasonStr, banSecondsLeft))
            return UserIsBanned;

        QSqlQuery *passwordQuery = prepareQuery("select password_sha512, active from {prefix}_users where name = :name");
        passwordQuery->bindValue(":name", user);
        if (!execSqlQuery(passwordQuery)) {
            qDebug("Login denied: SQL error");
            return NotLoggedIn;
        }

        if (passwordQuery->next()) {
            const QString correctPassword = passwordQuery->value(0).toString();
            const bool userIsActive = passwordQuery->value(1).toBool();
            if(!userIsActive) {
                qDebug("Login denied: user not active");
                return UserIsInactive;
            }
            if (correctPassword == PasswordHasher::computeHash(password, correctPassword.left(16))) {
                qDebug("Login accepted: password right");
                return PasswordRight;
            } else {
                qDebug("Login denied: password wrong");
                return NotLoggedIn;
            }
        } else {
            qDebug("Login accepted: unknown user");
            return UnknownUser;
        }
    }
    }
    return UnknownUser;
}
bool Servatrice_DatabaseInterface::userExists(const QString &user)
{
	if (server->getAuthenticationMethod() == Servatrice::AuthenticationSql) {
		checkSql();
	
		QSqlQuery query(sqlDatabase);
		query.prepare("select 1 from " + server->getDbPrefix() + "_users where name = :name and active = 1");
		query.bindValue(":name", user);
		if (!execSqlQuery(query))
			return false;
		return query.next();
	}
	return false;
}
int Servatrice_DatabaseInterface::getNextGameId()
{
	if (!sqlDatabase.isValid())
		return server->getNextLocalGameId();
	
	if (!checkSql())
		return -1;
	
	QSqlQuery query(sqlDatabase);
	query.prepare("insert into " + server->getDbPrefix() + "_games (time_started) values (now())");
	execSqlQuery(query);
	
	return query.lastInsertId().toInt();
}
Esempio n. 22
0
bool Servatrice_DatabaseInterface::changeUserPassword(const QString &user, const QString &oldPassword, const QString &newPassword)
{
    if(server->getAuthenticationMethod() != Servatrice::AuthenticationSql)
        return true;

    if (!checkSql())
        return true;

    QString error;
    if (!usernameIsValid(user, error))
        return true;

    QSqlQuery *passwordQuery = prepareQuery("select password_sha512 from {prefix}_users where name = :name");
    passwordQuery->bindValue(":name", user);
    if (!execSqlQuery(passwordQuery)) {
        qDebug("Change password denied: SQL error");
        return true;
    }

    if (!passwordQuery->next())
        return true;

    const QString correctPassword = passwordQuery->value(0).toString();
    if (correctPassword != PasswordHasher::computeHash(oldPassword, correctPassword.left(16)))
        return true;

    QString passwordSha512 = PasswordHasher::computeHash(newPassword, PasswordHasher::generateRandomSalt());

    passwordQuery = prepareQuery("update {prefix}_users set password_sha512=:password where name = :name");
    passwordQuery->bindValue(":password", passwordSha512);
    passwordQuery->bindValue(":name", user);
    if (!execSqlQuery(passwordQuery)) {
        qDebug("Change password denied: SQL error");
        return true;
    }
    return false;
}
Esempio n. 23
0
bool Servatrice::userExists(const QString &user)
{
	QMutexLocker locker(&dbMutex);
	const QString method = settings->value("authentication/method").toString();
	if (method == "sql") {
		checkSql();
	
		QSqlQuery query;
		query.prepare("select 1 from " + dbPrefix + "_users where name = :name and active = 1");
		query.bindValue(":name", user);
		if (!execSqlQuery(query))
			return false;
		return query.next();
	} else return false;
}
Esempio n. 24
0
DeckList *Servatrice_DatabaseInterface::getDeckFromDatabase(int deckId, int userId)
{
    checkSql();

    QSqlQuery *query = prepareQuery("select content from {prefix}_decklist_files where id = :id and id_user = :id_user");
    query->bindValue(":id", deckId);
    query->bindValue(":id_user", userId);
    execSqlQuery(query);
    if (!query->next())
        throw Response::RespNameNotFound;

    DeckList *deck = new DeckList;
    deck->loadFromString_Native(query->value(0).toString());

    return deck;
}
qint64 Servatrice_DatabaseInterface::startSession(const QString &userName, const QString &address)
{
	if (server->getAuthenticationMethod() == Servatrice::AuthenticationNone)
		return -1;
	
	if (!checkSql())
		return -1;
	
	QSqlQuery query(sqlDatabase);
	query.prepare("insert into " + server->getDbPrefix() + "_sessions (user_name, id_server, ip_address, start_time) values(:user_name, :id_server, :ip_address, NOW())");
	query.bindValue(":user_name", userName);
	query.bindValue(":id_server", server->getServerId());
	query.bindValue(":ip_address", address);
	if (execSqlQuery(query))
		return query.lastInsertId().toInt();
	return -1;
}
Esempio n. 26
0
bool Servatrice_DatabaseInterface::isInIgnoreList(const QString &whoseList, const QString &who)
{
    if (server->getAuthenticationMethod() == Servatrice::AuthenticationNone)
        return false;

    if (!checkSql())
        return false;

    int id1 = getUserIdInDB(whoseList);
    int id2 = getUserIdInDB(who);

    QSqlQuery *query = prepareQuery("select 1 from {prefix}_ignorelist where id_user1 = :id_user1 and id_user2 = :id_user2");
    query->bindValue(":id_user1", id1);
    query->bindValue(":id_user2", id2);
    if (!execSqlQuery(query))
        return false;
    return query->next();
}
Esempio n. 27
0
qint64 Servatrice_DatabaseInterface::startSession(const QString &userName, const QString &address, const QString &clientId, const QString & connectionType)
{
    if (server->getAuthenticationMethod() == Servatrice::AuthenticationNone)
        return -1;

    if (!checkSql())
        return -1;

    QSqlQuery *query = prepareQuery("insert into {prefix}_sessions (user_name, id_server, ip_address, start_time, clientid, connection_type) values(:user_name, :id_server, :ip_address, NOW(), :client_id, :connection_type)");
    query->bindValue(":user_name", userName);
    query->bindValue(":id_server", server->getServerID());
    query->bindValue(":ip_address", address);
    query->bindValue(":client_id", clientId);
    query->bindValue(":connection_type", connectionType);
    if (execSqlQuery(query))
        return query->lastInsertId().toInt();
    return -1;
}
Esempio n. 28
0
QMap<QString, ServerInfo_User> Servatrice_DatabaseInterface::getIgnoreList(const QString &name)
{
    QMap<QString, ServerInfo_User> result;

    if (server->getAuthenticationMethod() == Servatrice::AuthenticationSql) {
        checkSql();

        QSqlQuery *query = prepareQuery("select a.id, a.name, a.admin, a.country, a.privlevel from {prefix}_users a left join {prefix}_ignorelist b on a.id = b.id_user2 left join {prefix}_users c on b.id_user1 = c.id where c.name = :name");
        query->bindValue(":name", name);
        if (!execSqlQuery(query))
            return result;

        while (query->next()) {
            ServerInfo_User temp = evalUserQueryResult(query, false);
            result.insert(QString::fromStdString(temp.name()), temp);
        }
    }
    return result;
}
bool Servatrice_DatabaseInterface::isInBuddyList(const QString &whoseList, const QString &who)
{
	if (server->getAuthenticationMethod() == Servatrice::AuthenticationNone)
		return false;
	
	if (!checkSql())
		return false;
	
	int id1 = getUserIdInDB(whoseList);
	int id2 = getUserIdInDB(who);
	
	QSqlQuery query(sqlDatabase);
	query.prepare("select 1 from " + server->getDbPrefix() + "_buddylist where id_user1 = :id_user1 and id_user2 = :id_user2");
	query.bindValue(":id_user1", id1);
	query.bindValue(":id_user2", id2);
	if (!execSqlQuery(query))
		return false;
	return query.next();
}
Esempio n. 30
0
bool Servatrice_DatabaseInterface::addWarning(const QString userName, const QString adminName, const QString warningReason, const QString clientID)
{
    if (!checkSql())
        return false;

    int userID = getUserIdInDB(userName);
    QSqlQuery *query = prepareQuery("insert into {prefix}_warnings (user_id,user_name,mod_name,reason,time_of,clientid) values (:user_id,:user_name,:mod_name,:warn_reason,NOW(),:client_id)");
    query->bindValue(":user_id", userID);
    query->bindValue(":user_name", userName);
    query->bindValue(":mod_name", adminName);
    query->bindValue(":warn_reason", warningReason);
    query->bindValue(":client_id", clientID);
    if (!execSqlQuery(query)) {
        qDebug("Failed to collect create warning history information: SQL Error");
        return false;
    }

    return true;
}