Execute Query - Activities - Foundation 23.2 - Foundation 23.2 - Ready - Hyland RPA - external

Database

Description

Sends an SQL statement to a database server and receives an output DataTable in case there is a result.

Properties

Analyst

• Description: Optional text for documentation purposes.

Common

• Continue On Error: Continues the execution of the workflow even if an error occurs during the execution of the activity.

• Delay Before: Delay time in milliseconds before the activity is executed. The default value is 250 milliseconds.

• Delay After: Delay time in milliseconds after executing of the activity. The default value is 250 milliseconds.

• Disable Log: Disables the logging functionality for this activity.

• Disable Protocol: Disables the protocol functionality for this activity.

• Exclude from Global Delay: Excludes this activity from the Global Delay functionality.

• Timeout: Duration in milliseconds in which the activity tries to execute.

Input

• Database Provider: The type of SQL server you are using. Depending on which provider you choose the connection string syntax and parameter syntax change. See Database Provider Specifics below.
  • Microsoft SQL Server
  • MySQL
  • Oracle
  • PostgreSQL
• Parameter: This input defines parameters for the query. It is handed over as a Dictionary(String,Object). You should use this property if you want to enrich your query with dynamic data. Besides being a good strategy against SQL injection attacks (see SQL Injection Attacks below), it can also increase query execution performance and offers a convenient way to organize and separate static and dynamic data.
• Query: The SQL statement you want to execute in the database. Handed over as a String. Avoid dynamic data and string concatenation here. See SQL Injection Attacks below.

Optional

• Connectionstring: Set connection string if the desired database connection differs from the environment settings. If the string contains a {password} tag it will be replaced with the value of the 'Secure Password' property if that one was set.
• Secure Password: SecureString that used to replace the optional [password] tag in the connection string.

Output

• Result Table - The result of a SELECT-SQL statement, handed over as a DataTable. This field is optional since many queries do not return anything (for example, an UPDATE-Statement).

SQL Injection Attacks

We discourage the use of dynamic data and string concatenation in the Query property. Try to use the Parameter property for dynamic data. Parameters provide type checking and validation. Their values will be treated as literal values and not executable code.

Example

Imagine you want to return a row from the Users table where the username is equal to a name you read from some external source. For example, a website or a mail.

String concatenation approach

You could use the following query:

"SELECT * FROM users WHERE username='" + userName + "'" 

This query works if the userName only contains valid usernames (for example, John Doe). If anyone found a way to make sure userName contains John Doe; DROP TABLE users;", the query would delete the whole users table.

Parameter property approach

You should therefore use the following query (we use the '@' notation here see Database Provider Specifics below to see which notation is the right for your database provider):

"SELECT * FROM users WHERE username=@userName" 

And following value for the Parameter property:

New Dictionary(Of String, Object) From {{ "userName", userName}} 

This will make sure that everything that is inside the "userName" variable is used to search in the "username" column. A value of "John Doe'; DROP TABLE users;" will therefore search for a name of "John Doe'; DROP TABLE users;" and will probably have no result.

Database Provider Specifics

There are many different database servers available. Set up the Database Provider property equal to the database server you are using. While the server itself and its workings have an impact on how you write your queries there are also some more things that are influenced by your selection. See the following table for more info.

Connection String -Link to an external article that specifies how to create the correct connection string.

Parameter naming syntax -How you should mark parameter names inside a query. See "Parameter Property Approach" example above for an example that uses parameters.

End of statement -Which character to use at the end of the statement.

Tips and Tricks

• To work with the result DataTable, you can either use For each Row Activity to loop over the result and continue with these parameters or for easy printing and debugging use the Format DataTable to convert it into a String and use Write Log to print the result.
• If you want to work with parameters you need to create a dictionary first. The dictionary consist of Key-Value-Pairs that have keys that are named like your parameters in the query and values that contain the value you want to pass in for the parameter. You can either create a Dictionary using a VB expression like shown in the "Parameter Property Approach" example above or by using the Create Dictionary and Add or Replace Keyvalue Pair activities.

Troubleshooting

• Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'from': Have a look at the SQL syntax. Your statement is probably not correct.
• ArgumentException: The argument 'nameOrConnectionString' cannot be null, empty or contain only white space: There is no connection string set in the project information or the optional input property.