Apache Shiro LOGIN Failed using Token

Question

I'm new with apache shiro and currently working with jdbcRealm. But during login with token an SQL Exception is showing as below -

org.apache.shiro.authc.AuthenticationException: There was a SQL error while authenticating user [nnnnnnnn]

18:08:47,738 ERROR [stderr] (http-localhost-127.0.0.1-8443-1)   at org.apache.shiro.realm.jdbc.JdbcRealm.doGetAuthenticationInfo(JdbcRealm.java:254)

18:08:47,748 ERROR [stderr] (http-localhost-127.0.0.1-8443-1)   at org.apache.shiro.realm.AuthenticatingRealm.getAuthenticationInfo(AuthenticatingRealm.java:568)
....
Caused by: java.sql.SQLException: Invalid column index
....

SHIRO.INI File is written as below -

[main]

# Own Realm
jdbcRealm = org.apache.shiro.realm.jdbc.JdbcRealm
jdbcRealm.permissionsLookupEnabled = true

# datasource
ds = oracle.jdbc.pool.OracleConnectionPoolDataSource
ds.URL = jdbc:oracle:thin:@192.168.2.10:1522:WBORCLSTDONE
ds.user = WISENPA
ds.password = issac123
jdbcRealm.dataSource = $ds

jdbcRealm.authenticationQuery = "SELECT PASSWORD FROM WB_NETB_USER_MASTER WHERE LOGINID = ?"

[users]

[roles]

[urls]

# enable authc filter for all application pages
/InternetBanking_v1/**=authc

I have written one REST service where values have been checked and login with token has been tried.

///--- Fetching actual Hashed Password from Database Table

Query query = entityManager.createQuery("SELECT userLogin FROM UserLogin userLogin where userLogin.loginid=:loginid and userLogin.password=:passwd" )
                .setParameter("loginid", login.getLoginid())
        .setParameter("passwd",hashedPasswordBase64);
                loggedInUser = (UserLogin) query.getSingleResult();

                if (loggedInUser==null){
                    loggedInUser = new UserLogin();
                }   

                //---- Creating Token
                UsernamePasswordToken token = new UsernamePasswordToken(StrLoginId, hashedPasswordBase64);
                token.setRememberMe(true);
                SecurityUtils.setSecurityManager(sm);
                Subject currentUser = SecurityUtils.getSubject();

                try {
                    currentUser.login(token);     ////// HERE ERROR APPEARS 
                    System.out.println("----- Login Success -----");

                } catch (IncorrectCredentialsException ice) {
                      System.out.println("Incorrect username/password!");
                }

When the line containing -

currentUser.login(token);

is executed The Exception "java.sql.SQLException: Invalid column index" is coming.

Please anybody help me whether there are any configurational issue in shiro.ini file or any other flaws in my code -

Many thanks,

Jayanta P.

Wouter Wouter · Accepted Answer · 2014-08-13T17:22:33

You should remove the quotes " from your query.

Instead of:

jdbcRealm.authenticationQuery = "SELECT PASSWORD FROM WB_NETB_USER_MASTER WHERE LOGINID = ?"

do

jdbcRealm.authenticationQuery = SELECT PASSWORD FROM WB_NETB_USER_MASTER WHERE LOGINID = ?

Apache Shiro LOGIN Failed using Token

SHIRO.INI File is written as below -

1 Answers