Using Dynamic Table and Column Names

Generally, the SQL queries executed by the database filters and communication points are considered static: the queries themselves are provided at configuration time, but the values used in the queries are dynamically provided while processing the message. Internally, this uses prepared statements so that the queries are compiled once at configuration time, and then the values sent to the database separately when they are executed. This approach has benefits both for performance (as the query only needs to be compiled once rather than compiled every single time that it is used), and for security, in that it effectively prevents SQL injection attacks.

For example, consider the following SQL statement:

INSERT INTO MyTable (col1, col2, col3) VALUES ('literal', $property, @path.to.field)

The example SQL statement is transformed into the following prepared statement at configuration time by the database filters and communication points:

INSERT INTO MyTable (col1, col2, col3) VALUES (?, ?, ?)

The values are then provided separately for each message that requires the execution of this SQL statement.

Dynamic Table and Column Names

While normal statement parametrization can handle a large number of cases, there may be situations where a more dynamic approach is required. In order to support this, the database filters and communication points allow dynamic SQL queries where the table and column names used in the query can be changed at runtime using Rhapsody message properties. When this approach is used, the SQL statement is still converted into a prepared statement for processing, but the conversion takes place just before the message is processed through the filter or communication point. The relevant syntax is a double dollar symbol preceding the name of the message property. For example:

 INSERT INTO $$TableNameProperty (col1, col2, $$ColumnNameProperty) VALUES ('literal', $property, @path.to.field)

The trade-off when using this functionality is a minor performance hit as the query is recompiled repeatedly (although this is probably not noticeable), and an increased security risk as the prepared statements no longer provide complete protection against SQL injection. It is critical to ensure that the message properties containing table and column names are set to safe values from trusted sources. In no circumstances should these message properties ever be set to values retrieved from an incoming message.

An example of how these message properties can be safely set in JavaScript is as follows:

var next = output.append(input[0]);
var type = next.getField('MSH/MessageType/MessageType');
var tableName, columnName;
switch (type) {
  case 'ADT':
    tableName = 'ADT';
    columnName = 'adtColumn';
    break;
  case 'ORU':
    tableName = 'ORU';
    columnName = 'oruColumn';
    break;
  default:
    tableName = 'Unknown';
    columnName = 'col3';
    break;
}


next.setProperty('TableNameProperty', tableName);
next.setProperty('ColumnNameProperty', columnName); 

Enabling Dynamic Queries

As dynamic SQL queries are only rarely required and can introduce a security risk if not implemented properly, they are disabled by default in database filters and communication points. They can be enabled at any time in the Options window of the Database Configuration by selecting the Allow dynamic table and column names in SQL statements option.

If dynamic SQL queries are used without enabling this option, the database filters and communication points would fail to configure with an error indicating that support for dynamic queries is disabled. Additionally, the Database Configuration Editor displays a warning when saving the configuration, indicating that this option needs to be enabled for the configuration to run: