Spring Security: Cannot determine value type from string 'admin'
12,265
The users-query cannot be an arbitrary format unless you also replace the component that reads the result from that query.
In your case, I believe Spring Security thinks that admin is your password, because it is the 2nd column returned by the query.
public static final String DEF_USERS_BY_USERNAME_QUERY =
"select username,password,enabled from users where username = ?";
Spring Security when it queries the database does this
protected List<UserDetails> loadUsersByUsername(String username) {
return getJdbcTemplate().query(this.usersByUsernameQuery,
new String[] { username }, new RowMapper<UserDetails>() {
@Override
public UserDetails mapRow(ResultSet rs, int rowNum)
throws SQLException {
String username = rs.getString(1);
String password = rs.getString(2);
boolean enabled = rs.getBoolean(3);
return new User(username, password, enabled, true, true, true,
AuthorityUtils.NO_AUTHORITIES);
}
});
}
So the solution to your problem is to have your query return the correct pieces of information.
Comments
-
Artyomska almost 2 years
I am doing a Spring application and I want to implement a role based authorization for the users, so I followed this tutorial. The problem I have is that I receive the following error after I try to login:
org.springframework.security.authentication.InternalAuthenticationServiceException: PreparedStatementCallback; SQL [select id,username,password from user where username=?]; Cannot determine value type from string 'admin'; nested exception is java.sql.SQLDataException: Cannot determine value type from string 'admin' at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:123) ~[spring-security-core-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:144) ~[spring-security-core-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:175) ~[spring-security-core-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:200) ~[spring-security-core-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter.attemptAuthentication(UsernamePasswordAuthenticationFilter.java:94) ~[spring-security-web-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:212) ~[spring-security-web-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) [spring-security-web-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:74) [spring-security-web-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-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.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) [spring-security-web-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) [spring-security-web-5.1.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-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.3.RELEASE.jar:5.1.3.RELEASE] at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) [spring-security-web-5.1.3.RELEASE.jar:5.1.3.RELEASE]This is how my application properties look:
# =============================== # DATABASE # =============================== spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/library spring.datasource.username=root spring.datasource.password= # ============================================================== # = Keep the connection alive if idle for a long time (needed in production) # ============================================================== spring.datasource.tomcat.test-while-idle = true spring.datasource.tomcat.validation-query= SELECT 1 # =============================== # JPA / HIBERNATE # =============================== spring.jpa.show-sql=true spring.jpa.hibernate.ddl-auto = none spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect # ============================================================== # = Spring Security / Queries for AuthenticationManagerBuilder # ============================================================== myapp.queries.users-query=select id,username,password from user where username=? myapp.queries.roles-query=select u.username, r.name from user u inner join users_roles ur on(u.userid=ur.userid) inner join role r on(ur.roleid=r.roleid) where u.username=?This is how my security configuration looks like:
import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Configuration; import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder; 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 org.springframework.security.web.util.matcher.AntPathRequestMatcher; import javax.sql.DataSource; @Configuration @EnableWebSecurity public class SecurityConfiguration extends WebSecurityConfigurerAdapter { @Autowired private DataSource dataSource; @Value("${myapp.queries.users-query}") private String usersQuery; @Value("${myapp.queries.roles-query}") private String rolesQuery; @Override protected void configure(AuthenticationManagerBuilder auth) throws Exception { auth. jdbcAuthentication() .usersByUsernameQuery(usersQuery) .authoritiesByUsernameQuery(rolesQuery) .dataSource(dataSource); } @Override protected void configure(HttpSecurity http) throws Exception { http. authorizeRequests() .antMatchers("/").permitAll() .antMatchers("/login").permitAll() .antMatchers("/registration").permitAll() .antMatchers("/admin/**").hasAuthority("ROLE_ADMIN").anyRequest() .authenticated().and().csrf().disable().formLogin() .loginPage("/login").failureUrl("/login?error=true") .defaultSuccessUrl("/admin/home") .usernameParameter("username") .passwordParameter("password") .and().logout() .logoutRequestMatcher(new AntPathRequestMatcher("/logout")) .logoutSuccessUrl("/").and().exceptionHandling() .accessDeniedPage("/access-denied"); } @Override public void configure(WebSecurity web) throws Exception { web .ignoring() .antMatchers("/resources/**", "/static/**", "/css/**", "/js/**", "/images/**"); } }And this is how my User class looks like:
import javax.persistence.*; import java.util.Collection; import java.util.HashSet; import java.util.Set; @Entity @Table(name="user") public class User { @ManyToMany @JoinTable( name = "users_roles", joinColumns = @JoinColumn( name = "userid", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn( name = "roleid", referencedColumnName = "id")) private Collection<Role> roles; @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; @Column(name = "username", nullable = false) private String username; @Column(name = "password", nullable = false) private String password; @ManyToMany(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE }, mappedBy = "users") private Set<Book> books = new HashSet<>(); public Set<Book> getBooks() { return books; } public void setBooks(Set<Book> books) { this.books = books; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public Collection<Role> getRoles() { return roles; } public void setRoles(Collection<Role> roles) { this.roles = roles; } }This is my pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.2.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.session.library</groupId> <artifactId>libraryofbooks</artifactId> <version>1.0.0</version> <name>libraryofbooks</name> <description>Library of books</description> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-cache</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-integration</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-security</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-validation</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.retry</groupId> <artifactId>spring-retry</artifactId> </dependency> <dependency> <groupId>org.springframework.session</groupId> <artifactId>spring-session-core</artifactId> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-tomcat</artifactId> <scope>provided</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.security</groupId> <artifactId>spring-security-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>javax.xml.bind</groupId> <artifactId>jaxb-api</artifactId> <version>2.3.0</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <!-- Apache Commons FileUpload --> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.1</version> </dependency> <!-- Apache Commons IO --> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency> <!-- https://mvnrepository.com/artifact/org.webjars/jquery --> <dependency> <groupId>org.webjars</groupId> <artifactId>jquery</artifactId> <version>2.1.4</version> </dependency> <!-- MySQL --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>${mysql.version}</version> </dependency> <!-- Joda-Time --> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>${joda-time.version}</version> </dependency> <!-- https://mvnrepository.com/artifact/org.threeten/threetenbp --> <dependency> <groupId>org.threeten</groupId> <artifactId>threetenbp</artifactId> <version>1.3.6</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>I use the MySQL provided by xampp for my data storage and hibernate, and the User table in there has int(11) for the id field and varchar(100) for the username and password fields (I haven't encrypted the password yet by the way, it is just a simple varchar). The table has a field 1, admin, admin and a field 2, usr, usr.
I am pretty lost about what the problem consists in. I tried to use "spring" instead of "myapp" in the application.properties and it didn't work.