Spring Security登录异常：用户认证时发生内部错误

Hey there, let's figure out why your login system is failing and fix it up step by step!

First, let's unpack the error message #

The core issue from your log is:

PreparedStatementCallback; SQL [${spring.queries.roles-query]; 参数索引超出范围（1 > 参数数量，当前参数数量为0）

This tells us Spring is trying to run a broken SQL query that has no parameter placeholders (?), but it's still trying to pass a parameter (the user's email) to it.

1. Fix the broken configuration annotation (root cause of the SQL error) #

Looking at your SecurityConfig class, there's a syntax mistake in the @Value annotation for the roles query. The original code was missing the closing } in the annotation, so it looked like this:

@Value("${spring.queries.roles-query}") // 原始代码缺少闭合的}，导致配置读取失败
private String roleQuery;

This means Spring can't resolve the ${spring.queries.roles-query} placeholder — it just uses the literal string ${spring.queries.roles-query} as the SQL statement. Since this string has no ? placeholder, when Spring tries to pass the user's email as a parameter, it throws the "parameter index out of range" error.

Fix:
Correct the @Value annotation to include the closing curly brace:

@Value("${spring.queries.roles-query}")
private String roleQuery;

2. Fix login form field name mismatch (hidden issue waiting to happen) #

Even after fixing the SQL problem, your login would still fail! Your form uses email and senha as field names, but Spring Security's UsernamePasswordAuthenticationFilter expects username and password by default. It won't recognize your custom field names unless you tell it to.

You have two options here:

Option A: Update the form to match default field names #

Modify your login form inputs to use the default parameter names:

<!-- Change name="email" to name="username" -->
<input type="text" class="form-control" placeholder="邮箱" name="username">

<!-- Change name="senha" to name="password" -->
<input type="password" class="form-control" placeholder="密码" name="password">

Option B: Configure Spring Security to use your custom field names #

If you want to keep using email and senha in the form, add this configuration to your SecurityConfig's configure(HttpSecurity http) method:

@Override
protected void configure(HttpSecurity http) throws Exception {
    http
        .authorizeRequests()
            .anyRequest().authenticated()
            .and()
        .formLogin()
            .loginPage("/login")
            .usernameParameter("email") // Tell Spring to use "email" as the username parameter
            .passwordParameter("senha") // Tell Spring to use "senha" as the password parameter
            .permitAll();
    // Add other security configurations (like logout) here
}

3. Quick sanity check (to avoid future headaches) #

• Make sure your user passwords are stored as BCrypt-hashed values in the database. Since you're using BCryptPasswordEncoder, raw plain-text passwords will fail authentication. When creating users, always encode the password with passwordEncoder.encode(plainTextPassword).
• Double-check your application.properties queries: both users-query and roles-query look correct, mapping the email to username and assigning the ROLE_USER role properly.

Once you apply these fixes, your login system should work as expected!

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