In recent releases, Profound UI has been updated to increase the security of database-driven widgets, such as drop downs, grids, charts, and combo boxes. Along with hardened security, the updates also simplify the process of configuring these components to select or omit certain records through the introduction of SQL parameter markers.

All Profound UI users are encouraged to take advantage of these improvements as soon as possible, although some minor adjustments to your applications may be required to maintain compatibility.

Profound UI 4.5 (due to be released around COMMON 2013) will bring changes to the default behavior of database-driven components, although a backwards compatibility mode will be provided to ease the migration.

This article will highlight the upcoming changes and also guide you on making a smooth transition to 4.5.

Security Improvements

Profound UI version 4.2 (released in December 2012) introduced the new Enhanced SQL Security mode, although this mode is not enabled by default. Rather, users who would like to enable it can do so by setting an environment variable in the HTTP server configuration.

Enhanced SQL Security mode hardens security by eliminating any possibility for malicious users to alter the SQL statements used with database-driven components.

The increased security does come with a tradeoff – SQL-related properties can no longer be ‘scripted’ (populated using JavaScript expressions) under Enhanced SQL Security mode. This technique has been commonly used to insert variable data into the SQL statements, most often to filter out certain records. With Enhanced SQL Security mode, SQL parameter markers must be used instead in order make portions of the statements dynamic.

SQL Parameter Markers

The alternative to scripted SQL properties under Enhanced mode is the ability to insert SQL parameter markers (indicated by a question mark) into the ‘selection criteria’ and ‘choices selection criteria’ properties.

New properties ‘parameter value’ and ‘choices parameter value’ have been added to fill in the values. These can be either bound to fields in the display file, or scripted themselves. Scripting the parameter value is secure, because the database will allow ONLY the parameter value to change – it’s impossible to use parameter values to alter other portions of the SQL statement.

Parameter markers also happen to be more intuitive, as the developer will need to supply only the parameter value itself in the bound or scripted value, where before it was necessary to supply the entire WHERE clause for the SQL statement.

To see the difference, consider the following 2 example drop down box configurations. In both cases, the drop down box will load from table COMPANY, the column CONAME will display, and the column CONUM will be returned to the application. Only rows with column CODIVISION (division number) equal to the value in widget on screen identified by “DIVISION” will display.

The new way, compatible with Enhanced SQL Security mode. The widget “DIVISION” needs to contain only the division number and it’s impossible for the SQL statement to be altered, aside from the variable value.

The old way, not compatible with Enhanced SQL Security mode (cannot script “choices selection criteria”). Note that the entire WHERE clause must be scripted, and it is possible (although highly unlikely) for the SQL statement to be altered.

As Profound UI users would expect, SQL parameter values can also be bound to RPG program fields. Also, the concept of field binding has been extended to Genie for SQL parameter values, though a new 5250 field binding interface:

These properties can be bound to 5250 screen fields using a new Binding Dialog in the Genie designer:

Changes for Version 4.5 and Backwards Compatibility

Starting with version 4.5, Enhanced SQL Security mode will be enabled by default. Since some applications may need adjustment to eliminate usage of scripted SQL properties, the following transition tools are provided:

• The PUIFNDSTR utility can be used to search rich DDS source code for scripted SQL properties. The tool can scan all your source members to produce a report of any scripted properties, including the source member name, widget id, and property names.

See here for more information on PUIFNDSTR:

http://www.profoundlogic.com/docs/display/PUI/Searching+JSON+Data

• Backwards compatibility will be provided through the same PUI_SEC_LEVEL environment variable that is currently used to turn on Enhanced SQL Security mode. In version 4.5, this can be set to zero to disable Enhanced SQL Security mode, and SQL processing will be done as before. Users wishing to enable backwards compatibility will have to do so manually by editing the http server configuration file.

• Technical documentation on Enhanced SQL Security mode can be found at the link below, including information on which properties can no longer be scripted, how to enable/disable Enhanced SQL Security mode, etc.

http://www.profoundlogic.com/docs/display/PUI/SQL+Security