File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

To check if failed attempts is more than 5 within 24hrs

 
kamabotti khan
Greenhorn
Posts: 22
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

hi

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 = '"
+ accntname
+ "' AND domain_name = '"
+ domainName
+ "' "
+ "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;

while (rs_check_unlock_attempts.next())
{
unlock_attempts = rs_check_unlock_attempts.getInt(1);
}

if (unlock_attempts > 5)
model.put("errorStr", "You have reached Maxium Attempts of unlock");

}

Please help me on how to do this.


Thanks
 
Martin Vajsar
Sheriff
Pie
Posts: 3747
62
Chrome Netbeans IDE Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
kamabotti khan
Greenhorn
Posts: 22
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks!!!1 It worked like charmmm
 
Martin Vajsar
Sheriff
Pie
Posts: 3747
62
Chrome Netbeans IDE Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic