Error in login query using custom DB logins

When solving some problems, I saw the logs on this server were heavily poluted with errors about logging in via a custom database.

This has worked before, but it isn’t used a lot, so I don’t know when it broke.

I have checked all the queries, and they run in the database (if I replace the question marks with the right variables). I’ve checked the SQL events for failed queries, but these queries do not appear in it.

So I believe the error happens inside the JDBC driver, and doesn’t even reach the server. Probably there’s a difference in the number of variables that are passed to the query vs the number of question marks. But I have no idea what query I should check.

Does anyone have an idea how to solve this?

These are the used queries, note that these are not secure (they are only used for RFID logins with minimal rights), and not complete (there’s no schedule implementation). But as far as I can see, they should work.

Authentication query: SELECT UserName, UserName, '' FROM Logins WHERE UserName = ? AND RfidTag = ? AND RfidTag != ''
List roles query: SELECT Role FROM Roles
User’s roles query: SELECT Role FROM UserRoles WHERE UserName = ?
List users query: SELECT UserName, UserName, '' FROM Logins

I’m able to list the users and the roles, so those queries work. But I’m unable to login using an RFID username/badge combination.

Note that this is an older Ignition version, (7.8.5)

12:48:14 PM 	Login 	Error attempting client login.
java.lang.Exception: SQL exception during authentication
at com.inductiveautomation.ignition.gateway.authentication.impl.DatabaseExpertUserSource.authenticate(DatabaseExpertUserSource.java:137)
at com.inductiveautomation.ignition.gateway.authentication.UserSourceWrapper.authenticate(UserSourceWrapper.java:181)
at com.inductiveautomation.ignition.gateway.authentication.UserSourceWrapper.authenticate(UserSourceWrapper.java:185)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.Login.client(Login.java:109)
at sun.reflect.GeneratedMethodAccessor22.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:209)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:345)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
at org.eclipse.jetty.server.Server.handle(Server.java:518)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:698)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObjectNoType(SQLServerPreparedStatement.java:894)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:919)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.setObject(SRConnectionWrapper.java:971)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepQuery(SRConnectionWrapper.java:142)
at com.inductiveautomation.ignition.gateway.authentication.impl.DatabaseExpertUserSource.findContactInfo(DatabaseExpertUserSource.java:168)
at com.inductiveautomation.ignition.gateway.authentication.impl.DatabaseExpertUserSource.authenticate(DatabaseExpertUserSource.java:116)
... 34 more
12:48:14 PM 	DB_ManualMode 	[profileName=RFID_Logins] SQL exception during authentication
  	
com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:698)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObjectNoType(SQLServerPreparedStatement.java:894)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:919)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.setObject(SRConnectionWrapper.java:971)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepQuery(SRConnectionWrapper.java:142)
at com.inductiveautomation.ignition.gateway.authentication.impl.DatabaseExpertUserSource.findContactInfo(DatabaseExpertUserSource.java:168)
at com.inductiveautomation.ignition.gateway.authentication.impl.DatabaseExpertUserSource.authenticate(DatabaseExpertUserSource.java:116)
at com.inductiveautomation.ignition.gateway.authentication.UserSourceWrapper.authenticate(UserSourceWrapper.java:181)
at com.inductiveautomation.ignition.gateway.authentication.UserSourceWrapper.authenticate(UserSourceWrapper.java:208)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.Login.client(Login.java:109)
at sun.reflect.GeneratedMethodAccessor22.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:209)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:345)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
at org.eclipse.jetty.server.Server.handle(Server.java:518)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
at java.lang.Thread.run(Unknown Source)

Apparently, deleting and recreating the user source (with all the same settings) solved the issue.

I don’t know what went wrong with the old one.