Run SQL Query Unsafe Activity

Description

The Run SQL Query Unsafe activity fetches the results of a database query. Unlike the Run SQL Query activity, this activity allows an expression to be entered as the Command Text input.

Constructing expressions containing user input can expose a database to a SQL injection attack. Whenever possible, parameters should be used for safe entry of user input.

This activity is intended to execute queries that return results. INSERT, UPDATE, and other statements that alter the database should be executed with the Run SQL Non-Query activity.

Major data providers, such as Oracle and Microsoft SQL Server, are supported. The full list of supported data providers is documented here. Certain providers, such as Oracle, must be configured before use. Connection strings defined in the database configuration file can be stored securely and reused in any workflow.

Usage

A trusted user could be given permission to run a workflow that uses this activity. The Prompt activity could be used to gather a string of custom SQL where clause conditions. Then, the Run Workflow activity could pass this input to the Run SQL Query Unsafe activity, and an expression could be used for the Command Text which concatenates a predetermined SELECT statement with the user-defined WHERE clause. Finally, the DataTable results returned could be parsed, formatted, and displayed to an end user with the Set Workflow Output and Alert activities.

Environment

The Run SQL Query Unsafe activity is a server activity. It can only be used in server workflows, which require on-premises Workflow.

Offline Support

This activity does not work when the device is offline.

Inputs

Provider Name

Required

Type: Microsoft SQL Server | MySQL | ODBC | Oracle | SQLite | String

The data provider to use. Using an invalid provider name will cause an error. Certain providers, such as Oracle, must be configured before use.

Connection String

Optional

Type: String

The connection string to use to connect to the database. The connection string must match the format supported by the data provider. Examples for different database types can be found on https://www.connectionstrings.com/. If both the ConnectionString and ConnectionConfigName are set, the ConnectionString parameter will be preferred.

Connection Config Name

Optional

Type: String

The name of the key in the database settings configuration file. The connection string value corresponding to this key will be used to connect to the database.

Command Text

Required

Type: String

The SQL command to execute. Unlike the Run SQL Query activity, this input can be an expression. For query syntax, see the documentation of the appropriate data provider.

Using an expression that contains user input can expose the database to a SQL injection attack. Whenever possible, use the Run SQL Query activity with parameters to execute SQL queries that have user input.

Parameters

Optional

Type: Object

The parameters object to pass to the SQL Query. A JSON object composed of key/value pairs that match named parameters in the Command Text input. For example, the SQL Query "SELECT * FROM climbs WHERE difficulty = @difficultyParam" would have a parameter object of the shape "{ 'difficultyParam' : '5.11a' }". Queries made with the ODBC provider can only take positional parameters, signified by question marks. The order the parameters are defined in the JSON object will be used as the positional order provided to the ODBC query command. For more information on this limitation, see here.

Command Timeout

Optional

Type: Number

The number of milliseconds to wait before cancelling the query. If not specified, the default timeout for the given data provider is used.

Outputs

datatable

Type: DataTable

A data table representing the results returned from the query. The data table's 'Rows' property can be indexed by both row number and column. For example, the expression =$sqlQuery1.datatable.Rows[2]['GRADE'] will retrieve the 'GRADE' column of the third row of the results.

Properties

For information about the ID, Display Name, and Description properties, see Properties Common to all Activities.

See Also...

DbCommand Class

DbDataAdapter Class SelectCommand property

SQL Injection

© 2019 Latitude Geographics Group Ltd. All Rights Reserved.

Documentation Version 5.11