Overview
Starting with Profound UI Version 4, Fix Pack 2.0, a new Enhanced SQL Security mode has been added to provide significant improvements to security for database-driven components (such as dropdown boxes, auto complete boxes, charts, database-driven grids, etc.) in Rich Display File and Genie Designer screens. Enhanced SQL Security mode is designed to prevent malicious users from attempting to modify portions of SQL SELECT statements generated by database-driven components. All users are encouraged to migrate to Enhanced SQL Security mode as soon as possible to take advantage of these improvements.
While Enhanced mode will provide significant improvements to security, some display programming which were previously supported will not work under Enhanced mode. To assist users in transition, Enhanced mode is not yet the default mode of operation, but must be enabled through HTTP server configuration. This will enable users to test/adjust applications while the previous behaviors will continue to be supported.
In a future update to Profound UI / Genie, the new Enhanced mode will become the default. Once this happens, new features and bug fixes will be offered for Enhanced mode only. So, all customers are encouraged to transition as soon as possible.
Mode Selection
As of Profound UI Version 4, Fix Pack 5.0, Enhanced SQL Security mode is the default mode of operation. To disable and revert to previous behavior, the following directive can be added to the global section of the HTTP server configuration file. The configuration file is located at /www/HTTP_SERVER_NAME/conf/httpd.conf. The default HTTP server name is 'PROFOUNDUI'.
If this variable is set to 0, Enhanced mode is disabled. If the environment variable is not set at all, or set to 1, Enhanced mode will be enabled.
After making this change, the HTTP server would need to be restarted to make it effective.
Security Improvements
Regardless of which security mode is used, Profound UI provides the following protections for SQL queries that are run to populate database-driven components:
- SQL queries must be run from a valid and current session, as identified by unique session id.
- SQL queries run using the permissions of the user profile that is signed into Profound UI / Genie. User profile QTMHHTP1 is used for anonymous Profound UI sessions.
- Only SELECT statements can be run -- no statements which modify data or objects such as tables (physical files) are permitted.
Enhanced SQL Security mode adds to this by strictly controlling which portions of the SQL statements the server processing will accept from the client. Under Enhanced mode, only values for programmer-specified SQL parameter markers are accepted. Usage of SQL parameter markers eliminates any possibility of a malicious user or hacker "injecting" additional SQL statements, or of changing the SQL statement that the programmer intended for the application to use.
Functional Differences Introduced with Enhanced Mode
Since Enhanced mode limits client-provided portions of the SQL queries to parameter marker values, only those portions of an SQL statement where parameter markers are valid can be modified by the client. This means that SQL-related properties can no longer be evaluated as JavaScript expressions under Enhanced Mode. Without enhanced mode, various SQL-related properties (such as database file, fields to select, selection criteria, etc.) are allowed to be evaluated as JavaScript expressions.
The properties which can no longer be treated as JavaScript expressions under Enhanced mode are:
For Dropdown, Combo Box and Textbox widgets:
- choices database file
- choice options field
- choice values field
- choices selection criteria (except through parameter markers)
- contains match (textbox)
For Grid widgets:
- database file
- database fields
- selection criteria
- custom sql
For Chart widgets:
- database file
- name field
- value field
- summary option
- selection criteria
In Profound UI Rich Display File screens, any of these property values can still be made dynamic through use of field binding, although any changes to bound values will require a submit to the server and screen reload in order to produce changes in the property values. However, in a typical application screen, these values are static and have no need to change, aside from the "selection criteria" (which becomes the WHERE clause of the SQL statement), where it is often desirable to allow a client-produced value. For this case, Enhanced Mode introduces the capability to use SQL parameter markers to more securely accept such values.
In Genie screens, these properties cannot be set dynamically -- the value from the Designer screen customization file will always be used, except for the case of "selection criteria" where parameter markers can be used to accept dynamic values from the client.
Evaluation of Property Values as JavaScript Expressions
Properties with prefixes script:, js:, or javascript: will be evaluated as JavaScript expressions. The result of the expression is then applied to the property value. Scripted values for "selection criteria" and "choices selection criteria" have been commonly used to set these properties based on dynamic screen data. Since this property cannot be evaluated under Enhanced security mode, any screens using this technique will need to be adjusted to use parameter markers instead:
The PUIFNDSTR search utility can be used to find any displays that will need adjustment due to using scripted values for any of the properties mentioned above, see here:
For example, to get a report of setting for property "choices selection criteria" for all source members in file MYLIB/MYFILE:
PUIFNDSTR STRING(*ALL) PROPERTY('choices selection criteria') SRCFILE(MYLIB/MYFILE)
Parameter Markers
With Enhanced Mode, Profound UI / Genie will allow insertion of parameter markers in the "selection criteria", "choice selection criteria", and "custom sql" properties. SQL parameter markers are specified by a question mark (?). New properties "parameter value" and "choices parameter value" (depending on widget type) can be used to provide the values for these parameter markers.
For example:
This configuration will accept a dynamic value in "choices selection criteria" for the field DIVCOMPANY. The dynamic value is supplied by either binding "choices parameter value" to a program field, or by providing a value through a JavaScript expression. A parameter value must be given for each parameter marker specified. Multiple "parameter value" properties can be added by right-clicking the property name and selecting "Add Another...":
Important note:
- Parameter values which are bound to program fields are processed entirely on the server. This means there is no possibility for the user to insert a value other than what the programmer intended. This option should be used whenever doing so would provide the necessary functionality. For example, if the user had selected the DIVCOMPANY on the previous screen, there is no need to allow the company value to be sent from the client. It's already known on the server and cannot change.
- Parameter values which are not bound to program fields can be accepted from the client. This means the user can cause different values to be used. This can be used to allow the user to change the company "on the fly" in this case. For example, the user might be able to change the company selection on the same screen as the division box. In that case, it would be appropriate to allow the company value to be sent from the client. Note that the server will allow ONLY the parameter value to be accepted from the client, all other portions of the SQL statement cannot be changed by any malicious user action.
Field binding for parameter values on Profound UI Rich Display File screens is done in the same way as any other property value. In Genie, a new field binding dialog is provided which allows for selection of a field in the 5250 data stream for server-side binding.
Example - Replacing Scripted Property Value with Parameter Marker
Consider the following application screen. In this case, a value "Company number" is displayed on screen, and we want to use this value to filter the records for the "Division" dropdown box so that only divisions belonging to the current company are displayed. The company number comes from program field COMPANY, and the corresponding output field widget has id "company": 
Before Enhanced SQL Security mode, the dropdown box may have been configured like this: 
However, Enhanced SQL Security mode does not allow for scripting the "choices selection criteria" property, so a parameter marker must be used instead. For example: 
This will produce an SQL statement like this:
If the statement were run "as is", an error would result due to not having a value for the parameter marker. We can specify the value using the "choices parameter value" property. This property does accept scripted values, so it could be specified like this: 
When the screen is loaded, the "choices parameter value" property will be set with the value from the "company" widget. Since a parameter marker is used to insert the dynamic value, it's not possible for any other portion of the SQL statement to be modified by the client. This provides significant improvement to using a scripted property value, which would potentially allow, for example, the field name in the WHERE clause to be modified by a malicious user.
An alternative method to dynamically assign a value to a parameter marker, which is especially useful when several of the user's inputs need to change the results of a SQL statement, would be to use the applyProperty() API in the onchange event in the widget who's value will be used for the SQL statement. You can find an example of this method on our blog here. In this example the application contains 3 select boxes, and the last 2 select boxes change their options based upon the choices made in the select boxes before them.
To increase security even further, the property could be bound to the COMPANY field. When server-side binding is used for parameter marker values, then no portion of the SQL statement can be modified:
Labels