I am trying to stop from unlocking an account for a domain if the user has made attempts more than 5 times within 24hrs.I have added the check for five times taking the count from the table where failure or success log is stored but I want an additional condition that is ,if the five attempts is within period of 24hrs .I am using ORACLE DB.
The SQL query I am using is
select count(*) from test where acct_name='1234' and domain_name='d' and log_time<= SYSDATE-1.
This gives me the count the user has tried to unlock an account name "1234" on domain name "d" .If this count is more than 5 then I display a error message to user,but know I have to check if the count is more than 5 with 24hrs then display a error message or else the user can unlock.
For example the user named "K" has tried his fifth attempt to unlock today at 10:00 AM ,so until tommorrow 10:00 AM he will be displayed that message,And after tommorrow 10:00 am ,he can start unlocking.
Here is my java implementation for that ,But checking for overall five times,not within 24hrs ,should add that,please help me on this
final String CHECK_UNLOCK_ATTEMPTS = "SELECT count(*) FROM test WHERE acct_name = '"
+ "' AND domain_name = '"
+ "' "
+ "and log_time <= SYSDATE - 1";
// check if the session user has reached more than 5 attempts of unlock
SqlRowSet rs_check_unlock_attempts = jt.queryForRowSet(CHECK_UNLOCK_ATTEMPTS);
int unlock_attempts = 0;
unlock_attempts = rs_check_unlock_attempts.getInt(1);
if (unlock_attempts > 5)
model.put("errorStr", "You have reached Maxium Attempts of unlock");
First and foremost, you should learn how to use bind variables and use PreparedStatement for your code. It shields you from SQL injection attacks and saves precious database resources (very true for Oracle). Any decent JDBC tutorial deals with bind variables and the topic has been discussed countless times here on Javaranch too.
I'd say your approach should generally work, you've just a small mistake in the time condition. You want to count records which are younger than one day. Your current condition (log_time<= SYSDATE-1) matches records that are older than one day.
Just reverting the condition should do the trick.
You should also create an index on acct_name, domain_name and log_time to speed up the query. And unless you want to keep the history, you should delete records older than on day periodically.