apereo/cas · error · FailedLoginException

Multiple records found for [username]

Error message

Multiple records found for [username]

What it means

When the SQL query returns more than one row, the resulting IncorrectResultSizeDataAccessException (actualSize > 0) is rethrown as FailedLoginException('Multiple records found for [username]'). The handler requires the query to return exactly one row per user and refuses to guess which record is correct.

Solutions

  1. Deduplicate the user rows in the database and add a unique constraint on the username column
  2. Rewrite the SQL so it returns exactly one row (DISTINCT, LIMIT 1, or aggregate the joined rows)
  3. Remove N:1 joins or move role/attribute collection to a separate attribute source
  4. Normalize case-sensitive duplicate usernames

Example fix

// before
// cas.authn.jdbc.encode[0].sql=SELECT * FROM users u JOIN user_roles r ON r.user_id=u.id WHERE u.username=?
// after
// cas.authn.jdbc.encode[0].sql=SELECT * FROM users WHERE username=?
Defensive patterns

Strategy: validation

Validate before calling

List<Map<String,Object>> rows = jdbc.queryForList(sql, username);
if (rows.size() != 1) throw new IllegalStateException("SQL must return exactly 1 row, got " + rows.size() + " for " + username);

Try / catch

try {
    authResult = handler.authenticate(credential);
} catch (FailedLoginException e) {
    if (e.getMessage().startsWith("Multiple records")) {
        alertDataIntegrityTeam(username); // duplicates are a data problem, not a login problem
    }
    throw new BadCredentialsException("Invalid credentials");
}

Prevention

When it happens

Trigger: performSqlQuery(username) returns 2+ rows for the submitted username; e.g. duplicate rows in the users table or a non-unique join producing one row per matching relation.

Common situations: Duplicate user rows from a bad import or missing unique constraint, LEFT JOIN to a 1-N table (roles, mfa rows) inflating row count, case-insensitive duplicates ('John'/'john').

Related errors

AI-assisted analysis of apereo/cas@e7288fc434 (2026-09-08). Data as JSON: /api/errors/0d96d75541e4d18f. Report an issue: GitHub.

Appendix: source

Thrown at support/cas-server-support-jdbc-authentication/src/main/java/org/apereo/cas/jdbc/QueryAndEncodeDatabaseAuthenticationHandler.java:79

                val dbExpired = sqlQueryResults.get(properties.getExpiredFieldName()).toString();
                if (BooleanUtils.toBoolean(dbExpired) || "1".equals(dbExpired)) {
                    throw new AccountPasswordMustChangeException("Password has expired");
                }
            }
            if (StringUtils.isNotBlank(properties.getDisabledFieldName()) && sqlQueryResults.containsKey(properties.getDisabledFieldName())) {
                val dbDisabled = sqlQueryResults.get(properties.getDisabledFieldName()).toString();
                if (BooleanUtils.toBoolean(dbDisabled) || "1".equals(dbDisabled)) {
                    throw new AccountDisabledException("Account has been disabled");
                }
            }
            val attributes = collectPrincipalAttributes(sqlQueryResults);
            val principal = principalFactory.createPrincipal(username, attributes);
            return createHandlerResult(transformedCredential, principal, new ArrayList<>());
        } catch (final IncorrectResultSizeDataAccessException e) {
            if (e.getActualSize() == 0) {
                throw new AccountNotFoundException(username + " not found with SQL query");
            }
            throw new FailedLoginException("Multiple records found for " + username);
        } catch (final DataAccessException e) {
            throw new PreventedException(e);
        }
    }

    protected Map<String, Object> performSqlQuery(final String username) {
        return getJdbcTemplate().queryForMap(properties.getSql(), username);
    }
}

View on GitHub (pinned to e7288fc434)