LoginDatabase.cpp 43 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928
  1. /*
  2. EQ2Emulator: Everquest II Server Emulator
  3. Copyright (C) 2007 EQ2EMulator Development Team (http://www.eq2emulator.net)
  4. This file is part of EQ2Emulator.
  5. */
  6. #include "../common/debug.h"
  7. #include <iostream>
  8. using namespace std;
  9. #ifdef WIN32
  10. #define WIN32_LEAN_AND_MEAN
  11. #include <windows.h>
  12. #define snprintf _snprintf
  13. #define strncasecmp _strnicmp
  14. #define strcasecmp _stricmp
  15. #else
  16. #include "../common/unix.h"
  17. #include <netinet/in.h>
  18. #endif
  19. #include "../common/Log.h"
  20. #include "LoginDatabase.h"
  21. #include "LoginAccount.h"
  22. #include "../common/MiscFunctions.h"
  23. #include "../common/packet_functions.h"
  24. #include "../common/packet_dump.h"
  25. #include "LWorld.h"
  26. extern LoginDatabase database;
  27. extern LWorldList world_list;
  28. void LoginDatabase::SetZoneInformation(int32 server_id, int32 zone_id, int32 version, PacketStruct* packet){
  29. if(packet){
  30. Query query;
  31. MYSQL_RES* result = 0;
  32. if ( version >= 1212 )
  33. result = query.RunQuery2(Q_SELECT, "SELECT name, description from ls_world_zones where server_id=%i and zone_id=%i", server_id, zone_id);
  34. MYSQL_ROW row;
  35. if(result && (row = mysql_fetch_row(result))) {
  36. if (row[0])
  37. packet->setMediumStringByName("zone", row[0]);
  38. else
  39. packet->setMediumStringByName("zone", " ");
  40. if(row[1])
  41. packet->setMediumStringByName("zonedesc", row[1]);
  42. else
  43. packet->setMediumStringByName("zonedesc", " ");
  44. }
  45. else{
  46. Query query2;
  47. MYSQL_RES* result2 = 0;
  48. if (version < 1212)
  49. result2 = query2.RunQuery2(Q_SELECT, "SELECT file, description from zones where id=%i", zone_id);
  50. else
  51. result2 = query2.RunQuery2(Q_SELECT, "SELECT name, description from zones where id=%i", zone_id);
  52. MYSQL_ROW row2;
  53. if(result2 && (row2 = mysql_fetch_row(result2))) {
  54. if (version != 546 && version < 1212)
  55. {
  56. if (row2[0])
  57. {
  58. int len = strlen(row2[0]);
  59. char* zoneName = new char[len + 2];
  60. strncpy(zoneName, row2[0], len);
  61. zoneName[len] = 0x2E;
  62. zoneName[len + 1] = 0x30;
  63. packet->setMediumStringByName("zone", zoneName);
  64. safe_delete_array(zoneName);
  65. }
  66. else
  67. packet->setMediumStringByName("zone", ".0");
  68. }
  69. else
  70. {
  71. if (row2[0])
  72. packet->setMediumStringByName("zone", row2[0]);
  73. else
  74. packet->setMediumStringByName("zone", " ");
  75. }
  76. if(row2[1])
  77. packet->setMediumStringByName("zonedesc", row2[1]);
  78. else
  79. packet->setMediumStringByName("zonedesc", " ");
  80. }
  81. }
  82. packet->setMediumStringByName("zonename2"," ");
  83. }
  84. }
  85. string LoginDatabase::GetZoneDescription(char* name){
  86. string ret;
  87. Query query;
  88. query.escaped_name = getEscapeString(name);
  89. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT description from zones where file=substring_index('%s', '.', 1)", query.escaped_name);
  90. MYSQL_ROW row;
  91. if((row = mysql_fetch_row(result))) {
  92. ret = string(row[0]);
  93. }
  94. return ret;
  95. }
  96. void LoginDatabase::SetServerZoneDescriptions(int32 server_id, map<int32, LoginZoneUpdate> zone_descriptions){
  97. if(zone_descriptions.size() > 0){
  98. map<int32, LoginZoneUpdate>::iterator zone_itr;
  99. string query_string = "replace into ls_world_zones (server_id, zone_id, name, description) values";
  100. int count=0;
  101. char server_id_str[12] = {0};
  102. sprintf(server_id_str, "%i", server_id);
  103. for(zone_itr = zone_descriptions.begin(); zone_itr != zone_descriptions.end(); zone_itr++, count++){
  104. char zone_id_str[12] = {0};
  105. sprintf(zone_id_str, "%i", zone_itr->first);
  106. if(count > 0)
  107. query_string.append(", ");
  108. query_string.append("(").append(server_id_str).append(",");
  109. query_string.append(zone_id_str).append(",");
  110. query_string.append("'").append(getSafeEscapeString(zone_itr->second.name.c_str()).c_str()).append("', '");
  111. query_string.append(getSafeEscapeString(zone_itr->second.description.c_str()).c_str()).append("')");
  112. }
  113. Query query;
  114. query.RunQuery2(query_string, Q_REPLACE);
  115. }
  116. }
  117. //this is really just for the version that doesn't send the server id in its play request
  118. int32 LoginDatabase::GetServer(int32 accountID, int32 charID, string name) {
  119. int32 id = 0;
  120. Query query;
  121. MYSQL_ROW row;
  122. query.escaped_name = getEscapeString(name.c_str());
  123. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT server_id from login_characters where account_id=%i and char_id=%i and name='%s'", accountID, charID, query.escaped_name);
  124. if (result && mysql_num_rows(result) == 1) {
  125. row = mysql_fetch_row(result);
  126. id = atoi(row[0]);
  127. }
  128. return id;
  129. }
  130. void LoginDatabase::LoadCharacters(LoginAccount* acct, int16 version){
  131. if(acct != NULL)
  132. acct->flushCharacters ( );
  133. Query query;
  134. Query query2;
  135. int32 id = 0;
  136. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT lc.char_id, lc.server_id, lc.name, lc.race, lc.class, lc.gender, lc.deity, lc.body_size, lc.body_age, lc.current_zone_id, lc.level, lc.soga_wing_type, lc.soga_chest_type, lc.soga_legs_type, lc.soga_hair_type, lc.legs_type, lc.chest_type, lc.wing_type, lc.hair_type, unix_timestamp(lc.created_date), unix_timestamp(lc.last_played), lc.id, lw.name, lc.facial_hair_type, lc.soga_facial_hair_type, lc.soga_model_type, lc.model_type from login_characters lc, login_worldservers lw where lw.id = lc.server_id and lc.account_id=%i and lc.deleted=0",acct->getLoginAccountID());
  137. if(result) {
  138. MYSQL_ROW row;
  139. MYSQL_ROW row2;
  140. MYSQL_ROW row3;
  141. while ((row = mysql_fetch_row(result))) {
  142. CharSelectProfile* player = new CharSelectProfile(version);
  143. id = atoul(row[0]);
  144. //for (int i = 0; i < 10; i++)
  145. // player->packet->setDataByName("hair_type", 0, i);
  146. //player->packet->setDataByName("test23", 413);
  147. //player->packet->setDataByName("test24", 414);
  148. player->packet->setDataByName("charid", id);
  149. player->packet->setDataByName("server_id", atoul(row[1]));
  150. player->packet->setMediumStringByName("name", row[2]);
  151. player->packet->setDataByName("race", atoi(row[3]));
  152. player->packet->setDataByName("class", atoi(row[4]));
  153. player->packet->setDataByName("gender", atoi(row[5]));
  154. player->packet->setDataByName("deity", atoi(row[6]));
  155. player->packet->setDataByName("body_size", atof(row[7]));
  156. player->packet->setDataByName("body_age", atof(row[8]));
  157. SetZoneInformation(atoi(row[1]), atoi(row[9]), version, player->packet);
  158. player->packet->setDataByName("level", atoi(row[10]));
  159. if(atoi(row[11]) > 0)
  160. player->packet->setDataByName("soga_wing_type", atoi(row[11]));
  161. else
  162. player->packet->setDataByName("soga_wing_type", atoi(row[17]));
  163. if(atoi(row[12]) > 0)
  164. player->packet->setDataByName("soga_chest_type", atoi(row[12]));
  165. else
  166. player->packet->setDataByName("soga_chest_type", atoi(row[16]));
  167. if(atoi(row[13]) > 0)
  168. player->packet->setDataByName("soga_legs_type", atoi(row[13]));
  169. else
  170. player->packet->setDataByName("soga_legs_type", atoi(row[15]));
  171. if(atoi(row[14]) > 0)
  172. player->packet->setDataByName("soga_hair_type", atoi(row[14]));
  173. else
  174. player->packet->setDataByName("soga_hair_type", atoi(row[18]));
  175. player->packet->setDataByName("legs_type", atoi(row[15]));
  176. player->packet->setDataByName("chest_type", atoi(row[16]));
  177. player->packet->setDataByName("wing_type", atoi(row[17]));
  178. player->packet->setDataByName("hair_type", atoi(row[18]));
  179. player->packet->setDataByName("created_date", atol(row[19]));
  180. if (row[20])
  181. player->packet->setDataByName("last_played", atol(row[20]));
  182. if(version == 546)
  183. player->packet->setDataByName("version", 11);
  184. else if(version >= 887)
  185. player->packet->setDataByName("version", 6);
  186. else
  187. player->packet->setDataByName("version", 5);
  188. player->packet->setDataByName("account_id", acct->getLoginAccountID());
  189. player->packet->setDataByName("account_id2", acct->getLoginAccountID());
  190. LoadAppearanceData(atol(row[21]), player->packet);
  191. if(row[22])
  192. player->packet->setMediumStringByName("server_name", row[22]);
  193. player->packet->setDataByName("hair_face_type", atoi(row[23]));
  194. if(atoi(row[24]) > 0)
  195. player->packet->setDataByName("soga_hair_face_type", atoi(row[24]));
  196. else
  197. player->packet->setDataByName("soga_hair_face_type", atoi(row[23]));
  198. if(atoi(row[25]) > 0)
  199. player->packet->setDataByName("soga_race_type", atoi(row[25]));
  200. else
  201. player->packet->setDataByName("soga_race_type", atoi(row[26]));
  202. player->packet->setDataByName("race_type", atoi(row[26]));
  203. player->packet->setDataByName("unknown3", 57);
  204. player->packet->setDataByName("unknown4", 56);
  205. player->packet->setDataByName("unknown6", 1, 1); //if not here will not display character
  206. player->packet->setDataByName("unknown8", 15);
  207. player->packet->setDataByName("unknown13", 212);
  208. player->packet->setColorByName("unknown14", 0xFF, 0xFF, 0xFF);
  209. uchar tmp[] = {0xFF, 0xFF, 0xFF, 0x61, 0x00, 0x2C, 0x04, 0xA5, 0x09, 0x02, 0x0F, 0x00, 0x00};
  210. for(size_t y=0;y<sizeof(tmp);y++)
  211. player->packet->setDataByName("unknown11", tmp[y], y);
  212. MYSQL_RES* result3 = query2.RunQuery2(Q_SELECT, "SELECT slot, equip_type, red, green, blue, highlight_red, highlight_green, highlight_blue from login_equipment where login_characters_id=%i order by slot",id);
  213. if(result3){
  214. for(int i=0;(row3 = mysql_fetch_row(result3)) && i<24; i++){
  215. player->packet->setEquipmentByName("equip", atoi(row3[1]), atoi(row3[2]), atoi(row3[3]), atoi(row3[4]), atoi(row3[5]), atoi(row3[6]), atoi(row3[7]), atoi(row3[0]));
  216. }
  217. }
  218. player->packet->setDataByName("mount", 1377);
  219. player->packet->setDataByName("mount_color1", 57);
  220. /*
  221. enum NetAppearance::NetAppearanceFlags
  222. {
  223. NAF_INVISIBLE=1,
  224. NAF_SHOW_HOOD=2
  225. };
  226. */
  227. acct->addCharacter(player);
  228. }
  229. }
  230. else
  231. LogWrite(LOGIN__ERROR, 0, "Login", "Error in LoadCharacters query '%s': %s", query.GetQuery(), query.GetError());
  232. }
  233. void LoginDatabase::CheckCharacterTimeStamps(LoginAccount* acct){
  234. Query query;
  235. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT char_id, unix_timestamp from login_characters where account_id=%i",acct->getLoginAccountID());
  236. if(result && mysql_num_rows(result) > 0) {
  237. MYSQL_ROW row;
  238. ServerPacket* outpack = new ServerPacket(ServerOP_CharTimeStamp, sizeof(CharacterTimeStamp_Struct));
  239. CharacterTimeStamp_Struct* cts = (CharacterTimeStamp_Struct*) outpack->pBuffer;
  240. cts->account_id = acct->getLoginAccountID();
  241. int32 server_id = 0;
  242. LWorld* world_server = 0;
  243. while ((row = mysql_fetch_row(result))) {
  244. server_id = atoi(row[1]);
  245. if(server_id != 0)
  246. world_server = world_list.FindByAccount(server_id, World);
  247. if(world_server) // If the pointer is 0, the world server must be down, we can't do any updates...
  248. {
  249. cts->char_id = atoi(row[0]);
  250. cts->unix_timestamp = atoi(row[1]);
  251. world_server->SendPacket(outpack);
  252. //Reset for next character
  253. world_server = 0;
  254. server_id = 0;
  255. }
  256. }
  257. safe_delete(outpack);
  258. }
  259. }
  260. void LoginDatabase::SaveCharacterFloats(int32 char_id, char* type, float float1, float float2, float float3){
  261. Query query;
  262. string create_char = string("insert into login_char_colors (login_characters_id, type, red, green, blue, signed_value) values(%i,'%s',%i,%i,%i, 1)");
  263. query.RunQuery2(Q_INSERT, create_char.c_str(), char_id, type, (sint8)(float1*100), (sint8)(float2*100), (sint8)(float3*100));
  264. }
  265. void LoginDatabase::SaveCharacterColors(int32 char_id, char* type, EQ2_Color color){
  266. Query query;
  267. string create_char = string("insert into login_char_colors (login_characters_id, type, red, green, blue) values(%i,'%s',%i,%i,%i)");
  268. query.RunQuery2(Q_INSERT, create_char.c_str(), char_id, type, color.red, color.green, color.blue);
  269. }
  270. void LoginDatabase::LoadAppearanceData(int32 char_id, PacketStruct* char_select_packet){
  271. Query query;
  272. MYSQL_ROW row;
  273. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT type, signed_value, red, green, blue from login_char_colors where login_characters_id = %i",char_id);
  274. while((row = mysql_fetch_row(result))){
  275. if(atoi(row[1]) == 0)
  276. char_select_packet->setColorByName(row[0], atoi(row[2]), atoi(row[3]), atoi(row[4]));
  277. else{
  278. if (char_select_packet->GetVersion() <= 283)
  279. {
  280. char_select_packet->setDataByName(row[0], atoi(row[2]) * 2.5, 0);
  281. char_select_packet->setDataByName(row[0], atoi(row[3]) * 2.5, 1);
  282. char_select_packet->setDataByName(row[0], atoi(row[4]) * 2.5, 2);
  283. }
  284. else
  285. {
  286. char_select_packet->setDataByName(row[0], atoi(row[2]), 0);
  287. char_select_packet->setDataByName(row[0], atoi(row[3]), 1);
  288. char_select_packet->setDataByName(row[0], atoi(row[4]), 2);
  289. }
  290. }
  291. }
  292. }
  293. int16 LoginDatabase::GetAppearanceID(string name){
  294. int32 id = 0;
  295. Query query;
  296. MYSQL_ROW row;
  297. query.escaped_name = getEscapeString(name.c_str());
  298. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT appearance_id from appearances where name='%s'", query.escaped_name);
  299. if(result && mysql_num_rows(result) == 1){
  300. row = mysql_fetch_row(result);
  301. id = atoi(row[0]);
  302. }
  303. return id;
  304. }
  305. void LoginDatabase::DeactivateCharID(int32 server_id, int32 char_id, int32 exception_id){
  306. Query query;
  307. query.RunQuery2(Q_UPDATE, "update login_characters set deleted=1 where char_id=%u and server_id=%u and id!=%u",char_id,server_id,exception_id);
  308. }
  309. int32 LoginDatabase::SaveCharacter(PacketStruct* create, LoginAccount* acct, int32 world_charid, int32 client_version){
  310. int32 ret_id = 0;
  311. Query query;
  312. string create_char =
  313. string("Insert into login_characters (account_id, server_id, char_id, name, race, class, gender, deity, body_size, body_age, soga_wing_type, soga_chest_type, soga_legs_type, soga_hair_type, soga_facial_hair_type, legs_type, chest_type, wing_type, hair_type, facial_hair_type, soga_model_type, model_type)"
  314. " values(%i, %i, %i, '%s', %i, %i, %i, %i, %f, %f, %i, %i, %i, %i, %i, %i, %i, %i, %i, %i, %i, %i)");
  315. query.RunQuery2(Q_INSERT, create_char.c_str(),
  316. acct->getLoginAccountID(),
  317. create->getType_int32_ByName("server_id"), world_charid,
  318. create->getType_EQ2_16BitString_ByName("name").data.c_str(),
  319. create->getType_int8_ByName("race"),
  320. create->getType_int8_ByName("class"),
  321. create->getType_int8_ByName("gender"),
  322. create->getType_int8_ByName("deity"),
  323. create->getType_float_ByName("body_size"),
  324. create->getType_float_ByName("body_age"),
  325. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_wing_file").data),
  326. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_chest_file").data),
  327. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_legs_file").data),
  328. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_hair_file").data),
  329. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_face_file").data),
  330. GetAppearanceID(create->getType_EQ2_16BitString_ByName("legs_file").data),
  331. GetAppearanceID(create->getType_EQ2_16BitString_ByName("chest_file").data),
  332. GetAppearanceID(create->getType_EQ2_16BitString_ByName("wing_file").data),
  333. GetAppearanceID(create->getType_EQ2_16BitString_ByName("hair_file").data),
  334. GetAppearanceID(create->getType_EQ2_16BitString_ByName("face_file").data),
  335. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_race_file").data),
  336. GetAppearanceID(create->getType_EQ2_16BitString_ByName("race_file").data));
  337. if(query.GetError() && strlen(query.GetError()) > 0){
  338. LogWrite(LOGIN__ERROR, 0, "Login", "Error in SaveCharacter query '%s': %s", query.GetQuery(), query.GetError());
  339. return 0;
  340. }
  341. int32 last_insert_id = query.GetLastInsertedID();
  342. //mark any remaining characters with same id as deleted (creates problems if world deleted their db and started assigning new char ids)
  343. DeactivateCharID(create->getType_int32_ByName("server_id"), world_charid, last_insert_id);
  344. int32 char_id = last_insert_id;
  345. if (client_version <= 546) {
  346. SaveCharacterFloats(char_id, "skin_color", create->getType_float_ByName("skin_color", 0), create->getType_float_ByName("skin_color", 1), create->getType_float_ByName("skin_color", 2));
  347. SaveCharacterFloats(char_id, "eye_color", create->getType_float_ByName("eye_color", 0), create->getType_float_ByName("eye_color", 1), create->getType_float_ByName("eye_color", 2));
  348. SaveCharacterFloats(char_id, "hair_color1", create->getType_float_ByName("hair_color1", 0), create->getType_float_ByName("hair_color1", 1), create->getType_float_ByName("hair_color1", 2));
  349. SaveCharacterFloats(char_id, "hair_color2", create->getType_float_ByName("hair_color2", 0), create->getType_float_ByName("hair_color2", 1), create->getType_float_ByName("hair_color2", 2));
  350. SaveCharacterFloats(char_id, "hair_highlight", create->getType_float_ByName("hair_highlight", 0), create->getType_float_ByName("hair_highlight", 1), create->getType_float_ByName("hair_highlight", 2));
  351. SaveCharacterFloats(char_id, "hair_type_color", create->getType_float_ByName("hair_type_color", 0), create->getType_float_ByName("hair_type_color", 1), create->getType_float_ByName("hair_type_color", 2));
  352. SaveCharacterFloats(char_id, "hair_type_highlight_color", create->getType_float_ByName("hair_type_highlight_color", 0), create->getType_float_ByName("hair_type_highlight_color", 1), create->getType_float_ByName("hair_type_highlight_color", 2));
  353. SaveCharacterFloats(char_id, "hair_type_color", create->getType_float_ByName("hair_type_color", 0), create->getType_float_ByName("hair_type_color", 1), create->getType_float_ByName("hair_type_color", 2));
  354. SaveCharacterFloats(char_id, "hair_type_highlight_color", create->getType_float_ByName("hair_type_highlight_color", 0), create->getType_float_ByName("hair_type_highlight_color", 1), create->getType_float_ByName("hair_type_highlight_color", 2));
  355. SaveCharacterFloats(char_id, "hair_face_color", create->getType_float_ByName("hair_face_color", 0), create->getType_float_ByName("hair_face_color", 1), create->getType_float_ByName("hair_face_color", 2));
  356. SaveCharacterFloats(char_id, "hair_face_highlight_color", create->getType_float_ByName("hair_face_highlight_color", 0), create->getType_float_ByName("hair_face_highlight_color", 1), create->getType_float_ByName("hair_face_highlight_color", 2));
  357. SaveCharacterFloats(char_id, "shirt_color", create->getType_float_ByName("shirt_color", 0), create->getType_float_ByName("shirt_color", 1), create->getType_float_ByName("shirt_color", 2));
  358. SaveCharacterFloats(char_id, "unknown_chest_color", create->getType_float_ByName("unknown_chest_color", 0), create->getType_float_ByName("unknown_chest_color", 1), create->getType_float_ByName("unknown_chest_color", 2));
  359. SaveCharacterFloats(char_id, "pants_color", create->getType_float_ByName("pants_color", 0), create->getType_float_ByName("pants_color", 1), create->getType_float_ByName("pants_color", 2));
  360. SaveCharacterFloats(char_id, "unknown_legs_color", create->getType_float_ByName("unknown_legs_color", 0), create->getType_float_ByName("unknown_legs_color", 1), create->getType_float_ByName("unknown_legs_color", 2));
  361. SaveCharacterFloats(char_id, "unknown9", create->getType_float_ByName("unknown9", 0), create->getType_float_ByName("unknown9", 1), create->getType_float_ByName("unknown9", 2));
  362. }
  363. else {
  364. SaveCharacterColors(char_id, "skin_color", create->getType_EQ2_Color_ByName("skin_color"));
  365. SaveCharacterColors(char_id, "model_color", create->getType_EQ2_Color_ByName("model_color"));
  366. SaveCharacterColors(char_id, "eye_color", create->getType_EQ2_Color_ByName("eye_color"));
  367. SaveCharacterColors(char_id, "hair_color1", create->getType_EQ2_Color_ByName("hair_color1"));
  368. SaveCharacterColors(char_id, "hair_color2", create->getType_EQ2_Color_ByName("hair_color2"));
  369. SaveCharacterColors(char_id, "hair_highlight", create->getType_EQ2_Color_ByName("hair_highlight"));
  370. SaveCharacterColors(char_id, "hair_type_color", create->getType_EQ2_Color_ByName("hair_type_color"));
  371. SaveCharacterColors(char_id, "hair_type_highlight_color", create->getType_EQ2_Color_ByName("hair_type_highlight_color"));
  372. SaveCharacterColors(char_id, "hair_face_color", create->getType_EQ2_Color_ByName("hair_face_color"));
  373. SaveCharacterColors(char_id, "hair_face_highlight_color", create->getType_EQ2_Color_ByName("hair_face_highlight_color"));
  374. SaveCharacterColors(char_id, "wing_color1", create->getType_EQ2_Color_ByName("wing_color1"));
  375. SaveCharacterColors(char_id, "wing_color2", create->getType_EQ2_Color_ByName("wing_color2"));
  376. SaveCharacterColors(char_id, "shirt_color", create->getType_EQ2_Color_ByName("shirt_color"));
  377. SaveCharacterColors(char_id, "unknown_chest_color", create->getType_EQ2_Color_ByName("unknown_chest_color"));
  378. SaveCharacterColors(char_id, "pants_color", create->getType_EQ2_Color_ByName("pants_color"));
  379. SaveCharacterColors(char_id, "unknown_legs_color", create->getType_EQ2_Color_ByName("unknown_legs_color"));
  380. SaveCharacterColors(char_id, "unknown9", create->getType_EQ2_Color_ByName("unknown9"));
  381. SaveCharacterColors(char_id, "soga_skin_color", create->getType_EQ2_Color_ByName("soga_skin_color"));
  382. SaveCharacterColors(char_id, "soga_model_color", create->getType_EQ2_Color_ByName("soga_model_color"));
  383. SaveCharacterColors(char_id, "soga_eye_color", create->getType_EQ2_Color_ByName("soga_eye_color"));
  384. SaveCharacterColors(char_id, "soga_hair_color1", create->getType_EQ2_Color_ByName("soga_hair_color1"));
  385. SaveCharacterColors(char_id, "soga_hair_color2", create->getType_EQ2_Color_ByName("soga_hair_color2"));
  386. SaveCharacterColors(char_id, "soga_hair_highlight", create->getType_EQ2_Color_ByName("soga_hair_highlight"));
  387. SaveCharacterColors(char_id, "soga_hair_type_color", create->getType_EQ2_Color_ByName("soga_hair_type_color"));
  388. SaveCharacterColors(char_id, "soga_hair_type_highlight_color", create->getType_EQ2_Color_ByName("soga_hair_type_highlight_color"));
  389. SaveCharacterColors(char_id, "soga_hair_face_color", create->getType_EQ2_Color_ByName("soga_hair_face_color"));
  390. SaveCharacterColors(char_id, "soga_hair_face_highlight_color", create->getType_EQ2_Color_ByName("soga_hair_face_highlight_color"));
  391. SaveCharacterColors(char_id, "soga_wing_color1", create->getType_EQ2_Color_ByName("soga_wing_color1"));
  392. SaveCharacterColors(char_id, "soga_wing_color2", create->getType_EQ2_Color_ByName("soga_wing_color2"));
  393. SaveCharacterColors(char_id, "soga_shirt_color", create->getType_EQ2_Color_ByName("soga_shirt_color"));
  394. SaveCharacterColors(char_id, "soga_unknown_chest_color", create->getType_EQ2_Color_ByName("soga_unknown_chest_color"));
  395. SaveCharacterColors(char_id, "soga_pants_color", create->getType_EQ2_Color_ByName("soga_pants_color"));
  396. SaveCharacterColors(char_id, "soga_unknown_legs_color", create->getType_EQ2_Color_ByName("soga_unknown_legs_color"));
  397. SaveCharacterColors(char_id, "soga_unknown13", create->getType_EQ2_Color_ByName("soga_unknown13"));
  398. SaveCharacterFloats(char_id, "soga_eye_type", create->getType_float_ByName("soga_eyes2", 0), create->getType_float_ByName("soga_eyes2", 1), create->getType_float_ByName("soga_eyes2", 2));
  399. SaveCharacterFloats(char_id, "soga_ear_type", create->getType_float_ByName("soga_ears", 0), create->getType_float_ByName("soga_ears", 1), create->getType_float_ByName("soga_ears", 2));
  400. SaveCharacterFloats(char_id, "soga_eye_brow_type", create->getType_float_ByName("soga_eye_brows", 0), create->getType_float_ByName("soga_eye_brows", 1), create->getType_float_ByName("soga_eye_brows", 2));
  401. SaveCharacterFloats(char_id, "soga_cheek_type", create->getType_float_ByName("soga_cheeks", 0), create->getType_float_ByName("soga_cheeks", 1), create->getType_float_ByName("soga_cheeks", 2));
  402. SaveCharacterFloats(char_id, "soga_lip_type", create->getType_float_ByName("soga_lips", 0), create->getType_float_ByName("soga_lips", 1), create->getType_float_ByName("soga_lips", 2));
  403. SaveCharacterFloats(char_id, "soga_chin_type", create->getType_float_ByName("soga_chin", 0), create->getType_float_ByName("soga_chin", 1), create->getType_float_ByName("soga_chin", 2));
  404. SaveCharacterFloats(char_id, "soga_nose_type", create->getType_float_ByName("soga_nose", 0), create->getType_float_ByName("soga_nose", 1), create->getType_float_ByName("soga_nose", 2));
  405. }
  406. SaveCharacterFloats(char_id, "eye_type", create->getType_float_ByName("eyes2", 0), create->getType_float_ByName("eyes2", 1), create->getType_float_ByName("eyes2", 2));
  407. SaveCharacterFloats(char_id, "ear_type", create->getType_float_ByName("ears", 0), create->getType_float_ByName("ears", 1), create->getType_float_ByName("ears", 2));
  408. SaveCharacterFloats(char_id, "eye_brow_type", create->getType_float_ByName("eye_brows", 0), create->getType_float_ByName("eye_brows", 1), create->getType_float_ByName("eye_brows", 2));
  409. SaveCharacterFloats(char_id, "cheek_type", create->getType_float_ByName("cheeks", 0), create->getType_float_ByName("cheeks", 1), create->getType_float_ByName("cheeks", 2));
  410. SaveCharacterFloats(char_id, "lip_type", create->getType_float_ByName("lips", 0), create->getType_float_ByName("lips", 1), create->getType_float_ByName("lips", 2));
  411. SaveCharacterFloats(char_id, "chin_type", create->getType_float_ByName("chin", 0), create->getType_float_ByName("chin", 1), create->getType_float_ByName("chin", 2));
  412. SaveCharacterFloats(char_id, "nose_type", create->getType_float_ByName("nose", 0), create->getType_float_ByName("nose", 1), create->getType_float_ByName("nose", 2));
  413. SaveCharacterFloats(char_id, "body_size", create->getType_float_ByName("body_size", 0), 0, 0);
  414. return ret_id;
  415. }
  416. bool LoginDatabase::DeleteCharacter(int32 account_id, int32 character_id, int32 server_id){
  417. Query query;
  418. string delete_char = string("delete from login_characters where char_id=%i and account_id=%i and server_id=%i");
  419. query.RunQuery2(Q_DELETE, delete_char.c_str(),character_id,account_id,server_id);
  420. if(!query.GetAffectedRows())
  421. {
  422. //No error just in case ppl try doing stupid stuff
  423. return false;
  424. }
  425. return true;
  426. }
  427. string LoginDatabase::GetCharacterName(int32 char_id, int32 server_id){
  428. Query query;
  429. MYSQL_ROW row;
  430. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT name from login_characters where char_id=%lu and server_id=%lu and deleted = 0", char_id,server_id);
  431. if(result && mysql_num_rows(result) == 1){
  432. row = mysql_fetch_row(result);
  433. return string(row[0]);
  434. }
  435. return string("");
  436. }
  437. bool LoginDatabase::UpdateCharacterTimeStamp(int32 account_id, int32 character_id, int32 timestamp_update, int32 server_id){
  438. Query query;
  439. string update_charts = string("update login_characters set unix_timestamp=%lu where char_id=%lu and account_id=%lu and server_id=%lu");
  440. query.RunQuery2(Q_UPDATE, update_charts.c_str(),timestamp_update,character_id,account_id,server_id);
  441. if(!query.GetAffectedRows())
  442. {
  443. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterTimeStamp query '%s': %s", query.GetQuery(), query.GetError());
  444. return false;
  445. }
  446. return true;
  447. }
  448. bool LoginDatabase::UpdateCharacterLevel(int32 account_id, int32 character_id, int8 in_level, int32 server_id){
  449. Query query;
  450. string update_charts = string("update login_characters set level=%i where char_id=%lu and account_id=%lu and server_id=%lu");
  451. query.RunQuery2(Q_UPDATE, update_charts.c_str(),in_level,character_id,account_id,server_id);
  452. if(!query.GetAffectedRows())
  453. {
  454. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterLevel query '%s': %s", query.GetQuery(), query.GetError());
  455. return false;
  456. }
  457. return true;
  458. }
  459. bool LoginDatabase::UpdateCharacterRace(int32 account_id, int32 character_id, int16 in_racetype, int8 in_race, int32 server_id){
  460. Query query;
  461. string update_charts = string("update login_characters set race_type=%i, race=%i where char_id=%lu and account_id=%lu and server_id=%lu");
  462. query.RunQuery2(Q_UPDATE, update_charts.c_str(),in_racetype,in_race,character_id,account_id,server_id);
  463. if(!query.GetAffectedRows())
  464. {
  465. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterRace query '%s': %s", query.GetQuery(), query.GetError());
  466. return false;
  467. }
  468. return true;
  469. }
  470. bool LoginDatabase::UpdateCharacterZone(int32 account_id, int32 character_id, int32 zone_id, int32 server_id){
  471. Query query;
  472. string update_chars = string("update login_characters set current_zone_id=%i where char_id=%lu and account_id=%lu and server_id=%lu");
  473. query.RunQuery2(Q_UPDATE, update_chars.c_str(), zone_id, character_id, account_id, server_id);
  474. if(!query.GetAffectedRows())
  475. {
  476. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterZone query '%s': %s", query.GetQuery(), query.GetError());
  477. return false;
  478. }
  479. return true;
  480. }
  481. bool LoginDatabase::UpdateCharacterClass(int32 account_id, int32 character_id, int8 in_class, int32 server_id){
  482. Query query;
  483. string update_charts = string("update login_characters set class=%i where char_id=%lu and account_id=%lu and server_id=%lu");
  484. query.RunQuery2(Q_UPDATE, update_charts.c_str(),in_class,character_id,account_id,server_id);
  485. if(!query.GetAffectedRows())
  486. {
  487. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterClass query '%s': %s", query.GetQuery(), query.GetError());
  488. return false;
  489. }
  490. return true;
  491. }
  492. bool LoginDatabase::UpdateCharacterGender(int32 account_id, int32 character_id, int8 in_gender, int32 server_id){
  493. Query query;
  494. string update_charts = string("update login_characters set gender=%i where char_id=%lu and account_id=%lu and server_id=%lu");
  495. query.RunQuery2(Q_UPDATE, update_charts.c_str(),in_gender,character_id,account_id,server_id);
  496. if(!query.GetAffectedRows())
  497. {
  498. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterClass query '%s': %s", query.GetQuery(), query.GetError());
  499. return false;
  500. }
  501. return true;
  502. }
  503. LoginAccount* LoginDatabase::LoadAccount(const char* name, const char* password, bool attemptAccountCreation){
  504. LoginAccount* acct = NULL;
  505. Query query;
  506. query.escaped_name = getEscapeString(name);
  507. query.escaped_pass = getEscapeString(password);
  508. MYSQL_ROW row;
  509. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT id from account where name='%s' and passwd=sha2('%s',512)", query.escaped_name, query.escaped_pass);
  510. if(result){
  511. if (mysql_num_rows(result) == 1){
  512. row = mysql_fetch_row(result);
  513. int32 id = atol(row[0]);
  514. acct = new LoginAccount(id, name, password);
  515. acct->setAuthenticated(true);
  516. }
  517. else if(mysql_num_rows(result) > 0)
  518. LogWrite(LOGIN__ERROR, 0, "Login", "Error in LoginAccount: more than one account returned for '%s'", name);
  519. else if (attemptAccountCreation && !database.GetAccountIDByName(name))
  520. {
  521. Query newquery;
  522. newquery.RunQuery2(Q_INSERT, "insert into account set name='%s',passwd=sha2('%s',512)", query.escaped_name, query.escaped_pass);
  523. // re-run the query for select only not account creation
  524. return LoadAccount(name, password, false);
  525. }
  526. }
  527. return acct;
  528. }
  529. int32 LoginDatabase::GetAccountIDByName(const char* name) {
  530. int32 id = 0;
  531. Query query;
  532. MYSQL_ROW row;
  533. query.escaped_name = getEscapeString(name);
  534. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT id from account where name='%s'", query.escaped_name);
  535. if (result && mysql_num_rows(result) == 1) {
  536. row = mysql_fetch_row(result);
  537. id = atoi(row[0]);
  538. }
  539. return id;
  540. }
  541. int32 LoginDatabase::CheckServerAccount(char* name, char* passwd){
  542. int32 id = 0;
  543. Query query;
  544. MYSQL_ROW row;
  545. query.escaped_name = getEscapeString(name);
  546. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT lower(password), id from login_worldservers where account='%s' and disabled = 0", query.escaped_name);
  547. LogWrite(LOGIN__INFO, 0, "Login", "WorldServer CheckServerAccount Account=%s\nSHA=%s", (char*)query.escaped_name, passwd);
  548. if(result && mysql_num_rows(result) == 1){
  549. row = mysql_fetch_row(result);
  550. LogWrite(LOGIN__INFO, 0, "Login", "WorldServer CheckServerAccountResult Account=%s\nPassword=%s", (char*)query.escaped_name, (row && row[0]) ? row[0] : "(NULL)");
  551. if (memcmp(row[0], passwd, strnlen(row[0], 256)) == 0)
  552. {
  553. LogWrite(LOGIN__INFO, 0, "Login", "WorldServer CheckServerAccountResultMatch Account=%s", (char*)query.escaped_name);
  554. id = atoi(row[1]);
  555. }
  556. }
  557. return id;
  558. }
  559. bool LoginDatabase::IsServerAccountDisabled(char* name){
  560. Query query;
  561. MYSQL_ROW row;
  562. query.escaped_name = getEscapeString(name);
  563. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT id from login_worldservers where account='%s' and disabled = 1", query.escaped_name);
  564. LogWrite(LOGIN__INFO, 0, "Login", "WorldServer IsServerAccountDisabled Account=%s", (char*)query.escaped_name);
  565. if(result && mysql_num_rows(result) > 0){
  566. row = mysql_fetch_row(result);
  567. LogWrite(LOGIN__INFO, 0, "Login", "WorldServer IsServerAccountDisabled Match Account=%s", (char*)query.escaped_name);
  568. return true;
  569. }
  570. return false;
  571. }
  572. bool LoginDatabase::IsIPBanned(char* ipaddr){
  573. if(!ipaddr)
  574. return false;
  575. Query query;
  576. MYSQL_ROW row;
  577. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT ip from login_bannedips where '%s' LIKE CONCAT(ip ,'%%')", ipaddr);
  578. LogWrite(LOGIN__INFO, 0, "Login", "WorldServer IsServerIPBanned IPPartial=%s", (char*)ipaddr);
  579. if(result && mysql_num_rows(result) > 0){
  580. row = mysql_fetch_row(result);
  581. LogWrite(LOGIN__INFO, 0, "Login", "WorldServer IsServerIPBanned Match IPBan=%s", row[0]);
  582. return true;
  583. }
  584. return false;
  585. }
  586. void LoginDatabase::GetServerAccounts(vector<LWorld*>* server_list){
  587. Query query;
  588. MYSQL_ROW row;
  589. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT id, account, name, admin_id from login_worldservers");
  590. while((row = mysql_fetch_row(result))){
  591. LWorld* world = new LWorld(atol(row[0]), row[1], row[2], atoi(row[3]));
  592. world->SetID(world->GetAccountID());
  593. server_list->push_back(world);
  594. }
  595. }
  596. void LoginDatabase::SaveClientLog(const char* type, const char* message, const char* player_name, int16 version){
  597. Query query;
  598. query.escaped_data1 = getEscapeString(message);
  599. query.escaped_name = getEscapeString(player_name);
  600. query.RunQuery2(Q_INSERT, "insert into log_messages (type, message, name, version) values('%s', '%s', '%s', %i)", type, query.escaped_data1, query.escaped_name, version);
  601. }
  602. bool LoginDatabase::VerifyDelete(int32 account_id, int32 character_id, const char* name){
  603. Query query;
  604. query.escaped_name = getEscapeString(name);
  605. query.RunQuery2(Q_UPDATE, "update login_characters set deleted = 1 where char_id=%i and account_id=%i and name='%s'", character_id, account_id, query.escaped_name);
  606. if(query.GetAffectedRows() == 1)
  607. return true;
  608. else
  609. return false;
  610. }
  611. char* LoginDatabase::GetServerAccountName(int32 id){
  612. Query query;
  613. MYSQL_ROW row;
  614. char* name = 0;
  615. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT name from login_worldservers where id=%lu", id);
  616. if(result && mysql_num_rows(result) == 1){
  617. row = mysql_fetch_row(result);
  618. if(strlen(row[0]) > 0){
  619. name = new char[strlen(row[0])+1];
  620. strcpy(name, row[0]);
  621. }
  622. }
  623. return name;
  624. }
  625. int32 LoginDatabase::GetRaceID(char* name){
  626. int32 ret = 1487;
  627. Query query;
  628. MYSQL_ROW row;
  629. query.escaped_name = getEscapeString(name);
  630. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT race_type from login_races where name='%s'", query.escaped_name);
  631. if(result && mysql_num_rows(result) == 1){
  632. row = mysql_fetch_row(result);
  633. ret = atol(row[0]);
  634. }
  635. else if(!result || mysql_num_rows(result) == 0)
  636. UpdateRaceID(query.escaped_name);
  637. return ret;
  638. }
  639. void LoginDatabase::UpdateRaceID(char* name){
  640. Query query;
  641. query.RunQuery2(Q_UPDATE, "insert into login_races (name) values('%s')", name);
  642. }
  643. bool LoginDatabase::CheckVersion(char* in_version){
  644. Query query;
  645. query.escaped_data1 = getEscapeString(in_version);
  646. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT id from login_versions where version='%s' or version='*'", query.escaped_data1);
  647. if(result && mysql_num_rows(result) > 0)
  648. return true;
  649. else
  650. return false;
  651. }
  652. void LoginDatabase::GetLatestTableVersions(LatestTableVersions* table_versions){
  653. Query query;
  654. MYSQL_ROW row;
  655. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT name, max(version) from login_table_versions group by name order by id");
  656. if(result && mysql_num_rows(result) > 0){
  657. table_versions->SetTableSize(mysql_num_rows(result));
  658. }
  659. else // we need to return if theres no result, otherwise it will crash attempting to loop through rows
  660. return;
  661. while((row = mysql_fetch_row(result))){
  662. if(VerifyDataTable(row[0]))
  663. table_versions->AddTable(row[0], atoi(row[1]), GetDataVersion(row[0]));
  664. else
  665. table_versions->AddTable(row[0], atoi(row[1]), 0);
  666. }
  667. }
  668. bool LoginDatabase::VerifyDataTable(char* name){
  669. Query query;
  670. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT table_name from download_tables where table_name='%s'", name);
  671. if(result && mysql_num_rows(result) > 0)
  672. return true;
  673. return false;
  674. }
  675. string LoginDatabase::GetColumnNames(char* name){
  676. Query query;
  677. MYSQL_ROW row;
  678. string columns = "(";
  679. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "show columns from %s", name);
  680. if(result && mysql_num_rows(result) > 0){
  681. int16 i = 0;
  682. while((row = mysql_fetch_row(result))){
  683. if(strcmp(row[0], "table_data_version") != 0){
  684. if(i>0)
  685. columns.append(",");
  686. columns.append(row[0]);
  687. i++;
  688. }
  689. }
  690. }
  691. columns.append(") ");
  692. return columns;
  693. }
  694. TableDataQuery* LoginDatabase::GetTableDataQuery(int32 server_ip, char* name, int16 version){
  695. Query query;
  696. MYSQL_ROW row;
  697. query.escaped_name = getEscapeString(name);
  698. TableDataQuery* table_query = 0;
  699. MYSQL_RES* result = 0;
  700. string columns;
  701. if(VerifyDataTable(query.escaped_name)){
  702. result = query.RunQuery2(Q_SELECT, "SELECT * from %s where table_data_version > %i", query.escaped_name, version);
  703. columns = GetColumnNames(query.escaped_name);
  704. }
  705. if(result && mysql_num_rows(result) > 0){
  706. table_query = new TableDataQuery(query.escaped_name);
  707. table_query->num_queries = mysql_num_rows(result);
  708. table_query->columns_size = columns.length() + 1;
  709. table_query->columns = new char[table_query->columns_size + 1];
  710. table_query->version = GetDataVersion(query.escaped_name);
  711. strcpy(table_query->columns, (char*)columns.c_str());
  712. string query_data;
  713. MYSQL_FIELD* field;
  714. int* int_list = new int[mysql_num_fields(result)];
  715. int16 ndx = 0;
  716. while((field = mysql_fetch_field(result))){
  717. int_list[ndx] = IS_NUM(field->type);
  718. if(strcmp(field->name,"table_data_version") == 0)
  719. int_list[ndx] = 2;
  720. ndx++;
  721. }
  722. ndx = 0;
  723. while((row = mysql_fetch_row(result))){
  724. query_data = "";
  725. for(int i=0;i<mysql_num_fields(result);i++){
  726. if(int_list[i]<2){
  727. if(i>0)
  728. query_data.append(",");
  729. if(!int_list[i]){
  730. query_data.append("'").append(getEscapeString(row[i])).append("'");
  731. }
  732. else
  733. query_data.append(row[i]);
  734. }
  735. }
  736. TableData* new_query = new TableData;
  737. new_query->size = query_data.length() + 1;
  738. new_query->query = new char[query_data.length() + 1];
  739. strcpy(new_query->query, query_data.c_str());
  740. table_query->queries.push_back(new_query);
  741. ndx++;
  742. }
  743. safe_delete_array(int_list);
  744. }
  745. else{
  746. string query2 = string("The user tried to download the following table: ").append(query.escaped_name);
  747. SaveClientLog("Possible Hacking Attempt", (char*)query2.c_str(), "Hacking Data", server_ip);
  748. }
  749. return table_query;
  750. }
  751. TableQuery* LoginDatabase::GetLatestTableQuery(int32 server_ip, char* name, int16 version){
  752. Query query;
  753. MYSQL_ROW row;
  754. query.escaped_name = getEscapeString(name);
  755. TableQuery* table_query = 0;
  756. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT query, version from login_table_versions where name = '%s' and version>=%i order by version", query.escaped_name, version + 1);
  757. if(result && mysql_num_rows(result) > 0){
  758. int16 i = 0;
  759. table_query = new TableQuery;
  760. while((row = mysql_fetch_row(result))){
  761. char* rowdata = row[0];
  762. if(strstr(rowdata, ";")){
  763. char* token = strtok(rowdata,";");
  764. while(token){
  765. char* new_query = new char[strlen(token) + 1];
  766. strcpy(new_query, token);
  767. table_query->AddQuery(new_query);
  768. token = strtok(NULL, ";");
  769. }
  770. }
  771. else
  772. table_query->AddQuery(rowdata);
  773. table_query->latest_version = atoi(row[1]);
  774. }
  775. strcpy(table_query->tablename, name);
  776. table_query->your_version = version;
  777. }
  778. else{
  779. string query2 = string("The following was the DB Query: ").append(query.GetQuery());
  780. SaveClientLog("Possible Hacking Attempt", (char*)query2.c_str(), "Hacking Query", server_ip);
  781. }
  782. return table_query;
  783. }
  784. sint16 LoginDatabase::GetDataVersion(char* name){
  785. Query query;
  786. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT max(table_data_version) from %s", name);
  787. sint16 ret_version = 0;
  788. if(result && mysql_num_rows(result) > 0) {
  789. MYSQL_ROW row;
  790. row = mysql_fetch_row(result);
  791. if(row[0])
  792. ret_version = atoi(row[0]);
  793. }
  794. return ret_version;
  795. }
  796. void LoginDatabase::RemoveOldWorldServerStats(){
  797. Query query;
  798. query.RunQuery2(Q_DELETE, "delete from login_worldstats where (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(last_update)) > 86400");
  799. }
  800. void LoginDatabase::UpdateWorldServerStats( LWorld* world, sint32 status )
  801. {
  802. if( !world || world->GetAccountID() == 0 )
  803. return;
  804. Query query;
  805. query.RunQuery2(Q_INSERT, "insert into login_worldstats (world_id, world_status, current_players, current_zones, last_update) values(%lu, %i, %i, %i, NOW()) ON DUPLICATE KEY UPDATE current_players=%i,current_zones=%i,world_status=%i,last_update=NOW()",
  806. world->GetAccountID(), status, world->GetPlayerNum(), world->GetZoneNum(), world->GetPlayerNum(), world->GetZoneNum(), status);
  807. }
  808. bool LoginDatabase::ResetWorldServerStatsConnectedTime(LWorld* world){
  809. if(!world || world->GetAccountID() == 0)
  810. return false;
  811. Query query;
  812. string update_stats = string("update login_worldstats set connected_time=now() where world_id=%i and (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(last_update)) > 300");
  813. query.RunQuery2(Q_UPDATE, update_stats.c_str(),world->GetAccountID());
  814. return true;
  815. }
  816. void LoginDatabase::ResetWorldStats ( )
  817. {
  818. Query query;
  819. string update_stats = string("update login_worldstats set world_status=-4, current_players=0, current_zones=0");
  820. query.RunQuery2(update_stats.c_str(), Q_UPDATE);
  821. }
  822. void LoginDatabase::SaveBugReport(int32 world_id, char* category, char* subcategory, char* causes_crash, char* reproducible, char* summary, char* description, char* version, char* player, int32 account_id, char* spawn_name, int32 spawn_id, int32 zone_id){
  823. Query query;
  824. string bug_report = string("insert into bugs (world_id, category, subcategory, causes_crash, reproducible, summary, description, version, player, account_id, spawn_name, spawn_id, zone_id) values(%lu, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %lu, '%s', %lu, %lu)");
  825. query.RunQuery2(Q_INSERT, bug_report.c_str(), world_id, database.getSafeEscapeString(category).c_str(), database.getSafeEscapeString(subcategory).c_str(),
  826. database.getSafeEscapeString(causes_crash).c_str(), database.getSafeEscapeString(reproducible).c_str(), database.getSafeEscapeString(summary).c_str(),
  827. database.getSafeEscapeString(description).c_str(), database.getSafeEscapeString(version).c_str(), database.getSafeEscapeString(player).c_str(), account_id,
  828. database.getSafeEscapeString(spawn_name).c_str(), spawn_id, zone_id);
  829. FixBugReport();
  830. }
  831. void LoginDatabase::FixBugReport(){
  832. Query query;
  833. string bug_report = string("update bugs set description = REPLACE(description,SUBSTRING(description,INSTR(description,'%'), 3),char(CONV(SUBSTRING(description,INSTR(description,'%')+1, 2), 16, 10))), summary = REPLACE(summary,SUBSTRING(summary,INSTR(summary,'%'), 3),char(CONV(SUBSTRING(summary,INSTR(summary,'%')+1, 2), 16, 10)))");
  834. query.RunQuery2(bug_report.c_str(), Q_UPDATE);
  835. }
  836. void LoginDatabase::UpdateWorldIPAddress(int32 world_id, int32 address){
  837. struct in_addr in;
  838. in.s_addr = address;
  839. Query query;
  840. query.RunQuery2(Q_UPDATE, "update login_worldservers set ip_address='%s' where id=%lu", inet_ntoa(in), world_id);
  841. }
  842. void LoginDatabase::UpdateAccountIPAddress(int32 account_id, int32 address){
  843. struct in_addr in;
  844. in.s_addr = address;
  845. Query query;
  846. query.RunQuery2(Q_UPDATE, "update account set ip_address='%s' where id=%lu", inet_ntoa(in), account_id);
  847. }