{"record":{"id":"3c2b8dccebe813ef","repo":"apereo/cas","slug":"missing-field-total-from-the-query-results-for","errorCode":null,"errorMessage":"Missing field 'total' from the query results for [username]","messagePattern":"Missing field 'total' from the query results for \\[username\\]","errorType":"exception","errorClass":"FailedLoginException","httpStatus":null,"severity":"error","filePath":"support/cas-server-support-jdbc-authentication/src/main/java/org/apereo/cas/jdbc/QueryDatabaseAuthenticationHandler.java","lineNumber":68,"sourceCode":"    @Override\n    protected AuthenticationHandlerExecutionResult authenticateUsernamePasswordInternal(\n        final UsernamePasswordCredential credential, final String originalPassword) throws Throwable {\n        val username = credential.getUsername();\n        val password = credential.toPassword();\n        try {\n            val dbFields = query(credential);\n            if (dbFields.containsKey(properties.getFieldPassword())) {\n                val dbPassword = (String) dbFields.get(properties.getFieldPassword());\n\n                val originalPasswordMatchFails = StringUtils.isNotBlank(originalPassword) && !matches(originalPassword, dbPassword);\n                val originalPasswordEquals = StringUtils.isBlank(originalPassword) && !Strings.CI.equals(password, dbPassword);\n                if (originalPasswordMatchFails || originalPasswordEquals) {\n                    throw new FailedLoginException(\"Password does not match value on record.\");\n                }\n            } else {\n                LOGGER.debug(\"Password field is not found in the query results. Checking for result count...\");\n                if (!dbFields.containsKey(\"total\")) {\n                    throw new FailedLoginException(\"Missing field 'total' from the query results for \" + username);\n                }\n\n                val count = dbFields.get(\"total\");\n                if (count == null || !NumberUtils.isCreatable(count.toString())) {\n                    throw new FailedLoginException(\"Missing field value 'total' from the query results for \"\n                        + username + \" or value not parseable as a number\");\n                }\n\n                val number = NumberUtils.createNumber(count.toString());\n                if (number.longValue() != 1) {\n                    throw new FailedLoginException(\"No records found for user \" + username);\n                }\n            }\n\n            if (StringUtils.isNotBlank(properties.getFieldDisabled()) && dbFields.containsKey(properties.getFieldDisabled())) {\n                val dbDisabled = dbFields.get(properties.getFieldDisabled()).toString();\n                if (BooleanUtils.toBoolean(dbDisabled) || \"1\".equals(dbDisabled)) {\n                    throw new AccountDisabledException(\"Account has been disabled\");","sourceCodeStart":50,"sourceCodeEnd":86,"githubUrl":"https://github.com/apereo/cas/blob/e7288fc434b4f4505b8452e1a57e8fb3111bb863/support/cas-server-support-jdbc-authentication/src/main/java/org/apereo/cas/jdbc/QueryDatabaseAuthenticationHandler.java#L50-L86","documentation":"When the query result does not contain the configured password field, the handler falls back to a 'total' row-count contract: the SQL must expose a column named 'total' counting matching users. If that column is absent, it throws FailedLoginException('Missing field 'total' from the query results for [username]').","triggerScenarios":"dbFields (single-row query result) lacks both properties.getFieldPassword() and the literal key 'total' — i.e. the SQL is neither a password-returning query nor a SELECT COUNT(*) AS total query.","commonSituations":"Misconfigured sql that selects neither password nor COUNT(*), column alias missing (SELECT COUNT(*) without 'AS total'), column alias upper-cased by the driver ('TOTAL' vs 'total'), query changed during migration.","solutions":["Alias the count column exactly as total: SELECT COUNT(*) AS total FROM users WHERE username=?","Verify sql config returns exactly one row containing 'total' when fieldPassword is not configured","Check driver/alias casing and quote the alias if needed","Alternatively configure fieldPassword to the real password column to use password comparison instead"],"exampleFix":"// before\n// cas.authn.jdbc.query[0].sql=SELECT COUNT(*) FROM users WHERE username=?\n// after\n// cas.authn.jdbc.query[0].sql=SELECT COUNT(*) AS total FROM users WHERE username=?","handlingStrategy":"validation","validationCode":"Map<String,Object> row = jdbc.queryForMap(sql, user);\nif (!row.containsKey(\"total\")) throw new IllegalStateException(\"Auth SQL must expose COUNT(*) AS total when no password field is configured\");","typeGuard":null,"tryCatchPattern":"try {\n    authResult = handler.authenticate(credential);\n} catch (FailedLoginException e) {\n    if (e.getMessage().contains(\"Missing field 'total'\")) {\n        log.error(\"Auth SQL contract violated: add SELECT ... COUNT(*) AS total\");\n        throw new ConfigurationException(\"Invalid cas.authn.jdbc.query sql\"); // fail fast, this is a config bug\n    }\n    throw e;\n}","preventionTips":["Always alias the count column exactly: SELECT COUNT(*) AS total","Include the password column in the sql if you intend password comparison instead","Validate the sql contract at deployment with an integration test","Quote the alias to avoid driver case-folding"],"tags":["jdbc","sql-config","query-contract"],"backgroundTag":"unexpected-response-shape","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"}