Variables de liaison¶

Aperçu des variables de liaison¶

Avec les variables de liaison, vous remplacez les valeurs littérales des instructions SQL par des caractères de remplacement. Par exemple, l’instruction SQL suivante utilise des littéraux pour les valeurs insérées :

INSERT INTO t (c1, c2) VALUES (1, 'Test string');

L’instruction SQL suivante utilise des caractères de remplacement pour les valeurs insérées :

INSERT INTO t (c1, c2) VALUES (?, ?);

Votre code d’application lie les données à chaque caractère de remplacement dans l’instruction SQL. La technique de liaison des données avec un caractère de remplacement dépend du langage de programmation. La syntaxe du caractère de remplacement varie également en fonction du langage de programmation. Soit ?, :varname ou %varname.

Utilisation de variables de liaison dans les procédures stockées Javascript¶

Vous pouvez utiliser Javascript pour créer des procédures stockées qui exécutent le SQL.

Pour spécifier des variables de liaison dans le code Javascript, utilisez des caractères de remplacement ?. Par exemple, l’instruction INSERT suivante spécifie des variables de liaison pour les valeurs insérées dans la ligne d’une table :

INSERT INTO t (col1, col2) VALUES (?, ?)

Dans le code Javascript, vous pouvez utiliser des variables de liaison pour les valeurs de la plupart des instructions SQL. Pour des informations sur les limites, voir Limites relatives aux variables de liaison.

Pour plus d’informations sur l’utilisation des variables de liaison en Javascript, voir Variables de liaison.

Utiliser des variables de liaison avec l’exécution de scripts Snowflake¶

Vous pouvez utiliser l’exécution de scripts Snowflake pour créer du code procédural qui exécute SQL, comme les blocs de code et les procédures stockées. Pour spécifier des variables de liaison dans le code Snowflake Scripting, faites précéder le nom de la variable d’un deux-points. Par exemple, l’instruction INSERT suivante spécifie une variable de liaison nommée variable1 :

INSERT INTO t (c1) VALUES (:variable1)

Lorsque vous exécutez une requête SQL dans une commande EXECUTE IMMEDIATE ou une commande OPEN pour un curseur, vous pouvez lier des variables avec la clause USING.

Cet exemple lie les variables d’une commande EXECUTE IMMEDIATE à une clause USING :

EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price);

Pour obtenir l’exemple complet incluant ce code, voir Exécution d’une instruction contenant des variables de liaison.

Lorsque vous déclarez un curseur, vous pouvez spécifier des paramètres de liaison (caractères ?) dans une instruction SELECT. Vous pouvez ensuite lier ces paramètres à des variables dans la clause USING lorsque vous ouvrez le curseur.

L’exemple suivant déclare un curseur et spécifie des paramètres de liaison, puis ouvre le curseur avec la clauseUSING :

LET c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
OPEN c1 USING (minimum_price, maximum_price);

Snowflake Scripting prend également en charge la numérotation des variables de liaison par position et la réutilisation d’une variable de liaison dans une instruction SQL. Pour les variables de liaison numérotées, un index est attribué à chaque déclaration de variable et vous pouvez faire référence à la énième variable déclarée à l’aide de :n. Par exemple, le bloc Snowflake Scripting suivant spécifie la variable de liaison :1 pour la variable i et :2 pour la variable v, et il réutilise la variable de liaison :1 dans une instruction SQL :

EXECUTE IMMEDIATE $$
DECLARE
  i INTEGER DEFAULT 1;
  v VARCHAR DEFAULT 'SnowFlake';
  r RESULTSET;
BEGIN
  CREATE OR REPLACE TABLE snowflake_scripting_bind_demo (id INTEGER, value VARCHAR);
  EXECUTE IMMEDIATE 'INSERT INTO snowflake_scripting_bind_demo (id, value)
    SELECT :1, (:2 || :1)' USING (i, v);
  r := (SELECT * FROM snowflake_scripting_bind_demo);
  RETURN TABLE(r);
END;
$$
;

+----+------------+
| ID | VALUE      |
|----+------------|
|  1 | SnowFlake1 |
+----+------------+

Dans le code Snowflake Scripting, vous pouvez utiliser des variables de liaison pour les valeurs de la plupart des instructions SQL. Pour des informations sur les limites, voir Limites relatives aux variables de liaison.

Pour plus d’informations sur l’utilisation des variables de liaison dans Snowflake Scripting, voir Utiliser une variable dans une instruction SQL (liaison) et Utilisation d’un argument dans une instruction SQL (liaison).

Utiliser des variables de liaison avec l’API SQL¶

Vous pouvez utiliser l” API SQL Snowflake pour accéder aux données d’une base de données Snowflake et les mettre à jour. Vous pouvez créer des applications qui utilisent l’API SQL pour soumettre des instructions SQL et gérer des déploiements.

Lorsque vous soumettez une requête qui exécute une instruction SQL, vous pouvez utiliser des variables de liaison pour les valeurs de l’instruction. Pour plus d’informations, voir Utilisation de variables de liaison dans une instruction.

Utiliser des variables de liaison avec des pilotes¶

En utilisant des pilotes Snowflake, vous pouvez écrire des applications qui effectuent des opérations sur Snowflake. Les pilotes prennent en charge des langages de programmation tels que Go, Java et Python. Pour plus d’informations sur l’utilisation des variables de liaison dans une application pour un pilote spécifique, suivez le lien correspondant au pilote :

• Go

• JDBC

• .NET

• Node.js

• ODBC

• Python

Utiliser des variables de liaison avec des tableaux de valeurs¶

Vous pouvez lier un tableau de valeurs à des variables dans des instructions SQL. Cette technique permet d’améliorer les performances en insérant plusieurs lignes en un seul lot, ce qui évite les allers-retours sur le réseau et les compilations. L’utilisation d’une liaison de tableaux est également appelée « insertion en masse » ou « insertion par lots ».

Voici un exemple de liaison de tableaux en code Python :

conn = snowflake.connector.connect( ... )
rows_to_insert = [('milk', 2), ('apple', 3), ('egg', 2)]
conn.cursor().executemany(
            "insert into grocery (item, quantity) values (?, ?)",
            rows_to_insert)

Cet exemple spécifie la liste de liaisons suivantes : [('milk', 2), ('apple', 3), ('egg', 2)]. La manière dont une application spécifie une liste de liaisons dépend du langage de programmation.

Ce code insère trois lignes dans le tableau :

+-------+----+
| C1    | C2 |
|-------+----|
| milk  |  2 |
| apple |  3 |
| egg   |  2 |
+-------+----+

Pour plus d’informations sur l’utilisation des liaisons de tableaux dans une application pour un pilote spécifique, suivez le lien correspondant au pilote :

• Go

• JDBC

• .NET

• Node.js

• ODBC

• Python

INSERT INTO t VALUES (?,?), (?,?);

Utiliser des variables de liaison avec des données semi-structurées¶

Pour lier des variables avec des données semi-structurées, liez la variable en tant que type de chaîne et utilisez des fonctions telles que PARSE_JSON ou ARRAY_CONSTRUCT.

L’exemple suivant crée une table avec une colonne VARIANT, puis appelle la fonction PARSE_JSON pour insérer des données semi-structurées dans la table à l’aide d’une variable de liaison :

CREATE TABLE t (a VARIANT);
-- Code that supplies a bind value for ? of '{'a': 'abc', 'x': 'xyz'}'
INSERT INTO t SELECT PARSE_JSON(a) FROM VALUES (?);

L’exemple suivant interroge la table :

SELECT * FROM t;

La requête renvoie le résultat suivant :

+---------------+
| A             |
|---------------|
| {             |
|   "a": "abc", |
|   "x": "xyz"  |
| }             |
+---------------+

L’instruction suivante appelle la fonction ARRAY_CONSTRUCT pour insérer un tableau de données semi-structurées dans une colonne VARIANT avec une variable de liaison :

INSERT INTO t SELECT ARRAY_CONSTRUCT(column1) FROM VALUES (?);

Ces deux exemples peuvent insérer une seule ligne ou utiliser une liaison de tableaux pour insérer plusieurs lignes en une seule fois. Vous pouvez utiliser cette technique pour insérer n’importe quel type de données semi-structurées valides dans une colonne VARIANT.

Récupérer les valeurs des variables de liaison¶

Pour récupérer les valeurs des variables de liaison dans une requête qui a été exécutée, vous pouvez utiliser la fonction de table BIND_VALUES dans le schéma INFORMATION_SCHEMA. Avec cette fonction, vous pouvez récupérer les valeurs des variables de liaison à partir de n’importe quel code qui prend en charge les variables de liaison, y compris Javascript et le code Exécution de scripts Snowflake.

Vous pouvez également accéder aux valeurs des variables de liaison à partir de la colonne bind_values dans la sortie pour la vue d’utilisation du compte QUERY_HISTORY, la vue d’utilisation du compte QUERY_HISTORY, ou la fonction QUERY_HISTORY.

Pour récupérer les valeurs des variables de liaison à l’aide de l’une de ces méthodes, vous devez définir le paramètre ALLOW_BIND_VALUES_ACCESS au niveau du compte sur TRUE.

Vous pouvez récupérer les valeurs des variables de liaison dans les cas suivants :

• Dépannage des requêtes - Lorsque vous connaissez les valeurs de liaison exactes utilisées dans les requêtes, il est plus facile d’optimiser les requêtes et de déboguer les types de problèmes suivants :

  • Une requête ne s’exécute pas.

  • Les performances d’une requête sont mauvaises.

  • Une requête n’utilise pas de caches ni de plans d’exécution attendus.

• Recréation de requêtes pour les tests - Les développeurs et les DBAs peuvent recréer des requêtes générées par l’utilisateur avec des valeurs de variables de liaison pour répliquer les problèmes et pour les tests de contrainte.

• Audit et conformité - Pour des raisons de sécurité et de conformité, les organisations doivent auditer les données auxquelles les utilisateurs accèdent. Elles peuvent utiliser des valeurs de variables de liaison pour déterminer les données exactes récupérées par les utilisateurs.

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('<query_id_value>'));

SELECT bind_values
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE query_id = '<query_id_value>';

DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;

EXECUTE IMMEDIATE
$$
DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;
$$
;

+--------------------------------------+
| anonymous block                      |
|--------------------------------------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |
+--------------------------------------+

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('01bbe3d6-0109-0863-0000-a99502ffa062'));

+--------------------------------------+----------+-------------+------+---------+
| QUERY_ID                             | POSITION | NAME        | TYPE | VALUE   |
|--------------------------------------+----------+-------------+------+---------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | TEMPERATURE | REAL | -20.14  |
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | NAME        | TEXT | Snowman |
+--------------------------------------+----------+-------------+------+---------+

cursor = conn.cursor()
print(cursor.execute(
          """
          SELECT
              CONCAT('Hello ', ?, '!') as greeting,
              CONCAT('It is ', ?, 'deg C today.') as weather
          """,
          params=["Snowman", -20.14],
      ).fetch_pandas_all())

query_id = cursor.sfqid
print(f"Bind values for query {query_id} are:")
print(cursor.execute("SELECT * FROM TABLE(INFORMATION_SCHEMA.BIND_VALUES(?))", params=[query_id]).fetch_pandas_all())

        GREETING                   WEATHER
0  Hello Snowman!  It is -20.14deg C today.

Bind values for query 01bbe918-0200-0001-0000-000000101145 are:

                               QUERY_ID POSITION  NAME  TYPE    VALUE
0  01bbe918-0200-0001-0000-000000101145        1  None  TEXT  Snowman
1  01bbe918-0200-0001-0000-000000101145        2  None  REAL   -20.14

Limites relatives aux variables de liaison¶

Les limites suivantes s’appliquent aux variables de liaison :

• Limites relatives aux instructions SELECT :

  • Les variables de liaison ne peuvent pas remplacer les nombres qui font partie de la définition d’un type de données (par exemple, NUMBER(?)) ou spécification de classement (par exemple, COLLATE ?).

  • Les variables de liaison ne peuvent pas être utilisées comme source dans une instruction SELECT qui interroge des fichiers sur une zone de préparation interne.

• Limites relatives aux commandes DDL :

  • Les variables de liaison ne peuvent pas être utilisées dans les commandes DDL suivantes :

    • CREATE/ALTER INTEGRATION

    • CREATE/ALTER REPLICATION GROUP

    • CREATE/ALTER PIPE

    • CREATE TABLE … USING TEMPLATE

  • Les variables de liaison ne peuvent pas être utilisées dans les clauses suivantes :

    • ALTER COLUMN

    • COMMENT ON CONSTRAINT

  • Dans les commandes CREATE/ALTER, les variables de liaison ne peuvent pas être utilisées pour les valeurs des paramètres suivants :

    • CREDENTIALS

    • DIRECTORY

    • ENCRYPTION

    • IMPORTS

    • PACKAGES

    • REFRESH

    • TAG

    • Paramètres spécifiques aux tables externes

  • Les variables de liaison ne peuvent pas être utilisées pour les propriétés qui font partie d’une valeur FILE FORMAT.

• Dans les commandes COPY INTO, les variables de liaison ne peuvent pas être utilisées pour les valeurs des paramètres suivants :

  • CREDENTIALS

  • ENCRYPTION

  • FILE_FORMAT

• Dans les commandes SHOW, les variables de liaison ne peuvent pas être utilisées dans le paramètre STARTS WITH.

• Les variables de liaison ne peuvent pas être utilisées dans une commande EXECUTE IMMEDIATE FROM.

• Les valeurs des variables de liaison ne peuvent pas être converties automatiquement d’un type de données à un autre lorsque les variables de liaison sont utilisées dans ce qui suit :

  • Code Snowflake Scripting qui spécifie explicitement le type de données

  • Instructions DDL

  • Noms de zones de préparation

Considérations de sécurité pour les variables de liaison¶

Les variables de liaison ne masquent pas les données sensibles dans tous les cas. Par exemple, les valeurs des variables de liaison peuvent apparaître dans les messages d’erreur et autres artefacts.

Les variables de liaison peuvent empêcher les attaques par injection SQL lorsque vous construisez des instructions SQL avec une entrée de l’utilisateur. Cependant, les variables de liaison peuvent présenter des risques potentiels pour la sécurité. Si les entrées effectuées dans les instructions SQL proviennent de sources externes, assurez-vous qu’elles sont validées. Pour plus d’informations, voir injection SQL.