允许用于根据用户名检索用户的默认查询字符串 * 覆盖，如果需要更改默认表或列名称.默认查询 * 是 {@link #DEF_USERS_BY_USERNAME_QUERY}；修改此查询时，请确保所有 * 返回的列映射回与默认查询中相同的列位置. * 如果源数据库中不存在已启用"列，则永久为真 * 可以使用类似于 的查询返回此列的值* select username,password,'true' as enabled from users where username = ?
您将sql查询密码作为第三个参数传入是否正确?
I'm trying to set up small web application in spring using bootrap and thymeleaf. I have no idea what's going on, because I have already one user in databe and I'm trying to log on it. When I pass wrong credentials is okey and spring-security doesn't pass me futher. But, when I pass corrent credentials, there is a NumberFormatException exception thrown from spring-security-core saing that it cannot parse my password to double. Password is hashed by BCrypt, but even when it wasn't hashed I had the same problem.
The exeption occurs in DaoAuthorizationProvider when is trying to getUserDetailsService().loadUserByUsername(username).
org.springframework.security.authentication.InternalAuthenticationServiceException: For input string: "$2a$10$jVoJ2TnQkTLKYn/j.ENlcemM90ap/MoZNUAGvsR9eMuL4P6uayCGG"
at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:123) ~[spring-security-core-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:144) ~[spring-security-core-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:175) ~[spring-security-core-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:200) ~[spring-security-core-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter.attemptAuthentication(UsernamePasswordAuthenticationFilter.java:94) ~[spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:212) ~[spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:124) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:74) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) [spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:836) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1747) [tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.19.jar:9.0.19]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1135) [na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [na:na]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.19.jar:9.0.19]
at java.base/java.lang.Thread.run(Thread.java:844) [na:na]
Caused by: java.lang.NumberFormatException: For input string: "$2a$10$jVoJ2TnQkTLKYn/j.ENlcemM90ap/MoZNUAGvsR9eMuL4P6uayCGG"
at java.base/jdk.internal.math.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:2054) ~[na:na]
at java.base/jdk.internal.math.FloatingDecimal.parseDouble(FloatingDecimal.java:110) ~[na:na]
at java.base/java.lang.Double.parseDouble(Double.java:543) ~[na:na]
at com.mysql.cj.protocol.a.MysqlTextValueDecoder.getDouble(MysqlTextValueDecoder.java:238) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.result.BooleanValueFactory.createFromBytes(BooleanValueFactory.java:113) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.result.BooleanValueFactory.createFromBytes(BooleanValueFactory.java:46) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeByteArray(MysqlTextValueDecoder.java:132) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:133) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:241) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.jdbc.result.ResultSetImpl.getBoolean(ResultSetImpl.java:678) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.zaxxer.hikari.pool.HikariProxyResultSet.getBoolean(HikariProxyResultSet.java) ~[HikariCP-3.2.0.jar:na]
at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:234) ~[spring-security-core-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:228) ~[spring-security-core-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:678) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:616) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:668) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:699) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:711) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:762) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUsersByUsername(JdbcDaoImpl.java:227) ~[spring-security-core-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUserByUsername(JdbcDaoImpl.java:184) ~[spring-security-core-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:108) ~[spring-security-core-5.1.4.RELEASE.jar:5.1.4.RELEASE]
... 57 common frames omitted
My WebSecurityConfig class
@Configuration
@EnableWebSecurity
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {
@Autowired
DataSource dataSource;
@Autowired
public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
auth.jdbcAuthentication()
.dataSource(dataSource)
.usersByUsernameQuery("select id, login as principal, pass as credentials from user where login=?").passwordEncoder(dPasswordEncoder());
}
@Override
protected void configure(HttpSecurity http) throws Exception {
http
.authorizeRequests()
.antMatchers("/**").permitAll()
.and()
.formLogin()
.loginPage("/")
.defaultSuccessUrl("/marks",true)
.permitAll();
}
@Bean
public JdbcUserDetailsManager jdbcUserDetailsManager() throws Exception {
JdbcUserDetailsManager jdbcUserDetailsManager = new JdbcUserDetailsManager();
jdbcUserDetailsManager.setDataSource(dataSource);
return jdbcUserDetailsManager;
}
@Bean
public AuthenticationSuccessHandler successHandler() {
SimpleUrlAuthenticationSuccessHandler handler = new SimpleUrlAuthenticationSuccessHandler();
handler.setUseReferer(true);
return handler;
}
@Bean
public PasswordEncoder dPasswordEncoder() {
return new BCryptPasswordEncoder();
}
}
I was using this configuration before and everything works fine.
解决方案
From javadoc of method setUsersByUsernameQuery :
Allows the default query string used to retrieve users based on username to be
* overridden, if default table or column names need to be changed. The default query
* is {@link #DEF_USERS_BY_USERNAME_QUERY}; when modifying this query, ensure that all
* returned columns are mapped back to the same column positions as in the default query.
* If the 'enabled' column does not exist in the source database, a permanent true
* value for this column may be returned by using a query similar to
* select username,password,'true' as enabled from users where username = ?
Is it correct, that you pass in sql query password as third parameter?
