I have almost finished developing my login system and there is one more thing that I'm not sure about. So many debates I found on the internet about counting invalid logins and locking users account. My system stores user names and passwords (that are salted[for each user different salt] and hashed) in database. If user enters invalid user name or password I keep track of their Username, Password, LoginTime, SessionID, IP and Browser. Here is example:

LoginID   LoginTime                 LoginUN    LoginPW    LoginSessionID    LoginIP     LoginBrowser    
   1    2018-03-15 13:40:25.000     jpapis     test       E72E.cfusion  Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:60.0) Gecko/20100101 Firefox/60.0
  98    2018-03-15 13:48:45.000     mhart      mypass55   E72E.cfusion  Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:60.0) Gecko/20100101 Firefox/60.0
  32    2018-03-15 14:29:14.000     skatre     1167mmB!   378E.cfusion    Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:60.0) Gecko/20100101 Firefox/60.0

I'm wondering if I should lock account after X attempts? If so what would be the best practice to do that? Here is one approach that I found:

SELECT COUNT(LoginID) AS countID, DATEDIFF(mi,LoginTime,GETDATE ( )) AS TimeElapsed
FROM FailedLogins
WHERE (LoginUN = '#username#' OR LoginSessionID = '#SESSION.sessionid#' OR LoginIP = '#REMOTE_ADDR#')
    AND DATEDIFF(mi,LoginTime,GETDATE ( )) <= 60
GROUP BY LoginID, LoginTime

Query above will look for username, sessionID or IP address. If either of these it's found in FailedLogin table within 60min and it's greater than 5 I would lock the account. Only problem here is I'm not sure what this would prevent, brute force attack can send way too many attempts in 60min so I'm not sure what would be the benefit of checking failed logins this way. Is there better way to handle failed logins now days? Should I even lock the account? If anyone can provide some thoughts and examples please let me know. Also I want to share some details about the system. We do not have any money transaction in the system or tied to user accounts. We do have sensitive information in the system and that's why I would like to implement security feature to prevent hackers of trying to break password with some of hacker techniques. Thank you.


Account lockouts on remote applications (e.g. where the attacker can't reset the number of attempts without further work) are mostly to prevent easy brute force attacks against accounts. If you have users who select very weak or common passwords, and your system doesn't make any attempts to prevent the use of these, it's entirely possible that an attacker would be able to gain access to some accounts, even with account lockout implemented. However, that doesn't mean that it's not worth implementing - for users who select slightly better passwords, it massively slows an attacker, and may alert you or your users to potential attacks.

This is different to choosing a password storage method, which is generally attempting to defend against attackers who have obtained your user database. Obviously, in that case, you can't stop someone from making as many attempts to guess the password as they like - they can simply hash a bunch of passwords with the salt from your DB and compare until they find a password which gives the same hash.

However, the specific method which you're using to log the failed attempts is a problem. You're logging the username and incorrect password. In most cases, the likelihood of a legitimate user mistyping their password is higher than that of an attacker trying to log into the system. In that case, the log table you're keeping becomes valuable to attackers - it contains usernames with strings which are likely to be close to valid passwords for the accounts. If users have passwords just for your site, this could massively decrease the effort required to brute force passwords if your DB was accessed, and if the users have accounts on other systems with the same passwords, even if the other systems use account lockout schemes, you've just given attackers a much better chance of guessing the correct password within the attempt limit.

You don't need to store the password. You only care about the number of attempts against each account. That could be an integer value linked to the loginid, and would be sufficient for your lockout process. You can separately store the source IP, and monitor for unusual traffic patterns, although you do risk blocking legitimate users who are connecting to your site via NAT-ed connections - mobile providers often group large numbers of users so they appear to be coming from a single IP address, for example.

One common pattern for account lockout is to monitor the number of failed attempts against an account, then to apply incrementing time blocks against that account. For example, after 3 failed attempts, you could block further attempts for 5 minutes. If another 3 failed attempts occur, block the account for 10 minutes, and so on - this can reset once a legitimate login occurs. It can also be useful to show the number of failed attempts to users upon login - if they spot something strange, they can potentially alert you to a problem.

One subtle thing to be aware of with implementing account lockout schemes is that they can act as a username oracle, if implemented poorly. In this case, it is possible to spot distinct behaviour between valid accounts and non-existent accounts, letting an attacker know that a given username is valid. To prevent this, you can either monitor all supplied usernames, and show the same behaviour for non-existent ones as for valid ones, or simply not display when an account is locked. If you choose this, you may cause confusion to users, who may not be able to log in if they're subject to an attack without their knowledge.

  • The downside of outright locking out accounts is that an attacker can use leverage this in an Denial of Service attack. If I know your username I can lock you out by purposely attempting failed logins. Even worse, if the usernames are predictable I could try and lockout more users. It might be better to apply some "throtteling" mechanic: instead of locking an account, the server simply waits 30 seconds before sending back a response. This way the user can still log in, the account is very difficult to brute force, and the account can't be locked out on purpose – Snappie Mar 16 '18 at 14:30
  • Agreed - that's why I don't suggest outright locking, but an incremental process. It's not perfect, and can still be abused, but is fairly effective in live environments. Unlocking accounts on password reset can help with this. The problem with a throttle on individual requests is the UX side - a legitimate user may not be willing to wait 30 seconds to log in, and may take their business elsewhere. People tend to be very impatient! – Matthew Mar 16 '18 at 14:36
  • @Matthew I was thinking about this approach but you can correct me if there is something wrong. If user have let's say 5 failed login attempts I would display security question below Username and password filed and make that required. Also I would add CAPTCHA too. This will add two extra steps in case they have 5 invalid attempts. Once they login successfully I would delete all records from InvalidLogin table related to that user. – espresso_coffee Mar 16 '18 at 15:04
  • 1
    Most security questions are pretty terrible, and aren't that hard to guess (things like "favourite colour" have a limited number of possibilities, while things like "first school" can often be obtained from social network investigation) so I'd be wary of using them as an additional login factor like that. Similarly, CAPTCHA tests slow bulk attacks, but are irrelevant for targeted attacks against a specific user - the attacker just completes the challenge. Don't store plain text passwords at all, even if you're deleting them after a few minutes - you can't tell when a copy would be made... – Matthew Mar 16 '18 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.