IG 2023.2


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.


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


"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 that will contain the query results.

See also Expressions.


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.