LoginDatabase.cpp 49 KB

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