DBI Datasets

The <select>, <insert>, <update>, <delete>, <merge>, <before>, and <after> parameters for DBI datasets are all SQL parameters.

The SQL statements in the dataset query definition may contain bind substitution parameters defined in curly braces with a preceding dollar symbol. e.g. {$parameter}.

These may also be entered as {{$parameter}}, {{parameter}} or {parameter}.

For DBI the actual name of the bind parameter specified in the dataset query definition may contain only:

• upper/lower-case a-z.
• digits 0-9.
• underscore, colon and hyphen.

In addition, the pipe character | may specify a fallback list, and the ? character may be appended to modify the parameter into an exists check.

All other characters will be ignored.

DBI Fallback Parameter

Refer to Parameter Expansion for the sources that will be searched for expanding a SQL binary parameter value. This includes:

• Client-Supplied Parameters.
• Server-Only Safe Parameters.
• Default Parameters.

The DBI agent also supports fallback parameters, were a | pipe character is used to separate a list of parameter names.

• The named parameter list is searched to find a parameter which exists (may be NULL).
• If none of the fallback parameters exist, then a NULL value will be used for the bind.
• Note: Any bind flags must be placed at the end of the fallback parameter list.

Consider the following <select> query in a DBI dataset.

<dataset read="*" write="*">
    <select>
        SELECT id, class, active FROM boat_class
        WHERE ({$1|class_name}::text IS NULL) OR class ~ {$1|class_name}
        ORDER BY class
    </select>
</dataset>

The following URLs are identical in this context:

http://localhost/jarvis-agent/demo/boat_filter/a
http://localhost/jarvis-agent/demo/boat_filter?class_name=a

The first is an indexed RESTful parameter. It is the first indexed RESTful parameter and is assigned to parameter 1. The second case is a CGI GET supplied user parameter named class_name.

Note: When supplying indexed RESTful parameters, it is not possible to supply e.g. parameter 3 without also defining 2 and 1 as at least empty strings. e.g.

http://localhost/jarvis-agent/demo/boat_filter/a//c

This will supply the indexed RESTful parameters 1 → a, 2 → '', and 3 → c. If you wished to supply the second parameter as NULL, you would need to use the CGI GET syntax with ? and &.

Note: This example shows the use of indexed RESTful parameters. To use named RESTful parameters, you must configure a route with variable parts. See the router configuration section for a worked example using named RESTful parameters.

DBI Exists Modifier

As described in the preceding two sections, a bind parameter (or fallback parameter list) evaluates to the first supplied parameter which exists as a supplied parameter.

There is an alternative format which appends a question mark ? to the parameter name. This tests if the named parameter exists in the supplied row parameters.

A parameter which has the exists modifier appended will evaluate to the value 1 if the parameter exists, and will evaluate to NULL if the input parameter does not exist.

This is specifically designed for merge datasets where we wish to allow the client to omit values which are not going to be changed.

For example:

<dataset read="*" write="*">
    <merge>
        UPDATE boat
        SET name = CASE WHEN {name?} THEN {name} ELSE name END,
            class = CASE WHEN {class?} THEN {class} ELSE class END,
            owner = CASE WHEN {owner?} THEN {owner} ELSE owner END,
            description = CASE WHEN {description?} THEN {description} ELSE description END,
            change_user = {{__username}},
            change_date = datetime ('now')
        WHERE id = {{id}};
    </merge>
</dataset>

Notes:

1. A parameter with the exists modifier in a fallback list will always end fallback evaluation.
2. The exists modifier is not applicable for textual substitution.

DBI Statement Extensions

The select, update, insert and delete sub-elements for DBI also support the following sub-parameters:

These features apply to DBI-style datasets only. They do not apply to SDP or MongoDB.

DBI Binding Extensions

The embedded bind parameters in DBI SQL statements may be followed by one or more bind flags which modify the bind behavior. The supported bind flags for DBI are:

Binding In/Out Parameters (under DBD::Oracle)

By default, all variables are bound as in parameters. When data is returned under a returning clause, this typically occurs automatically using a post-insert fetch result. This default behavior works fine for:

• Microsoft SQL Server
• Postgres

However, for OracleDB, you must use an in/out parameter binding in order to get data returned. For example, after inserting a row you wish to return the auto-generated sequential row ID. Under OracleDB you must use in/out parameters for this purpose.

You can request Jarvis to use an in/out bind by specifying the !out flag on the variable.

<insert returning="no">
    INSERT INTO MY_TABLE (CUSTOMER, NAME, DESCRIPTION)
    VALUES ({$customer_id}, {$name}, {$description})
    RETURNING ID INFO {$id!out}
</insert>

When Jarvis detects that an in/out binding has been requested, it will also do the following:

• Use in/out binding for all parameters flagged as !out in the statement.
• Use in binding for all other parameters in the statement.

Note: Jarvis will use SQL_VARCHAR for all parameter binding, unless you request otherwise with a !numeric flag.

The output values will be placed into a row inside the returning element of the response given back to the client, in a manner that is structurally identical to the returning elements given by the default returning mechanism.

Textual Substitution for DBI

Whenever possible, you should always use the {$parameter} mechanism to substitute parameters. This is because that mechanism creates bind parameters which are substituted into the statement at execution time using ? placeholders. This is guaranteed safe against any form of SQL injection.

However, there are situations where you need to use textual substitution. This is where a parameter is inserted as text into the statement before the statement is prepared.

The most common reason for this is the ability to use the LIMIT or OFFSET syntax to restrict the number of rows which are fetched. This is useful to perform SORT requests inside the database engine itself.

Note: Jarvis provides a built-in post-processing paging and sorting function. There are two reasons to consider using textual parameter substitution instead of the built-in mechanism.

• To sort by two or more columns. The built-in mechanism supports only one.
• To perform paging within the database query, for performance reasons.

The parameter values available for textual substitution are identical to those available for DBI bind parameters. However, the need to guard against SQL injection leads to special considerations:

1. By default, the following quoting rules apply:

   • If the parameter value is an integer or floating point, it is unquoted. Exponential numbers will be recognized as numbers.
   • Otherwise the parameter is quoted using the DBI database handle $dbh->quote(...) method appropriate for this database. According to the DBI documentation, this is safe from SQL injection.

2. Alternatively, if the !quote flag is specified, e.g. [$stringvar!quote] then the value is always quoted with the DBI quote function.

3. Alternatively, if the !noquote flag is specified, e.g. [$sortorder!noquote] then the value is never quoted. Instead, all characters except the following are deleted: 0-9, a-z, A-Z, space, underscore, hyphen, comma.

4. Alternatively, if the !raw flag is specified, e.g. [$__safevar!raw] then the value is textually substituted into the SQL with no checks or restrictions. Note that the !raw can only be used with safe substitutions variables, i.e. those that begin with a double underscore. Safe variables can never be supplied by the client. The !raw flag is restricted to use with variables that are provided by Jarvis itself, or by server-side hooks and plugins.

Note: To specify a client-supplied ORDER BY clause containing more than one column you will need to use the !noquote flag appended to the textual substitution variable name.

Note: For bind parameters, the name of the textual substitution parameters specified in the dataset query definition may contain only upper/lower-case a-z, digits 0-9, underscore, colon and hyphen. All other characters will be ignored.

Other DBI Parameter Notes

Jarvis uses SQL placeholders in all queries for maximum security and efficiency. This means that each variable is replaced by a prepared statement placeholder ?`` in the prepared SQL.

However, there are limits in the power of prepared statement placeholders. E.g. the following is not valid in this case:

<dataset>
    <select>
        SELECT * FROM t WHERE c LIKE '%{$filter}%';
        ...

Instead, use Postgres’s POSIX RE matching operator ~, or else use:

<dataset>
    <select>
        SELECT * FROM t WHERE c LIKE '%' || {$filter} || '%';
        ...

Other limitations also apply, e.g. the number of lines in SQL Server SELECT TOP may not be a placeholder parameter. In such cases you may wish to use textual substitution parameters.

Finally, note that by default, the prepared statement compiler will not know the data type of these substituted variables. In Postgres and other databases you may often need to provide it hints by using ::<type>, e.g.:

WHERE ({$1|class_name}::text IS NULL) OR class ~ {$1|class_name}