Search code examples
mysqlpostgresqlspring-securityspring-bootidentifier

Reason: PreparedStatementCallback; bad SQL grammar

I've been trying to use a Postgres database for authentication with Spring security.

This is my SQL table definition:

CREATE TABLE "UTILISATEUR" (
"IdUtilisateur" serial NOT NULL,
"Nom" character varying(50),
"Prenom" character varying(50),
"Profil" character varying(50),
"Pseudo" character varying(20),
"IdSite" integer DEFAULT 0,
"Password" character varying(1024),
role character varying(45),
CONSTRAINT "UTILISATEUR_pkey" PRIMARY KEY ("IdUtilisateur"),
CONSTRAINT fk1u FOREIGN KEY (role)
   REFERENCES "Role" (role) MATCH SIMPLE
   ON UPDATE NO ACTION ON DELETE NO ACTION
);

And here's my configuration class for spring security:

package com.ardia.MDValidation;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.method.configuration.EnableGlobalMethodSecurity;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.builders.WebSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import javax.sql.DataSource ;

@Configuration
@EnableWebSecurity
@EnableGlobalMethodSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {
    //Pour l'authentification des Utilisateur de Table Utilisateur
@Autowired  
public void GlobalConfig(AuthenticationManagerBuilder auth,DataSource dataSource) throws Exception {
  auth.jdbcAuthentication()
    .dataSource(dataSource)
        .usersByUsernameQuery("select Pseudo as principal , Password as credentials from UTILISATEUR where Pseudo = ? ")
            .authoritiesByUsernameQuery("select Pseudo as principal , role as role from UTILISATEUR where Pseudo = ? ")
                .rolePrefix("_ROLE");
}
    //ne pas appliqué la securité sur les ressources 
@Override
public void configure(WebSecurity web) throws Exception {
    web.ignoring()
    .antMatchers("/bootstrap/css/**","/css/**");
}
@Override
protected void configure(HttpSecurity http) throws Exception {
http

    .csrf().disable()   
    .authorizeRequests()
    .anyRequest()   
        .authenticated()        
            .and()
            .formLogin()
            .permitAll();
}

}

It used to work with in memory users. Now I can't make it work is because SQL grammar. Or should I change the table name because in the error the username is lower case and my table name is upper case?

Here's the error:

org.postgresql.util.PSQLException: ERREUR: la relation « utilisateur » n'existe pas Position : 32

I Found The Problem

It seems when you execute a query with jdbcAuthentication() it forces column and table names to lower case. Does anyone know how to change that?

Solution

  • Whoever created your table chose to preserve CaMeL-case identifiers with double-quotes. Now you have to use matching case and double-quote table and column names everywhere.

    Since double-quotes have special meaning within your client you have to escape or encode the special character. I am not familiar with Spring. Maybe escape with a backslash?

    "SELECT \"Pseudo\" AS principal, \"Password\"; AS credentials
 FROM \"UTILISATEUR\" WHERE \"Pseudo\" = ? "

    Etc.

    Related: