Add parameter binding to the Query tasks and Trigger triggers

[anna-geller]

We want to support parameter binding in SQL Query tasks. This is an optional property, so it is not a breaking change.

• parameters:
  • Type: map
  • Required: ❌
  • Description: A map of parameters to bind to the SQL queries. The keys should match the parameter placeholders in the SQL string, e.g., :parameterName.

Example flow:

id: run_query
namespace: company.team

tasks:
  - id: query
    type: io.kestra.plugin.jdbc.postgresql.Query
    url: jdbc:postgresql://host.docker.internal:5432/postgres
    username: postgres
    password: xxx
    sql: |
      INSERT INTO myusers (name, email, login) 
      VALUES (:name, :email, :login) 
      ON CONFLICT (email) DO NOTHING;
    parameters:
      name: Rick
      email: "rick@kestra.io"
      login: "{{ execution.startDate }}"

Similar parameters map will be needed on the Trigger https://kestra.io/plugins/plugin-jdbc-postgres/triggers/io.kestra.plugin.jdbc.postgresql.trigger

TODOs:

• start by adding this in Postgres
• adjust all JDBC Query tasks
• adjust all JDBC Trigger triggers

[Ben8t]

Advantage vs Pebble ?

-> less verbose (because one would have to use a "long" outputs.task.some_value or inputs.value)
-> 🤔 ?

INSERT INTO myusers (name, email, login) 
VALUES ({{inputs.name}}, {{inputs.email}}, {{inputs.login}}) 
ON CONFLICT ({{inputs.email}}) DO NOTHING

[loicmathieu]

Advantage vs Pebble ?

Avoid SQL injection!