代码之家  ›  专栏  ›  技术社区  ›  Joseph Hwang

如何在Spring引导安全性中将用户名传递到application.properties的登录SQL代码中?

spring-security spring-boot spring java
  •  0
  • Joseph Hwang  · 技术社区  · 7 年前

    我试着用Spring引导安全性编写简单的登录代码。首先,这是包含登录查询的application.properties代码。 

    server.error.whitelabel.enabled=FALSE

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/test?characterEncoding=utf8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=password
spring.queries.users-query=select user_name, password_hash, id from users where user_name=?
spring.queries.roles-query=select user_name, 'ADMIN' AS 'role' from users where user_name=?

    下面的代码是关于Spring引导登录安全性的 

    @Configuration
@EnableWebSecurity
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private DataSource dataSource;

    @Autowired
    private BCryptPasswordEncoder bCryptPasswordEncoder;

    @Value("{spring.queries.users-query}")
    private String usersQuery;

    @Value("{spring.queries.roles-query}")
    private String rolesQuery;

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        // TODO Auto-generated method stub
        auth.jdbcAuthentication().usersByUsernameQuery(usersQuery).authoritiesByUsernameQuery(rolesQuery)
            .dataSource(dataSource).passwordEncoder(bCryptPasswordEncoder);
    }

    问题是我不知道如何将用户名值传输到application.properties文件的spring.querys.users-query语句中。我执行这个Spring引导安全代码而不做任何修改,但是异常是这样抛出的, 

    2018-12-15 16:03:00.821 ERROR 2284 --- [nio-8090-exec-1] w.a.UsernamePasswordAuthenticationFilter : An internal error occurred while trying to authenticate the user.    

Caused by: org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [{spring.queries.users-query}]; Parameter index out of range (1 > number of parameters, which is 0).; nested exception is java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:657) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:751) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUsersByUsername(JdbcDaoImpl.java:227) ~[spring-security-core-5.0.9.RELEASE.jar:5.0.9.RELEASE]
        at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUserByUsername(JdbcDaoImpl.java:184) ~[spring-security-core-5.0.9.RELEASE.jar:5.0.9.RELEASE]
        at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:104) ~[spring-security-core-5.0.9.RELEASE.jar:5.0.9.RELEASE]
        ... 57 common frames omitted
    Caused by: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.47.jar:5.1.47]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[mysql-connector-java-5.1.47.jar:5.1.47]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[mysql-connector-java-5.1.47.jar:5.1.47]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) ~[mysql-connector-java-5.1.47.jar:5.1.47]
        at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3367) ~[mysql-connector-java-5.1.47.jar:5.1.47]
        at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3352) ~[mysql-connector-java-5.1.47.jar:5.1.47]
        at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4068) ~[mysql-connector-java-5.1.47.jar:5.1.47]
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setString(HikariProxyPreparedStatement.java) ~[HikariCP-2.7.9.jar:na]
        at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:400) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:232) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:163) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.doSetValue(ArgumentPreparedStatementSetter.java:69) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.setValues(ArgumentPreparedStatementSetter.java:50) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:664) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        ... 64 common frames omitted

    如何将用户名值传递到Spring安全配置的login sql语句中?

    1 回复  |  直到 7 年前
        1
  •  1
  •   Akash    7 年前

    看来@value注释有问题。它应该用作 @Value("${<property}") ,如果没有正确使用,Spring引导将无法绑定值。 

    @Value("${spring.queries.users-query}")
private String usersQuery;

@Value("${spring.queries.roles-query}")
private String rolesQuery;

    您不需要在此处传递/附加用户名值。 usersByUsernameQuery() 是显式设置查询。

    推荐文章
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号