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:
| Database | Placeholder | Example |
|---|---|---|
| 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
| Parameter | Parameter | Valid Blocks | Required |
|---|---|---|---|
Set a Dynamic Name | Show the Dynamic Name slot in the tree so that it can be set statically or dynamically | Boolean Block Group | No |
Dynamic Name | The dynamic name of this item | Text Block Group | No |
Attributes | Metadata values for this block. Used in XML and multipart/form-data. | Text Block Group | No |
Query Template | SQL query template to be executed | Text Block Group | No |
Arguments | A 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 Group | No |