SEC_SQL VARA Objects

SEC_SQL or Secure SQL VARA objects securely retrieve values from external databases. SEC_SQL VARA objects are similar to SQL VARA objects because they also run SQL statements on an external database. As opposed to SQL VARA objects, with SEC_SQL you can use all supported variables within SQL statements without putting the database at risk of an SQL injection. The variables are replaced regardless of the VAR_SECURITY_LEVEL settings in the UC_SYSTEM_SETTINGS - Systemwide Settings variable.

Defining SEC_SQL VARA Objects

A SEC_SQL VARA object definition is made up of the following pages:

• General Page

• Variables

  Here you define the object-specific settings. Some sections on this page are common to all VARA objects and are described for all of them in dedicated topics:

  • Variable Settings, see below
  • Parameters
  • Data Types and Formatting
  • Script Access
  • Client 0 VARA Objects in Other Clients
  • Preview, see below
• Version Management Page
• Documentation Page

Overview

You cannot directly specify the variables in the field SQL Statement, but you can include them using bind parameters. These bind parameters are maintained in the related tables and can include predefined variables, placeholders for VARA objects and PromptSet variables. In the SQL script, use the "?" character in the locations where the bind parameters will be inserted.

For technical reasons, the system cannot check whether only SELECT commands are used in the variable's SQL. To prevent that SQL commands other that SELECT are run, it is advisable to deploy a database user who has read-only access rights.

This variable type also requires a DB Service Agent that was started in DB service mode and that is version v9 SP3 or later. Older Agents do not support the variable type SQL SECURE. For more information, see Installing the Agent for Database Variables.

Important Considerations

When using SEC_SQL VARA objects take the following into account:

• The maximum number of lines that is returned is specified in the SQLVAR_MAX_ROWS setting in the UC_SYSTEM_SETTINGS variable.

• Database VARA objects (SEC_SQLI, SEC_SQL, SQLI, and SQL types) do not support binary fields. Binary data that result from an SQL query causes an error in the preview or when the values are retrieved at runtime.

• In database VARA objects (SEC_SQLI, SEC_SQL, SQLI, and SQL types), the system does not check the SQL statements that are processed for technical reasons. You can change or delete data records by using VARA objects. For security reasons, consider restricting user access accordingly. If possible, deploy a database user with read-only rights for VARA objects that can access an external database (SQL, SQL SECURE). For internal SQL variables (SQLI, SQLI SECURE), you can limit object access via a privilege (create and modify SQL-internal variables).

• Depending on the data type, database VARA objects (SEC_SQLI, SEC_SQL, SQLI, and SQL types) return the value "" (string, date, time, timestamp) or 0 (number) for NULL values.

• PromptSet variables are supported in select fields for BACKEND, EXEC, FILELIST, SEC_SQLI, SEC_SQL, SQLI, SQL, and XML variable types. Using PromptSet variables is necessary when using the Dynamic Reload (Combobox Prompts) feature.

• In the Variables Settings and Bind Parameters sections:

  You can include PromptSet variables here when using the Dynamic Reload (Combobox Prompts) feature for Combobox prompts. When you use PromptSet variables in a dynamic VARA object, the Preview feature returns an error. This is because the PromptSet variables do not exist in the dynamic VARA object. However, the PromptSet variables will be resolved when objects including the PromptSet are executed.

Variable Settings section

• Type

  Read-only, always SEC_SQL.

• Connection

  The Connection object of type Database that contains the connection parameters for the selected database.

• Login

  The Login object that stores the name and password of the database user, see Login (LOGIN).

  If you neither specify a Login object nor set the option Default login, the login data for accessing the database is retrieved from the Connection object.

  An error occurs if the VARA object is to be resolved and this object does not include any login information.

• Default Login

  You can also select a Login object for SQL VARA objects in the User object (default login), see Users (USER).

  Activate the Apply user's login option to apply the Login object that is assigned to the current user. As a result, the Login object that is specified in the VARA object is ignored.

  An error occurs if the VARA object is to be resolved and no login is defined in the User object.

  PromptSet variables cannot be used here.

• SQL Statement

  The SQL statements used to select specific database entries that serve as variable values.

  Variables that are directly specified in SQL will not be replaced. To include variables, use bind parameters.

  Maximum length: 4096 characters

Bind Parameters Section

You use bind parameters to insert variable values within SQL statements. You can use script variables, object variables, predefined variables, and placeholders for VARA objects that you enter in this table. Each line represents one bind parameter. You can use variable names as bind parameters, with any values of your choice.

Supported data types and input formats:

• Date: yyyy-mm-dd
• Time: hh:mm:ss
• Timestamp: yyyy-mm-dd hh:mm:ss
• String: any string
• Boolean: possible values: 0 / 1 or true / false
• Number: no thousand separator, a decimal point as a decimal separator

Other field types of the database and other formats are not supported. Data types are not converted (no conversion from timestamp to date). Inserting an invalid value causes an error when the SQL commands are processed.

Important!

• No values will be replaced if a specified variable does not exist. In this case, the variable name is used, which can cause an error when the variable is to be resolved.
• The variable VAR_SECURITY_LEVEL (UC_SYSTEM_SETTINGS) does not impose any limitations.
• In the SQL statements, you must use a "?" character at every location where the replaced value of a bind parameter is to be inserted. A bind parameter must exist for each "?". Make sure that you maintain the correct order (table). Note that you cannot use bind parameters multiple times.

Preview section

To run a command, select it and click Preview. The statements are executed and their results displayed. The preview function serves to check for potential errors in the database statements (for example, it the data type of the VARA object and of the database do not match).

The database Agent sends the preview data to the Automation Engine in the form of a message. If this message exceeds the maximum message length that a server process accepts, an error occurs.

Maximum length: Set in the MaxMsgSize= parameter of the Automation Engine UCSRV.INI file. For more information see Automation Engine.

Default value: 3145 728 bytes.

See also:

• VARA Objects
• Variables and VARA Objects