PageRenderTime 52ms CodeModel.GetById 22ms RepoModel.GetById 1ms app.codeStats 0ms

/projects/marauroa-3.8.1/src/marauroa/server/game/db/LoginEventDAO.java

https://gitlab.com/essere.lab.public/qualitas.class-corpus
Java | 523 lines | 249 code | 59 blank | 215 comment | 12 complexity | 228757640d87f7adc7d55b0da4c3d6b5 MD5 | raw file
  1. /* $Id: LoginEventDAO.java,v 1.20 2010/03/15 18:54:46 nhnb Exp $ */
  2. /***************************************************************************
  3. * (C) Copyright 2003-2009 - Marauroa *
  4. ***************************************************************************
  5. ***************************************************************************
  6. * *
  7. * This program is free software; you can redistribute it and/or modify *
  8. * it under the terms of the GNU General Public License as published by *
  9. * the Free Software Foundation; either version 2 of the License, or *
  10. * (at your option) any later version. *
  11. * *
  12. ***************************************************************************/
  13. package marauroa.server.game.db;
  14. import java.net.InetAddress;
  15. import java.sql.ResultSet;
  16. import java.sql.SQLException;
  17. import java.sql.Timestamp;
  18. import java.util.Calendar;
  19. import java.util.GregorianCalendar;
  20. import java.util.HashMap;
  21. import java.util.LinkedList;
  22. import java.util.List;
  23. import java.util.Map;
  24. import marauroa.common.Log4J;
  25. import marauroa.common.Pair;
  26. import marauroa.common.TimeoutConf;
  27. import marauroa.server.db.DBTransaction;
  28. import marauroa.server.db.TransactionPool;
  29. /**
  30. * data access object for login events
  31. *
  32. * @author miguel, hendrik
  33. */
  34. public class LoginEventDAO {
  35. private static final marauroa.common.Logger logger = Log4J.getLogger(LoginEventDAO.class);
  36. /**
  37. * Creates a new LoginEventDAO
  38. */
  39. protected LoginEventDAO() {
  40. // hide constructor as this class should only be instantiated by DAORegister
  41. }
  42. /**
  43. * logs an login attempt
  44. *
  45. * @param transaction DBTransaction
  46. * @param username username
  47. * @param source ip-address
  48. * @param correctLogin true, if the login was succesful; false otherwise
  49. * @throws SQLException in case of an database error
  50. */
  51. @Deprecated
  52. public void addLoginEvent(DBTransaction transaction, String username, InetAddress source, boolean correctLogin) throws SQLException {
  53. addLoginEvent(transaction, username, source, null, null, correctLogin);
  54. }
  55. /**
  56. * logs an login attempt
  57. *
  58. * @param transaction DBTransaction
  59. * @param username username
  60. * @param source ip-address
  61. * @param seed seed
  62. * @param correctLogin true, if the login was succesful; false otherwise
  63. * @throws SQLException in case of an database error
  64. */
  65. @Deprecated
  66. public void addLoginEvent(DBTransaction transaction, String username, InetAddress source, String seed, boolean correctLogin) throws SQLException {
  67. addLoginEvent(transaction, username, source, null, seed, correctLogin);
  68. }
  69. /**
  70. * logs an login attempt
  71. *
  72. * @param transaction DBTransaction
  73. * @param username username
  74. * @param source ip-address
  75. * @param service name of service
  76. * @param seed seed
  77. * @param correctLogin true, if the login was succesful; false otherwise
  78. * @throws SQLException in case of an database error
  79. */
  80. public void addLoginEvent(DBTransaction transaction, String username, InetAddress source, String service, String seed, boolean correctLogin) throws SQLException {
  81. try {
  82. int id = DAORegister.get().get(AccountDAO.class).getDatabasePlayerId(transaction, username);
  83. // Note: playerId == -1 means that the player does not exist. We log this anyway to
  84. // be able to notice if someone tries to hack accounts by picking a fixed password
  85. // and bruteforcing matching usernames.
  86. String query = "insert into loginEvent(player_id, address, service, seed, result)"
  87. + " values ([player_id], '[address]', '[service]', '[seed]', [result])";
  88. Map<String, Object> params = new HashMap<String, Object>();
  89. params.put("player_id", Integer.valueOf(id));
  90. params.put("address", source.getHostAddress());
  91. params.put("result", Integer.valueOf(correctLogin ? 1 : 0));
  92. if (service != null) {
  93. params.put("service", service);
  94. }
  95. if (seed == null) {
  96. seed = "";
  97. }
  98. params.put("seed", seed);
  99. transaction.execute(query, params);
  100. } catch (SQLException e) {
  101. logger.error("Can't query for player \"" + username + "\"", e);
  102. throw e;
  103. }
  104. }
  105. /** Class to store the login events */
  106. public static class LoginEvent {
  107. private long id = -1;
  108. private long playerId = -1;
  109. private String service = null;
  110. /** TCP/IP address of the source of the login message */
  111. public String address;
  112. /** Time and date of the login event */
  113. public String date;
  114. /** True if login was correct */
  115. public boolean correct;
  116. /**
  117. * Creates a new LoginEvent object
  118. *
  119. * @param address
  120. * the address from where the login was tried
  121. * @param date
  122. * the date at which login was tried
  123. * @param sucessful
  124. * true if login was sucessful
  125. */
  126. public LoginEvent(String address, String date, boolean sucessful) {
  127. this.address = address;
  128. this.date = date;
  129. this.correct = sucessful;
  130. }
  131. /**
  132. * Creates a new LoginEvent object
  133. *
  134. * @param id database id
  135. * @param playerId database id of account
  136. * @param service name of service
  137. * @param address the address from where the login was tried
  138. * @param date the date at which login was tried
  139. * @param sucessful true, if the attempt was successful; false otherwise
  140. */
  141. public LoginEvent(long id, long playerId, String service,
  142. String address, String date, boolean sucessful) {
  143. this(address, date, sucessful);
  144. this.id = id;
  145. this.playerId = playerId;
  146. this.service = service;
  147. }
  148. /**
  149. * This method returns a String that represent the object.
  150. *
  151. * @return a string representing the object.
  152. */
  153. @Override
  154. public String toString() {
  155. return "Login " + (correct ? "successful" : "FAILED") + " at " + date + " from " + address;
  156. }
  157. /**
  158. * gets the id of this database row
  159. *
  160. * @return id or -1
  161. */
  162. public long getId() {
  163. return id;
  164. }
  165. /**
  166. * gets the id of the account
  167. *
  168. * @return player_id or -1
  169. */
  170. public long getPlayerId() {
  171. return playerId;
  172. }
  173. /**
  174. * gets the name of the service
  175. *
  176. * @return id or <code>null</code>
  177. */
  178. public String getService() {
  179. return service;
  180. }
  181. /**
  182. * gets the ip-address
  183. *
  184. * @return ip-address
  185. */
  186. public String getAddress() {
  187. return address;
  188. }
  189. /**
  190. * gets the timestamp of the login attempt
  191. *
  192. * @return timestamp
  193. */
  194. public String getDate() {
  195. return date;
  196. }
  197. /**
  198. * was this a successful login attempt?
  199. *
  200. * @return true, if the attempt was successful; false otherwise
  201. */
  202. public boolean isSuccessful() {
  203. return correct;
  204. }
  205. }
  206. /**
  207. * gets a list of recent login events
  208. *
  209. * @param transaction DBTransaction
  210. * @param username username
  211. * @param events number of events
  212. * @return list of login attempts
  213. * @throws SQLException in case of an database error
  214. */
  215. public List<String> getLoginEvents(DBTransaction transaction, String username, int events) throws SQLException {
  216. try {
  217. int id = DAORegister.get().get(AccountDAO.class).getDatabasePlayerId(transaction, username);
  218. if (id == -1) {
  219. /*
  220. * This should never happen as we should check previously that
  221. * player exists...
  222. */
  223. throw new SQLException("Unable to find player(" + username + ")");
  224. }
  225. String query = "select address, timedate, result from loginEvent where player_id=[player_id]"
  226. + " order by timedate desc limit [events]";
  227. logger.debug("getLoginEvents is executing query " + query);
  228. Map<String, Object> params = new HashMap<String, Object>();
  229. params.put("player_id", Integer.valueOf(id));
  230. params.put("events", Integer.valueOf(events));
  231. ResultSet resultSet = transaction.query(query, params);
  232. List<String> list = new LinkedList<String>();
  233. while (resultSet.next()) {
  234. LoginEvent event = new LoginEvent(resultSet.getString("address"), resultSet
  235. .getString("timedate"), resultSet.getBoolean("result"));
  236. list.add(event.toString());
  237. }
  238. resultSet.close();
  239. return list;
  240. } catch (SQLException e) {
  241. logger.error("Can't query for player \"" + username + "\"", e);
  242. throw e;
  243. }
  244. }
  245. /**
  246. * gets the last successful login event
  247. *
  248. * @param transaction DBTransaction
  249. * @param playerId accountId
  250. * @param service name of service, may be <code>null</code> for all
  251. * @return last succesful login event
  252. * @throws SQLException in case of an database error
  253. */
  254. public LoginEvent getLastSuccessfulLoginEvent(DBTransaction transaction, int playerId, String service) throws SQLException {
  255. try {
  256. String serviceQuery = "";
  257. if (service != null) {
  258. serviceQuery = " AND service='[service]'";
  259. }
  260. String query = "SELECT id, player_id, service, address, timedate, result FROM loginEvent"
  261. + " WHERE player_id=[player_id] AND result=1" + serviceQuery + " ORDER BY timedate desc LIMIT 2";
  262. Map<String, Object> params = new HashMap<String, Object>();
  263. params.put("service", service);
  264. params.put("player_id", Integer.toString(playerId));
  265. logger.debug("getLastSuccessfulLoginEvent is executing query " + query);
  266. ResultSet resultSet = transaction.query(query, params);
  267. // go to this login
  268. if (!resultSet.next()) {
  269. resultSet.close();
  270. return null;
  271. }
  272. // go to the last login
  273. if (!resultSet.next()) {
  274. resultSet.close();
  275. return null;
  276. }
  277. LoginEvent event = new LoginEvent(resultSet.getLong("id"),
  278. resultSet.getLong("player_id"), resultSet.getString("service"),
  279. resultSet.getString("address"), resultSet.getString("timedate"),
  280. resultSet.getBoolean("result"));
  281. resultSet.close();
  282. return event;
  283. } catch (SQLException e) {
  284. logger.error("Can't query for player \"" + playerId + "\"", e);
  285. throw e;
  286. }
  287. }
  288. /**
  289. * gets the amount of failed login attemps
  290. *
  291. * @param transaction DBTransaction
  292. * @param id only look for events younger than this id
  293. * @param playerId accountId
  294. * @return amount of failed login attempts grouped by service name
  295. * @throws SQLException in case of an database error
  296. */
  297. public List<Pair<String, Long>> getAmountOfFailedLogins(DBTransaction transaction, long id, int playerId) throws SQLException {
  298. try {
  299. String query = "SELECT service, count(*) FROM loginEvent"
  300. + " WHERE player_id=[player_id] + AND id > [id] AND result = 0 GROUP BY service";
  301. Map<String, Object> params = new HashMap<String, Object>();
  302. params.put("id", Long.toString(id));
  303. params.put("player_id", Integer.toString(playerId));
  304. logger.debug("getAmountOfFailedLogins is executing query " + query);
  305. ResultSet resultSet = transaction.query(query, params);
  306. List<Pair<String, Long>> list = new LinkedList<Pair<String, Long>>();
  307. while (resultSet.next()) {
  308. list.add(new Pair<String, Long>(resultSet.getString(1), Long.valueOf(resultSet.getLong(2))));
  309. }
  310. resultSet.close();
  311. return list;
  312. } catch (SQLException e) {
  313. logger.error("Can't query for player \"" + playerId + "\"", e);
  314. throw e;
  315. }
  316. }
  317. /**
  318. * checks if this account is temporary blocked because of too many failed login attempts.
  319. * Blocking accounts is not related to banning accounts.
  320. *
  321. * @param transaction DBTransaction
  322. * @param username username
  323. * @return true, if this account is blocked; false otherwise
  324. * @throws SQLException in case of an database error
  325. */
  326. public boolean isAccountBlocked(DBTransaction transaction, String username) throws SQLException {
  327. String query = "SELECT count(*) as amount FROM loginEvent, account"
  328. + " WHERE loginEvent.player_id=account.id"
  329. + " AND username='[username]'"
  330. + " AND loginEvent.result=0 and loginEvent.timedate > '[timestamp]'";
  331. Map<String, Object> params = new HashMap<String, Object>();
  332. params.put("username", username);
  333. Calendar calendar = new GregorianCalendar();
  334. calendar.add(Calendar.SECOND, -1 * TimeoutConf.FAILED_LOGIN_BLOCKTIME);
  335. params.put("timestamp", new Timestamp(calendar.getTimeInMillis()).toString());
  336. int attemps = transaction.querySingleCellInt(query, params);
  337. return attemps > TimeoutConf.FAILED_LOGIN_ATTEMPTS_ACCOUNT;
  338. }
  339. /**
  340. * checks if the ip-address is temporary blocked because of too many failed login attempts.
  341. * Blocking ip-addresses is not related to banning ip-addresses.
  342. *
  343. * @param transaction DBTransaction
  344. * @param address ip-address
  345. * @return true, if this address is blocked; false otherwise
  346. * @throws SQLException in case of an database error
  347. */
  348. public boolean isAddressBlocked(DBTransaction transaction, String address) throws SQLException {
  349. String query = "SELECT count(*) as amount FROM loginEvent"
  350. + " WHERE address='[address]'"
  351. + " AND result=0 and timedate > '[timestamp]'";
  352. Map<String, Object> params = new HashMap<String, Object>();
  353. params.put("address", address);
  354. Calendar calendar = new GregorianCalendar();
  355. calendar.add(Calendar.SECOND, -1 * TimeoutConf.FAILED_LOGIN_BLOCKTIME);
  356. params.put("timestamp", new Timestamp(calendar.getTimeInMillis()).toString());
  357. int attemps = transaction.querySingleCellInt(query, params);
  358. return attemps > TimeoutConf.FAILED_LOGIN_ATTEMPTS_IP;
  359. }
  360. /**
  361. * logs an login attempt
  362. *
  363. * @param username username
  364. * @param source ip-address
  365. * @param correctLogin true, if the login was succesful; false otherwise
  366. * @throws SQLException in case of an database error
  367. */
  368. @Deprecated
  369. public void addLoginEvent(String username, InetAddress source, boolean correctLogin) throws SQLException {
  370. DBTransaction transaction = TransactionPool.get().beginWork();
  371. try {
  372. addLoginEvent(transaction, username, source, null, null, correctLogin);
  373. } finally {
  374. TransactionPool.get().commit(transaction);
  375. }
  376. }
  377. /**
  378. * logs an login attempt
  379. *
  380. * @param seed a seed to log
  381. * @param username username
  382. * @param source ip-address
  383. * @param correctLogin true, if the login was succesful; false otherwise
  384. * @throws SQLException in case of an database error
  385. */
  386. @Deprecated
  387. public void addLoginEvent(String username, InetAddress source, String seed, boolean correctLogin) throws SQLException {
  388. DBTransaction transaction = TransactionPool.get().beginWork();
  389. try {
  390. addLoginEvent(transaction, username, source, null, seed, correctLogin);
  391. } finally {
  392. TransactionPool.get().commit(transaction);
  393. }
  394. }
  395. /**
  396. * logs an login attempt
  397. *
  398. * @param username username
  399. * @param source ip-address
  400. * @param service name of service
  401. * @param seed seed
  402. * @param correctLogin true, if the login was succesful; false otherwise
  403. * @throws SQLException in case of an database error
  404. */
  405. public void addLoginEvent(String username, InetAddress source, String service, String seed, boolean correctLogin) throws SQLException {
  406. DBTransaction transaction = TransactionPool.get().beginWork();
  407. try {
  408. addLoginEvent(transaction, username, source, service, seed, correctLogin);
  409. } finally {
  410. TransactionPool.get().commit(transaction);
  411. }
  412. }
  413. /**
  414. * gets a list of recent login events
  415. *
  416. * @param username username
  417. * @param events number of events
  418. * @return list of login attempts
  419. * @throws SQLException in case of an database error
  420. */
  421. public List<String> getLoginEvents(String username, int events) throws SQLException {
  422. DBTransaction transaction = TransactionPool.get().beginWork();
  423. try {
  424. List<String> res = getLoginEvents(transaction, username, events);
  425. return res;
  426. } finally {
  427. TransactionPool.get().commit(transaction);
  428. }
  429. }
  430. /**
  431. * checks if this account is temporary blocked because of too many failed login attempts.
  432. * Blocking accounts is not related to banning accounts.
  433. *
  434. * @param username username
  435. * @return true, if this account is blocked; false otherwise
  436. * @throws SQLException in case of an database error
  437. */
  438. public boolean isAccountBlocked(String username) throws SQLException {
  439. DBTransaction transaction = TransactionPool.get().beginWork();
  440. try {
  441. boolean res = isAccountBlocked(transaction, username);
  442. return res;
  443. } finally {
  444. TransactionPool.get().commit(transaction);
  445. }
  446. }
  447. /**
  448. * checks if the ip-address is temporary blocked because of too many failed login attempts.
  449. * Blocking ip-addresses is not related to banning ip-addresses.
  450. *
  451. * @param address ip-address
  452. * @return true, if this address is blocked; false otherwise
  453. * @throws SQLException in case of an database error
  454. */
  455. public boolean isAddressBlocked(String address) throws SQLException {
  456. DBTransaction transaction = TransactionPool.get().beginWork();
  457. try {
  458. boolean res = isAddressBlocked(transaction, address);
  459. return res;
  460. } finally {
  461. TransactionPool.get().commit(transaction);
  462. }
  463. }
  464. }