Package marauroa.server.game.db

Source Code of marauroa.server.game.db.LoginEventDAO$LoginEvent

/* $Id: LoginEventDAO.java,v 1.20 2010/03/15 18:54:46 nhnb Exp $ */
/***************************************************************************
*                   (C) Copyright 2003-2009 - Marauroa                    *
***************************************************************************
***************************************************************************
*                                                                         *
*   This program is free software; you can redistribute it and/or modify  *
*   it under the terms of the GNU General Public License as published by  *
*   the Free Software Foundation; either version 2 of the License, or     *
*   (at your option) any later version.                                   *
*                                                                         *
***************************************************************************/
package marauroa.server.game.db;

import java.net.InetAddress;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import marauroa.common.Log4J;
import marauroa.common.Pair;
import marauroa.common.TimeoutConf;
import marauroa.server.db.DBTransaction;
import marauroa.server.db.TransactionPool;

/**
* data access object for login events
*
* @author miguel, hendrik
*/
public class LoginEventDAO {
  private static final marauroa.common.Logger logger = Log4J.getLogger(LoginEventDAO.class);

  /**
   * Creates a new LoginEventDAO
   */
  protected LoginEventDAO() {
    // hide constructor as this class should only be instantiated by DAORegister
  }

  /**
   * logs an login attempt
   *
   * @param transaction DBTransaction
   * @param username username
   * @param source ip-address
   * @param correctLogin true, if the login was successful; false otherwise
   * @throws SQLException in case of an database error
   */
  @Deprecated
  public void addLoginEvent(DBTransaction transaction, String username, InetAddress source, boolean correctLogin) throws SQLException {
    addLoginEvent(transaction, username, source, null, null, correctLogin);
  }

  /**
   * logs an login attempt
   *
   * @param transaction DBTransaction
   * @param username username
   * @param source ip-address
   * @param seed seed
   * @param correctLogin true, if the login was successful; false otherwise
   * @throws SQLException in case of an database error
   */
  @Deprecated
  public void addLoginEvent(DBTransaction transaction, String username, InetAddress source, String seed, boolean correctLogin) throws SQLException {
    addLoginEvent(transaction, username, source, null, seed, correctLogin);
  }

  /**
   * logs an login attempt
   *
   * @param transaction DBTransaction
   * @param username username
   * @param source ip-address
   * @param service name of service
   * @param seed seed
   * @param correctLogin true, if the login was successful; false otherwise
   * @throws SQLException in case of an database error
   */
  public void addLoginEvent(DBTransaction transaction, String username, InetAddress source, String service, String seed, boolean correctLogin) throws SQLException {

    try {
      int id = DAORegister.get().get(AccountDAO.class).getDatabasePlayerId(transaction, username);
      // Note: playerId == -1 means that the player does not exist. We log this anyway to
      // be able to notice if someone tries to hack accounts by picking  a fixed password
      // and bruteforcing matching usernames.

      String query = "insert into loginEvent(player_id, address, service, seed, result)"
        + " values ([player_id], '[address]', '[service]', '[seed]', [result])";
      Map<String, Object> params = new HashMap<String, Object>();
      params.put("player_id", Integer.valueOf(id));
      params.put("address", source.getHostAddress());
      params.put("result", Integer.valueOf(correctLogin ? 1 : 0));
      if (service != null) {
        params.put("service", service);
      }
      if (seed == null) {
          params.put("seed", "");
      } else {
          params.put("seed", seed);
      }
      transaction.execute(query, params);
    } catch (SQLException e) {
      logger.error("Can't query for player \"" + username + "\"", e);
      throw e;
    }
  }

  /** Class to store the login events */
  public static class LoginEvent {

    private long id = -1;
   
    private long playerId = -1;
   
    private String service = null;

    /** TCP/IP address of the source of the login message */
    public String address;

    /** Time and date of the login event */
    public String date;

    /** True if login was correct */
    public boolean correct;

    /**
     * Creates a new LoginEvent object
     *
     * @param address
     *            the address from where the login was tried
     * @param date
     *            the date at which login was tried
     * @param sucessful
     *            true if login was sucessful
     */
    public LoginEvent(String address, String date, boolean sucessful) {
      this.address = address;
      this.date = date;
      this.correct = sucessful;
    }

    /**
     * Creates a new LoginEvent object
     *
     * @param id database id
     * @param playerId database id of account
     * @param service name of service
     * @param address the address from where the login was tried
     * @param date the date at which login was tried
     * @param sucessful true, if the attempt was successful; false otherwise
     */
    public LoginEvent(long id, long playerId, String service,
        String address, String date, boolean sucessful) {

      this(address, date, sucessful);
      this.id = id;
      this.playerId = playerId;
      this.service = service;
    }

    /**
     * This method returns a String that represent the object.
     *
     * @return a string representing the object.
     */
    @Override
    public String toString() {
      return "Login " + (correct ? "successful" : "FAILED") + " at " + date + " from " + address;
    }

    /**
     * gets the id of this database row
     *
     * @return id or -1
     */
    public long getId() {
      return id;
    }

    /**
     * gets the id of the account
     *
     * @return player_id or -1
     */
    public long getPlayerId() {
      return playerId;
    }

    /**
     * gets the name of the service
     *
     * @return id or <code>null</code>
     */
    public String getService() {
      return service;
    }

    /**
     * gets the ip-address
     *
     * @return ip-address
     */
    public String getAddress() {
      return address;
    }

    /**
     * gets the timestamp of the login attempt
     *
     * @return timestamp
     */
    public String getDate() {
      return date;
    }

    /**
     * was this a successful login attempt?
     *
     * @return true, if the attempt was successful; false otherwise
     */
    public boolean isSuccessful() {
      return correct;
    }

  }

  /**
   * gets a list of recent login events
   *
   * @param transaction DBTransaction
   * @param username username
   * @param events number of events
   * @return list of login attempts
   * @throws SQLException in case of an database error
   */
  public List<String> getLoginEvents(DBTransaction transaction, String username, int events) throws SQLException {
    try {
      int id = DAORegister.get().get(AccountDAO.class).getDatabasePlayerId(transaction, username);
      if (id == -1) {
        /*
         * This should never happen as we should check previously that
         * player exists...
         */
        throw new SQLException("Unable to find player(" + username + ")");
      }

      String query = "select address, timedate, result from loginEvent where player_id=[player_id]"
              + " order by timedate desc limit [events]";
      logger.debug("getLoginEvents is executing query " + query);
      Map<String, Object> params = new HashMap<String, Object>();
      params.put("player_id", Integer.valueOf(id));
      params.put("events", Integer.valueOf(events));

      ResultSet resultSet = transaction.query(query, params);

      List<String> list = new LinkedList<String>();

      while (resultSet.next()) {
        LoginEvent event = new LoginEvent(resultSet.getString("address"), resultSet
                .getString("timedate"), resultSet.getBoolean("result"));
        list.add(event.toString());
      }

      resultSet.close();
      return list;
    } catch (SQLException e) {
      logger.error("Can't query for player \"" + username + "\"", e);
      throw e;
    }
  }

  /**
   * gets the last successful login event
   *
   * @param transaction DBTransaction
   * @param playerId accountId
   * @param service name of service, may be <code>null</code> for all
   * @return last successful login event
   * @throws SQLException in case of an database error
   */
  public LoginEvent getLastSuccessfulLoginEvent(DBTransaction transaction, int playerId, String service) throws SQLException {
    try {
      String serviceQuery = "";
      if (service != null) {
        serviceQuery = " AND service='[service]'";
      }
      String query = "SELECT id, player_id, service, address, timedate, result FROM loginEvent"
        + " WHERE player_id=[player_id] AND result=1" + serviceQuery + " ORDER BY timedate desc LIMIT 2";

      Map<String, Object> params = new HashMap<String, Object>();
      params.put("service", service);
      params.put("player_id", Integer.toString(playerId));
     
      logger.debug("getLastSuccessfulLoginEvent is executing query " + query);

      ResultSet resultSet = transaction.query(query, params);

      // go to this login
      if (!resultSet.next()) {
        resultSet.close();
        return null;
      }
      // go to the last login
      if (!resultSet.next()) {
        resultSet.close();
        return null;
      }
     
      LoginEvent event = new LoginEvent(resultSet.getLong("id"),
          resultSet.getLong("player_id"), resultSet.getString("service"),
          resultSet.getString("address"), resultSet.getString("timedate"),
          resultSet.getBoolean("result"));
      resultSet.close();
      return event;
    } catch (SQLException e) {
      logger.error("Can't query for player \"" + playerId + "\"", e);
      throw e;
    }
  } 

  /**
   * gets the amount of failed login attemps
   *
   * @param transaction DBTransaction
   * @param id only look for events younger than this id
   * @param playerId accountId
   * @return amount of failed login attempts grouped by service name
   * @throws SQLException in case of an database error
   */
  public List<Pair<String, Long>> getAmountOfFailedLogins(DBTransaction transaction, long id, int playerId) throws SQLException {
    try {
      String query = "SELECT service, count(*) FROM loginEvent"
        + " WHERE player_id=[player_id] + AND id > [id] AND result = 0 GROUP BY service";
      Map<String, Object> params = new HashMap<String, Object>();
      params.put("id", Long.toString(id));
      params.put("player_id", Integer.toString(playerId));
      logger.debug("getAmountOfFailedLogins is executing query " + query);
   
      ResultSet resultSet = transaction.query(query, params);
      List<Pair<String, Long>> list = new LinkedList<Pair<String, Long>>();
      while (resultSet.next()) {
        list.add(new Pair<String, Long>(resultSet.getString(1), Long.valueOf(resultSet.getLong(2))));
      }
      resultSet.close();
      return list;
    } catch (SQLException e) {
      logger.error("Can't query for player \"" + playerId + "\"", e);
      throw e;
    }
  } 

  /**
   * checks if this account is temporary blocked because of too many failed login attempts.
   * Blocking accounts is not related to banning accounts.
   *
   * @param transaction DBTransaction
   * @param username username
   * @return true, if this account is blocked; false otherwise
   * @throws SQLException in case of an database error
   */
  public boolean isAccountBlocked(DBTransaction transaction, String username) throws SQLException {
    String query = "SELECT count(*) as amount FROM loginEvent, account"
        + " WHERE loginEvent.player_id=account.id"
        + " AND username='[username]'"
            + " AND loginEvent.result=0 and loginEvent.timedate > '[timestamp]'";

    Map<String, Object> params = new HashMap<String, Object>();
    params.put("username", username);

    Calendar calendar = new GregorianCalendar();
    calendar.add(Calendar.SECOND, -1 * TimeoutConf.FAILED_LOGIN_BLOCKTIME);
    params.put("timestamp", new Timestamp(calendar.getTimeInMillis()).toString());

    int attemps = transaction.querySingleCellInt(query, params);
    return attemps > TimeoutConf.FAILED_LOGIN_ATTEMPTS_ACCOUNT;
  }

  /**
   * checks if the ip-address is temporary blocked because of too many failed login attempts.
   * Blocking ip-addresses is not related to banning ip-addresses.
   *
   * @param transaction DBTransaction
   * @param address ip-address
   * @return true, if this address is blocked; false otherwise
   * @throws SQLException in case of an database error
   */
  public boolean isAddressBlocked(DBTransaction transaction, String address) throws SQLException {
    String query = "SELECT count(*) as amount FROM loginEvent"
        + " WHERE address='[address]'"
            + " AND result=0 and timedate > '[timestamp]'";
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("address", address);

    Calendar calendar = new GregorianCalendar();
    calendar.add(Calendar.SECOND, -1 * TimeoutConf.FAILED_LOGIN_BLOCKTIME);
    params.put("timestamp", new Timestamp(calendar.getTimeInMillis()).toString());

    int attemps = transaction.querySingleCellInt(query, params);
    return attemps > TimeoutConf.FAILED_LOGIN_ATTEMPTS_IP;
  }
 

  /**
   * logs an login attempt
   *
   * @param username username
   * @param source ip-address
   * @param correctLogin true, if the login was successful; false otherwise
   * @throws SQLException in case of an database error
   */
  @Deprecated
  public void addLoginEvent(String username, InetAddress source, boolean correctLogin) throws SQLException {
    DBTransaction transaction = TransactionPool.get().beginWork();
    try {
      addLoginEvent(transaction, username, source, null, null, correctLogin);
    } finally {
      TransactionPool.get().commit(transaction);
    }
  }


  /**
   * logs an login attempt
   *
   * @param seed a seed to log
   * @param username username
   * @param source ip-address
   * @param correctLogin true, if the login was successful; false otherwise
   * @throws SQLException in case of an database error
   */
  @Deprecated
  public void addLoginEvent(String username, InetAddress source, String seed, boolean correctLogin) throws SQLException {
    DBTransaction transaction = TransactionPool.get().beginWork();
    try {
      addLoginEvent(transaction, username, source, null, seed, correctLogin);
    } finally {
      TransactionPool.get().commit(transaction);
    }
  }

  /**
   * logs an login attempt
   *
   * @param username username
   * @param source ip-address
   * @param service name of service
   * @param seed seed
   * @param correctLogin true, if the login was successful; false otherwise
   * @throws SQLException in case of an database error
   */
  public void addLoginEvent(String username, InetAddress source, String service, String seed, boolean correctLogin) throws SQLException {
    DBTransaction transaction = TransactionPool.get().beginWork();
    try {
      addLoginEvent(transaction, username, source, service, seed, correctLogin);
    } finally {
      TransactionPool.get().commit(transaction);
    }
  }

 
  /**
   * gets a list of recent login events
   *
   * @param username username
   * @param events number of events
   * @return list of login attempts
   * @throws SQLException in case of an database error
   */
  public List<String> getLoginEvents(String username, int events) throws SQLException {
    DBTransaction transaction = TransactionPool.get().beginWork();
    try {
      List<String> res = getLoginEvents(transaction, username, events);
      return res;
    } finally {
      TransactionPool.get().commit(transaction);
    }
  }


  /**
   * checks if this account is temporary blocked because of too many failed login attempts.
   * Blocking accounts is not related to banning accounts.
   *
   * @param username username
   * @return true, if this account is blocked; false otherwise
   * @throws SQLException in case of an database error
   */
  public boolean isAccountBlocked(String username) throws SQLException {
    DBTransaction transaction = TransactionPool.get().beginWork();
    try {
      boolean res = isAccountBlocked(transaction, username);
      return res;
    } finally {
      TransactionPool.get().commit(transaction);
    }
  }

  /**
   * checks if the ip-address is temporary blocked because of too many failed login attempts.
   * Blocking ip-addresses is not related to banning ip-addresses.
   *
   * @param address ip-address
   * @return true, if this address is blocked; false otherwise
   * @throws SQLException in case of an database error
   */
  public boolean isAddressBlocked(String address) throws SQLException {
    DBTransaction transaction = TransactionPool.get().beginWork();
    try {
      boolean res = isAddressBlocked(transaction, address);
      return res;
    } finally {
      TransactionPool.get().commit(transaction);
    }
  }

}
TOP

Related Classes of marauroa.server.game.db.LoginEventDAO$LoginEvent

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.