title: Spring security : java.sql.SQLException: Column Index out of range, 3 > 2
body: <p>I'm using spring 4 and I want to use spring security 3 to control my login form.
  So I have the following tables in database : </p>
  
  <p><strong>users table :</strong> </p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `users` (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(45) NOT NULL,
    password VARCHAR(45) NOT NULL,
    email VARCHAR(45) NOT NULL,
    enabled TINYINT NOT NULL DEFAULT 1 ,
    PRIMARY KEY (id),
    UNIQUE KEY uni_name_email_key (name, email)
    )ENGINE = InnoDB;
  </code></pre>
  
  <p><strong>roles table :</strong> </p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `roles` (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(45) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uni_name_id_role (name)
    )ENGINE = InnoDB;
  </code></pre>
  
  <p>And <strong>users_roles</strong></p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `users_roles` (
    id INT(11) NOT NULL AUTO_INCREMENT,
    user_id INT(11) NOT NULL ,
    role_id INT(11) NOT NULL ,
    PRIMARY KEY (id),
    UNIQUE KEY uni_user_role_key (user_id,role_id),
    CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users (id),
    CONSTRAINT fk_role_id FOREIGN KEY (role_id) REFERENCES roles (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
  )ENGINE = InnoDB;
  </code></pre>
  
  <p>My <strong>spring-security config</strong> file has the following lines : </p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;beans:beans xmlns="http://www.springframework.org/schema/security"
               xmlns:beans="http://www.springframework.org/schema/beans"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xsi:schemaLocation="http://www.springframework.org/schema/beans
             http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
             http://www.springframework.org/schema/security
             http://www.springframework.org/schema/security/spring-security.xsd"&gt;
  
      &lt;http use-expressions="true"&gt;
          &lt;intercept-url pattern="/users**" access="hasRole('ROLE_ADMIN')" /&gt;
          &lt;intercept-url pattern="/users/ **" access="hasRole('ROLE_ADMIN')" /&gt;
          &lt;intercept-url pattern="/roles **" access="hasRole('ROLE_ADMIN')" /&gt;
          &lt;intercept-url pattern="/roles/ **" access="hasRole('ROLE_ADMIN')" /&gt;
          &lt;form-login login-page='/login'/&gt;
          &lt;logout logout-url="/logout"/&gt;
          &lt;logout /&gt;
      &lt;/http&gt;
  
      &lt;authentication-manager&gt;
          &lt;authentication-provider&gt;
              &lt;jdbc-user-service data-source-ref="dataSource"
                  users-by-username-query="select users.name, roles.name from users
                      join users_roles on users.id = users_roles.user_id
                      join roles on users_roles.role_id = roles.id
                      where users.name = ?"
                  authorities-by-username-query="select users.name, users.password , 1
                      from users where users.name = ?" /&gt;
          &lt;/authentication-provider&gt;
      &lt;/authentication-manager&gt;
  
  &lt;/beans:beans&gt;
  </code></pre>
  
  <p>When i try to connect i have the following error : </p>
  
  <pre><code>&gt; 2014-12-31 20:35:21 ERROR UsernamePasswordAuthenticationFilter:218 -
  &gt; An internal error occurred while trying to authenticate the user.
  &gt; org.springframework.security.authentication.InternalAuthenticationServiceException:
  &gt; PreparedStatementCallback; SQL [select users.name, roles.name from
  &gt; users join users_roles on users.id = users_roles.user_id join roles on
  &gt; users_roles.role_id = roles.id where users.name = ?]; Column Index out
  &gt; of range, 3 &gt; 2. ; nested exception is java.sql.SQLException: Column
  &gt; Index out of range, 3 &gt; 2.    at
  &gt; org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:110)
  &gt;   at
  &gt; org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:132)
  &gt;   at
  &gt; org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:156)
  &gt;   at
  &gt; org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:177)
  &gt;   at
  &gt; org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter.attemptAuthentication(UsernamePasswordAuthenticationFilter.java:94)
  &gt;   at
  &gt; org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:211)
  &gt;   at
  &gt; org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  &gt;   at
  &gt; org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
  &gt;   at
  &gt; org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  &gt;   at
  &gt; org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
  &gt;   at
  &gt; org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  &gt;   at
  &gt; org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
  &gt;   at
  &gt; org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
  &gt;   at
  &gt; org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)
  &gt;   at
  &gt; org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)
  &gt;   at
  &gt; org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
  &gt;   at
  &gt; org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
  &gt;   at
  &gt; org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
  &gt;   at
  &gt; org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
  &gt;   at
  &gt; org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
  &gt;   at
  &gt; org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
  &gt;   at
  &gt; org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
  &gt;   at
  &gt; org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
  &gt;   at
  &gt; org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
  &gt;   at
  &gt; org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
  &gt;   at
  &gt; org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
  &gt;   at
  &gt; org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
  &gt;   at
  &gt; org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
  &gt;   at
  &gt; org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
  &gt;   at
  &gt; org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
  &gt;   at java.lang.Thread.run(Thread.java:745) Caused by:
  &gt; org.springframework.dao.TransientDataAccessResourceException:
  &gt; PreparedStatementCallback; SQL [select users.name, roles.name from
  &gt; users join users_roles on users.id = users_roles.user_id join roles on
  &gt; users_roles.role_id = roles.id where users.name = ?]; Column Index out
  &gt; of range, 3 &gt; 2. ; nested exception is java.sql.SQLException: Column
  &gt; Index out of range, 3 &gt; 2.    at
  &gt; org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108)
  &gt;   at
  &gt; org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
  &gt;   at
  &gt; org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
  &gt;   at
  &gt; org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
  &gt;   at
  &gt; org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
  &gt;   at
  &gt; org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
  &gt;   at
  &gt; org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:727)
  &gt;   at
  &gt; org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:737)
  &gt;   at
  &gt; org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:787)
  &gt;   at
  &gt; org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUsersByUsername(JdbcDaoImpl.java:192)
  &gt;   at
  &gt; org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUserByUsername(JdbcDaoImpl.java:151)
  &gt;   at
  &gt; org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:102)
  &gt;   ... 30 more Caused by: java.sql.SQLException: Column Index out of
  &gt; range, 3 &gt; 2.     at
  &gt; com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)     at
  &gt; com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)     at
  &gt; com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)     at
  &gt; com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)     at
  &gt; com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:758)
  &gt;   at com.mysql.jdbc.ResultSetImpl.getBoolean(ResultSetImpl.java:1503)
  &gt;   at
  &gt; org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:196)
  &gt;   at
  &gt; org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:192)
  &gt;   at
  &gt; org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
  &gt;   at
  &gt; org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
  &gt;   at
  &gt; org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:708)
  &gt;   at
  &gt; org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
  &gt;   ... 37 more
  </code></pre>
  
  <p>Could you please tell me what's wrong in my configuration ?</p>
  
  <p>Thank you</p>