# Authenticate Username Against Database during Login

**URL:** <https://forum.inductiveautomation.com/t/authenticate-username-against-database-during-login/4493>\
**Category:** Ignition\
**Created:** [May 17, 2012, 6:24pm UTC](https://forum.inductiveautomation.com/t/authenticate-username-against-database-during-login/4493 "2012-05-17T18:24:33Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![jlr573](https://avatars.discourse-cdn.com/v4/letter/j/c6cbf5/32.png) [@jlr573](https://forum.inductiveautomation.com/u/jlr573)\
**Post date:** [May 17, 2012, 6:24pm UTC](https://forum.inductiveautomation.com/t/authenticate-username-against-database-during-login/4493/1 "2012-05-17T18:24:33Z")

</div>

This script is supposed to pop-up a warningBox when the badgeID that is entered is not found in the database but the script is missing the if( statement and running directly to the messageBox saying the user has logged-in as user None. What am I missing?

[code]  
badgeID = event.source.parent.getComponent(‘txtBadgeID’).text  
exists = system.db.runScalarQuery(“SELECT badge\_id FROM users WHERE badge\_id LIKE ‘%s’” % badgeID)  
userName = system.db.runScalarQuery(“SELECT username FROM users WHERE badge\_id LIKE ‘%s’” % badgeID)

if exists == “None”:  
system.gui.warningBox(“Username Doesn’t Exist!”)

else:  
system.gui.messageBox(“Welcome You are Logged in as: %s” % userName)  
[/code]

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [May 17, 2012, 6:26pm UTC](https://forum.inductiveautomation.com/t/authenticate-username-against-database-during-login/4493/2 "2012-05-17T18:26:35Z")

</div>

Well I am guessing the exists query returns a NULL or a value. You are checking right now if the return value is a string “None”. I think you want:[code]badgeID = event.source.parent.getComponent(‘txtBadgeID’).text  
exists = system.db.runScalarQuery(“SELECT badge\_id FROM users WHERE badge\_id LIKE ‘%s’” % badgeID)  
userName = system.db.runScalarQuery(“SELECT username FROM users WHERE badge\_id LIKE ‘%s’” % badgeID)

if exists == None:  
system.gui.warningBox(“Username Doesn’t Exist!”)

else:  
system.gui.messageBox(“Welcome You are Logged in as: %s” % userName)[/code]

---

<div class="post-metadata">

**Author:** ![jlr573](https://avatars.discourse-cdn.com/v4/letter/j/c6cbf5/32.png) [@jlr573](https://forum.inductiveautomation.com/u/jlr573)\
**Post date:** [May 18, 2012, 1:26pm UTC](https://forum.inductiveautomation.com/t/authenticate-username-against-database-during-login/4493/3 "2012-05-18T13:26:16Z")

</div>

When I try to log in into the application using the credentials from the database user Auth. I get this error:

[code]com.inductiveautomation.ignition.client.gateway\_interface.GatewayException: Error creating authentication profile.  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.newGatewayException(GatewayInterface.java:302)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:276)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:229)  
at com.inductiveautomation.factorypmi.application.runtime.ClientGatewayConnection.doLogin(ClientGatewayConnection.java:90)  
at com.inductiveautomation.ignition.client.gateway\_interface.AbstractGatewayConnection.loginEncoded(AbstractGatewayConnection.java:524)  
at com.inductiveautomation.ignition.client.gateway\_interface.AbstractGatewayConnection.login(AbstractGatewayConnection.java:519)  
at com.inductiveautomation.factorypmi.application.runtime.ClientPanel$LoginAction$LoginTask.run(ClientPanel.java:461)  
at java.lang.Thread.run(Unknown Source)  
Caused by: java.lang.Exception: SQL exception during authentication  
at com.inductiveautomation.ignition.gateway.authentication.impl.JDBCAuthenticator.authenticate(JDBCAuthenticator.java:232)  
at com.inductiveautomation.ignition.gateway.authentication.AuthenticatorFailoverWrapper.authenticate(AuthenticatorFailoverWrapper.java:36)  
at com.inductiveautomation.ignition.gateway.servlets.Gateway.runLogin(Gateway.java:930)  
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:416)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)  
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)  
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)  
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)  
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)  
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)  
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)  
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)  
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)  
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)  
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)  
at java.lang.Thread.run(null)

Ignition v7.4.2 (b953)  
Java: Sun Microsystems Inc. 1.6.0\_29  
[/code]

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [May 18, 2012, 4:34pm UTC](https://forum.inductiveautomation.com/t/authenticate-username-against-database-during-login/4493/4 "2012-05-18T16:34:57Z")

</div>

Have you ever been able to authenticate against that authentication profile? Do you get the same error if you try the “Verify Authentication Profile” link on the authentication profiles page?

What is your setup exactly? What database are you using? What authentication profile type are you using?

---

<div class="post-metadata">

**Author:** ![jlr573](https://avatars.discourse-cdn.com/v4/letter/j/c6cbf5/32.png) [@jlr573](https://forum.inductiveautomation.com/u/jlr573)\
**Post date:** [May 18, 2012, 5:01pm UTC](https://forum.inductiveautomation.com/t/authenticate-username-against-database-during-login/4493/5 "2012-05-18T17:01:02Z")

</div>

No I have never been able to authenticate against this profile. When I Verify the Authentication Profile I get this error

`com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'mapping.Rolename' in 'where clause'
`

Where is this “Where” clause located?

Setup:  
Authentication Profile name : UserAuth  
Type: Database  
User table: users

- user\_id
- username
- password
- badge\_id
- role  
Role table: roles
- role\_id
- rolename  
Mapping table: user\_role\_mapping
- user\_id
- role\_id

Database is MySQL - latest rev from site

Code on the Login button

[code]  
badgeID = event.source.parent.getComponent(‘txtBadgeID’).text  
exists = system.db.runScalarQuery(“SELECT badge\_id FROM users WHERE badge\_id LIKE ‘%s’” % badgeID)  
userName = system.db.runScalarQuery(“SELECT username FROM users WHERE badge\_id LIKE ‘%s’” % badgeID)

if exists == None:  
system.gui.warningBox(“Username Doesn’t Exist!”)

else:  
success = system.security.switchUser(userName, badgeID, event)  
system.gui.messageBox(“Welcome You are Logged in as: %s” % userName)  
system.nav.closeParentWindow(event)

```
if not success:
	event.source.parent.getComponent("txtBadgeID").requestFocusInWindow()[/code]
```

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [May 18, 2012, 6:44pm UTC](https://forum.inductiveautomation.com/t/authenticate-username-against-database-during-login/4493/6 "2012-05-18T18:44:44Z")

</div>

I see. Ignition builds queries for you to authenticate a user. Since your tables are a little different from what Ignition would create we need to use the “Expert Configuration” under advanced properties. So, edit your authentication profile. Uncheck the auto create checkbox and click on the advanced properties checkbox. Check the box to use expert configuration. You will see three fields to enter queries.

Your three queries should be:

1. Authentication Query`SELECT Username FROM USERS WHERE Username = '$username$' AND Password = '$password$'`2) Role List Query`SELECT Rolename FROM ROLES`3) User’s Roles Query`SELECT r.Rolename FROM USER_ROLE_MAPPING m JOIN ROLES r ON r.Role_ID = m.Role_ID JOIN USERS u ON u.User_ID = m.User_ID WHERE u.Username = '$username$'`Save and try authenticating.

---

<div class="post-metadata">

**Author:** ![jlr573](https://avatars.discourse-cdn.com/v4/letter/j/c6cbf5/32.png) [@jlr573](https://forum.inductiveautomation.com/u/jlr573)\
**Post date:** [May 18, 2012, 6:56pm UTC](https://forum.inductiveautomation.com/t/authenticate-username-against-database-during-login/4493/7 "2012-05-18T18:56:41Z")

</div>

That allowed me to verify the Auth Profile but when I run the Log-in script, the script fails with this error:

[code]com.inductiveautomation.ignition.client.gateway\_interface.GatewayException: Login failed.  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.newGatewayException(GatewayInterface.java:302)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:276)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:229)  
at com.inductiveautomation.factorypmi.application.runtime.ClientGatewayConnection.doLogin(ClientGatewayConnection.java:90)  
at com.inductiveautomation.ignition.client.gateway\_interface.AbstractGatewayConnection.loginEncoded(AbstractGatewayConnection.java:524)  
at com.inductiveautomation.ignition.client.gateway\_interface.AbstractGatewayConnection.login(AbstractGatewayConnection.java:519)  
at com.inductiveautomation.factorypmi.application.script.builtin.SecurityUtilities.login(SecurityUtilities.java:70)  
at com.inductiveautomation.factorypmi.application.script.builtin.SecurityUtilities.switchUser(SecurityUtilities.java:93)  
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)  
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)  
at java.lang.reflect.Method.invoke(Unknown Source)  
at org.python.core.PyReflectedFunction. **call** (PyReflectedFunction.java:186)  
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction. **call** (ScriptManager.java:455)  
at org.python.core.PyObject. **call** (PyObject.java:422)  
at org.python.core.PyObject. **call** (PyObject.java:426)  
at org.python.pycode.\_pyx97.f$0(event:actionPerformed:25)  
at org.python.pycode.\_pyx97.call\_function(event:actionPerformed)  
at org.python.core.PyTableCode.call(PyTableCode.java:165)  
at org.python.core.PyCode.call(PyCode.java:18)  
at org.python.core.Py.runCode(Py.java:1261)  
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:539)  
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:152)  
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:261)  
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)  
at $Proxy14.actionPerformed(Unknown Source)  
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)  
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)  
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)  
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)  
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)  
at java.awt.Component.processMouseEvent(Unknown Source)  
at javax.swing.JComponent.processMouseEvent(Unknown Source)  
at java.awt.Component.processEvent(Unknown Source)  
at java.awt.Container.processEvent(Unknown Source)  
at java.awt.Component.dispatchEventImpl(Unknown Source)  
at java.awt.Container.dispatchEventImpl(Unknown Source)  
at java.awt.Component.dispatchEvent(Unknown Source)  
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)  
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)  
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)  
at java.awt.Container.dispatchEventImpl(Unknown Source)  
at java.awt.Window.dispatchEventImpl(Unknown Source)  
at java.awt.Component.dispatchEvent(Unknown Source)  
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)  
at java.awt.EventQueue.access$000(Unknown Source)  
at java.awt.EventQueue$1.run(Unknown Source)  
at java.awt.EventQueue$1.run(Unknown Source)  
at java.security.AccessController.doPrivileged(Native Method)  
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)  
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)  
at java.awt.EventQueue$2.run(Unknown Source)  
at java.awt.EventQueue$2.run(Unknown Source)  
at java.security.AccessController.doPrivileged(Native Method)  
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)  
at java.awt.EventQueue.dispatchEvent(Unknown Source)  
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)  
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)  
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)  
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)  
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)  
at java.awt.EventDispatchThread.run(Unknown Source)

Ignition v7.4.2 (b953)  
Java: Sun Microsystems Inc. 1.6.0\_29  
[/code]

The Log-in script is on a button in my application. show in the post above

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [May 18, 2012, 7:14pm UTC](https://forum.inductiveautomation.com/t/authenticate-username-against-database-during-login/4493/8 "2012-05-18T19:14:17Z")

</div>

If you can verify the script should work. Make sure that authentication profile is attached to your project. Call us if you can’t get it to work.

---

<div class="post-metadata">

**Author:** ![jlr573](https://avatars.discourse-cdn.com/v4/letter/j/c6cbf5/32.png) [@jlr573](https://forum.inductiveautomation.com/u/jlr573)\
**Post date:** [May 18, 2012, 7:19pm UTC](https://forum.inductiveautomation.com/t/authenticate-username-against-database-during-login/4493/9 "2012-05-18T19:19:06Z")

</div>

Yep something simple, I forgot that I was using the default Auth profile in my project so I could get logged-in. Switched it back to the database Auth profile and everything works.  
Thanks for all your help.