SEC_SQLI VARA Objects

SEC_SQLI or Secure SQL Internal VARA objects retrieve values from the Automation Engine database in a secure manner. The difference between SQLI (SQL Internal) and SEC_SQLI VARA objects is that with SEC_SQLI variables can always be inserted in the SQL statements regardless of the VAR_SECURITY_LEVEL setting in the UC_SYSTEM_SETTINGS - Systemwide Settings variable. To include variables in the SQL, you need to use bind parameters. In doing so, you cannot compose SQL statements, and this protects the database from the risk of an SQL injection.

As in SQLI VARA objects, the variable values in SEC_SQLI are directly retrieved from the Automation Engine database. This means that you do not need a database Agent.

For technical reasons, the system cannot check whether only SELECT commands are used in the variable's SQL.

Prerequisites

• The SQLVAR_INTERNAL parameter in the UC_SYSTEM_SETTINGS - Systemwide Settings variable is set to YES .
• Set the maximum number of returned lines in the SQLVAR_MAX_ROWS setting in the UC_SYSTEM_SETTINGS - Systemwide Settings variable .
• Your user definition has the right to Create and modify SQL-internal variables.

Defining SEC_SQLI VARA Objects

A SEC_SQLI 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

Important Considerations

When using SEC_SQLI VARA objects take the following into account:

• 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.

• 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

• Type

  Read-only, always SQL - internal.

• SQL Server / Oracle / DB2 / PostgreSQL Statement

  Statements used to select specific database entries that serve as variable values.

  You have two options:

  • Type your statement in the input field.
  • To open a cell editor and enter your SQL statement, click the icon, enter the statement and click OK.

  Maximum length: 4096 characters.

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

Bind Parameters

You insert variable values within SQL statements in the Bind Parameters section. You can use variable or VARA object names as bind parameters with any values of your choice. If a variable or VARA object does not exist, no values are replaced but the variable or VARA object name is used. This can cause an error when the variable or VARA object is resolved.

You can enter the following type of variables and VARA objects:

• Script variables
• Object variables
• Predefined variables
• Predefined and user-defined VARA objects

Each line in the table represents one bind parameter.

The following field data types and input formats are supported for bind parameters:

• 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

Important!

• Only the field types and formats previously listed are 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.
• The VAR_SECURITY_LEVEL (UC_SYSTEM_SETTINGS - Systemwide Settings) variable 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

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).

Example:

The following SQL statement is entered in the SQL Server Statement field. The values of the bind variables replace the "?" characters.

select ah_name as ObjectName,ah_timestamp1 as ActivationTime,ah_timestamp2 as StartTime,ah_timestamp4 as EndTime from ah

where ah_client = ?

and ah_otype = ?

and ah_deleteflag = ?

and ah_hostdst = ?

order by ah_name asc

See also:

• VARA Objects
• Variables and VARA Objects