Query

Runs a SQL query against a Relational Database. Outputs query results.

Usage

You can view the list of supported databases and how to configure them in the Project Editor documentation. There is a separate Query Node in the Node Selector for each database technology supported in Sanbox.

Configuring a Query Node

Configuring a Connection

The Query Node takes a connection object that tells it how to communicate with the database. You can configure Project level connections in the Project Editor.

Output

By default, the Query Node outputs an array with all the records that came back from the query. You can check Output Single Result to output just one record.

When Output Single Result is checked:

  • One object is outputted for the first record in the query result. For each column, the object has a property with the column name as the property name and the data found in the column as the value.

  • If multiple records come back from the query, the first record is still outputted.

  • If no records come back, null is outputted.

When Output Single Result is not checked:

  • Your query comes back as an array of objects. There is an object in the array for each row returned from the result set. For each column of each row, the object has a property with the column name as the property name and the data found in the column as the value.

  • When an empty result set comes back from the query, an empty array is outputted.

Writing Your Query

The Query Node has two modes available to run your query using Sanbox Text Templates - Protected Mode and Full Mode.

Protected Mode

In Protected Mode, your queries are not susceptible to traditional SQL injection attacks. Protected Mode places some constraints on your template. Any template in your query must only use JSONPath and Sanbox text functions in the template, and you should not add any quotes for strings in this mode.

Example: Input is string ACEE

Correct

Select * From Customers where Customer.ID = {{ input }}

Incorrect

Select * From Customers where Customer.ID = '{{ input }}'

Example: Input is object:

{
"customerId": "ACEE",
"firstName": "John",
"lastName": "Smith"
}

Correct - Use jPathInput instead of member access.

Select * From Customers where Customer.ID = {{ jPathInput "customerId" }}

Incorrect - Using member access which is disallowed in Protected Mode.

Select * From Customers where Customer.ID = {{ input.customerId }}

Full Mode

In Full Mode, no constraints are placed on your query template, however, your query is not protected from SQL Injection hacking. Full Mode should only be used if you are comfortable with the risks. (For example, building an internal only utility app where SQL is never combined with data from a potential vector of attack.)

Unlike Protected Mode, Full Mode requires you to add string quotes.