Skip to main content

SQL Database Safe Operation

Overview​

Provides a safe way to build parameterised SQL statements which help avoid SQL injection

See the table below for examples of paramaterised queries which can differ per database type:

DatabasePlaceholderExample
PostgreSQL$1, $2, ...SELECT * FROM users WHERE id = $1
Oracle:1, :2, ...SELECT * FROM users WHERE id = :1
MySQL?SELECT * FROM users WHERE id = ?
MSSQL@p1, @p2, ...SELECT * FROM users WHERE id = @p1
  • PostgreSQL uses numbered placeholders, indicated with a dollar sign and an integer (e.g., $1, $2, etc.). These placeholders are used sequentially according to their position in the statement.
  • Oracle uses numbered placeholders similar to PostgreSQL but with a colon instead of a dollar sign (e.g., :1, :2, etc.). These placeholders are used sequentially as well.
  • MySQL uses the question mark ? as placeholders for prepared statements. Each ? is replaced with the corresponding parameter in the order they are passed to the statement.
  • MSSQL uses named placeholders, which are typically indicated by @ followed by a name (e.g., @p1, @p2, etc.). The names are arbitrary and do not have to be sequential, but they should match the names used in the statement when passing the parameters.

The examples show a simple SELECT statement to retrieve user records from a users table where the id column matches a specified parameter.

Returns
Data Structure

Parameters​

ParameterParameterValid BlocksRequired
Use Dynamic NameShow the Dynamic Name slot in the tree so that it can be set dynamicallyBoolean Block GroupNo
Dynamic NameThe dynamic name of this itemText Block GroupNo
AttributesMetadata values for this block. Used in XML and multipart/form-data.Text Block GroupNo
Query TemplateSQL query template to be executedText Block GroupNo
ArgumentsA list of arguments to be plugged into the query, in order of appearance or value - see docs for how to access them ($1 for Postgres or ? for MySQL etc.)Any Data Type Block GroupNo