PreparedStatementCallback;invalid ResultSet access for SQL[SQLStatememnt];nested exception is java.sql.SQLException:Invalid column index Spring Auth

Question

I am running a Spring Boot app with the Spring BootSecurtiy i.e., authentication manager builder. The login should be done by the entities of the database. But it shows the following error whenever I login.

PreparedStatementCallback; invalid ResultSet access for SQL [SELECT USER_LOGIN_ID, PASSWORD FROM USER_ACCOUNT WHERE USER_LOGIN_ID=?]; nested exception is java.sql.SQLException: Invalid column index

Edit 1: As suggested I changed the query to:

SELECT USER_LOGIN_ID, PASSWORD, ENABLED FROM USER_ACCOUNT WHERE USER_LOGIN_ID=?

and

SELECT USER_LOGIN_ID, PASSWORD, ACTIVE FROM USER_ACCOUNT WHERE USER_LOGIN_ID=?

I still get the error as

Reason: PreparedStatementCallback; bad SQL grammar [SELECT USER_LOGIN_ID, PASSWORD, ENABLED FROM USER_ACCOUNT WHERE USER_LOGIN_ID=?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "ENABLED": invalid identifier

My Security Config class

package io.trial;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;

import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.method.configuration.EnableGlobalMethodSecurity;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;

@Configuration
@EnableWebSecurity
@EnableGlobalMethodSecurity(securedEnabled=true)
public class SecurityConfig extends WebSecurityConfigurerAdapter  {

    @Autowired
    public void globalConfig(AuthenticationManagerBuilder auth, DataSource dataSource) throws Exception{
        /*auth.jdbcAuthentication()
          .dataSource(dataSource)
          .authoritiesByUsernameQuery("select p.user as principal, p.password as credentials, true from Provider p where p.user= ?");*/
        //System.out.println(dataSource);
        auth.jdbcAuthentication().dataSource(dataSource)
          .usersByUsernameQuery("SELECT USER_LOGIN_ID, PASSWORD FROM USER_ACCOUNT WHERE USER_LOGIN_ID=?")
          .authoritiesByUsernameQuery("SELECT USER_LOGIN_ID , PASSWORD FROM USER_ACCOUNT WHERE USER_LOGIN_ID=?");
    }
}

Answer 1

It's trying to read the column index that doesn't exist causing this exception. Your data model should support the active/inactive of user as an additional column. So, the query should be:

SELECT USER_LOGIN_ID, PASSWORD, ACTIVE FROM USER_ACCOUNT WHERE USER_LOGIN_ID=?

I don't see any other issue.

Answer 2

Thanks to Nisse Engström. Changing the query to

SELECT USER_LOGIN_ID, PASSWORD, 'TRUE' FROM USER_ACCOUNT WHERE USER_LOGIN_ID=?

solved it.