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 |