Appels imbriqués de procédures avec EXECUTE : exécution dynamique de code SQL

Avant de passer à la pratique, répondons à la question : c'est quoi exactement le SQL dynamique ? Imagine que tu dois créer une table avec un nom unique, passé en paramètre. Ou exécuter une requête sur une table dont le nom est déterminé à l'exécution du programme. Ici, le SQL statique ne suffit pas — c'est là que l'exécution dynamique entre en jeu.

PL/pgSQL fournit la commande EXECUTE, qui exécute une requête SQL passée sous forme de chaîne de caractères. Ça te permet de construire et lancer du code SQL "à la volée", en créant des requêtes qui changent selon les paramètres.

Pourquoi le SQL dynamique peut être utile :

1. Flexibilité : Possibilité de construire des requêtes dynamiquement selon les données d'entrée. Par exemple, bosser sur des tables ou colonnes dont les noms ne sont pas connus à l'avance.
2. Automatisation : Créer des tables ou des index avec des noms uniques.
3. Universalité : Pouvoir bosser avec différentes structures de données sans devoir réécrire la procédure.

Exemple dans la vraie vie : imagine que tu développes un système d'analytics, et pour chaque nouveau client tu dois créer une table séparée pour stocker ses données. Tout ça peut être automatisé avec EXECUTE.

Syntaxe de EXECUTE

L'utilisation du SQL dynamique via EXECUTE ressemble à ça :

EXECUTE 'chaîne-SQL';

Exemple de requête simple :

DO $$
BEGIN
  EXECUTE 'CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)';
END $$;

Ce bloc de code va créer la table test_table. Facile, mais voyons des scénarios plus complexes.

Exemples d'utilisation de EXECUTE

1. Créer une table avec un nom dynamique

Supposons que tu dois créer des tables dont les noms dépendent de la date du jour. Voilà comment faire :

DO $$
DECLARE
  table_name TEXT;
BEGIN
  -- Générer le nom de la table
  table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');

  -- Créer la table avec un nom dynamique
  EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';

  -- Afficher un message pour vérifier
  RAISE NOTICE 'Table % créée avec succès', table_name;
END $$;

Ici, le nom dynamique est généré à partir de la date du jour, et la chaîne SQL finale est passée à EXECUTE.

2. Exécuter une requête avec des paramètres dynamiques

Disons que tu veux extraire des données d'une table dont le nom est passé en paramètre. Créons une fonction pour ça :

CREATE OR REPLACE FUNCTION get_data_from_table(table_name TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT id, name FROM ' || table_name || ' WHERE id < 10';
END $$ LANGUAGE plpgsql;

Appel de la fonction :

SELECT * FROM get_data_from_table('employees');

Cette approche est parfaite pour construire des utilitaires universels, genre des systèmes de reporting dynamiques.

Problèmes et limites du SQL dynamique

L'exécution dynamique de code SQL donne beaucoup de liberté, mais comme dans la vie, la liberté implique des responsabilités. Voilà où ça peut coincer :

1. Injections SQL : si tu passes des paramètres de type chaîne dans la requête sans traitement, tu peux permettre à un attaquant d'exécuter du code SQL arbitraire.

   Exemple de code vulnérable :

   EXECUTE 'SELECT * FROM users WHERE name = ''' || user_input || '''';
   

   Si user_input contient la chaîne '; DROP TABLE users; --, la requête va détruire la table users.

2. Débogage compliqué : le code dynamique est plus dur à analyser et à déboguer, car la requête est construite et exécutée à l'exécution.

3. Perte de performance : les requêtes dynamiques court-circuitent le cache de plan d'exécution de PostgreSQL, ce qui peut ralentir les perfs.

Comment se protéger des injections SQL

Pour éviter les attaques par injection SQL, utilise la paramétrisation dans les requêtes dynamiques au lieu de concaténer bêtement des chaînes. En PL/pgSQL, tu fais ça avec la fonction quote_literal() pour les paramètres de type chaîne et quote_ident() pour les identifiants (genre noms de tables ou de colonnes).

Exemple de code sécurisé :

DO $$
DECLARE
  table_name TEXT;
  user_input TEXT := 'John';
BEGIN
  table_name := 'employees';

  EXECUTE 'SELECT * FROM ' || quote_ident(table_name) ||
          ' WHERE name = ' || quote_literal(user_input);
END $$;

Implémentation : mise à jour dynamique de tables

Voici un exemple de procédure qui met à jour des valeurs dans une table dont le nom est passé en paramètre :

CREATE OR REPLACE FUNCTION update_table_data(table_name TEXT, id_value INT, new_data TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE 'UPDATE ' || quote_ident(table_name) ||
          ' SET data = ' || quote_literal(new_data) ||
          ' WHERE id = ' || id_value;
END $$ LANGUAGE plpgsql;

Appel de la fonction :

SELECT update_table_data('test_table', 1, 'Valeur mise à jour');

Exemple : création d'un rapport pour un client

Imaginons que tu gères les commandes par client et que tu veux automatiser la création d'une table de rapport pour chaque client.

CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  -- Générer le nom de la table de rapport
  table_name := 'client_report_' || client_id;

  -- Créer la table pour le rapport
  EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';

  -- Remplir la table avec des données
  EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
          ' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;

  RAISE NOTICE 'Rapport pour le client % créé : table %', client_id, table_name;
END $$ LANGUAGE plpgsql;

Le SQL dynamique avec EXECUTE, c'est un outil puissant qui ouvre des possibilités de ouf pour l'automatisation et la flexibilité en PL/pgSQL. Utilise-le avec précaution, en gardant en tête les risques d'injection SQL. Si tu veux que tes requêtes soient fiables et sécures, pense à utiliser quote_ident() et quote_literal().

Dans la prochaine leçon, on va plonger dans la création de procédures complexes, avec validation des données, mise à jour des enregistrements et log des opérations. Prépare-toi, car bosser avec des requêtes dynamiques va devenir la base pour réaliser ce genre de trucs !

1

Étude/Quiz

Transactions imbriquées, niveau 53, leçon 4

Indisponible

Transactions imbriquées

Transactions imbriquées

Ouvrir