使用Spring Security登录时出现Bad SQL Grammar错误

Alright, let's break down what's causing your error and get your login functionality working with email/password authentication.

Why You're Seeing This Error #

Spring Security's default JDBC authentication setup expects two tables out of the box:

1. A users table (which you already have since registration works)
2. An authorities table to fetch user permissions/roles

Since you haven't created the authorities table and aren't using a custom authentication flow, Spring Security throws that SQL syntax error when it tries to query the missing table. Additionally, you want to use email instead of the default username field for login, which we'll need to configure explicitly.

Solution 1: Configure Custom JDBC Queries (Quick Fix) #

If you want to stick with JDBC authentication but avoid the authorities table for now, you can override Spring Security's default queries to use your existing user table and return a default role.

Here's how to update your SecurityConfig:

@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private DataSource dataSource;

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.jdbcAuthentication()
                .dataSource(dataSource)
                // Tell Spring Security to use email as the "username" for login
                .usersByUsernameQuery(
                    "SELECT email AS username, password, enabled FROM users WHERE email = ?"
                )
                // Return a default role (since we don't have an authorities table yet)
                .authoritiesByUsernameQuery(
                    "SELECT email AS username, 'ROLE_USER' AS authority FROM users WHERE email = ?"
                );
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
                .authorizeRequests()
                // Allow public access to registration and static resources
                .antMatchers("/register", "/css/**", "/js/**").permitAll()
                .anyRequest().authenticated()
                .and()
                .formLogin()
                // Map the login form's email input to Spring Security's "username" parameter
                .usernameParameter("email")
                .loginPage("/login")
                .permitAll()
                .and()
                .logout()
                .permitAll();
    }
}

Key Notes:

• Replace users with your actual user table name if it's different.
• Make sure your login form's email input has name="email" (matches usernameParameter("email")).

Solution 2: Custom UserDetailsService (More Flexible) #

If you want more control over how user data is fetched (e.g., using your existing repository), create a custom UserDetailsService:

Step 1: Implement the Custom Service

@Service
public class CustomUserDetailsService implements UserDetailsService {

    @Autowired
    private UserRepository userRepository; // Your existing user repository from registration

    @Override
    public UserDetails loadUserByUsername(String email) throws UsernameNotFoundException {
        // Fetch user by email (your registration logic uses this repository)
        User user = userRepository.findByEmail(email)
                .orElseThrow(() -> new UsernameNotFoundException("User not found with email: " + email));

        // Build a UserDetails object with default role (adjust later for real permissions)
        return User.builder()
                .username(user.getEmail())
                .password(user.getPassword())
                .roles("USER") // Or use .authorities(List.of(new SimpleGrantedAuthority("ROLE_USER")))
                .enabled(true)
                .build();
    }
}

Step 2: Update SecurityConfig to Use the Custom Service

@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private CustomUserDetailsService userDetailsService;

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(userDetailsService);
        // Add this later when you implement password encryption:
        // .passwordEncoder(passwordEncoder());
    }

    // Optional: Add password encoder for future use (required when you encrypt passwords)
    @Bean
    public PasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder();
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
                .authorizeRequests()
                .antMatchers("/register", "/css/**", "/js/**").permitAll()
                .anyRequest().authenticated()
                .and()
                .formLogin()
                .usernameParameter("email")
                .loginPage("/login")
                .permitAll()
                .and()
                .logout()
                .permitAll();
    }
}

Adding Permissions Later (If Needed) #

When you're ready to implement role-based access, create the authorities table with this schema (adjust to match your user table's primary key):

CREATE TABLE authorities (
    username VARCHAR(255) NOT NULL,
    authority VARCHAR(50) NOT NULL,
    FOREIGN KEY (username) REFERENCES users(email) -- Links to your user table's email field
);

Then update your authentication logic to fetch roles from this table instead of returning a default role.

Final Check #

• Ensure your registration logic saves the email and password correctly (when you add encryption, use the same PasswordEncoder in both registration and login).
• Verify your login form's email input has the correct name attribute (email).

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