bool Database::SendMail(std::string recipient, std::string from, std::string subject, std::string body, std::string recipientsString) {
int characterID;
std::string characterName;
auto lastPeriod = recipient.find_last_of(".");
if(lastPeriod == std::string::npos)
characterName = recipient;
else
characterName = recipient.substr(lastPeriod+1);
characterName[0] = toupper(characterName[0]);
for(unsigned int i = 1; i < characterName.length(); i++)
characterName[i] = tolower(characterName[i]);
characterID = FindCharacter(characterName.c_str());
_log(UCS__TRACE, "SendMail: CharacterID for recipient %s is %i", characterName.c_str(), characterID);
if(characterID <= 0)
return false;
char *escSubject = new char[subject.length() * 2 + 1];
char *escBody = new char[body.length() * 2 + 1];
DoEscapeString(escSubject, subject.c_str(), subject.length());
DoEscapeString(escBody, body.c_str(), body.length());
int now = time(nullptr); // time returns a 64 bit int on Windows at least, which vsnprintf doesn't like.
std::string query = StringFormat("INSERT INTO `mail` "
"(`charid`, `timestamp`, `from`, `subject`, `body`, `to`, `status`) "
"VALUES ('%i', %i, '%s', '%s', '%s', '%s', %i)",
characterID, now, from.c_str(), escSubject, escBody,
recipientsString.c_str(), 1);
safe_delete_array(escSubject);
safe_delete_array(escBody);
auto results = QueryDatabase(query);
if(!results.Success()) {
_log(UCS__ERROR, "SendMail: Query %s failed with error %s", query.c_str(), results.ErrorMessage().c_str());
return false;
}
_log(UCS__TRACE, "MessageID %i generated, from %s, to %s", results.LastInsertedID(), from.c_str(), recipient.c_str());
Client *client = CL->IsCharacterOnline(characterName);
if(client) {
std::string FQN = GetMailPrefix() + from;
client->SendNotification(client->GetMailBoxNumber(characterName), subject, FQN, results.LastInsertedID());
}
MailMessagesSent++;
return true;
}
bool Database::CreateWorldRegistration(std::string long_name, std::string short_name, unsigned int &id)
{
char escaped_long_name[201];
char escaped_short_name[101];
DoEscapeString(escaped_long_name, long_name.substr(0, 100).c_str(), (int)strlen(long_name.substr(0, 100).c_str()));
DoEscapeString(escaped_short_name, short_name.substr(0, 100).c_str(), (int)strlen(short_name.substr(0, 100).c_str()));
std::string query;
query = StringFormat("SELECT max(ServerID) FROM %s", LoadServerSettings("schema", "world_registration_table").c_str());
auto results = QueryDatabase(query);
if (!results.Success())
{
server_log->Log(log_database_error, "Mysql query failed: %s", query.c_str());
return false;
}
auto row = results.begin();
if (row[0] != nullptr)
{
id = atoi(row[0]) + 1;
}
else
{
id = 0;
}
query = StringFormat("INSERT INTO %s SET "
"ServerID = '%i', "
"ServerLongName = '%s', "
"ServerShortName = '%s', "
"ServerListTypeID = 0, "
"ServerAdminID = 0, "
"ServerTrusted = 0, "
"ServerTagDescription = ''",
LoadServerSettings("schema", "world_registration_table").c_str(),
id,
escaped_long_name,
escaped_short_name
);
auto results2 = QueryDatabase(query);
if (!results2.Success())
{
server_log->Log(log_database_error, "Mysql query failed: %s", query.c_str());
return false;
}
return true;
}
void Database::UpdateAccessLog(unsigned int account_id, std::string account_name, std::string IP, unsigned int accessed, std::string reason)
{
std::string query;
char tmpUN[1024];
DoEscapeString(tmpUN, account_name.c_str(), (int)account_name.length());
query = StringFormat("INSERT INTO %s SET "
"account_id = %s, "
"account_name = '%s', "
"IP = '%s', "
"accessed = '%s', "
"reason = '%s'",
LoadServerSettings("schema", "access_log_table").c_str(),
std::to_string(account_id).c_str(),
tmpUN,
IP.c_str(),
std::to_string(accessed).c_str(),
reason.c_str()
);
auto results = QueryDatabase(query);
if (!results.Success())
{
server_log->Log(log_database_error, "Mysql query failed: %s", query.c_str());
}
}
void Database::UpdateWorldRegistration(unsigned int id, std::string long_name, std::string ip_address)
{
char escaped_long_name[101];
DoEscapeString(escaped_long_name, long_name.substr(0, 100).c_str(), (int)strlen(long_name.substr(0, 100).c_str()));
std::string query;
query = StringFormat("UPDATE %s "
"SET "
"ServerLastLoginDate = now(), "
"ServerLastIPAddr = '%s', "
"ServerLongName = '%s' "
"WHERE "
"ServerID = '%s'",
LoadServerSettings("schema", "world_registration_table").c_str(),
ip_address.c_str(),
escaped_long_name,
std::to_string(id).c_str()
);
auto results = QueryDatabase(query);
if (!results.Success())
{
server_log->Log(log_database_error, "Mysql query failed: %s", query.c_str());
}
}
// Update information about existing object in database
void ZoneDatabase::UpdateObject(uint32 id, uint32 type, uint32 icon, const Object_Struct& object, const ItemInst* inst)
{
uint32 item_id = 0;
int16 charges = 0;
if (inst && inst->GetItem()) {
item_id = inst->GetItem()->ID;
charges = inst->GetCharges();
}
// SQL Escape object_name
uint32 len = strlen(object.object_name) * 2 + 1;
char* object_name = new char[len];
DoEscapeString(object_name, object.object_name, strlen(object.object_name));
// Save new record for object
std::string query = StringFormat("UPDATE object SET "
"zoneid = %i, xpos = %f, ypos = %f, zpos = %f, heading = %f, "
"itemid = %i, charges = %i, objectname = '%s', type = %i, icon = %i "
"WHERE id = %i",
object.zone_id, object.x, object.y, object.z, object.heading,
item_id, charges, object_name, type, icon, id);
safe_delete_array(object_name);
auto results = QueryDatabase(query);
if (!results.Success()) {
LogFile->write(EQEMuLog::Error, "Unable to update object: %s", results.ErrorMessage().c_str());
return;
}
// Save container contents, if container
if (inst && inst->IsType(ItemClassContainer))
SaveWorldContainer(object.zone_id, id, inst);
}
void ZoneDatabase::InsertPetitionToDB(Petition* wpet)
{
uint32 len = strlen(wpet->GetPetitionText());
char* petitiontext = new char[2*len+1];
memset(petitiontext, 0, 2*len+1);
DoEscapeString(petitiontext, wpet->GetPetitionText(), len);
std::string query = StringFormat("INSERT INTO petitions "
"(petid, charname, accountname, lastgm, "
"petitiontext, zone, urgency, charclass, "
"charrace, charlevel, checkouts, unavailables, "
"ischeckedout, senttime, gmtext) "
"VALUES (%i, '%s', '%s', '%s', '%s', "
"%i, %i, %i, %i, %i, "
"%i, %i, %i, %i, '%s')",
wpet->GetID(), wpet->GetCharName(), wpet->GetAccountName(), wpet->GetLastGM(),
petitiontext, wpet->GetZone(), wpet->GetUrgency(), wpet->GetCharClass(),
wpet->GetCharRace(), wpet->GetCharLevel(), wpet->GetCheckouts(), wpet->GetUnavails(),
wpet->CheckedOut()? 1: 0, wpet->GetSentTime(), wpet->GetGMText());
safe_delete_array(petitiontext);
auto results = QueryDatabase(query);
if (!results.Success()) {
LogFile->write(EQEMuLog::Error, "Error in InsertPetitionToDB query '%s': %s", query.c_str(), results.ErrorMessage().c_str());
return;
}
#if EQDEBUG >= 5
LogFile->write(EQEMuLog::Debug, "New petition created");
#endif
}
void Database::AddSpeech(const char* from, const char* to, const char* message, uint16 minstatus, uint32 guilddbid, uint8 type) {
char errbuf[MYSQL_ERRMSG_SIZE];
char* query = 0;
char *S1 = new char[strlen(from) * 2 + 1];
char *S2 = new char[strlen(to) * 2 + 1];
char *S3 = new char[strlen(message) * 2 + 1];
DoEscapeString(S1, from, strlen(from));
DoEscapeString(S2, to, strlen(to));
DoEscapeString(S3, message, strlen(message));
if(!RunQuery(query, MakeAnyLenString(&query, "INSERT INTO `qs_player_speech` SET `from`='%s', `to`='%s', `message`='%s', `minstatus`='%i', `guilddbid`='%i', `type`='%i'", S1, S2, S3, minstatus, guilddbid, type), errbuf, 0, 0)) {
_log(NET__WORLD, "Failed Speech Entry Insert: %s", errbuf);
_log(NET__WORLD, "%s", query);
}
safe_delete_array(query);
safe_delete_array(S1);
safe_delete_array(S2);
safe_delete_array(S3);
}
void Database::AddSpeech(const char* from, const char* to, const char* message, uint16 minstatus, uint32 guilddbid, uint8 type) {
char *escapedFrom = new char[strlen(from) * 2 + 1];
char *escapedTo = new char[strlen(to) * 2 + 1];
char *escapedMessage = new char[strlen(message) * 2 + 1];
DoEscapeString(escapedFrom, from, strlen(from));
DoEscapeString(escapedTo, to, strlen(to));
DoEscapeString(escapedMessage, message, strlen(message));
std::string query = StringFormat("INSERT INTO `qs_player_speech` "
"SET `from` = '%s', `to` = '%s', `message`='%s', "
"`minstatus`='%i', `guilddbid`='%i', `type`='%i'",
escapedFrom, escapedTo, escapedMessage, minstatus, guilddbid, type);
safe_delete_array(escapedFrom);
safe_delete_array(escapedTo);
safe_delete_array(escapedMessage);
auto results = QueryDatabase(query);
if(!results.Success()) {
Log.Out(Logs::Detail, Logs::QS_Server, "Failed Speech Entry Insert: %s", results.ErrorMessage().c_str());
Log.Out(Logs::Detail, Logs::QS_Server, "%s", query.c_str());
}
}
// Add new Zone Object (theoretically only called for items dropped to ground)
uint32 ZoneDatabase::AddObject(uint32 type, uint32 icon, const Object_Struct& object, const ItemInst* inst)
{
char errbuf[MYSQL_ERRMSG_SIZE];
char* query = 0;
uint32 database_id = 0;
uint32 item_id = 0;
int16 charges = 0;
if (inst && inst->GetItem()) {
item_id = inst->GetItem()->ID;
charges = inst->GetCharges();
}
// SQL Escape object_name
uint32 len = strlen(object.object_name) * 2 + 1;
char* object_name = new char[len];
DoEscapeString(object_name, object.object_name, strlen(object.object_name));
// Construct query
uint32 len_query = MakeAnyLenString(&query,
"insert into object (zoneid, xpos, ypos, zpos, heading, itemid, charges, objectname, "
"type, icon) values (%i, %f, %f, %f, %f, %i, %i, '%s', %i, %i)",
object.zone_id, object.x, object.y, object.z, object.heading,
item_id, charges, object_name, type, icon);
// Save new record for object
if (!RunQuery(query, len_query, errbuf, nullptr, nullptr, &database_id)) {
LogFile->write(EQEMuLog::Error, "Unable to insert object: %s", errbuf);
}
else {
// Save container contents, if container
if (inst && inst->IsType(ItemClassContainer)) {
SaveWorldContainer(object.zone_id, database_id, inst);
}
}
safe_delete_array(object_name);
safe_delete_array(query);
return database_id;
}
// Update information about existing object in database
void ZoneDatabase::UpdateObject(uint32 id, uint32 type, uint32 icon, const Object_Struct& object, const ItemInst* inst)
{
char errbuf[MYSQL_ERRMSG_SIZE];
char* query = 0;
uint32 item_id = 0;
int16 charges = 0;
if (inst && inst->GetItem()) {
item_id = inst->GetItem()->ID;
charges = inst->GetCharges();
}
// SQL Escape object_name
uint32 len = strlen(object.object_name) * 2 + 1;
char* object_name = new char[len];
DoEscapeString(object_name, object.object_name, strlen(object.object_name));
// Construct query
uint32 len_query = MakeAnyLenString(&query,
"update object set zoneid=%i, xpos=%f, ypos=%f, zpos=%f, heading=%f, "
"itemid=%i, charges=%i, objectname='%s', type=%i, icon=%i where id=%i",
object.zone_id, object.x, object.y, object.z, object.heading,
item_id, charges, object_name, type, icon, id);
// Save new record for object
if (!RunQuery(query, len_query, errbuf)) {
LogFile->write(EQEMuLog::Error, "Unable to update object: %s", errbuf);
}
else {
// Save container contents, if container
if (inst && inst->IsType(ItemClassContainer)) {
SaveWorldContainer(object.zone_id, id, inst);
}
}
safe_delete_array(object_name);
safe_delete_array(query);
}
bool Database::GetLoginDataFromAccountName(std::string name, std::string &password, unsigned int &id)
{
std::string query;
char tmpUN[1024];
DoEscapeString(tmpUN, name.c_str(), (int)name.length()); (tmpUN, name.c_str(), (int)name.length());
query = StringFormat("SELECT LoginServerID, AccountPassword "
"FROM %s WHERE "
"AccountName = '%s'",
LoadServerSettings("schema", "account_table").c_str(),
tmpUN
);
auto results = QueryDatabase(query);
if (!results.Success())
{
server_log->Log(log_database_error, "Mysql query failed: %s", query.c_str());
return false;
}
if (results.RowCount() > 0)
{
auto row = results.begin();
if (row[0] != nullptr)
{
id = atoi(row[0]);
password = row[1];
return true;
}
server_log->Log(log_database_error, "Unknown error, no result for: %s", query.c_str());
return false;
}
server_log->Log(log_database_error, "Mysql query returned no result for: %s", query.c_str());
return false;
}
void Database::CreateLSAccount(std::string name, std::string password, std::string email, unsigned int created_by, std::string LastIPAddress, std::string creationIP)
{
bool activate = 0;
if (LoadServerSettings("options", "auto_account_activate") == "TRUE")
{
activate = 1;
}
std::string query;
char tmpUN[1024];
DoEscapeString(tmpUN, name.c_str(), (int)name.length());
query = StringFormat("INSERT INTO %s "
"SET AccountName = '%s', "
"AccountPassword = sha('%s'), "
"AccountCreateDate = now(), "
"LastLoginDate = now(), "
"LastIPAddress = '%s', "
"client_unlock = '%s', "
"created_by = '%s', "
"creationIP = '%s'",
LoadServerSettings("schema", "account_table").c_str(),
tmpUN,
password.c_str(),
LastIPAddress.c_str(),
std::to_string(activate).c_str(),
std::to_string(created_by).c_str(),
creationIP.c_str()
);
auto results = QueryDatabase(query);
if (!results.Success())
{
server_log->Log(log_database_error, "Mysql query failed: %s", query.c_str());
}
}
// Add new Zone Object (theoretically only called for items dropped to ground)
uint32 ZoneDatabase::AddObject(uint32 type, uint32 icon, const Object_Struct& object, const ItemInst* inst)
{
uint32 database_id = 0;
uint32 item_id = 0;
int16 charges = 0;
if (inst && inst->GetItem()) {
item_id = inst->GetItem()->ID;
charges = inst->GetCharges();
}
// SQL Escape object_name
uint32 len = strlen(object.object_name) * 2 + 1;
char* object_name = new char[len];
DoEscapeString(object_name, object.object_name, strlen(object.object_name));
// Save new record for object
std::string query = StringFormat("INSERT INTO object "
"(zoneid, xpos, ypos, zpos, heading, "
"itemid, charges, objectname, type, icon) "
"values (%i, %f, %f, %f, %f, %i, %i, '%s', %i, %i)",
object.zone_id, object.x, object.y, object.z, object.heading,
item_id, charges, object_name, type, icon);
safe_delete_array(object_name);
auto results = QueryDatabase(query);
if (!results.Success()) {
LogFile->write(EQEMuLog::Error, "Unable to insert object: %s", results.ErrorMessage().c_str());
return 0;
}
// Save container contents, if container
if (inst && inst->IsType(ItemClassContainer))
SaveWorldContainer(object.zone_id, database_id, inst);
return database_id;
}
bool Database::GetWorldRegistration(unsigned int &id, std::string &desc, unsigned int &trusted, unsigned int &list_id,
std::string &account, std::string &password, std::string long_name, std::string short_name)
{
char escaped_short_name[101];
DoEscapeString(escaped_short_name, short_name.substr(0, 100).c_str(), (int)strlen(short_name.substr(0, 100).c_str()));
std::string query;
query = StringFormat("SELECT "
"ServerID, ServerTagDescription, ServerTrusted, ServerListTypeID, ServerAdminID "
"FROM %s WHERE ServerShortName = '%s'",
LoadServerSettings("schema", "world_registration_table").c_str(),
escaped_short_name
);
auto results = QueryDatabase(query);
if (!results.Success())
{
server_log->Log(log_database_error, "Mysql query failed: %s", query.c_str());
return false;
}
auto row = results.begin();
if (results.RowCount() > 0)
{
id = atoi(row[0]);
desc = row[1];
trusted = atoi(row[2]);
list_id = atoi(row[3]);
int db_account_id = atoi(row[5]);
if (db_account_id > 0)
{
std::string query;
query = StringFormat("SELECT AccountName, AccountPassword "
"FROM %s WHERE ServerAdminID = %s",
LoadServerSettings("schema", "world_admin_registration_table").c_str(),
std::to_string(db_account_id).c_str()
);
auto results = QueryDatabase(query);
if (!results.Success())
{
server_log->Log(log_database_error, "Mysql query failed: %s", query.c_str());
return false;
}
auto row = results.begin();
if (results.RowCount() > 0)
{
account = row[0];
password = row[1];
return true;
}
server_log->Log(log_database_trace, "Mysql query returned no result: %s", query.c_str());
return false;
}
return true;
}
server_log->Log(log_database_error, "Mysql query returned no result: %s", query.c_str());
return false;
}
bool Database::SendMail(std::string Recipient, std::string From, std::string Subject, std::string Body, std::string RecipientsString) {
int CharacterID;
std::string CharacterName;
//printf("Database::SendMail(%s, %s, %s)\n", Recipient.c_str(), From.c_str(), Subject.c_str());
std::string::size_type LastPeriod = Recipient.find_last_of(".");
if(LastPeriod == std::string::npos)
CharacterName = Recipient;
else
CharacterName = Recipient.substr(LastPeriod+1);
CharacterName[0] = toupper(CharacterName[0]);
for(unsigned int i = 1; i < CharacterName.length(); i++)
CharacterName[i] = tolower(CharacterName[i]);
CharacterID = FindCharacter(CharacterName.c_str());
_log(UCS__TRACE, "SendMail: CharacterID for recipient %s is %i", CharacterName.c_str(), CharacterID);
if(CharacterID <= 0) return false;
char errbuf[MYSQL_ERRMSG_SIZE];
char* query = 0;
char *EscSubject = new char[Subject.length() * 2 + 1];
char *EscBody = new char[Body.length() * 2 + 1];
DoEscapeString(EscSubject, Subject.c_str(), Subject.length());
DoEscapeString(EscBody, Body.c_str(), Body.length());
const char *MailQuery="INSERT INTO `mail` (`charid`, `timestamp`, `from`, `subject`, `body`, `to`, `status`) "
"VALUES ('%i', %i, '%s', '%s', '%s', '%s', %i)";
uint32 LastMsgID;
int Now = time(nullptr); // time returns a 64 bit int on Windows at least, which vsnprintf doesn't like.
if(!RunQuery(query, MakeAnyLenString(&query, MailQuery, CharacterID, Now, From.c_str(), EscSubject, EscBody,
RecipientsString.c_str(), 1), errbuf, 0, 0, &LastMsgID)) {
_log(UCS__ERROR, "SendMail: Query %s failed with error %s", query, errbuf);
safe_delete_array(EscSubject);
safe_delete_array(EscBody);
safe_delete_array(query);
return false;
}
_log(UCS__TRACE, "MessageID %i generated, from %s, to %s", LastMsgID, From.c_str(), Recipient.c_str());
safe_delete_array(EscSubject);
safe_delete_array(EscBody);
safe_delete_array(query);
Client *c = CL->IsCharacterOnline(CharacterName);
if(c) {
std::string FQN = GetMailPrefix() + From;
c->SendNotification(c->GetMailBoxNumber(CharacterName), Subject, FQN, LastMsgID);
}
MailMessagesSent++;
return true;
}
