int dbcontroller::setChallengeSolved(int id) {
int team = cur_ctx->session().get<int>("teamid");
std::shared_ptr<QSqlQuery> stmt(new QSqlQuery(this->db));
bool ok = doQuery(stmt, [&](std::shared_ptr<QSqlQuery> stmt) {
if (!stmt->prepare("call SolvedChallenge(:team,:idchallenge,@points);")) return false;
stmt->bindValue("team", team);
stmt->bindValue("idchallenge", id);
return true;
});
if (!ok) {
BOOSTER_ERROR("scoreboard") << "SolvedChallenge() failed, aborting";
return 0;
}
stmt->exec("select @points as points from dual;");
std::stringstream trigger;
trigger.str("");
trigger << "teamsolved_" << team;
cur_ctx->cache().rise(trigger.str());
if ((stmt->size()) == 1) {
stmt->next();
return stmt->record().value("points").toInt();
} else {
BOOSTER_ERROR("scoreboard") << "Something went very wrong. SP SolvedChallenge returned " << stmt->size() << " records. Team " << team << " just solved challenge " << id;
return 0;
}
}
static void thread_function(io::io_service *io)
{
bool stop=false;
try{
while(!stop) {
try {
io->run();
stop=true;
}
catch(cppcms::cppcms_error const &e) {
// Not much to do...
// Object will be destroyed automatically
// Because it does not resubmit itself
BOOSTER_ERROR("cache_server") <<"CppCMS Error "<<e.what();
}
}
}
catch(std::exception const &e)
{
BOOSTER_ERROR("cache_server") << "Fatal" << e.what();
}
catch(...){
BOOSTER_ERROR("cache_server") << "Unknown exception" << std::endl;
}
}
void dbcontroller::logIP(int teamid) {
std::stringstream ss;
ss.str("");
ss << "teamip_" << teamid;
teamip ti;
std::string IP = cur_ctx->request().remote_addr();
BOOSTER_NOTICE("scoreboard") << "team " << teamid << " has IP " << IP;
if (cur_ctx->cache().fetch_data(ss.str(), ti)) {
for (std::vector<std::string>::iterator it = ti.seenip.begin(); it != ti.seenip.end(); ++it) {
if (IP.compare(*it) == 0) {
//already known IP for this team
BOOSTER_DEBUG("scoreboard") << "found in cache.";
return;
}
}
}
//Still not known. :(
std::shared_ptr<QSqlQuery> stmt(new QSqlQuery(this->db));
bool ok = doQuery(stmt, [&](std::shared_ptr<QSqlQuery> stmt) {
if (!stmt->prepare("INSERT IGNORE INTO ipteams set idteam=:team, ip=:ip;")) return false;
stmt->bindValue("team", teamid);
stmt->bindValue("ip", QString::fromStdString(IP));
return true;
});
if (!ok) {
BOOSTER_ERROR("scoreboard") << "Could not add IP to teams IPs, aborting";
return;
}
ok = doQuery(stmt, [&](std::shared_ptr<QSqlQuery> stmt) {
if (!stmt->prepare("SELECT ip FROM ipteams WHERE idteam=:team;")) return false;
stmt->bindValue("team", teamid);
return true;
});
if (!ok) {
BOOSTER_ERROR("scoreboard") << "Could not fetch team IP, aborting";
return;
}
if (!ok) return;
ti.id = teamid;
ti.seenip.clear();
while (stmt->next()) {
ti.seenip.push_back(stmt->record().value(0).toString().toStdString());
BOOSTER_DEBUG("scoreboard") << "Pushing in cache for team " << teamid << " value " << stmt->record().value(0).toString().toStdString();
}
cur_ctx->cache().store_data(ss.str(), ti, -1);
return;
}
// static
void context::dispatch(booster::intrusive_ptr<application> app,std::string url,bool syncronous)
{
try {
if(syncronous)
app->context().session().load();
app->main(url);
}
catch(std::exception const &e){
BOOSTER_ERROR("cppcms") << "Caught exception ["<<e.what()<<"]\n" << booster::trace(e) ;
if(app->get_context()) {
if(!app->response().some_output_was_written()) {
if(app->service().cached_settings().security.display_error_message) {
std::ostringstream ss;
ss << e.what() << '\n';
ss << booster::trace(e);
app->response().make_error_response(http::response::internal_server_error,ss.str());
}
else
app->response().make_error_response(http::response::internal_server_error);
}
}
}
if(app->get_context()) {
if(syncronous) {
app->context().complete_response();
}
else {
app->context().async_complete_response();
}
}
}
teamInfo dbcontroller::fetchTeamInfo(int teamId) {
std::stringstream cacheKey;
std::stringstream trigger;
teamInfo teamInfo;
cacheKey.str("");
cacheKey << "teaminfo_" << teamId;
trigger.str("");
trigger << "teamsolved_" << teamId;
std::shared_ptr<QSqlQuery> sqlQuery(new QSqlQuery(this->db));
bool ok = doQuery(sqlQuery, [&](std::shared_ptr<QSqlQuery> stmt) {
if (!stmt->prepare("SELECT id, name, points from team where id=:id")) return false;
stmt->bindValue("id", teamId);
return true;
});
if (!ok) {
BOOSTER_ERROR("scoreboard") << "Could not fetch info for team, aborting";
return teamInfo;
}
if (sqlQuery->size() != 1) {
//WTF?
return teamInfo;
}
sqlQuery->next();
teamInfo.name = sqlQuery->record().value("name").toString().toStdString();
teamInfo.points = sqlQuery->record().value("points").toUInt();
cur_ctx->cache().store_data(
cacheKey.str(), teamInfo, this->makeStringSet(trigger.str()),
srv->settings().get<int>("cache.timeout.teaminfo")
);
return teamInfo;
}
challengesol dbcontroller::fetchSolution(std::string const& flag) {
std::string cachekey = "challenge_sol_" + flag;
challengesol data;
if (cur_ctx->cache().fetch_data(cachekey, data)) {
return data;
}
data.valid = false;
std::shared_ptr<QSqlQuery> stmt(new QSqlQuery(this->db));
bool ok = doQuery(stmt, [&](std::shared_ptr<QSqlQuery> stmt) {
if (!stmt->prepare("SELECT idchallenge, points from challenges where flaghash=sha2(:flag, 512) and opentime < NOW()")) return false;
stmt->bindValue("flag", flag.c_str());
return true;
});
if (!ok) {
BOOSTER_ERROR("scoreboard") << "fetch solution failed, aborting";
return data;
}
if ((stmt->size()) == 1) {
stmt->next();
data.valid = true;
data.id = stmt->record().value("idchallenge").toInt();
data.points = stmt->record().value("points").toInt();
}
cur_ctx->cache().store_data(
cachekey, data,
srv->settings().get<int>("cache.timeout.challengesol"));
return data;
}
/**
* @brief Handle a SQL error if possible
* @param err error object to be handled
* @return true if the error was successfully handled, false if not
*/
bool dbcontroller::handleError(QSqlError err) {
switch (err.number()) {
case CR_SERVER_GONE_ERROR:
case CR_SERVER_LOST:
db.close();
db.open();
BOOSTER_INFO("scoreboard") << "Reopening connection to database.";
return true;
case -1:
BOOSTER_ERROR("scoreboard") << "Unknown DB error";
return false;
default:
BOOSTER_ERROR("scoreboard") << "Error from database: " << err.number() << " - " << err.text().toStdString();
return false;
}
}
bool dbcontroller::doQuery(std::shared_ptr<QSqlQuery> stmt, std::function<bool(std::shared_ptr<QSqlQuery>)> prepare, int maxretries) {
bool prepared = false;
for (int retry = 0; retry < maxretries; ++retry) {
if (prepare(stmt)) {
prepared = true;
break;
}
if (!handleError(stmt->lastError())) break; // Could not handle error, bail out
}
if (!prepared) {
BOOSTER_ERROR("scoreboard") << "Could not prepare query for max retries, returning error";
return false;
}
for (int retry = 0; retry < maxretries; ++retry) {
if (stmt->exec()) return true; // Success!
if (!handleError(stmt->lastError())) return false; // Could not handle error, bail out
}
BOOSTER_ERROR("scoreboard") << "Query failed for max retries, returning error";
return false;
}
int dbcontroller::login(std::string tname, std::string password) {
// FIXME - Removed caching layer for login() as interferred with the
// password reset functionality and with having multiple load balanced instances.
// We assume that there are "few" logins from each user\team so having every
// request hit the database is OK --- and caching w.r.t. the team name\password does
// not solve any intentional DOS problem.
std::shared_ptr<QSqlQuery> stmt(new QSqlQuery(this->db));
bool ok = doQuery(stmt, [&](std::shared_ptr<QSqlQuery> stmt) {
if (!stmt->prepare("CALL verify_login(:nome,:psw, @idt, @isactive);")) return false;
stmt->bindValue(":nome", QString::fromStdString(tname));
stmt->bindValue(":psw", QString::fromStdString(password));
return true;
});
if (!ok) {
BOOSTER_ERROR("scoreboard") << "verify_login() failed, aborting";
throw dbException("DB error");
}
stmt->exec("SELECT @idt is not null as success, @idt, @isactive;");
if(!stmt->isActive()) {
throw dbException("DB error");
}
if ((stmt->size()) != 1) {
return false;
}
stmt->next();
int success = stmt->record().value(0).toBool();
int idt = stmt->record().value(1).toInt();
int isactive = stmt->record().value(2).toBool();
if(!success) {
return false;
}
if(!isactive) {
// team registered, password OK, but not confirmed
throw loginException("Team e-mail not yet confirmed. Did you click on the link in the confirmation e-mail?");
}
return idt;
}
// static
void context::dispatch(booster::intrusive_ptr<application> app,std::string url,bool syncronous)
{
try {
if(syncronous)
app->context().session().load();
app->main(url);
}
catch(std::exception const &e){
if(app->get_context() && !app->response().some_output_was_written()) {
app->response().make_error_response(http::response::internal_server_error,e.what());
}
else {
BOOSTER_ERROR("cppcms") << "Catched excepion ["<<e.what()<<"]";
}
}
}
teamSolutionList dbcontroller::fetchSolutionsForTeam(int teamId) {
std::stringstream cacheKey;
std::stringstream trigger;
teamSolutionList solutions;
cacheKey.str("");
trigger.str("");
cacheKey << "challenges_" << teamId;
trigger << "teamsolved_" << teamId;
std::shared_ptr<QSqlQuery> sqlQuery(new QSqlQuery(this->db));
bool ok = doQuery(sqlQuery, [&](std::shared_ptr<QSqlQuery> stmt) {
if (!stmt->prepare("SELECT solutions.idchallenge as idchallenge,bonus,points,is_flash from solutions inner join challenges on (challenges.idchallenge=solutions.idchallenge) where idteam=:id")) return false;
stmt->bindValue("id", teamId);
return true;
});
if (!ok) {
BOOSTER_ERROR("scoreboard") << "Could not fetch solutions for team, aborting";
return {};
}
if (sqlQuery->size() == 0) {
//LOL empty store, but let's not bother db until it's necessary
cur_ctx->cache().store_data(
cacheKey.str(), solutions,
srv->settings().get<int>("cache.timeout.teamsolutions")
);
return solutions;
}
while (sqlQuery->next()) {
teamSolution solution;
solution.bonus = sqlQuery->record().value("bonus").toInt();
solution.points = sqlQuery->record().value("points").toInt();
solution.id = sqlQuery->record().value("idchallenge").toInt();
solution.flashChallenge = sqlQuery->record().value("is_flash").toInt();
solutions.challenges.push_back(solution);
}
cur_ctx->cache().store_data(
cacheKey.str(), solutions, makeStringSet(trigger.str()),
srv->settings().get<int>("cache.timeout.teamsolutions")
);
return solutions;
}
teamAlertList dbcontroller::fetchAlertsForTeam(int teamId) {
std::stringstream cacheKey;
teamAlertList alerts;
cacheKey.str("");
cacheKey << "alerts_" << teamId;
std::shared_ptr<QSqlQuery> sqlQuery(new QSqlQuery(this->db));
bool ok = doQuery(sqlQuery, [&](std::shared_ptr<QSqlQuery> stmt) {
if (!stmt->prepare("SELECT text,UNIX_TIMESTAMP(timestamp) as timestamp,points from alerts where recipient=:id")) return false;
stmt->bindValue("id", teamId);
return true;
});
if (!ok) {
BOOSTER_ERROR("scoreboard") << "Could not fetch alert for team, aborting";
return {};
}
if (sqlQuery->size() == 0) {
//LOL empty store, but let's not bother db until it's necessary
cur_ctx->cache().store_data(
cacheKey.str(), alerts,
srv->settings().get<int>("cache.timeout.teamalerts")
);
// Returning empty alerts for team
return alerts;
}
while (sqlQuery->next()) {
teamAlert alert;
alert.message = sqlQuery->record().value("text").toString().toStdString();
alert.timestamp = sqlQuery->record().value("timestamp").toInt();
alert.points = sqlQuery->record().value("points").toInt();
alerts.alerts.push_back(alert);
}
cur_ctx->cache().store_data(
cacheKey.str(), alerts,
srv->settings().get<int>("cache.timeout.teamalerts")
);
return alerts;
}
