(Web Security)로그인 시도 횟수 제한 예제 및 쿼리

로그인 시도 횟수에 제약을 주지 않는다면 해커 입장에서 무작위 공격을 시도해 정보를 매우 쉽게 취득할 수 있다. 이를 방지하기 위해 아이디에 대한 접속 횟수를 제한해 일정 횟수가 넘는다면 로그인 시도를 막는 기법을 사용하도록 한다. 

ID마다 로그인 실패 횟수, 로그인 제한여부, 최근 로그인 시도 시각, 제한횟수를 저장하는 칼럼을 추가한다.

구현 기능 
- 4회 이상 로그인 시도 실패시 제한횟수 * 10분동안 로그인 시도를 금지한다.
- 10분 이후 로그인 시도시 로그인 실패횟수, 로그인 제한 여부를 갱신하고 로그인 시도를 반복한다.
- 로그인 성공시 제한횟수까지 0으로 초기화시킨다.

다음과 같이 로그인 시도 횟수 제한하기 위한 칼럼들을 추가한다.
LOGIN_FAIL_COUNT : 로그인 실패 횟수
IS_LOCK : 로그인 시도 제한 여부 Y, N으로 저장
LATEST_TRY_LOGIN_DATE : 최근 접속 시도 시각
LOCK_COUNT : 로그인 시도 제한 횟수

메소드 정리 

메소드명	리턴 타입	설명
selectLockStatus	int	현재 시간 기준으로 로그인 제한이 풀렸는지 확인하는 메소드. 제한 걸린 로그인 시도시간(LATEST_TRY_LOGIN_DATE) 에 제한시간 더한 값과 현재 시간(SYSDATE) 비교해 결과 리턴함.  * DATE 대소비교시 더 이후 날짜가 더 큼
plusLoginFailCount	int	로그인 실패시 LOGIN_FAIL_COUNT 하나 늘림
updateLockStatus	int	LOGIN_TRY_COUNT가 일정 횟수 넘는 row에 대해 IS_LOCK를 'Y'로 LOCK_COUNT 를 증가시킴
updateClearLoginFailCount	int	LOGIN_TRY_COUNT를 0으로, IS_LOCK을 'N'으로 초기화
updateClearLockCount	int	LOCK_COUNT를 0으로 초기화

@Override
	public int selectLockStatus(String userId) {
		/*SELECT  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')   FAIL_DATE
        , TO_CHAR(SYSDATE + (1 /24 / 60) * LOCK_COUNT  , 'YYYY-MM-DD HH24:MI:SS') LOCK_DATE
        , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') NOW_DATE
        FROM    DUAL*/
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		//FIXME SQLInjection 방어하기
		StringBuffer query = new StringBuffer();
		
		query.append(" SELECT	COUNT(1) CNT                                                   ");
		query.append(" FROM		SYSTEM.USERS                                                   ");
		query.append(" WHERE	USER_ID = ?                                                    ");
		query.append(" AND		IS_LOCK = 'Y'                                                  ");
		query.append(" AND		LATEST_TRY_LOGIN_DATE + ( 1 / 24 / 60 ) * LOCK_COUNT > SYSDATE ");
		
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(query.toString());
			
			stmt.setString(1, userId);
			
			rs = stmt.executeQuery();
			
			if(rs.next()) {
				return rs.getInt("CNT");
			}
			return 0;
		}
		catch(SQLException sqle) {
			throw new RuntimeException(sqle.getMessage(), sqle);
		}
		finally {
			DBCloseUtil.close(conn, stmt, rs);
		}
	}
	
	
	
	@Override
	public int plusLoginFailCount(String userId) {
		Connection conn = null;
		PreparedStatement stmt = null;
		
		
		StringBuffer query = new StringBuffer();
		query.append(" UPDATE	SYSTEM.USERS                            ");
		query.append(" SET		LOGIN_FAIL_COUNT = LOGIN_FAIL_COUNT + 1 ");
		query.append(" 			, LATEST_TRY_LOGIN_DATE = SYSDATE       ");
		query.append(" WHERE	USER_ID = ?                             ");
		
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(query.toString());
			stmt.setString(1, userId);
			return stmt.executeUpdate();
		}
		catch(SQLException sqle) {
			throw new RuntimeException(sqle.getMessage(), sqle);
		}
		finally {
			DBCloseUtil.close(conn, stmt, null);
		}
	}
	
	@Override
	public int updateLockStatus(String userId) {
		Connection conn = null;
		PreparedStatement stmt = null;
		
		StringBuffer query = new StringBuffer();
		
		query.append(" UPDATE	SYSTEM.USERS                  ");
		query.append(" SET		IS_LOCK = 'Y'                 ");
		query.append(" 			, LOCK_COUNT = LOCK_COUNT + 1 ");
		query.append(" WHERE	USER_ID = ?                   ");
		query.append(" AND		LOGIN_FAIL_COUNT > 3          ");
		
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(query.toString());
			stmt.setString(1, userId);
			return stmt.executeUpdate();
		}
		catch(SQLException sqle) {
			throw new RuntimeException(sqle.getMessage(), sqle);
		}
		finally {
			DBCloseUtil.close(conn, stmt, null);
		}
	}
	
	@Override
	public int updateClearLoginFailCount(String userId) {
		Connection conn = null;
		PreparedStatement stmt = null;
		
		StringBuffer query = new StringBuffer();
		
		query.append(" UPDATE	SYSTEM.USERS   ");
		query.append(" SET		LOGIN_FAIL_COUNT = 0 ");
		query.append(" WHERE	USER_ID = ?    ");
		query.append(" AND		IS_LOCK = 'Y'  ");
		
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(query.toString());
			stmt.setString(1, userId);
			return stmt.executeUpdate();
		}
		catch(SQLException sqle) {
			throw new RuntimeException(sqle.getMessage(), sqle);
		}
		finally {
			DBCloseUtil.close(conn, stmt, null);
		}
	}
	
	@Override
	public int updateClearLockCount(String userId) {
		Connection conn = null;
		PreparedStatement stmt = null;
		
		StringBuffer query = new StringBuffer();
		
		query.append(" UPDATE	SYSTEM.USERS   ");
		query.append(" SET		LOCK_COUNT = 0 ");
		query.append(" WHERE	USER_ID = ?    ");
		
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(query.toString());
			stmt.setString(1, userId);
			return stmt.executeUpdate();
		}
		catch(SQLException sqle) {
			throw new RuntimeException(sqle.getMessage(), sqle);
		}
		finally {
			DBCloseUtil.close(conn, stmt, null);
		}
	}

DAO 클래스. 로그인 시도 실패 횟수가 특정 시점에 도달하면 그때 시각을 저장하고, 페널티 시간을 더해 로그인 시도 제한 시각을 설정한다. 
로그인 시도 제한 시각 : 마지막 로그인 시도 실패 시점 + 페널티 시간. 

※ 페널티 시간은 Date 타입의 경우 +- 를 통해 설정한다
- 1을 더하면 그 날에서 하루 다음날이 된다. 
- 시간을 더하고 싶은 경우 1을 하루의 시간 24로 나눈 (1 / 24 )를 더하면 된다.
- 분의 경우 ( 1 / 24 / 60 ) 을 사용하면 된다.

이를 적용시킨 쿼리가 다음과 같다.

SELECT COUNT(1) CNT                                                    
 FROM SYSTEM.USERS                                                
 WHERE USER_ID = ?                                                   
 AND IS_LOCK = 'Y'                                                   
 AND LATEST_TRY_LOGIN_DATE + ( 1 / 24 / 60 ) * LOCK_COUNT > SYSDATE 

마지막 줄을 살펴보면 
최근 접속 실패 시간 + LOCK_COUNT 분만큼 더한 시각이 로그인 시도가 제한되는 시각이고, 현재 시각 이전이라면 결과가 0이 되도록 해 이 값을 Service 객체의 멤버함수가 비교하는 로직이다.


수행 결과 확인해보자

DB에 다음과 같이 아이디와 비밀번호가 등록되어 있다. admin을 사용해 확인

맞지 않는 비밀번호를 입력하면 실패 팝업이 노출되며

DB에는 LOGIN_FAIL_COUNT와 LATEST_TRY_LOGIN_DATE가 갱신된다.

이를 반복해 제한 조건 횟수를 만족시키면 ( 코드에서 LOGIN_FAIL_COUNT가 4가 될 때부터 )
IS_LOCK이 'y'로, LOCK_COUNT가 1로 변경된다.

이때부터는 일정 시간동안 맞는 비밀번호 입력해도 실패하게 된다.

일정시간이 지나 접속 성공하면

Service 로직에 따라 값들이 초기화된다.