File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Tomcat and the fly likes 'Exception performing authentication' using JDBC Realm Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Tomcat
Bookmark " Watch " New topic

'Exception performing authentication' using JDBC Realm

Adam Kronicki
Ranch Hand

Joined: Sep 01, 2009
Posts: 68

I have an annoying error which I can't solve for quite a while. I recently was introduced to container-based security and try to implement it. I have configure the realm as following:

Unfortunately I can't login with this. The log error messages are:

SEVERE: Exception performing authentication
java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null WHERE login = 'user1'' at line 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(
at com.mysql.jdbc.MysqlIO.sendCommand(
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(
at com.mysql.jdbc.Connection.execSQL(
at com.mysql.jdbc.PreparedStatement.executeInternal(
at com.mysql.jdbc.PreparedStatement.executeQuery(
at org.apache.catalina.realm.JDBCRealm.getRoles(
at org.apache.catalina.realm.JDBCRealm.authenticate(
at org.apache.catalina.realm.JDBCRealm.authenticate(
at org.apache.catalina.realm.CombinedRealm.authenticate(
at org.apache.catalina.realm.LockOutRealm.authenticate(
at org.apache.catalina.authenticator.FormAuthenticator.authenticate(
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(
at org.apache.catalina.core.StandardHostValve.invoke(
at org.apache.catalina.valves.ErrorReportValve.invoke(
at org.apache.catalina.valves.AccessLogValve.invoke(
at org.apache.catalina.core.StandardEngineValve.invoke(
at org.apache.catalina.connector.CoyoteAdapter.service(
at org.apache.coyote.http11.Http11Processor.process(
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(
at java.util.concurrent.ThreadPoolExecutor.runWorker(
at java.util.concurrent.ThreadPoolExecutor$

Please notice the '' around the user name... Is this correct?

As you see I also use allRolesMode="authOnly", because I don't need this functionality and moreover the database doesn't have and won't ever have an additional column for user roles (it is quite pointless if won't use it than every user will have the same value in this column - big waste of recourses.).

The server is Tomcat 7.0.19
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 17410

The Tomcat Context definition is XML. XML requires all attribute values to be delimited by double-quote characters, unlike HTML which can use single-quote characters or no delimiters at all. If you're referring to the usage of quotes in the MySQL error message in the SQLException, its usage of single and double quotes is just plain bizarre.

I wasn't familiar with the allRolesMode option myself. My apps tend to have a minimum of 3 roles: unsecured, app user, and app administrator. So I've never had that issue.

I think your real problem is that you've setup the following structure:

That's malformed XML. It's the equivalent of this:

Assuming what you gave us is correct, I'm surprized that Tomcat didn't simple fail to start because of a digester XML error.

An IDE is no substitute for an Intelligent Developer.
Adam Kronicki
Ranch Hand

Joined: Sep 01, 2009
Posts: 68
No Tomcat works fine with this. But i is the problem of allRolesMode="authOnly" . When I added user role tables and columns it works. Unfortunately as I wrote The db won't have user roles columns so I needed to pick one with enum values and listed all of them in the application web.xml file :/ I don't like this but it seems that there is no other way :/ Unless anybody knows another approach to ignore roles checking?
I agree. Here's the link:
subject: 'Exception performing authentication' using JDBC Realm
It's not a secret anymore!