{"record":{"id":"ac4df585d19f027f","repo":"apereo/cas","slug":"username-not-found-with-sql-query-ac4df5","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/QueryDatabaseAuthenticationHandler.java","lineNumber":102,"sourceCode":"                val dbDisabled = dbFields.get(properties.getFieldDisabled()).toString();\n                if (BooleanUtils.toBoolean(dbDisabled) || \"1\".equals(dbDisabled)) {\n                    throw new AccountDisabledException(\"Account has been disabled\");\n                }\n            }\n            if (StringUtils.isNotBlank(properties.getFieldExpired()) && dbFields.containsKey(properties.getFieldExpired())) {\n                val dbExpired = dbFields.get(properties.getFieldExpired()).toString();\n                if (BooleanUtils.toBoolean(dbExpired) || \"1\".equals(dbExpired)) {\n                    throw new AccountPasswordMustChangeException(\"Password has expired\");\n                }\n            }\n\n            val attributes = collectPrincipalAttributes(dbFields);\n            val principal = this.principalFactory.createPrincipal(username, attributes);\n            return createHandlerResult(credential, principal, new ArrayList<>());\n\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> query(final UsernamePasswordCredential credential) {\n        val sql = SpringExpressionLanguageValueResolver.getInstance().resolve(properties.getSql());\n        if (sql.contains(\"?\")) {\n            return getJdbcTemplate().queryForMap(sql, credential.getUsername());\n        }\n        val parameters = new LinkedHashMap<String, Object>();\n        parameters.put(\"username\", credential.getUsername());\n        parameters.put(\"password\", credential.toPassword());\n        return getNamedParameterJdbcTemplate().queryForMap(sql, parameters);\n    }\n}","sourceCodeStart":84,"sourceCodeEnd":120,"githubUrl":"https://github.com/apereo/cas/blob/e7288fc434b4f4505b8452e1a57e8fb3111bb863/support/cas-server-support-jdbc-authentication/src/main/java/org/apereo/cas/jdbc/QueryDatabaseAuthenticationHandler.java#L84-L120","documentation":"Spring's IncorrectResultSizeDataAccessException with actualSize 0 means the SQL query (sqlFindByUsername) matched no rows. The handler translates that into AccountNotFoundException, i.e. the username does not exist in the database according to the configured query.","triggerScenarios":"authenticateUsernamePasswordInternal calls query(credential); getJdbcTemplate().queryForMap throws IncorrectResultSizeDataAccessException with getActualSize()==0 because the WHERE clause (username column) matched nothing.","commonSituations":"Typo in username; wrong table/column names in cas.authn.jdbc.query[].sql; connecting to wrong database/schema/environment; case-sensitive username comparison.","solutions":["Verify the username exists: run the configured SQL manually against the target datasource.","Check cas.authn.jdbc.query[].sql selects the correct table and username column; fix typos.","Confirm datasource URL/credentials point to the intended database and schema (dev vs prod).","Handle case sensitivity: use LOWER(username)=LOWER(?) in the SQL or normalize input."],"exampleFix":"// before\ncas.authn.jdbc.query[0].sql=SELECT * FROM users WHERE usr=?\n// after\ncas.authn.jdbc.query[0].sql=SELECT * FROM users WHERE username=?","handlingStrategy":"try-catch","validationCode":"// pre-check user existence\nInteger n = jdbcTemplate.queryForObject(\n    \"SELECT COUNT(*) FROM users WHERE username=?\", Integer.class, username);\nif (n == null || n == 0) { throw new AccountNotFoundException(username); }","typeGuard":null,"tryCatchPattern":"try { result = handler.authenticate(credential); }\ncatch (AccountNotFoundException e) { log.warn(\"Unknown user: {}\", username); return 401_unknown_user; }","preventionTips":["Always smoke-test the configured SQL against the actual datasource.","Keep username comparisons case-normalized on both sides.","Validate datasource URLs per environment before startup."],"tags":["jdbc","sql","user-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"}