Migrating from JDBCAuthState to ScriptState

Deprecation notice

The JDBCAuthState is deprecated as of the May 2026 release and will be removed in the November 2026 release. Use the ScriptState with a custom Groovy script as a replacement.

This page provides a ready-to-use Groovy ScriptState that replaces the core functionality of the JDBCAuthState: authentication against a SQL database, optional role retrieval, and result column propagation.

AuthState configuration

<AuthState name="JdbcScriptAuth"
           class="ch.nevis.esauth.auth.states.scripting.ScriptState"
           final="false"
           resumeState="true">
  <ResultCond name="ok" next="AuthDone"/>
  <Response value="AUTH_CONTINUE">
    <GuiElem name="isiwebuserid" type="text" label="login.label" value=""/>
    <GuiElem name="isiwebpasswd" type="pw-text" label="password.label" value=""/>
    <GuiElem name="submit" type="button" label="login.button.label" value="true"/>
  </Response>
  <property name="scriptLanguage" value="Groovy"/>
  <property name="script" value="file:///var/opt/nevisauth/default/scripts/jdbc_auth.gy"/>

  <!-- JDBC connection -->
  <property name="parameter.jdbc.url"      value="jdbc:mariadb://db-host:3306/mydb?autocommit=true"/>
  <property name="parameter.jdbc.user"     value="app_user"/>
  <property name="parameter.jdbc.password" value="secret"/>

  <!-- Authentication query (first ? is bound to the login name) -->
  <property name="parameter.jdbc.query.authentication"
            value="SELECT * FROM USERS WHERE LOGIN_NAME = ?"/>
  <property name="parameter.jdbc.column.userid"   value="USER_ID"/>
  <property name="parameter.jdbc.column.password"  value="PASSWORD"/>

  <!-- Optional: authorization query (first ? is bound to the resolved user ID) -->
  <property name="parameter.jdbc.query.authorization"
            value="SELECT R.ROLE_NAME FROM ROLES R INNER JOIN USER_ROLES UR ON R.ROLE_ID = UR.ROLE_ID WHERE UR.USER_ID = ?"/>
  <property name="parameter.jdbc.column.roles" value="ROLE_NAME"/>

  <!-- Optional: hash-based password verification (omit for plain-text comparison) -->
  <!-- <property name="parameter.jdbc.hash.algorithm"  value="SHA-256"/> -->
  <!-- <property name="parameter.jdbc.hash.saltLength" value="16"/> -->

  <!-- Propagation scope for result columns: notes (default), session, or outargs -->
  <property name="parameter.jdbc.propagationScope" value="notes"/>
</AuthState>

Groovy script — jdbc_auth.gy

import groovy.sql.Sql
import java.security.MessageDigest

// ---------------------------------------------------------------------------
// Supported parameters (set via <property name="parameter.jdbc.*" .../>):
//   jdbc.url                  (required) JDBC connection URL
//   jdbc.user                 (required) Database username
//   jdbc.password             (required) Database password
//   jdbc.query.authentication (required) SELECT with ? bound to login name
//   jdbc.column.userid        (required) Column name for the user ID
//   jdbc.column.password      (optional) Column containing the stored password
//   jdbc.query.authorization  (optional) SELECT with ? bound to user ID
//   jdbc.column.roles         (optional) Column name for role names
//   jdbc.hash.algorithm       (optional) e.g. SHA-256; omit for plain-text
//   jdbc.hash.saltLength      (optional) Salt prefix length in bytes (default: 0)
//   jdbc.propagationScope     (optional) notes (default), session, or outargs
//   jdbc.loginId.field        (optional) Form field for login (default: isiwebuserid)
//   jdbc.password.field       (optional) Form field for password (default: isiwebpasswd)
// ---------------------------------------------------------------------------

def cfg = { key, fallback = null -> parameters.get(key) ?: fallback }

def loginId = inargs[cfg('jdbc.loginId.field', 'isiwebuserid')]
def password = inargs[cfg('jdbc.password.field', 'isiwebpasswd')]
if (!loginId || !password) return

try {
    Sql.withInstance(url: cfg('jdbc.url'), user: cfg('jdbc.user'), password: cfg('jdbc.password')) { sql ->
        def row = sql.firstRow(cfg('jdbc.query.authentication'), [loginId])
        if (!row) { response.setError(1, 'Login failed'); return }

        def userId = row[cfg('jdbc.column.userid')]
        if (!userId) {
            LOG.error('jdbc.column.userid misconfigured — column not found in query result')
            response.setError(99, 'Authentication temporarily unavailable'); return
        }

        def pwCol  = cfg('jdbc.column.password')
        def stored = pwCol ? row[pwCol] : null
        if (stored != null) {
            def algo    = cfg('jdbc.hash.algorithm')
            def saltLen = cfg('jdbc.hash.saltLength', '0') as int
            def match   = algo ? verifyHash(password, stored, algo, saltLen) : password == stored
            if (!match) { response.setError(1, 'Login failed'); return }
        }

        def propagate = [
            session: { k, v -> session.put(k, v) },
            outargs: { k, v -> outargs.setProperty(k, v) },
            notes:   { k, v -> notes.setProperty(k, v) }
        ].withDefault { { k, v -> notes.setProperty(k, v) } }[cfg('jdbc.propagationScope', 'notes')]

        row.each { col, val ->
            if (!pwCol || !col.equalsIgnoreCase(pwCol))
                propagate("jdbc.${col}", val?.toString() ?: '')
        }

        response.with { setUserId(userId); setLoginId(userId); setResult('ok') }
        ['loginid', 'userid'].each { notes.setProperty(it, userId) }

        def authzQuery = cfg('jdbc.query.authorization')
        def rolesCol   = cfg('jdbc.column.roles')
        if (authzQuery && rolesCol) {
            def roles = sql.rows(authzQuery, [userId])*.getAt(rolesCol)
            if (roles) response.setActualRoles(roles as String[])
        }
    }
} catch (Exception e) {
    LOG.error('JDBC authentication error', e)
    response.setError(99, 'Authentication temporarily unavailable')
}

def verifyHash(String input, String stored, String algo, int saltLen) {
    byte[] decoded  = Base64.decoder.decode(stored)
    byte[] salt     = decoded[0..<saltLen]
    byte[] expected = decoded[saltLen..-1]
    def md = MessageDigest.getInstance(algo)
    if (saltLen) md.update(salt)
    md.update(input.bytes)
    MessageDigest.isEqual(md.digest(), expected)
}

How it works

1. Guard clause — if no credentials are submitted yet, the script returns without setting a result. The <Response> element renders the login form.
2. Authentication query — Sql.firstRow() executes the query as a PreparedStatement (preventing SQL injection). The ? placeholder is bound to the submitted login name.
3. User ID validation — if jdbc.column.userid is misconfigured and the column is not found, the script logs an error and returns a system error instead of failing silently.
4. Password verification — plain-text comparison by default. When jdbc.hash.algorithm is set, the stored value is Base64-decoded, an optional salt prefix is extracted, and the input is hashed for comparison.
5. Column propagation — after successful authentication, every column from the result row is stored with a jdbc. prefix into notes, session, or outargs. The password column is excluded from propagation for security.
6. Authorization query — if configured, Sql.rows() retrieves role names and sets them via response.setActualRoles().
7. Resource cleanup — Sql.withInstance() automatically closes the underlying JDBC connection when the closure completes, even if an exception is thrown.

Configuration mapping

The following table maps JDBCAuthState properties to the equivalent parameter.* properties in the ScriptState.

JDBCAuthState property	ScriptState parameter	Notes
url	parameter.jdbc.url
user	parameter.jdbc.user
password	parameter.jdbc.password
query.template.authentication	parameter.jdbc.query.authentication	SQL stays the same; the ? is bound to the login name
query.template.authorization	parameter.jdbc.query.authorization	? is bound to the resolved user ID
column.name.userid	parameter.jdbc.column.userid
column.name.password	parameter.jdbc.column.password
column.name.roles	parameter.jdbc.column.roles
login.password.src	parameter.jdbc.password.field	Field name only (e.g., isiwebpasswd) — no ${…} substitution needed
(login name field)	parameter.jdbc.loginId.field	Defaults to isiwebuserid; set when the login form uses a different field name
authentication.checker.impl	parameter.jdbc.hash.algorithm	Set to SHA-256, SHA-1, or MD5 for hash-based verification; omit for plain-text
hashAlgorithm	parameter.jdbc.hash.algorithm
saltLength	parameter.jdbc.hash.saltLength
propagationScope	parameter.jdbc.propagationScope	notes (default), session, or outargs

Features not covered by this recipe

JDBCAuthState feature	Recommendation
Connection pooling	The script opens a new connection per request via Sql.withInstance(). For high-throughput deployments, place a connection pool library (e.g., HikariCP) in the nevisAuth instance lib/ directory and configure the pool as a javax.sql.DataSource.
Audit statements (stmt.template.*)	Add sql.execute(...) calls at the appropriate points in the script (see example below).
Multi-argument query templates (arg1…argN with ${…} substitution)	In Groovy you have direct access to inargs, notes, session, and outargs. Build your query arguments programmatically.
Oracle / MSSQL specific pool tuning	Not applicable — connection pooling is delegated to an external library.

JDBC driver setup

The JDBC driver for your database must be available on the nevisAuth classpath. Copy the driver JAR into the instance lib/ directory:

/var/opt/nevisauth/<instance>/lib/mariadb-java-client-<version>.jar

Restart nevisAuth after adding the driver. Supported databases include MariaDB, MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

Example: adding audit statements

To log authentication outcomes into an AUDIT_EVENT table, add the following calls to the script at the appropriate locations:

// After the authorization query block (at the end of the Sql.withInstance closure):
sql.execute("INSERT INTO AUDIT_EVENT (USER_ID, EVENT) VALUES (?, 'auth_succeeded')", [userId])

// After failed authentication (before return):
sql.execute("INSERT INTO AUDIT_EVENT (USER_ID, EVENT) VALUES (?, 'auth_failed')", [loginId])