IG 2023.11

SqlAttributesFilter

This filter uses synchronous architecture. Accessing the filter target triggers a call to the database that blocks the executing thread until the database responds.

Consider the performance impact of this filter, especially for deployments with a small number of gateway units (therefore, a small number of executing threads) and a long execution time for the JDBC call.

Executes a SQL query through a prepared statement and exposes its first result. Parameters in the prepared statement are derived from expressions. The query result is exposed in an object whose location is specified by the target expression. If the query yields no result, then the resulting object is empty.

The execution of the query is performed lazily; it does not occur until the first attempt to access a value in the target. This defers the overhead of connection pool, network and database query processing until a value is first required. This also means that the parameters expressions is not evaluated until the object is first accessed.

Usage

{
  "name": string,
  "type": "SqlAttributesFilter",
   "config": {
     "dataSource": JdbcDataSource reference,
     "preparedStatement": configuration expression<string>,
     "parameters": [ runtime expression<string>, ... ],
     "target": lvalue-expression
   }
}

Properties

"dataSource": JdbcDataSource reference, required
dataSource as a JNDI lookup name is deprecated; use dataSource as a JdbcDataSource configuration object instead. For more information, refer to the Deprecated section of the Release Notes.

The JdbcDataSource to use for connections. Configure JdbcDataSource as described in JdbcDataSource.

"preparedStatement": configuration expression<string>, required

The parameterized SQL query to execute, with ? parameter placeholders.

"parameters": array of runtime expressions<strings>, optional

The parameters to evaluate and include in the execution of the prepared statement.

See also Expressions.

"target": <lvalue-expression>, required

Expression that yields the target object containing the query results. For example, ${target.sql.queryresult}.

Access to target triggers a call to the database that blocks the executing thread until the database responds.

See also Expressions.

Example

Using the user’s session ID from a cookie, query the database to find the user logged in and set the profile attributes in the attributes context:

{
        "name": "SqlAttributesFilter",
        "type": "SqlAttributesFilter",
        "config": {
              "target": "${attributes.sql}",
              "dataSource": "java:comp/env/jdbc/mysql",
              "preparedStatement": "SELECT f.value AS 'first', l.value AS 'last', u.mail AS 'email', GROUP_CONCAT(CAST(r.rid AS CHAR)) AS 'roles' FROM sessions s INNER JOIN users u ON ( u.uid = s.uid AND u.status = 1 ) LEFT OUTER JOIN profile_values f ON ( f.uid = u.uid AND f.fid = 1 ) LEFT OUTER JOIN profile_values l ON ( l.uid = u.uid AND l.fid = 2 ) LEFT OUTER JOIN users_roles r ON ( r.uid = u.uid ) WHERE (s.sid = ? AND s.uid <> 0) GROUP BY s.sid;",
              "parameters": [ "${request.cookies[keyMatch(request.cookies,'JSESSION1234')][0].value}" ]
         }
 }

Lines are folded for readability in this example. In your JSON, keep the values for "preparedStatement" and "parameters" on one line.

Copyright © 2010-2023 ForgeRock, all rights reserved.