title: Spring Security: Cannot determine value type from string 'admin'
body: <p>I am doing a Spring application and I want to implement a role based authorization for the users, so I followed <a href="https://medium.com/@gustavo.ponce.ch/spring-boot-spring-mvc-spring-security-mysql-a5d8545d837d" rel="nofollow noreferrer">this</a> tutorial. The problem I have is that I receive the following error after I try to login:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>This is how my application properties look:</p>
  
  <pre><code># ===============================
  # 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=?
  </code></pre>
  
  <p>This is how my security configuration looks like:</p>
  
  <pre><code>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/**");
      }
  
  }
  </code></pre>
  
  <p>And this is how my User class looks like:</p>
  
  <pre><code>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&lt;Role&gt; 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&lt;Book&gt; books = new HashSet&lt;&gt;();
  
      public Set&lt;Book&gt; getBooks() {
          return books;
      }
  
      public void setBooks(Set&lt;Book&gt; 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&lt;Role&gt; getRoles() {
          return roles;
      }
  
      public void setRoles(Collection&lt;Role&gt; roles) {
          this.roles = roles;
      }
  }
  </code></pre>
  
  <p>This is my pom.xml</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;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"&gt;
      &lt;modelVersion&gt;4.0.0&lt;/modelVersion&gt;
      &lt;parent&gt;
          &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
          &lt;artifactId&gt;spring-boot-starter-parent&lt;/artifactId&gt;
          &lt;version&gt;2.1.2.RELEASE&lt;/version&gt;
          &lt;relativePath/&gt; &lt;!-- lookup parent from repository --&gt;
      &lt;/parent&gt;
      &lt;groupId&gt;com.session.library&lt;/groupId&gt;
      &lt;artifactId&gt;libraryofbooks&lt;/artifactId&gt;
      &lt;version&gt;1.0.0&lt;/version&gt;
      &lt;name&gt;libraryofbooks&lt;/name&gt;
      &lt;description&gt;Library of books&lt;/description&gt;
  
      &lt;properties&gt;
          &lt;project.build.sourceEncoding&gt;UTF-8&lt;/project.build.sourceEncoding&gt;
          &lt;project.reporting.outputEncoding&gt;UTF-8&lt;/project.reporting.outputEncoding&gt;
          &lt;java.version&gt;1.8&lt;/java.version&gt;
      &lt;/properties&gt;
  
      &lt;dependencies&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-aop&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-cache&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-data-jpa&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-integration&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-security&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-thymeleaf&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-validation&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-web&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.retry&lt;/groupId&gt;
              &lt;artifactId&gt;spring-retry&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.session&lt;/groupId&gt;
              &lt;artifactId&gt;spring-session-core&lt;/artifactId&gt;
          &lt;/dependency&gt;
  
  
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework&lt;/groupId&gt;
              &lt;artifactId&gt;spring-jdbc&lt;/artifactId&gt;
          &lt;/dependency&gt;
  
  
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-devtools&lt;/artifactId&gt;
              &lt;scope&gt;runtime&lt;/scope&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;mysql&lt;/groupId&gt;
              &lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;
              &lt;scope&gt;runtime&lt;/scope&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-configuration-processor&lt;/artifactId&gt;
              &lt;optional&gt;true&lt;/optional&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-tomcat&lt;/artifactId&gt;
              &lt;scope&gt;provided&lt;/scope&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-test&lt;/artifactId&gt;
              &lt;scope&gt;test&lt;/scope&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.security&lt;/groupId&gt;
              &lt;artifactId&gt;spring-security-test&lt;/artifactId&gt;
              &lt;scope&gt;test&lt;/scope&gt;
          &lt;/dependency&gt;
  
          &lt;dependency&gt;
              &lt;groupId&gt;javax.xml.bind&lt;/groupId&gt;
              &lt;artifactId&gt;jaxb-api&lt;/artifactId&gt;
              &lt;version&gt;2.3.0&lt;/version&gt;
          &lt;/dependency&gt;
  
          &lt;dependency&gt;
              &lt;groupId&gt;org.projectlombok&lt;/groupId&gt;
              &lt;artifactId&gt;lombok&lt;/artifactId&gt;
              &lt;optional&gt;true&lt;/optional&gt;
          &lt;/dependency&gt;
  
          &lt;!-- Apache Commons FileUpload --&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;commons-fileupload&lt;/groupId&gt;
              &lt;artifactId&gt;commons-fileupload&lt;/artifactId&gt;
              &lt;version&gt;1.3.1&lt;/version&gt;
          &lt;/dependency&gt;
  
          &lt;!-- Apache Commons IO --&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;commons-io&lt;/groupId&gt;
              &lt;artifactId&gt;commons-io&lt;/artifactId&gt;
              &lt;version&gt;2.4&lt;/version&gt;
          &lt;/dependency&gt;
          &lt;!-- https://mvnrepository.com/artifact/org.webjars/jquery --&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.webjars&lt;/groupId&gt;
              &lt;artifactId&gt;jquery&lt;/artifactId&gt;
              &lt;version&gt;2.1.4&lt;/version&gt;
          &lt;/dependency&gt;
  
          &lt;!-- MySQL --&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;mysql&lt;/groupId&gt;
              &lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;
              &lt;version&gt;${mysql.version}&lt;/version&gt;
          &lt;/dependency&gt;
  
          &lt;!-- Joda-Time --&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;joda-time&lt;/groupId&gt;
              &lt;artifactId&gt;joda-time&lt;/artifactId&gt;
              &lt;version&gt;${joda-time.version}&lt;/version&gt;
          &lt;/dependency&gt;
  
          &lt;!-- https://mvnrepository.com/artifact/org.threeten/threetenbp --&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.threeten&lt;/groupId&gt;
              &lt;artifactId&gt;threetenbp&lt;/artifactId&gt;
              &lt;version&gt;1.3.6&lt;/version&gt;
          &lt;/dependency&gt;
      &lt;/dependencies&gt;
  
      &lt;build&gt;
          &lt;plugins&gt;
              &lt;plugin&gt;
                  &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
                  &lt;artifactId&gt;spring-boot-maven-plugin&lt;/artifactId&gt;
              &lt;/plugin&gt;
          &lt;/plugins&gt;
      &lt;/build&gt;
  
  &lt;/project&gt;
  </code></pre>
  
  <p>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. </p>
  
  <p>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.</p>