LoginDatabase.cpp 34 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756
  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, PacketStruct* packet){
  29. if(packet){
  30. Query query;
  31. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT name, description from ls_world_zones where server_id=%i and zone_id=%i", server_id, zone_id);
  32. MYSQL_ROW row;
  33. if(result && (row = mysql_fetch_row(result))) {
  34. if(row[0])
  35. packet->setMediumStringByName("zone", row[0]);
  36. else
  37. packet->setMediumStringByName("zone", " ");
  38. if(row[1])
  39. packet->setMediumStringByName("zonedesc", row[1]);
  40. else
  41. packet->setMediumStringByName("zonedesc", " ");
  42. }
  43. else{
  44. Query query2;
  45. MYSQL_RES* result2 = query2.RunQuery2(Q_SELECT, "SELECT name, description from zones where id=%i", zone_id);
  46. MYSQL_ROW row2;
  47. if(result2 && (row2 = mysql_fetch_row(result2))) {
  48. if(row2[0])
  49. packet->setMediumStringByName("zone", row2[0]);
  50. else
  51. packet->setMediumStringByName("zone", " ");
  52. if(row2[1])
  53. packet->setMediumStringByName("zonedesc", row2[1]);
  54. else
  55. packet->setMediumStringByName("zonedesc", " ");
  56. }
  57. }
  58. packet->setMediumStringByName("zonename2"," ");
  59. }
  60. }
  61. string LoginDatabase::GetZoneDescription(char* name){
  62. string ret;
  63. Query query;
  64. query.escaped_name = getEscapeString(name);
  65. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT description from zones where file=substring_index('%s', '.', 1)", query.escaped_name);
  66. MYSQL_ROW row;
  67. if((row = mysql_fetch_row(result))) {
  68. ret = string(row[0]);
  69. }
  70. return ret;
  71. }
  72. void LoginDatabase::SetServerZoneDescriptions(int32 server_id, map<int32, LoginZoneUpdate> zone_descriptions){
  73. if(zone_descriptions.size() > 0){
  74. map<int32, LoginZoneUpdate>::iterator zone_itr;
  75. string query_string = "replace into ls_world_zones (server_id, zone_id, name, description) values";
  76. int count=0;
  77. char server_id_str[12] = {0};
  78. sprintf(server_id_str, "%i", server_id);
  79. for(zone_itr = zone_descriptions.begin(); zone_itr != zone_descriptions.end(); zone_itr++, count++){
  80. char zone_id_str[12] = {0};
  81. sprintf(zone_id_str, "%i", zone_itr->first);
  82. if(count > 0)
  83. query_string.append(", ");
  84. query_string.append("(").append(server_id_str).append(",");
  85. query_string.append(zone_id_str).append(",");
  86. query_string.append("'").append(getSafeEscapeString(zone_itr->second.name.c_str()).c_str()).append("', '");
  87. query_string.append(getSafeEscapeString(zone_itr->second.description.c_str()).c_str()).append("')");
  88. }
  89. Query query;
  90. query.RunQuery2(query_string, Q_REPLACE);
  91. }
  92. }
  93. void LoginDatabase::LoadCharacters(LoginAccount* acct, int16 version){
  94. if(acct != NULL)
  95. acct->flushCharacters ( );
  96. Query query;
  97. Query query2;
  98. int32 id = 0;
  99. 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());
  100. if(result) {
  101. MYSQL_ROW row;
  102. MYSQL_ROW row2;
  103. MYSQL_ROW row3;
  104. while ((row = mysql_fetch_row(result))) {
  105. CharSelectProfile* player = new CharSelectProfile(version);
  106. id = atoul(row[0]);
  107. player->packet->setDataByName("charid", id);
  108. player->packet->setDataByName("server_id", atoul(row[1]));
  109. player->packet->setMediumStringByName("name", row[2]);
  110. player->packet->setDataByName("race", atoi(row[3]));
  111. player->packet->setDataByName("class", atoi(row[4]));
  112. player->packet->setDataByName("gender", atoi(row[5]));
  113. player->packet->setDataByName("deity", atoi(row[6]));
  114. player->packet->setDataByName("body_size", atof(row[7]));
  115. player->packet->setDataByName("body_age", atof(row[8]));
  116. SetZoneInformation(atoi(row[1]), atoi(row[9]), player->packet);
  117. player->packet->setDataByName("level", atoi(row[10]));
  118. player->packet->setDataByName("soga_wing_type", atoi(row[11]));
  119. player->packet->setDataByName("soga_chest_type", atoi(row[12]));
  120. player->packet->setDataByName("soga_legs_type", atoi(row[13]));
  121. player->packet->setDataByName("soga_hair_type", atoi(row[14]));
  122. player->packet->setDataByName("legs_type", atoi(row[15]));
  123. player->packet->setDataByName("chest_type", atoi(row[16]));
  124. player->packet->setDataByName("wing_type", atoi(row[17]));
  125. player->packet->setDataByName("hair_type", atoi(row[18]));
  126. player->packet->setDataByName("created_date", atol(row[19]));
  127. if (row[20])
  128. player->packet->setDataByName("last_played", atol(row[20]));
  129. if(version >= 887)
  130. player->packet->setDataByName("version", 6);
  131. else
  132. player->packet->setDataByName("version", 5);
  133. player->packet->setDataByName("account_id", acct->getLoginAccountID());
  134. player->packet->setDataByName("account_id2", acct->getLoginAccountID());
  135. LoadAppearanceData(atol(row[21]), player->packet);
  136. if(row[22])
  137. player->packet->setMediumStringByName("server_name", row[22]);
  138. player->packet->setDataByName("hair_face_type", atoi(row[23]));
  139. player->packet->setDataByName("soga_hair_face_type", atoi(row[24]));
  140. player->packet->setDataByName("soga_race_type", atoi(row[25]));
  141. player->packet->setDataByName("race_type", atoi(row[26]));
  142. player->packet->setDataByName("unknown3", 57);
  143. player->packet->setDataByName("unknown4", 56);
  144. player->packet->setDataByName("unknown6", 1, 1); //if not here will not display character
  145. player->packet->setDataByName("unknown8", 15);
  146. player->packet->setDataByName("unknown13", 212);
  147. player->packet->setColorByName("unknown14", 0xFF, 0xFF, 0xFF);
  148. uchar tmp[] = {0xFF, 0xFF, 0xFF, 0x61, 0x00, 0x2C, 0x04, 0xA5, 0x09, 0x02, 0x0F, 0x00, 0x00};
  149. for(int y=0;y<sizeof(tmp);y++)
  150. player->packet->setDataByName("unknown11", tmp[y], y);
  151. 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);
  152. if(result3){
  153. for(int i=0;(row3 = mysql_fetch_row(result3)) && i<24; i++){
  154. 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]));
  155. }
  156. }
  157. acct->addCharacter(player);
  158. }
  159. }
  160. else
  161. LogWrite(LOGIN__ERROR, 0, "Login", "Error in LoadCharacters query '%s': %s", query.GetQuery(), query.GetError());
  162. }
  163. void LoginDatabase::CheckCharacterTimeStamps(LoginAccount* acct){
  164. Query query;
  165. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT char_id, unix_timestamp from login_characters where account_id=%i",acct->getLoginAccountID());
  166. if(result && mysql_num_rows(result) > 0) {
  167. MYSQL_ROW row;
  168. ServerPacket* outpack = new ServerPacket(ServerOP_CharTimeStamp, sizeof(CharacterTimeStamp_Struct));
  169. CharacterTimeStamp_Struct* cts = (CharacterTimeStamp_Struct*) outpack->pBuffer;
  170. cts->account_id = acct->getLoginAccountID();
  171. int32 server_id = 0;
  172. LWorld* world_server = 0;
  173. while ((row = mysql_fetch_row(result))) {
  174. server_id = atoi(row[1]);
  175. if(server_id != 0)
  176. world_server = world_list.FindByAccount(server_id, World);
  177. if(world_server) // If the pointer is 0, the world server must be down, we can't do any updates...
  178. {
  179. cts->char_id = atoi(row[0]);
  180. cts->unix_timestamp = atoi(row[1]);
  181. world_server->SendPacket(outpack);
  182. //Reset for next character
  183. world_server = 0;
  184. server_id = 0;
  185. }
  186. }
  187. safe_delete(outpack);
  188. }
  189. }
  190. void LoginDatabase::SaveCharacterFloats(int32 char_id, char* type, float float1, float float2, float float3){
  191. Query query;
  192. 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)");
  193. query.RunQuery2(Q_INSERT, create_char.c_str(), char_id, type, (sint8)(float1*100), (sint8)(float2*100), (sint8)(float3*100));
  194. }
  195. void LoginDatabase::SaveCharacterColors(int32 char_id, char* type, EQ2_Color color){
  196. Query query;
  197. string create_char = string("insert into login_char_colors (login_characters_id, type, red, green, blue) values(%i,'%s',%i,%i,%i)");
  198. query.RunQuery2(Q_INSERT, create_char.c_str(), char_id, type, color.red, color.green, color.blue);
  199. }
  200. void LoginDatabase::LoadAppearanceData(int32 char_id, PacketStruct* char_select_packet){
  201. Query query;
  202. MYSQL_ROW row;
  203. 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);
  204. while((row = mysql_fetch_row(result))){
  205. if(atoi(row[1]) == 0)
  206. char_select_packet->setColorByName(row[0], atoi(row[2]), atoi(row[3]), atoi(row[4]));
  207. else{
  208. char_select_packet->setDataByName(row[0], atoi(row[2]),0);
  209. char_select_packet->setDataByName(row[0], atoi(row[3]),1);
  210. char_select_packet->setDataByName(row[0], atoi(row[4]),2);
  211. }
  212. }
  213. }
  214. int16 LoginDatabase::GetAppearanceID(string name){
  215. int32 id = 0;
  216. Query query;
  217. MYSQL_ROW row;
  218. query.escaped_name = getEscapeString(name.c_str());
  219. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT appearance_id from appearances where name='%s'", query.escaped_name);
  220. if(result && mysql_num_rows(result) == 1){
  221. row = mysql_fetch_row(result);
  222. id = atoi(row[0]);
  223. }
  224. return id;
  225. }
  226. void LoginDatabase::DeactivateCharID(int32 server_id, int32 char_id, int32 exception_id){
  227. Query query;
  228. 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);
  229. }
  230. int32 LoginDatabase::SaveCharacter(PacketStruct* create, LoginAccount* acct, int32 world_charid){
  231. int32 ret_id = 0;
  232. Query query;
  233. string create_char =
  234. 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)"
  235. " values(%i, %i, %i, '%s', %i, %i, %i, %i, %f, %f, %i, %i, %i, %i, %i, %i, %i, %i, %i, %i, %i, %i)");
  236. query.RunQuery2(Q_INSERT, create_char.c_str(),
  237. acct->getLoginAccountID(),
  238. create->getType_int32_ByName("server_id"), world_charid,
  239. create->getType_EQ2_16BitString_ByName("name").data.c_str(),
  240. create->getType_int8_ByName("race"),
  241. create->getType_int8_ByName("class"),
  242. create->getType_int8_ByName("gender"),
  243. create->getType_int8_ByName("deity"),
  244. create->getType_float_ByName("body_size"),
  245. create->getType_float_ByName("body_age"),
  246. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_wing_file").data),
  247. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_chest_file").data),
  248. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_legs_file").data),
  249. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_hair_file").data),
  250. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_face_file").data),
  251. GetAppearanceID(create->getType_EQ2_16BitString_ByName("legs_file").data),
  252. GetAppearanceID(create->getType_EQ2_16BitString_ByName("chest_file").data),
  253. GetAppearanceID(create->getType_EQ2_16BitString_ByName("wing_file").data),
  254. GetAppearanceID(create->getType_EQ2_16BitString_ByName("hair_file").data),
  255. GetAppearanceID(create->getType_EQ2_16BitString_ByName("face_file").data),
  256. GetAppearanceID(create->getType_EQ2_16BitString_ByName("soga_race_file").data),
  257. GetAppearanceID(create->getType_EQ2_16BitString_ByName("race_file").data));
  258. if(query.GetError() && strlen(query.GetError()) > 0){
  259. LogWrite(LOGIN__ERROR, 0, "Login", "Error in SaveCharacter query '%s': %s", query.GetQuery(), query.GetError());
  260. return 0;
  261. }
  262. int32 last_insert_id = query.GetLastInsertedID();
  263. //mark any remaining characters with same id as deleted (creates problems if world deleted their db and started assigning new char ids)
  264. DeactivateCharID(create->getType_int32_ByName("server_id"), world_charid, last_insert_id);
  265. int32 char_id = last_insert_id;
  266. SaveCharacterColors(char_id,"skin_color", create->getType_EQ2_Color_ByName("skin_color"));
  267. SaveCharacterColors(char_id,"eye_color", create->getType_EQ2_Color_ByName("eye_color"));
  268. SaveCharacterColors(char_id,"hair_color1", create->getType_EQ2_Color_ByName("hair_color1"));
  269. SaveCharacterColors(char_id,"hair_color2", create->getType_EQ2_Color_ByName("hair_color2"));
  270. SaveCharacterColors(char_id,"hair_highlight", create->getType_EQ2_Color_ByName("hair_highlight"));
  271. SaveCharacterColors(char_id,"hair_type_color", create->getType_EQ2_Color_ByName("hair_type_color"));
  272. SaveCharacterColors(char_id,"hair_type_highlight_color", create->getType_EQ2_Color_ByName("hair_type_highlight_color"));
  273. SaveCharacterColors(char_id,"hair_face_color", create->getType_EQ2_Color_ByName("hair_face_color"));
  274. SaveCharacterColors(char_id,"hair_face_highlight_color", create->getType_EQ2_Color_ByName("hair_face_highlight_color"));
  275. SaveCharacterColors(char_id,"wing_color1", create->getType_EQ2_Color_ByName("wing_color1"));
  276. SaveCharacterColors(char_id,"wing_color2", create->getType_EQ2_Color_ByName("wing_color2"));
  277. SaveCharacterColors(char_id,"shirt_color", create->getType_EQ2_Color_ByName("shirt_color"));
  278. SaveCharacterColors(char_id,"unknown_chest_color", create->getType_EQ2_Color_ByName("unknown_chest_color"));
  279. SaveCharacterColors(char_id,"pants_color", create->getType_EQ2_Color_ByName("pants_color"));
  280. SaveCharacterColors(char_id,"unknown_legs_color", create->getType_EQ2_Color_ByName("unknown_legs_color"));
  281. SaveCharacterColors(char_id,"unknown9", create->getType_EQ2_Color_ByName("unknown9"));
  282. SaveCharacterFloats(char_id,"eye_type", create->getType_float_ByName("eyes2",0), create->getType_float_ByName("eyes2",1), create->getType_float_ByName("eyes2",2));
  283. SaveCharacterFloats(char_id,"ear_type", create->getType_float_ByName("ears",0), create->getType_float_ByName("ears",1), create->getType_float_ByName("ears",2));
  284. 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));
  285. SaveCharacterFloats(char_id,"cheek_type", create->getType_float_ByName("cheeks",0), create->getType_float_ByName("cheeks",1), create->getType_float_ByName("cheeks",2));
  286. SaveCharacterFloats(char_id,"lip_type", create->getType_float_ByName("lips",0), create->getType_float_ByName("lips",1), create->getType_float_ByName("lips",2));
  287. SaveCharacterFloats(char_id,"chin_type", create->getType_float_ByName("chin",0), create->getType_float_ByName("chin",1), create->getType_float_ByName("chin",2));
  288. SaveCharacterFloats(char_id,"nose_type", create->getType_float_ByName("nose",0), create->getType_float_ByName("nose",1), create->getType_float_ByName("nose",2));
  289. SaveCharacterFloats(char_id,"body_size", create->getType_float_ByName("body_size",0), 0, 0);
  290. SaveCharacterColors(char_id,"soga_skin_color", create->getType_EQ2_Color_ByName("soga_skin_color"));
  291. SaveCharacterColors(char_id,"soga_eye_color", create->getType_EQ2_Color_ByName("soga_eye_color"));
  292. SaveCharacterColors(char_id,"soga_hair_color1", create->getType_EQ2_Color_ByName("soga_hair_color1"));
  293. SaveCharacterColors(char_id,"soga_hair_color2", create->getType_EQ2_Color_ByName("soga_hair_color2"));
  294. SaveCharacterColors(char_id,"soga_hair_highlight", create->getType_EQ2_Color_ByName("soga_hair_highlight"));
  295. SaveCharacterColors(char_id,"soga_hair_type_color", create->getType_EQ2_Color_ByName("soga_hair_type_color"));
  296. SaveCharacterColors(char_id,"soga_hair_type_highlight_color", create->getType_EQ2_Color_ByName("soga_hair_type_highlight_color"));
  297. SaveCharacterColors(char_id,"soga_hair_face_color", create->getType_EQ2_Color_ByName("soga_hair_face_color"));
  298. SaveCharacterColors(char_id,"soga_hair_face_highlight_color", create->getType_EQ2_Color_ByName("soga_hair_face_highlight_color"));
  299. SaveCharacterColors(char_id,"soga_wing_color1", create->getType_EQ2_Color_ByName("soga_wing_color1"));
  300. SaveCharacterColors(char_id,"soga_wing_color2", create->getType_EQ2_Color_ByName("soga_wing_color2"));
  301. SaveCharacterColors(char_id,"soga_shirt_color", create->getType_EQ2_Color_ByName("soga_shirt_color"));
  302. SaveCharacterColors(char_id,"soga_unknown_chest_color", create->getType_EQ2_Color_ByName("soga_unknown_chest_color"));
  303. SaveCharacterColors(char_id,"soga_pants_color", create->getType_EQ2_Color_ByName("soga_pants_color"));
  304. SaveCharacterColors(char_id,"soga_unknown_legs_color", create->getType_EQ2_Color_ByName("soga_unknown_legs_color"));
  305. SaveCharacterColors(char_id,"soga_unknown13", create->getType_EQ2_Color_ByName("soga_unknown13"));
  306. 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));
  307. 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));
  308. 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));
  309. 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));
  310. 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));
  311. 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));
  312. 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));
  313. return ret_id;
  314. }
  315. bool LoginDatabase::DeleteCharacter(int32 account_id, int32 character_id, int32 server_id){
  316. Query query;
  317. string delete_char = string("delete from login_characters where char_id=%i and account_id=%i and server_id=%i");
  318. query.RunQuery2(Q_DELETE, delete_char.c_str(),character_id,account_id,server_id);
  319. if(!query.GetAffectedRows())
  320. {
  321. //No error just in case ppl try doing stupid stuff
  322. return false;
  323. }
  324. return true;
  325. }
  326. string LoginDatabase::GetCharacterName(int32 char_id, int32 server_id){
  327. Query query;
  328. MYSQL_ROW row;
  329. 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);
  330. if(result && mysql_num_rows(result) == 1){
  331. row = mysql_fetch_row(result);
  332. return string(row[0]);
  333. }
  334. return string("");
  335. }
  336. bool LoginDatabase::UpdateCharacterTimeStamp(int32 account_id, int32 character_id, int32 timestamp_update, int32 server_id){
  337. Query query;
  338. string update_charts = string("update login_characters set unix_timestamp=%lu where char_id=%lu and account_id=%lu and server_id=%lu");
  339. query.RunQuery2(Q_UPDATE, update_charts.c_str(),timestamp_update,character_id,account_id,server_id);
  340. if(!query.GetAffectedRows())
  341. {
  342. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterTimeStamp query '%s': %s", query.GetQuery(), query.GetError());
  343. return false;
  344. }
  345. return true;
  346. }
  347. bool LoginDatabase::UpdateCharacterLevel(int32 account_id, int32 character_id, int8 in_level, int32 server_id){
  348. Query query;
  349. string update_charts = string("update login_characters set level=%i where char_id=%lu and account_id=%lu and server_id=%lu");
  350. query.RunQuery2(Q_UPDATE, update_charts.c_str(),in_level,character_id,account_id,server_id);
  351. if(!query.GetAffectedRows())
  352. {
  353. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterLevel query '%s': %s", query.GetQuery(), query.GetError());
  354. return false;
  355. }
  356. return true;
  357. }
  358. bool LoginDatabase::UpdateCharacterRace(int32 account_id, int32 character_id, int16 in_racetype, int8 in_race, int32 server_id){
  359. Query query;
  360. 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");
  361. query.RunQuery2(Q_UPDATE, update_charts.c_str(),in_racetype,in_race,character_id,account_id,server_id);
  362. if(!query.GetAffectedRows())
  363. {
  364. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterRace query '%s': %s", query.GetQuery(), query.GetError());
  365. return false;
  366. }
  367. return true;
  368. }
  369. bool LoginDatabase::UpdateCharacterZone(int32 account_id, int32 character_id, int32 zone_id, int32 server_id){
  370. Query query;
  371. string update_chars = string("update login_characters set current_zone_id=%i where char_id=%lu and account_id=%lu and server_id=%lu");
  372. query.RunQuery2(Q_UPDATE, update_chars.c_str(), zone_id, character_id, account_id, server_id);
  373. if(!query.GetAffectedRows())
  374. {
  375. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterZone query '%s': %s", query.GetQuery(), query.GetError());
  376. return false;
  377. }
  378. return true;
  379. }
  380. bool LoginDatabase::UpdateCharacterClass(int32 account_id, int32 character_id, int8 in_class, int32 server_id){
  381. Query query;
  382. string update_charts = string("update login_characters set class=%i where char_id=%lu and account_id=%lu and server_id=%lu");
  383. query.RunQuery2(Q_UPDATE, update_charts.c_str(),in_class,character_id,account_id,server_id);
  384. if(!query.GetAffectedRows())
  385. {
  386. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterClass query '%s': %s", query.GetQuery(), query.GetError());
  387. return false;
  388. }
  389. return true;
  390. }
  391. bool LoginDatabase::UpdateCharacterGender(int32 account_id, int32 character_id, int8 in_gender, int32 server_id){
  392. Query query;
  393. string update_charts = string("update login_characters set gender=%i where char_id=%lu and account_id=%lu and server_id=%lu");
  394. query.RunQuery2(Q_UPDATE, update_charts.c_str(),in_gender,character_id,account_id,server_id);
  395. if(!query.GetAffectedRows())
  396. {
  397. LogWrite(LOGIN__ERROR, 0, "Login", "Error in UpdateCharacterClass query '%s': %s", query.GetQuery(), query.GetError());
  398. return false;
  399. }
  400. return true;
  401. }
  402. LoginAccount* LoginDatabase::LoadAccount(const char* name, const char* password){
  403. LoginAccount* acct = NULL;
  404. Query query;
  405. query.escaped_name = getEscapeString(name);
  406. query.escaped_pass = getEscapeString(password);
  407. MYSQL_ROW row;
  408. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT id from account where name='%s' and passwd=md5('%s')", query.escaped_name, query.escaped_pass);
  409. if(result){
  410. if (mysql_num_rows(result) == 1){
  411. row = mysql_fetch_row(result);
  412. int32 id = atol(row[0]);
  413. acct = new LoginAccount(id, name, password);
  414. acct->setAuthenticated(true);
  415. }
  416. else if(mysql_num_rows(result) > 0)
  417. LogWrite(LOGIN__ERROR, 0, "Login", "Error in LoginAccount: more than one account returned for '%s'", name);
  418. }
  419. return acct;
  420. }
  421. int32 LoginDatabase::CheckServerAccount(char* name, char* passwd){
  422. int32 id = 0;
  423. Query query;
  424. MYSQL_ROW row;
  425. query.escaped_name = getEscapeString(name);
  426. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT password, id from login_worldservers where account='%s'", query.escaped_name);
  427. if(result && mysql_num_rows(result) == 1){
  428. row = mysql_fetch_row(result);
  429. if(memcmp(row[0], passwd, 1) == 0)
  430. id = atoi(row[1]);
  431. }
  432. return id;
  433. }
  434. void LoginDatabase::GetServerAccounts(vector<LWorld*>* server_list){
  435. Query query;
  436. MYSQL_ROW row;
  437. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT id, account, name, admin_id from login_worldservers");
  438. while((row = mysql_fetch_row(result))){
  439. LWorld* world = new LWorld(atol(row[0]), row[1], row[2], atoi(row[3]));
  440. world->SetID(world->GetAccountID());
  441. server_list->push_back(world);
  442. }
  443. }
  444. void LoginDatabase::SaveClientLog(char* type, char* message, char* player_name, int16 version){
  445. Query query;
  446. query.escaped_data1 = getEscapeString(message);
  447. query.escaped_name = getEscapeString(player_name);
  448. 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);
  449. }
  450. bool LoginDatabase::VerifyDelete(int32 account_id, int32 character_id, const char* name){
  451. Query query;
  452. query.escaped_name = getEscapeString(name);
  453. 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);
  454. if(query.GetAffectedRows() == 1)
  455. return true;
  456. else
  457. return false;
  458. }
  459. char* LoginDatabase::GetServerAccountName(int32 id){
  460. Query query;
  461. MYSQL_ROW row;
  462. char* name = 0;
  463. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT name from login_worldservers where id=%lu", id);
  464. if(result && mysql_num_rows(result) == 1){
  465. row = mysql_fetch_row(result);
  466. if(strlen(row[0]) > 0){
  467. name = new char[strlen(row[0])+1];
  468. strcpy(name, row[0]);
  469. }
  470. }
  471. return name;
  472. }
  473. int32 LoginDatabase::GetRaceID(char* name){
  474. int32 ret = 1487;
  475. Query query;
  476. MYSQL_ROW row;
  477. query.escaped_name = getEscapeString(name);
  478. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT race_type from login_races where name='%s'", query.escaped_name);
  479. if(result && mysql_num_rows(result) == 1){
  480. row = mysql_fetch_row(result);
  481. ret = atol(row[0]);
  482. }
  483. else if(!result || mysql_num_rows(result) == 0)
  484. UpdateRaceID(query.escaped_name);
  485. return ret;
  486. }
  487. void LoginDatabase::UpdateRaceID(char* name){
  488. Query query;
  489. query.RunQuery2(Q_UPDATE, "insert into login_races (name) values('%s')", name);
  490. }
  491. bool LoginDatabase::CheckVersion(char* in_version){
  492. Query query;
  493. query.escaped_data1 = getEscapeString(in_version);
  494. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT id from login_versions where version='%s'", query.escaped_data1);
  495. if(result && mysql_num_rows(result) == 1)
  496. return true;
  497. else
  498. return false;
  499. }
  500. void LoginDatabase::GetLatestTableVersions(LatestTableVersions* table_versions){
  501. Query query;
  502. MYSQL_ROW row;
  503. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT name, max(version) from login_table_versions group by name order by id");
  504. if(result && mysql_num_rows(result) > 0){
  505. table_versions->SetTableSize(mysql_num_rows(result));
  506. }
  507. else // we need to return if theres no result, otherwise it will crash attempting to loop through rows
  508. return;
  509. while((row = mysql_fetch_row(result))){
  510. if(VerifyDataTable(row[0]))
  511. table_versions->AddTable(row[0], atoi(row[1]), GetDataVersion(row[0]));
  512. else
  513. table_versions->AddTable(row[0], atoi(row[1]), 0);
  514. }
  515. }
  516. bool LoginDatabase::VerifyDataTable(char* name){
  517. Query query;
  518. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT table_name from download_tables where table_name='%s'", name);
  519. if(result && mysql_num_rows(result) > 0)
  520. return true;
  521. return false;
  522. }
  523. string LoginDatabase::GetColumnNames(char* name){
  524. Query query;
  525. MYSQL_ROW row;
  526. string columns = "(";
  527. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "show columns from %s", name);
  528. if(result && mysql_num_rows(result) > 0){
  529. int16 i = 0;
  530. while((row = mysql_fetch_row(result))){
  531. if(strcmp(row[0], "table_data_version") != 0){
  532. if(i>0)
  533. columns.append(",");
  534. columns.append(row[0]);
  535. i++;
  536. }
  537. }
  538. }
  539. columns.append(") ");
  540. return columns;
  541. }
  542. TableDataQuery* LoginDatabase::GetTableDataQuery(int32 server_ip, char* name, int16 version){
  543. Query query;
  544. MYSQL_ROW row;
  545. query.escaped_name = getEscapeString(name);
  546. TableDataQuery* table_query = 0;
  547. MYSQL_RES* result = 0;
  548. string columns;
  549. if(VerifyDataTable(query.escaped_name)){
  550. result = query.RunQuery2(Q_SELECT, "SELECT * from %s where table_data_version > %i", query.escaped_name, version);
  551. columns = GetColumnNames(query.escaped_name);
  552. }
  553. if(result && mysql_num_rows(result) > 0){
  554. table_query = new TableDataQuery(query.escaped_name);
  555. table_query->num_queries = mysql_num_rows(result);
  556. table_query->columns_size = columns.length() + 1;
  557. table_query->columns = new char[table_query->columns_size + 1];
  558. table_query->version = GetDataVersion(query.escaped_name);
  559. strcpy(table_query->columns, (char*)columns.c_str());
  560. string query_data;
  561. MYSQL_FIELD* field;
  562. int* int_list = new int[mysql_num_fields(result)];
  563. int16 ndx = 0;
  564. while((field = mysql_fetch_field(result))){
  565. int_list[ndx] = IS_NUM(field->type);
  566. if(strcmp(field->name,"table_data_version") == 0)
  567. int_list[ndx] = 2;
  568. ndx++;
  569. }
  570. ndx = 0;
  571. while((row = mysql_fetch_row(result))){
  572. query_data = "";
  573. for(int i=0;i<mysql_num_fields(result);i++){
  574. if(int_list[i]<2){
  575. if(i>0)
  576. query_data.append(",");
  577. if(!int_list[i]){
  578. query_data.append("'").append(getEscapeString(row[i])).append("'");
  579. }
  580. else
  581. query_data.append(row[i]);
  582. }
  583. }
  584. TableData* new_query = new TableData;
  585. new_query->size = query_data.length() + 1;
  586. new_query->query = new char[query_data.length() + 1];
  587. strcpy(new_query->query, query_data.c_str());
  588. table_query->queries.push_back(new_query);
  589. ndx++;
  590. }
  591. safe_delete_array(int_list);
  592. }
  593. else{
  594. string query2 = string("The user tried to download the following table: ").append(query.escaped_name);
  595. SaveClientLog("Possible Hacking Attempt", (char*)query2.c_str(), "Hacking Data", server_ip);
  596. }
  597. return table_query;
  598. }
  599. TableQuery* LoginDatabase::GetLatestTableQuery(int32 server_ip, char* name, int16 version){
  600. Query query;
  601. MYSQL_ROW row;
  602. query.escaped_name = getEscapeString(name);
  603. TableQuery* table_query = 0;
  604. 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);
  605. if(result && mysql_num_rows(result) > 0){
  606. int16 i = 0;
  607. table_query = new TableQuery;
  608. while((row = mysql_fetch_row(result))){
  609. char* rowdata = row[0];
  610. if(strstr(rowdata, ";")){
  611. char* token = strtok(rowdata,";");
  612. while(token){
  613. char* new_query = new char[strlen(token) + 1];
  614. strcpy(new_query, token);
  615. table_query->AddQuery(new_query);
  616. token = strtok(NULL, ";");
  617. }
  618. }
  619. else
  620. table_query->AddQuery(rowdata);
  621. table_query->latest_version = atoi(row[1]);
  622. }
  623. strcpy(table_query->tablename, name);
  624. table_query->your_version = version;
  625. }
  626. else{
  627. string query2 = string("The following was the DB Query: ").append(query.GetQuery());
  628. SaveClientLog("Possible Hacking Attempt", (char*)query2.c_str(), "Hacking Query", server_ip);
  629. }
  630. return table_query;
  631. }
  632. sint16 LoginDatabase::GetDataVersion(char* name){
  633. Query query;
  634. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT max(table_data_version) from %s", name);
  635. sint16 ret_version = 0;
  636. if(result && mysql_num_rows(result) > 0) {
  637. MYSQL_ROW row;
  638. row = mysql_fetch_row(result);
  639. if(row[0])
  640. ret_version = atoi(row[0]);
  641. }
  642. return ret_version;
  643. }
  644. void LoginDatabase::RemoveOldWorldServerStats(){
  645. Query query;
  646. query.RunQuery2(Q_DELETE, "delete from login_worldstats where (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(last_update)) > 86400");
  647. }
  648. void LoginDatabase::UpdateWorldServerStats( LWorld* world, sint32 status )
  649. {
  650. if( !world || world->GetAccountID() == 0 )
  651. return;
  652. Query query;
  653. 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()",
  654. world->GetAccountID(), status, world->GetPlayerNum(), world->GetZoneNum(), world->GetPlayerNum(), world->GetZoneNum(), status);
  655. }
  656. bool LoginDatabase::ResetWorldServerStatsConnectedTime(LWorld* world){
  657. if(!world || world->GetAccountID() == 0)
  658. return false;
  659. Query query;
  660. string update_stats = string("update login_worldstats set connected_time=now() where world_id=%i and (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(last_update)) > 300");
  661. query.RunQuery2(Q_UPDATE, update_stats.c_str(),world->GetAccountID());
  662. return true;
  663. }
  664. void LoginDatabase::ResetWorldStats ( )
  665. {
  666. Query query;
  667. string update_stats = string("update login_worldstats set world_status=-4, current_players=0, current_zones=0");
  668. query.RunQuery2(update_stats.c_str(), Q_UPDATE);
  669. }
  670. 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){
  671. Query query;
  672. 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)");
  673. query.RunQuery2(Q_INSERT, bug_report.c_str(), world_id, database.getSafeEscapeString(category).c_str(), database.getSafeEscapeString(subcategory).c_str(),
  674. database.getSafeEscapeString(causes_crash).c_str(), database.getSafeEscapeString(reproducible).c_str(), database.getSafeEscapeString(summary).c_str(),
  675. database.getSafeEscapeString(description).c_str(), database.getSafeEscapeString(version).c_str(), database.getSafeEscapeString(player).c_str(), account_id,
  676. database.getSafeEscapeString(spawn_name).c_str(), spawn_id, zone_id);
  677. FixBugReport();
  678. }
  679. void LoginDatabase::FixBugReport(){
  680. Query query;
  681. 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)))");
  682. query.RunQuery2(bug_report.c_str(), Q_UPDATE);
  683. }
  684. void LoginDatabase::UpdateWorldIPAddress(int32 world_id, int32 address){
  685. struct in_addr in;
  686. in.s_addr = address;
  687. Query query;
  688. query.RunQuery2(Q_UPDATE, "update login_worldservers set ip_address='%s' where id=%lu", inet_ntoa(in), world_id);
  689. }
  690. void LoginDatabase::UpdateAccountIPAddress(int32 account_id, int32 address){
  691. struct in_addr in;
  692. in.s_addr = address;
  693. Query query;
  694. query.RunQuery2(Q_UPDATE, "update account set ip_address='%s' where id=%lu", inet_ntoa(in), account_id);
  695. }