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;
}
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;
}
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;
}
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;
}
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;
}
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);
}
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();
}
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);
}
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();
}
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();
}
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);
}
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();
}
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);
}
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;
}
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();
}
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;
}
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;
}
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();
}
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;
}
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;
}
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;
}
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();
}
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;
}
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();
}
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;
}