Advertisement
Guest User

Untitled

a guest
Jun 15th, 2018
392
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 29.82 KB | None | 0 0
  1. // Copyright (c) Athena Dev Teams - Licensed under GNU GPL
  2. // For more information, see LICENCE in the main folder
  3.  
  4. #include "../common/malloc.h"
  5. #include "../common/mmo.h"
  6. #include "../common/showmsg.h"
  7. #include "../common/sql.h"
  8. #include "../common/strlib.h"
  9. #include "../common/timer.h"
  10. #include "account.h"
  11. #include <stdlib.h>
  12. #include <string.h>
  13.  
  14. /// global defines
  15. #define ACCOUNT_SQL_DB_VERSION 20110114
  16.  
  17. /// internal structure
  18. typedef struct AccountDB_SQL
  19. {
  20. AccountDB vtable; // public interface
  21.  
  22. Sql* accounts; // SQL accounts storage
  23.  
  24. // global sql settings
  25. char global_db_hostname[32];
  26. uint16 global_db_port;
  27. char global_db_username[32];
  28. char global_db_password[32];
  29. char global_db_database[32];
  30. char global_codepage[32];
  31. // local sql settings
  32. char db_hostname[32];
  33. uint16 db_port;
  34. char db_username[32];
  35. char db_password[32];
  36. char db_database[32];
  37. char codepage[32];
  38. // other settings
  39. bool case_sensitive;
  40. char account_db[32];
  41. char accreg_db[32];
  42.  
  43. } AccountDB_SQL;
  44.  
  45. /// internal structure
  46. typedef struct AccountDBIterator_SQL
  47. {
  48. AccountDBIterator vtable; // public interface
  49.  
  50. AccountDB_SQL* db;
  51. int last_account_id;
  52. } AccountDBIterator_SQL;
  53.  
  54.  
  55. // (^~_~^) Gepard Shield Start
  56. static AccountDB_SQL* db_sql;
  57.  
  58. void account_gepard_init(AccountDB* self)
  59. {
  60. db_sql = (AccountDB_SQL*)self;
  61.  
  62. if (SQL_SUCCESS != Sql_Query(db_sql->accounts,
  63. "CREATE TABLE IF NOT EXISTS `gepard_block` ("
  64. "`unique_id` int(11) unsigned NOT NULL DEFAULT '0',"
  65. "`unban_time` datetime NOT NULL,"
  66. "`reason` varchar(50) NOT NULL,"
  67. "UNIQUE KEY `unique_id` (`unique_id`)"
  68. ") ENGINE=MyISAM DEFAULT CHARSET=latin1;"))
  69. {
  70. Sql_ShowDebug(db_sql->accounts);
  71. exit(EXIT_FAILURE);
  72. }
  73.  
  74. if (SQL_SUCCESS != Sql_Query(db_sql->accounts,
  75. "CREATE TABLE IF NOT EXISTS `gepard_block_log` ("
  76. "`id` int(11) unsigned NOT NULL AUTO_INCREMENT,"
  77. "`unique_id` int(11) unsigned NOT NULL DEFAULT '0',"
  78. "`block_time` datetime NOT NULL,"
  79. "`unban_time` datetime NOT NULL,"
  80. "`violator_name` varchar(24) NOT NULL,"
  81. "`violator_account_id` int(11) NOT NULL,"
  82. "`initiator_name` varchar(24) NOT NULL,"
  83. "`initiator_account_id` int(11) NOT NULL,"
  84. "`reason` varchar(50) NOT NULL,"
  85. "PRIMARY KEY (`id`)"
  86. ") ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;"))
  87. {
  88. Sql_ShowDebug(db_sql->accounts);
  89. exit(EXIT_FAILURE);
  90. }
  91.  
  92. if (SQL_SUCCESS != Sql_Query(db_sql->accounts,
  93. "CREATE TABLE IF NOT EXISTS `gepard_min_allowed_license_version` ("
  94. "`version` int(11) unsigned NOT NULL DEFAULT '2018033001'"
  95. ") ENGINE=MyISAM DEFAULT CHARSET=latin1;"))
  96. {
  97. Sql_ShowDebug(db_sql->accounts);
  98. exit(EXIT_FAILURE);
  99. }
  100.  
  101. if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "SELECT `version` FROM `gepard_min_allowed_license_version`"))
  102. {
  103. Sql_ShowDebug(db_sql->accounts);
  104. exit(EXIT_FAILURE);
  105. }
  106.  
  107. if (SQL_SUCCESS != Sql_NextRow(db_sql->accounts))
  108. {
  109. if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "INSERT INTO `gepard_min_allowed_license_version` VALUES ('0')"))
  110. {
  111. Sql_ShowDebug(db_sql->accounts);
  112. exit(EXIT_FAILURE);
  113. }
  114. }
  115.  
  116. if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "SHOW COLUMNS FROM `login` LIKE 'last_unique_id'"))
  117. {
  118. Sql_ShowDebug(db_sql->accounts);
  119. exit(EXIT_FAILURE);
  120. }
  121.  
  122. if (Sql_NumRows(db_sql->accounts) == 0)
  123. {
  124. if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "ALTER TABLE `login` ADD `last_unique_id` INT(11) UNSIGNED NOT NULL DEFAULT '0';"))
  125. {
  126. Sql_ShowDebug(db_sql->accounts);
  127. exit(EXIT_FAILURE);
  128. }
  129. }
  130.  
  131. if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "SHOW COLUMNS FROM `login` LIKE 'blocked_unique_id'"))
  132. {
  133. Sql_ShowDebug(db_sql->accounts);
  134. exit(EXIT_FAILURE);
  135. }
  136.  
  137. if (Sql_NumRows(db_sql->accounts) == 0)
  138. {
  139. if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "ALTER TABLE `login` ADD `blocked_unique_id` INT(11) UNSIGNED NOT NULL DEFAULT '0';"))
  140. {
  141. Sql_ShowDebug(db_sql->accounts);
  142. exit(EXIT_FAILURE);
  143. }
  144. }
  145. }
  146.  
  147. int account_gepard_check_license_version(struct socket_data* s, int fd, int group_id)
  148. {
  149. const char* message_info_sql_error = "Tell administrator about SQL problem.";
  150.  
  151. if (is_gepard_active == true)
  152. {
  153. return 0;
  154. }
  155.  
  156. if (group_id == 99)
  157. {
  158. return 0;
  159. }
  160.  
  161. if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "SELECT `version` FROM `gepard_min_allowed_license_version`"))
  162. {
  163. Sql_ShowDebug(db_sql->accounts);
  164.  
  165. gepard_send_info(fd, GEPARD_INFO_MESSAGE, message_info_sql_error);
  166. }
  167. else if (SQL_SUCCESS != Sql_NextRow(db_sql->accounts))
  168. {
  169. if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "INSERT INTO `gepard_min_allowed_license_version` VALUES ('0')"))
  170. {
  171. Sql_ShowDebug(db_sql->accounts);
  172.  
  173. gepard_send_info(fd, GEPARD_INFO_MESSAGE, message_info_sql_error);
  174. }
  175. }
  176. else
  177. {
  178. char* data;
  179. const char* message_info_outdated_license = "You are using outdated Gepard Shield.\n\nYou have to close the game and run updater.";
  180.  
  181. Sql_GetData(db_sql->accounts, 0, &data, NULL);
  182.  
  183. min_allowed_license_version = (unsigned int)strtoul(data, NULL, 10);
  184.  
  185. Sql_FreeResult(db_sql->accounts);
  186.  
  187. if (s->gepard_info.license_version >= min_allowed_license_version)
  188. {
  189. return 0;
  190. }
  191.  
  192. gepard_send_info(fd, GEPARD_INFO_OLD_LICENSE_VERSION, message_info_outdated_license);
  193. }
  194.  
  195. return 1;
  196. }
  197.  
  198. void account_gepard_update_last_unique_id(int account_id, unsigned int unique_id)
  199. {
  200. if (is_gepard_active == true)
  201. {
  202. return 0;
  203. }
  204.  
  205. if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "UPDATE `login` SET `last_unique_id`= '%u' WHERE `account_id` = '%d'", unique_id, account_id))
  206. {
  207. Sql_ShowDebug(db_sql->accounts);
  208. }
  209. else if (SQL_SUCCESS == Sql_NextRow(db_sql->accounts))
  210. {
  211. Sql_ShowDebug(db_sql->accounts);
  212. }
  213.  
  214. Sql_FreeResult(db_sql->accounts);
  215. }
  216.  
  217. bool account_gepard_check_unique_id(int fd, struct socket_data* s)
  218. {
  219. unsigned int unique_id = s->gepard_info.unique_id;
  220.  
  221. if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "SELECT `unban_time`, `reason` FROM `gepard_block` WHERE `unique_id` = '%u'", unique_id))
  222. {
  223. Sql_ShowDebug(db_sql->accounts);
  224. gepard_send_info(fd, GEPARD_INFO_MESSAGE, "Tell administrator about SQL problem.");
  225. }
  226. else if (SQL_SUCCESS == Sql_NextRow(db_sql->accounts))
  227. {
  228. char* data;
  229. struct tm unblock_tm;
  230. time_t time_unban, time_server;
  231. int year, month, day, hour, min, sec;
  232. char reason_str[GEPARD_REASON_LENGTH];
  233. char unban_time_str[GEPARD_TIME_STR_LENGTH];
  234.  
  235. memset((void*)&unblock_tm, 0, sizeof(unblock_tm));
  236.  
  237. Sql_GetData(db_sql->accounts, 0, &data, NULL);
  238. safestrncpy(unban_time_str, data, sizeof(unban_time_str));
  239.  
  240. sscanf(unban_time_str, "%d-%d-%d %d:%d:%d", &year, &month, &day, &hour, &min, &sec);
  241.  
  242. unblock_tm.tm_year = year - 1900;
  243. unblock_tm.tm_mon = month - 1;
  244. unblock_tm.tm_mday = day;
  245. unblock_tm.tm_hour = hour;
  246. unblock_tm.tm_min = min;
  247. unblock_tm.tm_sec = sec;
  248.  
  249. time_unban = mktime(&unblock_tm);
  250. time(&time_server);
  251.  
  252. if (time_server <= time_unban)
  253. {
  254. char message_info[200];
  255.  
  256. Sql_GetData(db_sql->accounts, 1, &data, NULL);
  257. safestrncpy(reason_str, data, sizeof(reason_str));
  258.  
  259. safesnprintf(message_info, sizeof(message_info), "Unique ID has been banned!\r\rDate of unban: %s\r\rUnique id: %u\r\rReason: %s", unban_time_str, unique_id, reason_str);
  260.  
  261. s->gepard_info.is_init_ack_received = false;
  262.  
  263. gepard_send_info(fd, GEPARD_INFO_BANNED, message_info);
  264. }
  265. else if (SQL_ERROR == Sql_Query(db_sql->accounts, "DELETE FROM `gepard_block` WHERE `unique_id` = '%u'", unique_id))
  266. {
  267. Sql_ShowDebug(db_sql->accounts);
  268. }
  269. }
  270.  
  271. Sql_FreeResult(db_sql->accounts);
  272.  
  273. return false;
  274. }
  275. // (^~_~^) Gepard Shield End
  276.  
  277. /// internal functions
  278. static bool account_db_sql_init(AccountDB* self);
  279. static void account_db_sql_destroy(AccountDB* self);
  280. static bool account_db_sql_get_property(AccountDB* self, const char* key, char* buf, size_t buflen);
  281. static bool account_db_sql_set_property(AccountDB* self, const char* option, const char* value);
  282. static bool account_db_sql_create(AccountDB* self, struct mmo_account* acc);
  283. static bool account_db_sql_remove(AccountDB* self, const int account_id);
  284. static bool account_db_sql_save(AccountDB* self, const struct mmo_account* acc);
  285. static bool account_db_sql_load_num(AccountDB* self, struct mmo_account* acc, const int account_id);
  286. static bool account_db_sql_load_str(AccountDB* self, struct mmo_account* acc, const char* userid);
  287. static AccountDBIterator* account_db_sql_iterator(AccountDB* self);
  288. static void account_db_sql_iter_destroy(AccountDBIterator* self);
  289. static bool account_db_sql_iter_next(AccountDBIterator* self, struct mmo_account* acc);
  290.  
  291. static bool mmo_auth_fromsql(AccountDB_SQL* db, struct mmo_account* acc, int account_id);
  292. static bool mmo_auth_tosql(AccountDB_SQL* db, const struct mmo_account* acc, bool is_new);
  293.  
  294. /// public constructor
  295. AccountDB* account_db_sql(void)
  296. {
  297. AccountDB_SQL* db = (AccountDB_SQL*)aCalloc(1, sizeof(AccountDB_SQL));
  298.  
  299. // set up the vtable
  300. db->vtable.init = &account_db_sql_init;
  301. db->vtable.destroy = &account_db_sql_destroy;
  302. db->vtable.get_property = &account_db_sql_get_property;
  303. db->vtable.set_property = &account_db_sql_set_property;
  304. db->vtable.save = &account_db_sql_save;
  305. db->vtable.create = &account_db_sql_create;
  306. db->vtable.remove = &account_db_sql_remove;
  307. db->vtable.load_num = &account_db_sql_load_num;
  308. db->vtable.load_str = &account_db_sql_load_str;
  309. db->vtable.iterator = &account_db_sql_iterator;
  310.  
  311. // initialize to default values
  312. db->accounts = NULL;
  313. // global sql settings
  314. safestrncpy(db->global_db_hostname, "127.0.0.1", sizeof(db->global_db_hostname));
  315. db->global_db_port = 3306;
  316. safestrncpy(db->global_db_username, "ragnarok", sizeof(db->global_db_username));
  317. safestrncpy(db->global_db_password, "ragnarok", sizeof(db->global_db_password));
  318. safestrncpy(db->global_db_database, "ragnarok", sizeof(db->global_db_database));
  319. safestrncpy(db->global_codepage, "", sizeof(db->global_codepage));
  320. // local sql settings
  321. safestrncpy(db->db_hostname, "", sizeof(db->db_hostname));
  322. db->db_port = 3306;
  323. safestrncpy(db->db_username, "", sizeof(db->db_username));
  324. safestrncpy(db->db_password, "", sizeof(db->db_password));
  325. safestrncpy(db->db_database, "", sizeof(db->db_database));
  326. safestrncpy(db->codepage, "", sizeof(db->codepage));
  327. // other settings
  328. db->case_sensitive = false;
  329. safestrncpy(db->account_db, "login", sizeof(db->account_db));
  330. safestrncpy(db->accreg_db, "global_reg_value", sizeof(db->accreg_db));
  331.  
  332. return &db->vtable;
  333. }
  334.  
  335.  
  336. /* ------------------------------------------------------------------------- */
  337.  
  338.  
  339. /// establishes database connection
  340. static bool account_db_sql_init(AccountDB* self)
  341. {
  342. AccountDB_SQL* db = (AccountDB_SQL*)self;
  343. Sql* sql_handle;
  344. const char* username;
  345. const char* password;
  346. const char* hostname;
  347. uint16 port;
  348. const char* database;
  349. const char* codepage;
  350.  
  351. db->accounts = Sql_Malloc();
  352. sql_handle = db->accounts;
  353.  
  354. if( db->db_hostname[0] != '\0' )
  355. {// local settings
  356. username = db->db_username;
  357. password = db->db_password;
  358. hostname = db->db_hostname;
  359. port = db->db_port;
  360. database = db->db_database;
  361. codepage = db->codepage;
  362. }
  363. else
  364. {// global settings
  365. username = db->global_db_username;
  366. password = db->global_db_password;
  367. hostname = db->global_db_hostname;
  368. port = db->global_db_port;
  369. database = db->global_db_database;
  370. codepage = db->global_codepage;
  371. }
  372.  
  373. if( SQL_ERROR == Sql_Connect(sql_handle, username, password, hostname, port, database) )
  374. {
  375. Sql_ShowDebug(sql_handle);
  376. Sql_Free(db->accounts);
  377. db->accounts = NULL;
  378. return false;
  379. }
  380.  
  381. if( codepage[0] != '\0' && SQL_ERROR == Sql_SetEncoding(sql_handle, codepage) )
  382. Sql_ShowDebug(sql_handle);
  383.  
  384. // (^~_~^) Gepard Shield Start
  385.  
  386. if (is_gepard_active == true)
  387. {
  388. account_gepard_init(self);
  389. }
  390.  
  391. // (^~_~^) Gepard Shield End
  392.  
  393. return true;
  394. }
  395.  
  396. /// disconnects from database
  397. static void account_db_sql_destroy(AccountDB* self)
  398. {
  399. AccountDB_SQL* db = (AccountDB_SQL*)self;
  400.  
  401. Sql_Free(db->accounts);
  402. db->accounts = NULL;
  403. aFree(db);
  404. }
  405.  
  406. /// Gets a property from this database.
  407. static bool account_db_sql_get_property(AccountDB* self, const char* key, char* buf, size_t buflen)
  408. {
  409. AccountDB_SQL* db = (AccountDB_SQL*)self;
  410. const char* signature;
  411.  
  412. signature = "engine.";
  413. if( strncmpi(key, signature, strlen(signature)) == 0 )
  414. {
  415. key += strlen(signature);
  416. if( strcmpi(key, "name") == 0 )
  417. safesnprintf(buf, buflen, "sql");
  418. else
  419. if( strcmpi(key, "version") == 0 )
  420. safesnprintf(buf, buflen, "%d", ACCOUNT_SQL_DB_VERSION);
  421. else
  422. if( strcmpi(key, "comment") == 0 )
  423. safesnprintf(buf, buflen, "SQL Account Database");
  424. else
  425. return false;// not found
  426. return true;
  427. }
  428.  
  429. signature = "sql.";
  430. if( strncmpi(key, signature, strlen(signature)) == 0 )
  431. {
  432. key += strlen(signature);
  433. if( strcmpi(key, "db_hostname") == 0 )
  434. safesnprintf(buf, buflen, "%s", db->global_db_hostname);
  435. else
  436. if( strcmpi(key, "db_port") == 0 )
  437. safesnprintf(buf, buflen, "%d", db->global_db_port);
  438. else
  439. if( strcmpi(key, "db_username") == 0 )
  440. safesnprintf(buf, buflen, "%s", db->global_db_username);
  441. else
  442. if( strcmpi(key, "db_password") == 0 )
  443. safesnprintf(buf, buflen, "%s", db->global_db_password);
  444. else
  445. if( strcmpi(key, "db_database") == 0 )
  446. safesnprintf(buf, buflen, "%s", db->global_db_database);
  447. else
  448. if( strcmpi(key, "codepage") == 0 )
  449. safesnprintf(buf, buflen, "%s", db->global_codepage);
  450. else
  451. return false;// not found
  452. return true;
  453. }
  454.  
  455. signature = "account.sql.";
  456. if( strncmpi(key, signature, strlen(signature)) == 0 )
  457. {
  458. key += strlen(signature);
  459. if( strcmpi(key, "db_hostname") == 0 )
  460. safesnprintf(buf, buflen, "%s", db->db_hostname);
  461. else
  462. if( strcmpi(key, "db_port") == 0 )
  463. safesnprintf(buf, buflen, "%d", db->db_port);
  464. else
  465. if( strcmpi(key, "db_username") == 0 )
  466. safesnprintf(buf, buflen, "%s", db->db_username);
  467. else
  468. if( strcmpi(key, "db_password") == 0 )
  469. safesnprintf(buf, buflen, "%s", db->db_password);
  470. else
  471. if( strcmpi(key, "db_database") == 0 )
  472. safesnprintf(buf, buflen, "%s", db->db_database);
  473. else
  474. if( strcmpi(key, "codepage") == 0 )
  475. safesnprintf(buf, buflen, "%s", db->codepage);
  476. else
  477. if( strcmpi(key, "case_sensitive") == 0 )
  478. safesnprintf(buf, buflen, "%d", (db->case_sensitive ? 1 : 0));
  479. else
  480. if( strcmpi(key, "account_db") == 0 )
  481. safesnprintf(buf, buflen, "%s", db->account_db);
  482. else
  483. if( strcmpi(key, "accreg_db") == 0 )
  484. safesnprintf(buf, buflen, "%s", db->accreg_db);
  485. else
  486. return false;// not found
  487. return true;
  488. }
  489.  
  490. return false;// not found
  491. }
  492.  
  493. /// if the option is supported, adjusts the internal state
  494. static bool account_db_sql_set_property(AccountDB* self, const char* key, const char* value)
  495. {
  496. AccountDB_SQL* db = (AccountDB_SQL*)self;
  497. const char* signature;
  498.  
  499.  
  500. signature = "sql.";
  501. if( strncmp(key, signature, strlen(signature)) == 0 )
  502. {
  503. key += strlen(signature);
  504. if( strcmpi(key, "db_hostname") == 0 )
  505. safestrncpy(db->global_db_hostname, value, sizeof(db->global_db_hostname));
  506. else
  507. if( strcmpi(key, "db_port") == 0 )
  508. db->global_db_port = (uint16)strtoul(value, NULL, 10);
  509. else
  510. if( strcmpi(key, "db_username") == 0 )
  511. safestrncpy(db->global_db_username, value, sizeof(db->global_db_username));
  512. else
  513. if( strcmpi(key, "db_password") == 0 )
  514. safestrncpy(db->global_db_password, value, sizeof(db->global_db_password));
  515. else
  516. if( strcmpi(key, "db_database") == 0 )
  517. safestrncpy(db->global_db_database, value, sizeof(db->global_db_database));
  518. else
  519. if( strcmpi(key, "codepage") == 0 )
  520. safestrncpy(db->global_codepage, value, sizeof(db->global_codepage));
  521. else
  522. return false;// not found
  523. return true;
  524. }
  525.  
  526. signature = "account.sql.";
  527. if( strncmp(key, signature, strlen(signature)) == 0 )
  528. {
  529. key += strlen(signature);
  530. if( strcmpi(key, "db_hostname") == 0 )
  531. safestrncpy(db->db_hostname, value, sizeof(db->db_hostname));
  532. else
  533. if( strcmpi(key, "db_port") == 0 )
  534. db->db_port = (uint16)strtoul(value, NULL, 10);
  535. else
  536. if( strcmpi(key, "db_username") == 0 )
  537. safestrncpy(db->db_username, value, sizeof(db->db_username));
  538. else
  539. if( strcmpi(key, "db_password") == 0 )
  540. safestrncpy(db->db_password, value, sizeof(db->db_password));
  541. else
  542. if( strcmpi(key, "db_database") == 0 )
  543. safestrncpy(db->db_database, value, sizeof(db->db_database));
  544. else
  545. if( strcmpi(key, "codepage") == 0 )
  546. safestrncpy(db->codepage, value, sizeof(db->codepage));
  547. else
  548. if( strcmpi(key, "case_sensitive") == 0 )
  549. db->case_sensitive = config_switch(value);
  550. else
  551. if( strcmpi(key, "account_db") == 0 )
  552. safestrncpy(db->account_db, value, sizeof(db->account_db));
  553. else
  554. if( strcmpi(key, "accreg_db") == 0 )
  555. safestrncpy(db->accreg_db, value, sizeof(db->accreg_db));
  556. else
  557. return false;// not found
  558. return true;
  559. }
  560.  
  561. return false;// not found
  562. }
  563.  
  564. /// create a new account entry
  565. /// If acc->account_id is -1, the account id will be auto-generated,
  566. /// and its value will be written to acc->account_id if everything succeeds.
  567. static bool account_db_sql_create(AccountDB* self, struct mmo_account* acc)
  568. {
  569. AccountDB_SQL* db = (AccountDB_SQL*)self;
  570. Sql* sql_handle = db->accounts;
  571.  
  572. // decide on the account id to assign
  573. int account_id;
  574. if( acc->account_id != -1 )
  575. {// caller specifies it manually
  576. account_id = acc->account_id;
  577. }
  578. else
  579. {// ask the database
  580. char* data;
  581. size_t len;
  582.  
  583. if( SQL_SUCCESS != Sql_Query(sql_handle, "SELECT MAX(`account_id`)+1 FROM `%s`", db->account_db) )
  584. {
  585. Sql_ShowDebug(sql_handle);
  586. return false;
  587. }
  588. if( SQL_SUCCESS != Sql_NextRow(sql_handle) )
  589. {
  590. Sql_ShowDebug(sql_handle);
  591. Sql_FreeResult(sql_handle);
  592. return false;
  593. }
  594.  
  595. Sql_GetData(sql_handle, 0, &data, &len);
  596. account_id = ( data != NULL ) ? atoi(data) : 0;
  597. Sql_FreeResult(sql_handle);
  598.  
  599. if( account_id < START_ACCOUNT_NUM )
  600. account_id = START_ACCOUNT_NUM;
  601.  
  602. }
  603.  
  604. // zero value is prohibited
  605. if( account_id == 0 )
  606. return false;
  607.  
  608. // absolute maximum
  609. if( account_id > END_ACCOUNT_NUM )
  610. return false;
  611.  
  612. // insert the data into the database
  613. acc->account_id = account_id;
  614. return mmo_auth_tosql(db, acc, true);
  615. }
  616.  
  617. /// delete an existing account entry + its regs
  618. static bool account_db_sql_remove(AccountDB* self, const int account_id)
  619. {
  620. AccountDB_SQL* db = (AccountDB_SQL*)self;
  621. Sql* sql_handle = db->accounts;
  622. bool result = false;
  623.  
  624. if( SQL_SUCCESS != Sql_QueryStr(sql_handle, "START TRANSACTION")
  625. || SQL_SUCCESS != Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `account_id` = %d", db->account_db, account_id)
  626. || SQL_SUCCESS != Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `account_id` = %d", db->accreg_db, account_id) )
  627. Sql_ShowDebug(sql_handle);
  628. else
  629. result = true;
  630.  
  631. result &= ( SQL_SUCCESS == Sql_QueryStr(sql_handle, (result == true) ? "COMMIT" : "ROLLBACK") );
  632.  
  633. return result;
  634. }
  635.  
  636. /// update an existing account with the provided new data (both account and regs)
  637. static bool account_db_sql_save(AccountDB* self, const struct mmo_account* acc)
  638. {
  639. AccountDB_SQL* db = (AccountDB_SQL*)self;
  640. return mmo_auth_tosql(db, acc, false);
  641. }
  642.  
  643. /// retrieve data from db and store it in the provided data structure
  644. static bool account_db_sql_load_num(AccountDB* self, struct mmo_account* acc, const int account_id)
  645. {
  646. AccountDB_SQL* db = (AccountDB_SQL*)self;
  647. return mmo_auth_fromsql(db, acc, account_id);
  648. }
  649.  
  650. /// retrieve data from db and store it in the provided data structure
  651. static bool account_db_sql_load_str(AccountDB* self, struct mmo_account* acc, const char* userid)
  652. {
  653. AccountDB_SQL* db = (AccountDB_SQL*)self;
  654. Sql* sql_handle = db->accounts;
  655. char esc_userid[2*NAME_LENGTH+1];
  656. int account_id;
  657. char* data;
  658.  
  659. Sql_EscapeString(sql_handle, esc_userid, userid);
  660.  
  661. // get the list of account IDs for this user ID
  662. if( SQL_ERROR == Sql_Query(sql_handle, "SELECT `account_id` FROM `%s` WHERE `userid`= %s '%s'",
  663. db->account_db, (db->case_sensitive ? "BINARY" : ""), esc_userid) )
  664. {
  665. Sql_ShowDebug(sql_handle);
  666. return false;
  667. }
  668.  
  669. if( Sql_NumRows(sql_handle) > 1 )
  670. {// serious problem - duplicit account
  671. ShowError("account_db_sql_load_str: multiple accounts found when retrieving data for account '%s'!\n", userid);
  672. Sql_FreeResult(sql_handle);
  673. return false;
  674. }
  675.  
  676. if( SQL_SUCCESS != Sql_NextRow(sql_handle) )
  677. {// no such entry
  678. Sql_FreeResult(sql_handle);
  679. return false;
  680. }
  681.  
  682. Sql_GetData(sql_handle, 0, &data, NULL);
  683. account_id = atoi(data);
  684.  
  685. return account_db_sql_load_num(self, acc, account_id);
  686. }
  687.  
  688.  
  689. /// Returns a new forward iterator.
  690. static AccountDBIterator* account_db_sql_iterator(AccountDB* self)
  691. {
  692. AccountDB_SQL* db = (AccountDB_SQL*)self;
  693. AccountDBIterator_SQL* iter = (AccountDBIterator_SQL*)aCalloc(1, sizeof(AccountDBIterator_SQL));
  694.  
  695. // set up the vtable
  696. iter->vtable.destroy = &account_db_sql_iter_destroy;
  697. iter->vtable.next = &account_db_sql_iter_next;
  698.  
  699. // fill data
  700. iter->db = db;
  701. iter->last_account_id = -1;
  702.  
  703. return &iter->vtable;
  704. }
  705.  
  706.  
  707. /// Destroys this iterator, releasing all allocated memory (including itself).
  708. static void account_db_sql_iter_destroy(AccountDBIterator* self)
  709. {
  710. AccountDBIterator_SQL* iter = (AccountDBIterator_SQL*)self;
  711. aFree(iter);
  712. }
  713.  
  714.  
  715. /// Fetches the next account in the database.
  716. static bool account_db_sql_iter_next(AccountDBIterator* self, struct mmo_account* acc)
  717. {
  718. AccountDBIterator_SQL* iter = (AccountDBIterator_SQL*)self;
  719. AccountDB_SQL* db = (AccountDB_SQL*)iter->db;
  720. Sql* sql_handle = db->accounts;
  721. int account_id;
  722. char* data;
  723.  
  724. // get next account ID
  725. if( SQL_ERROR == Sql_Query(sql_handle, "SELECT `account_id` FROM `%s` WHERE `account_id` > '%d' ORDER BY `account_id` ASC LIMIT 1",
  726. db->account_db, iter->last_account_id) )
  727. {
  728. Sql_ShowDebug(sql_handle);
  729. return false;
  730. }
  731.  
  732. if( SQL_SUCCESS == Sql_NextRow(sql_handle) &&
  733. SQL_SUCCESS == Sql_GetData(sql_handle, 0, &data, NULL) &&
  734. data != NULL )
  735. {// get account data
  736. account_id = atoi(data);
  737. if( mmo_auth_fromsql(db, acc, account_id) )
  738. {
  739. iter->last_account_id = account_id;
  740. Sql_FreeResult(sql_handle);
  741. return true;
  742. }
  743. }
  744. Sql_FreeResult(sql_handle);
  745. return false;
  746. }
  747.  
  748.  
  749. static bool mmo_auth_fromsql(AccountDB_SQL* db, struct mmo_account* acc, int account_id)
  750. {
  751. Sql* sql_handle = db->accounts;
  752. char* data;
  753. int i = 0;
  754.  
  755. // retrieve login entry for the specified account
  756. if( SQL_ERROR == Sql_Query(sql_handle,
  757. "SELECT `account_id`,`userid`,`user_pass`,`sex`,`email`,`level`,`state`,`unban_time`,`expiration_time`,`logincount`,`lastlogin`,`last_ip`,`birthdate` FROM `%s` WHERE `account_id` = %d",
  758. db->account_db, account_id )
  759. ) {
  760. Sql_ShowDebug(sql_handle);
  761. return false;
  762. }
  763.  
  764. if( SQL_SUCCESS != Sql_NextRow(sql_handle) )
  765. {// no such entry
  766. Sql_FreeResult(sql_handle);
  767. return false;
  768. }
  769.  
  770. Sql_GetData(sql_handle, 0, &data, NULL); acc->account_id = atoi(data);
  771. Sql_GetData(sql_handle, 1, &data, NULL); safestrncpy(acc->userid, data, sizeof(acc->userid));
  772. Sql_GetData(sql_handle, 2, &data, NULL); safestrncpy(acc->pass, data, sizeof(acc->pass));
  773. Sql_GetData(sql_handle, 3, &data, NULL); acc->sex = data[0];
  774. Sql_GetData(sql_handle, 4, &data, NULL); safestrncpy(acc->email, data, sizeof(acc->email));
  775. Sql_GetData(sql_handle, 5, &data, NULL); acc->level = atoi(data);
  776. Sql_GetData(sql_handle, 6, &data, NULL); acc->state = strtoul(data, NULL, 10);
  777. Sql_GetData(sql_handle, 7, &data, NULL); acc->unban_time = atol(data);
  778. Sql_GetData(sql_handle, 8, &data, NULL); acc->expiration_time = atol(data);
  779. Sql_GetData(sql_handle, 9, &data, NULL); acc->logincount = strtoul(data, NULL, 10);
  780. Sql_GetData(sql_handle, 10, &data, NULL); safestrncpy(acc->lastlogin, data, sizeof(acc->lastlogin));
  781. Sql_GetData(sql_handle, 11, &data, NULL); safestrncpy(acc->last_ip, data, sizeof(acc->last_ip));
  782. Sql_GetData(sql_handle, 12, &data, NULL); safestrncpy(acc->birthdate, data, sizeof(acc->birthdate));
  783.  
  784. Sql_FreeResult(sql_handle);
  785.  
  786.  
  787. // retrieve account regs for the specified user
  788. if( SQL_ERROR == Sql_Query(sql_handle, "SELECT `str`,`value` FROM `%s` WHERE `type`='1' AND `account_id`='%d'", db->accreg_db, acc->account_id) )
  789. {
  790. Sql_ShowDebug(sql_handle);
  791. return false;
  792. }
  793.  
  794. acc->account_reg2_num = (int)Sql_NumRows(sql_handle);
  795.  
  796. while( SQL_SUCCESS == Sql_NextRow(sql_handle) )
  797. {
  798. char* data;
  799. Sql_GetData(sql_handle, 0, &data, NULL); safestrncpy(acc->account_reg2[i].str, data, sizeof(acc->account_reg2[i].str));
  800. Sql_GetData(sql_handle, 1, &data, NULL); safestrncpy(acc->account_reg2[i].value, data, sizeof(acc->account_reg2[i].value));
  801. ++i;
  802. }
  803. Sql_FreeResult(sql_handle);
  804.  
  805. if( i != acc->account_reg2_num )
  806. return false;
  807.  
  808. return true;
  809. }
  810.  
  811. static bool mmo_auth_tosql(AccountDB_SQL* db, const struct mmo_account* acc, bool is_new)
  812. {
  813. Sql* sql_handle = db->accounts;
  814. SqlStmt* stmt = SqlStmt_Malloc(sql_handle);
  815. bool result = false;
  816. int i;
  817.  
  818. // try
  819. do
  820. {
  821.  
  822. if( SQL_SUCCESS != Sql_QueryStr(sql_handle, "START TRANSACTION") )
  823. {
  824. Sql_ShowDebug(sql_handle);
  825. break;
  826. }
  827.  
  828. if( is_new )
  829. {// insert into account table
  830. if( SQL_SUCCESS != SqlStmt_Prepare(stmt,
  831. "INSERT INTO `%s` (`account_id`, `userid`, `user_pass`, `sex`, `email`, `level`, `state`, `unban_time`, `expiration_time`, `logincount`, `lastlogin`, `last_ip`, `birthdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
  832. db->account_db)
  833. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 0, SQLDT_INT, (void*)&acc->account_id, sizeof(acc->account_id))
  834. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 1, SQLDT_STRING, (void*)acc->userid, strlen(acc->userid))
  835. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 2, SQLDT_STRING, (void*)acc->pass, strlen(acc->pass))
  836. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 3, SQLDT_ENUM, (void*)&acc->sex, sizeof(acc->sex))
  837. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 4, SQLDT_STRING, (void*)&acc->email, strlen(acc->email))
  838. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 5, SQLDT_INT, (void*)&acc->level, sizeof(acc->level))
  839. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 6, SQLDT_UINT, (void*)&acc->state, sizeof(acc->state))
  840. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 7, SQLDT_LONG, (void*)&acc->unban_time, sizeof(acc->unban_time))
  841. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 8, SQLDT_INT, (void*)&acc->expiration_time, sizeof(acc->expiration_time))
  842. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 9, SQLDT_UINT, (void*)&acc->logincount, sizeof(acc->logincount))
  843. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 10, SQLDT_STRING, (void*)&acc->lastlogin, strlen(acc->lastlogin))
  844. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 11, SQLDT_STRING, (void*)&acc->last_ip, strlen(acc->last_ip))
  845. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 12, SQLDT_STRING, (void*)&acc->birthdate, strlen(acc->birthdate))
  846. || SQL_SUCCESS != SqlStmt_Execute(stmt)
  847. ) {
  848. SqlStmt_ShowDebug(stmt);
  849. break;
  850. }
  851. }
  852. else
  853. {// update account table
  854. if( SQL_SUCCESS != SqlStmt_Prepare(stmt, "UPDATE `%s` SET `userid`=?,`user_pass`=?,`sex`=?,`email`=?,`level`=?,`state`=?,`unban_time`=?,`expiration_time`=?,`logincount`=?,`lastlogin`=?,`last_ip`=?,`birthdate`=? WHERE `account_id` = '%d'", db->account_db, acc->account_id)
  855. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 0, SQLDT_STRING, (void*)acc->userid, strlen(acc->userid))
  856. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 1, SQLDT_STRING, (void*)acc->pass, strlen(acc->pass))
  857. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 2, SQLDT_ENUM, (void*)&acc->sex, sizeof(acc->sex))
  858. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 3, SQLDT_STRING, (void*)acc->email, strlen(acc->email))
  859. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 4, SQLDT_INT, (void*)&acc->level, sizeof(acc->level))
  860. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 5, SQLDT_UINT, (void*)&acc->state, sizeof(acc->state))
  861. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 6, SQLDT_LONG, (void*)&acc->unban_time, sizeof(acc->unban_time))
  862. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 7, SQLDT_LONG, (void*)&acc->expiration_time, sizeof(acc->expiration_time))
  863. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 8, SQLDT_UINT, (void*)&acc->logincount, sizeof(acc->logincount))
  864. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 9, SQLDT_STRING, (void*)&acc->lastlogin, strlen(acc->lastlogin))
  865. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 10, SQLDT_STRING, (void*)&acc->last_ip, strlen(acc->last_ip))
  866. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 11, SQLDT_STRING, (void*)&acc->birthdate, strlen(acc->birthdate))
  867. || SQL_SUCCESS != SqlStmt_Execute(stmt)
  868. ) {
  869. SqlStmt_ShowDebug(stmt);
  870. break;
  871. }
  872. }
  873.  
  874. // remove old account regs
  875. if( SQL_SUCCESS != Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `type`='1' AND `account_id`='%d'", db->accreg_db, acc->account_id) )
  876. {
  877. Sql_ShowDebug(sql_handle);
  878. break;
  879. }
  880. // insert new account regs
  881. if( SQL_SUCCESS != SqlStmt_Prepare(stmt, "INSERT INTO `%s` (`type`, `account_id`, `str`, `value`) VALUES ( 1 , '%d' , ? , ? );", db->accreg_db, acc->account_id) )
  882. {
  883. SqlStmt_ShowDebug(stmt);
  884. break;
  885. }
  886. for( i = 0; i < acc->account_reg2_num; ++i )
  887. {
  888. if( SQL_SUCCESS != SqlStmt_BindParam(stmt, 0, SQLDT_STRING, (void*)acc->account_reg2[i].str, strlen(acc->account_reg2[i].str))
  889. || SQL_SUCCESS != SqlStmt_BindParam(stmt, 1, SQLDT_STRING, (void*)acc->account_reg2[i].value, strlen(acc->account_reg2[i].value))
  890. || SQL_SUCCESS != SqlStmt_Execute(stmt)
  891. ) {
  892. SqlStmt_ShowDebug(stmt);
  893. break;
  894. }
  895. }
  896. if( i < acc->account_reg2_num )
  897. {
  898. result = false;
  899. break;
  900. }
  901.  
  902. // if we got this far, everything was successful
  903. result = true;
  904.  
  905. } while(0);
  906. // finally
  907.  
  908. result &= ( SQL_SUCCESS == Sql_QueryStr(sql_handle, (result == true) ? "COMMIT" : "ROLLBACK") );
  909. SqlStmt_Free(stmt);
  910.  
  911. return result;
  912. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement