스프링 보안 : java.sql.SQLException : 컬럼 인덱스가 범위를 벗어남, 3> 2

저는 스프링 4를 사용 중이며 스프링 보안 3을 사용하여 로그인 양식을 제어하려고합니다. 그래서 데이터베이스에 다음 표가 있습니다.

사용자 표 :

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;

역할 테이블 :

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;

그리고 users_roles

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;

내 봄 보안 구성 파일에는 다음과 같은 줄이 있습니다.

<?xml version="1.0" encoding="UTF-8"?>
<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">

    <http use-expressions="true">
        <intercept-url pattern="/users**" access="hasRole('ROLE_ADMIN')" />
        <intercept-url pattern="/users/ **" access="hasRole('ROLE_ADMIN')" />
        <intercept-url pattern="/roles **" access="hasRole('ROLE_ADMIN')" />
        <intercept-url pattern="/roles/ **" access="hasRole('ROLE_ADMIN')" />
        <form-login login-page='/login'/>
        <logout logout-url="/logout"/>
        <logout />
    </http>

    <authentication-manager>
        <authentication-provider>
            <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 = ?" />
        </authentication-provider>
    </authentication-manager>

</beans:beans>

연결하려고하면 다음 오류가 있습니다.

> 2014-12-31 20:35:21 ERROR UsernamePasswordAuthenticationFilter:218 -
> An internal error occurred while trying to authenticate the user.
> org.springframework.security.authentication.InternalAuthenticationServiceException:
> PreparedStatementCallback; SQL [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 = ?]; Column Index out
> of range, 3 > 2. ; nested exception is java.sql.SQLException: Column
> Index out of range, 3 > 2.    at
> org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:110)
>   at
> org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:132)
>   at
> org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:156)
>   at
> org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:177)
>   at
> org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter.attemptAuthentication(UsernamePasswordAuthenticationFilter.java:94)
>   at
> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:211)
>   at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>   at
> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
>   at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>   at
> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
>   at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>   at
> org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
>   at
> org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
>   at
> org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)
>   at
> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)
>   at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
>   at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
>   at
> org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
>   at
> org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
>   at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
>   at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
>   at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
>   at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
>   at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
>   at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>   at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
>   at
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
>   at
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
>   at
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
>   at
> org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
>   at java.lang.Thread.run(Thread.java:745) Caused by:
> org.springframework.dao.TransientDataAccessResourceException:
> PreparedStatementCallback; SQL [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 = ?]; Column Index out
> of range, 3 > 2. ; nested exception is java.sql.SQLException: Column
> Index out of range, 3 > 2.    at
> org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108)
>   at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
>   at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
>   at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
>   at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
>   at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
>   at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:727)
>   at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:737)
>   at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:787)
>   at
> org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUsersByUsername(JdbcDaoImpl.java:192)
>   at
> org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUserByUsername(JdbcDaoImpl.java:151)
>   at
> org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:102)
>   ... 30 more Caused by: java.sql.SQLException: Column Index out of
> range, 3 > 2.     at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)     at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)     at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)     at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)     at
> com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:758)
>   at com.mysql.jdbc.ResultSetImpl.getBoolean(ResultSetImpl.java:1503)
>   at
> org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:196)
>   at
> org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:192)
>   at
> org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
>   at
> org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
>   at
> org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:708)
>   at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
>   ... 37 more

제 구성에서 무엇이 잘못 됐는지 말해 주시겠습니까?

고맙습니다

해결법

==============================
1.users-by-username-query와 authority-by-username-query가 섞여 있습니다. 첫 번째 SQL 쿼리를 두 번째 SQL 쿼리로 이동하고 그 반대도 마찬가지입니다.

users-by-username-query와 authority-by-username-query가 섞여 있습니다. 첫 번째 SQL 쿼리를 두 번째 SQL 쿼리로 이동하고 그 반대도 마찬가지입니다.

어쩌면 1을 users.enabled로 변경할 수도 있습니다.
==============================
2.이 오류는 열이 누락되어 있기 때문에 발생합니다. PreparedStatement는 3 개의 값을 얻기 위해 3 개의 열을 찾고 2만을 찾는다.

이 오류는 열이 누락되어 있기 때문에 발생합니다. PreparedStatement는 3 개의 값을 얻기 위해 3 개의 열을 찾고 2만을 찾는다.

users-by-username-query 및 authorities-by-username-query는 select에서 3 개의 열을 필요로합니다.

from https://stackoverflow.com/questions/27725814/spring-security-java-sql-sqlexception-column-index-out-of-range-3-2 by cc-by-sa and MIT license

'SPRING' 카테고리의 다른 글

[SPRING] 여러 필드가있는 사용자 정의 주석 유효성 검사 (0)	2019.07.19
[SPRING] Hibernate Envers - createQuery (...) .executeUpdate (), .persist () 및 .merge ()에 대한 감사 레코드를 작성하지 않습니다. (0)	2019.07.19
[SPRING] Spring Security 버전 2.0의 커스텀 TokenGranter를 가진 OAuth2. + (0)	2019.07.19
[SPRING] 컨트롤러에서 응답 방지 (0)	2019.07.19
[SPRING] 봄철 자바 주석 스캐닝 (0)	2019.07.19

복붙노트

[SPRING] 스프링 보안 : java.sql.SQLException : 컬럼 인덱스가 범위를 벗어남, 3> 2

스프링 보안 : java.sql.SQLException : 컬럼 인덱스가 범위를 벗어남, 3> 2

해결법

1.users-by-username-query와 authority-by-username-query가 섞여 있습니다. 첫 번째 SQL 쿼리를 두 번째 SQL 쿼리로 이동하고 그 반대도 마찬가지입니다.

2.이 오류는 열이 누락되어 있기 때문에 발생합니다. PreparedStatement는 3 개의 값을 얻기 위해 3 개의 열을 찾고 2만을 찾는다.

'SPRING' 카테고리의 다른 글

티스토리툴바