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

sam ross

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=?");
    }
}
YuVi

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type

From Dev

java.sql.SQLException: Invalid column name

From Dev

SQL exception column invalid, java sql statement

From Dev

Hibernate java.sql.SQLException: Invalid column name

From Dev

Error: java.sql.SqlException:Invalid column type

From Dev

Invalid For loop control variable, nested ResultSet SQL

From Dev

java.sql.SQLException: invalid arguments in call

From Dev

java.sql.SQLException: Invalid handle

From Dev

Invalid cursor state java.sql.SQLException

From Dev

java.sql.SQLException: Invalid column type when executing procedure with package level types from jdbc

From Dev

Java SQL ResultSet Exception

From Dev

Spring security : java.sql.SQLException: Column Index out of range, 3 > 2

From Dev

Exception in thread "main" java.sql.SQLException: Operation not allowed after ResultSet closed

From Dev

java.sql.SQLException: ResultSet closed

From Dev

java.sql.SQLException: Exhausted Resultset error

From Dev

-> java.sql.SQLException: Exhausted Resultset

From Dev

java.sql.SQLException: Exhausted Resultset error

From Dev

Invalid Column Index with PL/SQL Functions

From Dev

SQL with variable in LIKE statement: Invalid column Index

From Dev

java.sql.SQLException: Invalid or Stale Connection found in the Connection Cache

From Dev

Get the SQLException java.sql.SQLException: ResultSet.next was not called

From Dev

Invalid column index error oracle db java

From Dev

java.sql.SQLException: Column Index out of range, 0 < 1

From Dev

java.sql.SQLException: Column Index out of range, 8 > 6

From Dev

Invalid Column exception

From Dev

" nested exception is java.sql.SQLException: Incorrect syntax near '?' "

From Dev

Invalid column index in preparedStatement

From Dev

Invalid column index in preparedStatement

From Dev

Invalid column name for SQL Server in nested queries

Related Related

  1. 1

    SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type

  2. 2

    java.sql.SQLException: Invalid column name

  3. 3

    SQL exception column invalid, java sql statement

  4. 4

    Hibernate java.sql.SQLException: Invalid column name

  5. 5

    Error: java.sql.SqlException:Invalid column type

  6. 6

    Invalid For loop control variable, nested ResultSet SQL

  7. 7

    java.sql.SQLException: invalid arguments in call

  8. 8

    java.sql.SQLException: Invalid handle

  9. 9

    Invalid cursor state java.sql.SQLException

  10. 10

    java.sql.SQLException: Invalid column type when executing procedure with package level types from jdbc

  11. 11

    Java SQL ResultSet Exception

  12. 12

    Spring security : java.sql.SQLException: Column Index out of range, 3 > 2

  13. 13

    Exception in thread "main" java.sql.SQLException: Operation not allowed after ResultSet closed

  14. 14

    java.sql.SQLException: ResultSet closed

  15. 15

    java.sql.SQLException: Exhausted Resultset error

  16. 16

    -> java.sql.SQLException: Exhausted Resultset

  17. 17

    java.sql.SQLException: Exhausted Resultset error

  18. 18

    Invalid Column Index with PL/SQL Functions

  19. 19

    SQL with variable in LIKE statement: Invalid column Index

  20. 20

    java.sql.SQLException: Invalid or Stale Connection found in the Connection Cache

  21. 21

    Get the SQLException java.sql.SQLException: ResultSet.next was not called

  22. 22

    Invalid column index error oracle db java

  23. 23

    java.sql.SQLException: Column Index out of range, 0 < 1

  24. 24

    java.sql.SQLException: Column Index out of range, 8 > 6

  25. 25

    Invalid Column exception

  26. 26

    " nested exception is java.sql.SQLException: Incorrect syntax near '?' "

  27. 27

    Invalid column index in preparedStatement

  28. 28

    Invalid column index in preparedStatement

  29. 29

    Invalid column name for SQL Server in nested queries

HotTag

Archive