Hey there, let's tackle your two questions with practical, hands-on solutions that fit your existing setup.

To lock a user account for a specific window, the core is tracking the lock status and expiration time directly in your user table. Here's a step-by-step implementation:

Step 1: Update your user table schema #

Add two fields to your users table to manage locks:

• is_locked: Boolean (tinyint/bit) to mark if the account is locked
• lock_expires_at: Datetime to store when the lock will automatically lift

Step 2: Add lock checks to your login flow #

Before validating credentials, check if the account is currently locked. If the lock has expired, auto-unlock it first:

<!--- Check if the account is locked --->
<cfquery name="checkAccountLock" datasource="yourDSN">
  SELECT is_locked, lock_expires_at
  FROM users
  WHERE username = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.username#">
</cfquery>

<cfif checkAccountLock.recordCount>
  <cfset currentTime = Now()>
  <cfif checkAccountLock.is_locked AND currentTime LT checkAccountLock.lock_expires_at>
    <!--- Calculate remaining lock time for user feedback --->
    <cfset remainingMinutes = DateDiff("n", currentTime, checkAccountLock.lock_expires_at)>
    <cfoutput>Your account is locked. Please try again in #remainingMinutes# minute(s).</cfoutput>
    <cfabort>
  <cfelseif checkAccountLock.is_locked AND currentTime GE checkAccountLock.lock_expires_at>
    <!--- Auto-unlock the account since the lock period has ended --->
    <cfquery datasource="yourDSN">
      UPDATE users
      SET is_locked = 0, lock_expires_at = NULL
      WHERE username = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.username#">
    </cfquery>
  </cfif>
</cfif>

<!--- Proceed with normal login validation here... --->

Your existing approach of tracking failures in a login_failures table is solid—let's refine it to meet the "5-minute lock after last failure" requirement, while boosting performance and reliability.

Key Improvements & Full Workflow #

a. Optimize failure count queries

Instead of counting all-time failures, only count failures in a recent window (e.g., 15 minutes) to avoid old attempts triggering locks. We'll also grab the last failure time to set the lock expiration.

b. Add database indexing

Speed up failure count queries by adding a composite index to your login_failures table:

CREATE INDEX idx_failures_username_timestamp ON login_failures (username, timestamp);

c. Full ColdFusion Implementation

<cfif loginCredentialsInvalid>
  <!--- Log the failed login attempt --->
  <cfquery datasource="yourDSN">
    INSERT INTO login_failures (timestamp, username, ip_address, browser_type)
    VALUES (
      <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Now()#">,
      <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.username#">,
      <cfqueryparam cfsqltype="cf_sql_varchar" value="#CGI.REMOTE_ADDR#">,
      <cfqueryparam cfsqltype="cf_sql_varchar" value="#CGI.HTTP_USER_AGENT#">
    )
  </cfquery>

  <!--- Count recent failures (last 15 minutes) and get the last failure time --->
  <cfquery name="getRecentFailures" datasource="yourDSN">
    SELECT 
      COUNT(*) AS failure_count,
      MAX(timestamp) AS last_failure_time
    FROM login_failures
    WHERE username = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.username#">
      AND timestamp >= DATEADD("mi", -15, <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Now()#">)
  </cfquery>

  <!--- Trigger lock if 5+ failures occurred --->
  <cfif getRecentFailures.failure_count >= 5>
    <cfset lockExpiration = DateAdd("mi", 5, getRecentFailures.last_failure_time)>
    <cfquery datasource="yourDSN">
      UPDATE users
      SET is_locked = 1, lock_expires_at = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#lockExpiration#">
      WHERE username = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.username#">
    </cfquery>
    <cfoutput>Too many failed login attempts. Your account is locked for 5 minutes starting from your last attempt.</cfoutput>
    <cfabort>
  </cfif>
</cfif>

Bonus Tips #

• Clean up old failure records: Schedule a daily job to delete entries older than 7 days to keep your table lean:

  DELETE FROM login_failures WHERE timestamp < DATEADD("d", -7, GETDATE());
  

• IP-based locking (optional): If you want to block malicious IPs regardless of username, add a check for ip_address in your failure count query to prevent brute-force attacks on multiple accounts.

内容的提问来源于stack exchange，提问作者espresso_coffee