Replace verbatim SQL with SQL defined in AXVW or Java

• SQL restriction on requests for data from a data source / JSON restriction with sql attribute

  • Use an Optional SQL Restriction on a data source in the AXVW.

• Parameters to custom workflow rules

  • Each rule should validate inputs against a finite list (e.g. table names, field names, comparators) or treat them as bind variables, constructing SQL using the com.archibus.db.Sql class. No rule should accept SQL from the client.

• Verbatim Parameters (except tables and fields)

  • Use non-verbatim parameters if possible. Use an Optional SQL Restriction on a data source in the AXVW for each possible SQL string, using non-verbatim parameters as needed.

• Table and Field names as parameters

  • In workflow rules, validate these using Sql.valueOfTable(String) or Sql.valueOfField(String).

  • In data sources, use an Optional SQL Restriction in the AXVW.

  • The support for use of field names in verbatim parameters may continue, so long as they are valid DBMS identifiers and accessible to the user.

• Select Value commands with restrictions

  • SQL restrictions for selectValue commands will be maintained on the server and will not be passed to the client. The browser may still evaluate some expressions that are parsed on the client and will be treated as bind variable values on the server.

  • SQL should not be provided as a restriction to a selectValue command from JavaScript. Either specify clauses, or for an alternative approach see below: SelectValue commands with restrictions.

• Clause values with OGNL that have SQL expressions as inputs

  • A single OGNL expression that evaluates to a literal is permitted. A literal is permitted. Any SQL outside of an OGNL expression is not permitted.

  • If a parameter is used as a clause value, it should no longer be verbatim, but should be the type of the bind variable.

  • The server will now try to convert literal clause values specified in JSON to the the appropriate type for the field.

Upgrade Caveats

• OGNL expressions such as ${user.name} should no longer be in quotes as they will evaluate to a bind variable. If you see '?' in a query, this is likely the issue. Check VPA restrictions and AXVWs.

• Take care when converting verbatim parameters to text parameters that the parameter value is not surrounded by single quotes. This may result in a warning being logged.

• Parameters used in clause values should not be verbatim, as they were before, but of the type required for the clause.

• A type=”grouping” data source with a calculated field that takes a parameter within a function, or a custom query of this pattern won't work with bind variables:

SELECT somefunction(?) FROM table GROUP BY somefunction(?)

The DBMS doesn’t correlate the two bind variables, so this is a syntax error. It can be fixed by restructuring the query:

SELECT field FROM (SELECT somefunction(?) field FROM table) table GROUP BY field

Custom queries need to be modified directly, but data sources of type “grouping” can change to “groupingSubquery” in v2024.02 where this is an issue. This isn't a problem for sorting or other patterns, only grouping.

Optional SQL Restrictions

The general approach is to, in nearly all cases is to replace these with named restrictions on a data source enabled by a boolean parameter.

<dataSource id="ds_ab-sp-hl-comn-gp_tree_bl" usePreparedStatement="true">

<table name="bl" role="main"/>

<field table="bl" name="bl_id"/>

<parameter name="blIdParam" dataType="text" value=""/>

<parameter name="isFilterBlId" dataType="boolean" value="false"/>

<restriction enabled="isFilterBlId" type="sql" sql="bl.bl_id = ${sql.getBindVariable('blIdParam')}"/>

</dataSource>

See Optional SQL Restrictions help page for more details.

Verbatim Parameters

Except perhaps for field names, verbatim parameters will no longer be supported in a future release and will be replaced with Optional SQL Restrictions on the dataSource.