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 SQL injection attacks. 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.
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 query. A JSON object composed of key/value pairs that match named parameters in the Command Text input. For example, the following SQL query would have a parameter object of the shape "{ 'difficultyParam' : '5.11a' }": ▪SQL Server: "SELECT * FROM climbs WHERE difficulty = @difficultyParam" ▪Oracle: "SELECT * FROM climbs WHERE difficulty = :difficultyParam" 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. For details on query syntax, see the documentation for your data provider. |
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.
Connectivity Requirements
This activity does not work when the device has intermittent connectivity to the network.
See also...
Database Connections for Server Workflows
