How to lock user account for some period of time?

Question

I'm wondering how and what is the best way to lock user account after X times failed logins? I have table where I keep track of users failed login attempts. Table stores time stamp, username, ip address and browser type. After I detect incorrect login information, cfquery will pull records from failed login table based on username or IP address. If there is 5 or more invalid attempts I set account for inactive. Now I would like to somehow set timer that will start counting 5 minutes since last invalid attempt for that user. Then account should change the status to active. Here is my code that I have so far:

<cfquery name="checkUser" datasource="#dsn#">
    SELECT UserName, Password, Salt, LockedUntil
    FROM Users
    WHERE UserName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(FORM.username)#" maxlength="50">
       AND Active = 1
</cfquery>

<cfif len(checkUser.LockedUntil) AND dateCompare(now(), checkUser.LockedUntil,'n') EQ -1>
    <cfset fnResults.status = "400">
    <cfset fnResults.message = "This account is locked for 5 min.">
    <cfreturn fnResults>
    <cfabort>
</cfif>

<cfset storedPW = checkUser.Password>
<cfset enteredPW = FORM.password & checkUser.Salt>

<cfif checkUser.recordCount NEQ '1' OR (hash(enteredPW,"SHA-512") NEQ storedPW>
    <cfquery name="logFail" datasource="#dsn#">
        INSERT INTO FailedLogins(
           LoginTime,
           LoginUN,
           LoginIP,
           LoginBrowser
        )VALUES(
           CURRENT_TIMESTAMP,
           <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.username#" maxlength="50">,
           <cfqueryparam cfsqltype="cf_sql_varchar" value="#REMOTE_ADDR#" maxlength="20">,
           <cfqueryparam cfsqltype="cf_sql_varchar" value="#CGI.HTTP_USER_AGENT#" maxlength="500">
        )
    </cfquery>

    <!--- Pull failed logins based on username or IP address. --->
    <cfquery name="failedAttempts" datasource="#dsn#">
        SELECT LoginTime
        FROM FailedLogins
        WHERE LoginUN = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(FORM.username)#" maxlength="50">
            OR LoginIP = <cfqueryparam cfsqltype="cf_sql_varchar" value="#REMOTE_ADDR#" maxlength="20">
    </cfquery>

    <cfif failedAttempts.recordcount LT 4>
        <cfset fnResults.status = "400">
        <cfset fnResults.message = "Invalid Username or Password!">
    <cfelseif failedAttempts.recordcount EQ 4>
        <cfset fnResults.status = "400">
        <cfset fnResults.message = "This is your last attempt. If you fail to provide correct information account will be locked!">
    <cfelseif failedAttempts.recordcount GTE 5>
        <cfset lockUntil = DateAdd('n', 5, now())>
        <cfquery name="blockUser" datasource="#dsn#">
            UPDATE Users
            SET LockedUntil = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#lockUntil#">
            WHERE UserName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(FORM.username)#" maxlength="50">
        </cfquery>

        <cfset fnResults.status = "400">
        <cfset fnResults.message = "This account is locked for 5 min.">
    </cfif>
<cfelse>
   //Clear failed login attempts
   //Update lockedUntil field to NULL
   //User logged in authentication successful!
</cfif>

After account is set to inactive / locked what would be the best way to set time count down and change the flag status? I saw some people recommended SQL Job but I'm not sure how often job should run and how to create that statement? If anyone can provide some example please let me know. Thank you.

Answer 1

What you can do is add a condition to the checkUser query:

<cfquery name="checkUser" datasource="#dsn#">
    SELECT UserName, Password, Salt, Active
      FROM Users u
     WHERE UserName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(FORM.username)#" maxlength="50">
      -- AND Active = 1
      AND NOT EXISTS ( SELECT 1 FROM FailedLogins fl
                        WHERE fl.LoginUN = u.UserName
                          AND DATEDIFF('ss', fl.LoginTime, CURRENT_TIMESTAMP) >= 300 )
</cfquery>

I've used 300 seconds instead of 5 minutes since DATEDIFF(), I believe, returns an int. I apologize in advance if this isn't quite the ideal syntax for SQL Server (I don't often work with it).

Then, above, if Active is zero, you can then (assuming the password is correct) update it to a value of 1 and either delete the failed logins associated with that account or somehow mark them inactive so they don't count against the 5 failed logins any more.

Query edited at the suggestion of a commenter below: (good suggestion, by the way!)

<cfquery name="checkUser" datasource="#dsn#">
    SELECT UserName, Password, Salt, Active
      FROM Users u
     WHERE UserName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(FORM.username)#" maxlength="50">
      -- AND Active = 1
      AND NOT EXISTS ( SELECT 1 FROM FailedLogins fl
                        WHERE fl.LoginUN = u.UserName
                          AND fl.loginTime < DATEADD(second, -300, CURRENT_TIMESTAMP) )
</cfquery>

Answer 2

I think you'd have better luck with reversing your logic. Instead of having a column status with values Active or Inactive, consider having a column locked_until time instead.

Initially the locked_until value for a new user will be NULL (or 0) meaning it is not locked.

When there is a series of failed logins, set this to be the current time + 5 minutes.

For all actions for this user, check if current time is > locked_until value. If not, the account is still inactivate (locked).

Edit: I decided to write out some code because I forgot to account for users successfully logging in. Please see below; I'm not sure what language the original question is in but this answer is pseudo-python.

Assuming we have a database table similar to the following (ignoring salts etc..)

CREATE TABLE Users (
    UserName TEXT PRIMARY KEY,
    Password TEXT NOT NULL,
    LockUntil TIMESTAMP,
    FailedLogins INT DEFAULT 0
);

The login checking function is something like the following. Key points are:

• Successful login clears sets FailedLogins to 0.
• Set FailedLogins to 5 (along with LockUntil) when locking account.
• A new failed login where FailedLogins=5 is an attempt for a newly unlocked account. (i.e. The account was implicitly unlocked and user is trying again).

def try_login(username, password):
    row = execute("SELECT Password,LockUntil,FailedLogins FROM Users WHERE UserName=?", username);
    if row is None:
        print("Unknown username")
        return False

    if row.LockUntil is not None and current_time() < row.LockUntil:
        print("Account locked. Try again later.")
        return False

    if password == row.Password:
        print('Successful login')
        execute("UPDATE Users SET LockUntil=NULL, FailedLogins=0 WHERE UserName=?", username)
        return True

    if row.FailedLogins == 4:
        print("Too many failures; locking account for 5 mins")
        lock_until = current_time() + 300
        execute("UPDATE Users SET LockUntil=?,FailedLogins=5 WHERE UserName=?", lock_until, username)
        return False

    failures = row.FailedLogins + 1
    if failures == 6:
        # User had locked account, which is now unlocked again.
        # But they failed to login again, so this is failure 1.
        failures = 1
    execute("UPDATE Users SET FailedLogins=? WHERE UserName=?", failures, username)
    return False