debug spring security authentication-manager

Question

I'm trying to get Spring security using an example from the mykong website...
http://www.mkyong.com/spring-security/spring-security-form-login-using-database/

I had it working using a simple MySQL table but now I'm trying to get it to work using db2 where the passwords are stored in the database encrypted with md5.

Here is how I have set up...

<authentication-manager>
    <authentication-provider>
        <password-encoder hash="md5" />
        <jdbc-user-service data-source-ref="dataSource"

            users-by-username-query="
                select employeenumber, employeepassword 
                from employee where employeenumber like ? 
                and employeestatus = 'Active'"

            authorities-by-username-query="
                select employeenumber, securitygroupcode 
                from employee 
                where employeenumber like ? 
                and employeestatus = 'Active'" 
        />
    </authentication-provider>
</authentication-manager>

When I try and log in to my app and I enter incorrect login details it correctly tells me...

Your login attempt was not successful, try again. Caused: Invalid username or password

But when I login correctly I enter correct login details I get...

Your login attempt was not successful, try again. Caused : PreparedStatementCallback; uncategorized SQLException for SQL [select employeenumber as username, employeepassword as password from epos.employee where employeenumber like ? and sitedirectoryid = 10001 and employeestatus = 'Active']; SQL state [null]; error code [-99999]; [ibm][db2][jcc][10145][10897] Invalid parameter 3: Parameter index is out of range.; nested exception is com.ibm.db2.jcc.b.SqlException: [ibm][db2][jcc][10145][10897] Invalid parameter 3: Parameter index is out of range.

thanks all

Is it only a MD5 hash or is there something else in the generated hash (i.e. a salt or something). Also (if I'm not mistaken) the MD5 generated by spring is lower case so if the db one is uppercase it fails. — M. Deinum
Hi, I've just had a look at what is stored in my db and it matches what I get when I do a use lower case at md5encryption.com . So I guess that means it should be working right? — Richie
I figured out what was wrong. It was a mistake on my side. Now what happens is that when I enter the correct details i get a different error. I'm changing my post to reflect the new error — Richie
Please modify your question to reflect the actual code, judging from the stacktrace what you posted isn't the actual SQL. How are we to help you if we cannot see the real thing?! — M. Deinum
thanks for your help. I am an idiot. I have not included the enabled column in my users-by-username-query query. Very silly — Richie

Richie Richie · Accepted Answer · 2013-11-27T09:44:25

Thank you for your help. My issue was that I had not included the enabled column in my query which obviously would cause a parameter 3: Parameter index is out of range.

Anyway I decided to give myself a bit more control and extend the JdbcDaoImpl class and write my own setAuthoritiesByUsernameQuery(). I still haven't tested it yet but I'm much happier with this approach as it allows the developer to debug through the code executing the query and I can still inject md5 as my encrption. Its the best of both worlds. Hopefully I get it working!

debug spring security authentication-manager

1 Answers