{"record":{"id":"1998ddca5983c000","repo":"apereo/cas","slug":"username-not-found-with-sql-query","errorCode":null,"errorMessage":"[username] not found with SQL query","messagePattern":"\\[username\\] not found with SQL query","errorType":"exception","errorClass":"AccountNotFoundException","httpStatus":null,"severity":"error","filePath":"support/cas-server-support-jdbc-authentication/src/main/java/org/apereo/cas/jdbc/QueryAndEncodeDatabaseAuthenticationHandler.java","lineNumber":77,"sourceCode":"            }\n            if (StringUtils.isNotBlank(properties.getExpiredFieldName()) && sqlQueryResults.containsKey(properties.getExpiredFieldName())) {\n                val dbExpired = sqlQueryResults.get(properties.getExpiredFieldName()).toString();\n                if (BooleanUtils.toBoolean(dbExpired) || \"1\".equals(dbExpired)) {\n                    throw new AccountPasswordMustChangeException(\"Password has expired\");\n                }\n            }\n            if (StringUtils.isNotBlank(properties.getDisabledFieldName()) && sqlQueryResults.containsKey(properties.getDisabledFieldName())) {\n                val dbDisabled = sqlQueryResults.get(properties.getDisabledFieldName()).toString();\n                if (BooleanUtils.toBoolean(dbDisabled) || \"1\".equals(dbDisabled)) {\n                    throw new AccountDisabledException(\"Account has been disabled\");\n                }\n            }\n            val attributes = collectPrincipalAttributes(sqlQueryResults);\n            val principal = principalFactory.createPrincipal(username, attributes);\n            return createHandlerResult(transformedCredential, principal, new ArrayList<>());\n        } catch (final IncorrectResultSizeDataAccessException e) {\n            if (e.getActualSize() == 0) {\n                throw new AccountNotFoundException(username + \" not found with SQL query\");\n            }\n            throw new FailedLoginException(\"Multiple records found for \" + username);\n        } catch (final DataAccessException e) {\n            throw new PreventedException(e);\n        }\n    }\n\n    protected Map<String, Object> performSqlQuery(final String username) {\n        return getJdbcTemplate().queryForMap(properties.getSql(), username);\n    }\n}\n","sourceCodeStart":59,"sourceCodeEnd":89,"githubUrl":"https://github.com/apereo/cas/blob/e7288fc434b4f4505b8452e1a57e8fb3111bb863/support/cas-server-support-jdbc-authentication/src/main/java/org/apereo/cas/jdbc/QueryAndEncodeDatabaseAuthenticationHandler.java#L59-L89","documentation":"When the configured SQL query returns zero rows, Spring's IncorrectResultSizeDataAccessException with actualSize 0 is caught and rethrown as AccountNotFoundException('[username] not found with SQL query'). The username has no record in the table the query targets.","triggerScenarios":"performSqlQuery(username) returns no rows (singleRowSqlQuery / queryAndEncode flow) for the submitted username, causing EmptyResultDataAccessException translated to IncorrectResultSizeDataAccessException with getActualSize()==0.","commonSituations":"User typo or wrong username format (email vs local part), user absent from the auth database (exists only in another source), case-sensitivity mismatch in SQL, wrong table/schema/database configured.","solutions":["Run the SQL manually with the failing username to confirm zero rows","Verify the user exists in the configured database/table and correct the username or data source","Check sql config (cas.authn.jdbc.encode[0].sql) targets the right table and uses the right username placeholder","Handle AccountNotFoundException in the auth flow with a user-friendly 'unknown user' message"],"exampleFix":"// before\n// cas.authn.jdbc.encode[0].sql=SELECT * FROM users WHERE LOWER(email)=LOWER(?)\n// user stored with local-part usernames\n// after\n// cas.authn.jdbc.encode[0].sql=SELECT * FROM users WHERE username=?","handlingStrategy":"try-catch","validationCode":"int count = jdbc.queryForObject(\"SELECT COUNT(*) FROM users WHERE username=?\", Integer.class, username);\nif (count == 0) return showUnknownUserOrRegisterFlow(username);","typeGuard":null,"tryCatchPattern":"try {\n    authResult = handler.authenticate(credential);\n} catch (AccountNotFoundException e) {\n    // no row matched; show generic invalid-credentials to avoid user enumeration\n    throw new BadCredentialsException(\"Invalid credentials\");\n}","preventionTips":["Test the configured sql manually with representative usernames","Decide and document case-sensitivity/email-vs-username conventions","Ensure the user is provisioned into the auth database before first login","Return generic error text publicly to prevent username enumeration"],"tags":["jdbc","user-lookup","not-found"],"backgroundTag":"user-not-found","analyzedSha":"e7288fc434b4f4505b8452e1a57e8fb3111bb863","analyzedAt":"2026-09-08T15:39:16.015Z","contentChangedAt":"2026-09-08T15:39:16.015Z","schemaVersion":2},"datasetVersion":"2026-09-15T23:17:13.987Z"}