/* $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);
}
}
}