原因:PreparedStatementCallback;错误的SQL语法 [英] Reason: PreparedStatementCallback; bad SQL grammar
问题描述
我一直在尝试使用Postgres数据库进行Spring安全性验证。
I've been trying to use a Postgres database for authentication with Spring security.
这是我的SQL表定义:
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();
}
}
以前在内存中使用用户。现在我无法使用它是因为SQL语法。还是应该更改表名,因为在错误中用户名是小写而表名是大写?
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?
这是错误:
org.postgresql.util.PSQLException: ERREUR: la relation « utilisateur » n'existe pas Position : 32
我发现了问题
jdbcAuthentication()
的查询会强制将列名和表名转换为小写。有人知道如何更改吗?
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?
推荐答案
创建表的人选择保留带双引号的CaMeL-case标识符。现在,您必须在所有位置使用匹配的大小写以及双引号表和列名。
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.
由于双引号在客户端中具有特殊含义,因此必须转义或编码特殊字符字符。我对Spring不熟悉。
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\" = ? "
等等。
相关:
- Are PostgreSQL column names case-sensitive?
这篇关于原因:PreparedStatementCallback;错误的SQL语法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!