Escape Game SQL

Phase 2 : Cohérence et Intégrité des données

Mission

Suite à votre identification des activités suspectes dans la Phase 1, vous devez maintenant gérer les conséquences de l’attaque en restaurant les transactions légitimes tout en maintenant l’intégrité du système. Cette phase est cruciale car une erreur dans la gestion des données pourrait créer des incohérences dans le système de e-commerce.

Commencez par créer un notebook, copiez/collez le code suivant dans la première cellule et exécutez-la :

%reload_ext sql
%sql postgresql://user_tp:ue_info_reseaux_db@localhost/ue_info_reseaux_db
%config SqlMagic.displaylimit = None

Énigme 4 : Restauration des transactions

Objectif 1 : Gestion des états incohérents

Situation : Le service client a identifié que certaines transactions ont été marquées à tort comme frauduleuses lors de la réponse initiale à l’attaque. La transaction 738 en particulier a été confirmée comme légitime par le client. Cependant, la restauration d’une transaction implique non seulement de changer son statut mais aussi de s’assurer que les stocks des produits sont correctement ajustés.

Commençons par examiner l’état actuel de la transaction :

-- 1. État initial : transaction, produits et stocks par marchand
SELECT 
    t.transaction_id, 
    t.status, 
    p.product_name,
    td.merchant_id, 
    td.quantity,
    mp.available_quantity,
    mp.price as prix_unitaire,
    td.unit_price as prix_payé
FROM transactions t
JOIN transaction_details td ON t.transaction_id = td.transaction_id
JOIN products p ON td.product_id = p.product_id
JOIN merchant_products mp ON td.merchant_id = mp.merchant_id 
                        AND td.product_id = mp.product_id
WHERE t.transaction_id = 738;

Une fois l’état initial analysé, nous pouvons tenter de restaurer la transaction :

-- 2. Changement du statut de la transaction
UPDATE transactions 
SET status = 'COMPLETED'
WHERE transaction_id = 738;

Maintenant, nous devons mettre à jour les stocks :

-- 3. Mise à jour du stock par marchand
UPDATE merchant_products mp
SET available_quantity = mp.available_quantity - td.quantity
FROM transaction_details td
WHERE mp.merchant_id = td.merchant_id 
AND mp.product_id = td.product_id
AND td.transaction_id = 738;

SELECT con.conname AS constraint_name,
       pg_get_constraintdef(con.oid) AS constraint_definition
FROM pg_constraint con
JOIN pg_class tbl ON tbl.oid = con.conrelid
JOIN pg_namespace sch ON sch.oid = tbl.relnamespace
WHERE tbl.relname = 'merchant_products'
      AND con.contype = 'c';

Avant de passer à l’objectif suivant, remettez la transaction 738 dans son état initial :

UPDATE transactions 
SET status = 'CANCELED'
WHERE transaction_id = 738;

Objectif 2 : Maintien de l’intégrité des données avec des transactions SQL

Situation : L’échec de notre tentative précédente met en évidence un risque majeur : si une des opérations échoue, le système peut se retrouver dans un état incohérent. Par exemple, une transaction pourrait être marquée comme complétée alors que les stocks n’ont pas été ajustés, ou vice versa.

Avant de résoudre notre problème, voyons un exemple simple de transaction SQL pour effectuer un virement bancaire :

-- Début de la transaction
BEGIN;

-- Débiter le compte source
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Créditer le compte destinataire
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- Si le compte 2 n'existe pas, le SGBD annule automatiquement la transaction
-- et toutes les modifications précédentes sont annulées

COMMIT;

Dans cet exemple :

• BEGIN démarre la transaction et regroupe les opérations qui suivent
• Si une erreur survient (compte inexistant, contrainte violée…), le SGBD effectue automatiquement un ROLLBACK
• COMMIT valide définitivement les modifications si tout s’est bien passé

La transaction garantit ainsi l’intégrité des données : soit toutes les opérations réussissent, soit aucune modification n’est appliquée. Dans notre exemple, si le compte destinataire n’existe pas, le compte source ne sera pas débité.

A noter que l’utilisation de %%sql dans un Jupyter Notebook ne permet pas d’executer des transactions. Vous allez donc devoir utiliser un peu de code python pour vous connecter directement à la base de données :

import psycopg2

# Connexion
conn = psycopg2.connect(
    host="localhost",
    database="ue_info_reseaux_db",
    user="user_tp",
    password="ue_info_reseaux_db"
)

cur = conn.cursor()

# Transaction
try:
    cur.execute("""
        --ECRIVEZ ICI VOTRE REQUETE--
    """)
except Exception as e:
    print("Erreur:", e)
    conn.rollback()
finally:
    cur.close()
    conn.close()

BEGIN;
-- Vérification du stock
SELECT ...
-- Si suffisant
UPDATE transactions ...
UPDATE merchant_products ...
COMMIT;

Que se passe-t-il ? Vérifiez l’état après la transaction avec la requête de l’état initial. Les deux valeurs (statut et stock) devraient avoir changé de manière cohérente.

Validez vos réponses

Énigme 5 : Gestion de la concurrence d’accès

Situation : Suite à votre restauration de la transaction 738, le marchand a reçu un réapprovisionnement et dispose maintenant de 2 unités en stock du produit concerné. Cette information a déclenché une alerte automatique vers tous les clients qui surveillaient ce produit.

Afin de simuler cette situation exécutez la requête suivante :

UPDATE merchant_products mp
SET available_quantity = 2
FROM transaction_details td
WHERE mp.merchant_id = td.merchant_id 
AND mp.product_id = td.product_id
AND td.transaction_id = 738;

Pour simuler cette situation de concurrence d’accès, nous allons utiliser deux sessions distinctes représentant deux clients différents tentant d’accéder simultanément au même stock. Pour cela, vous devez :

• créer deux nouveaux notebooks Jupyter,
• les nommer respectivement “Client A” et “Client B” et
• copier le code correspondant à chaque client dans son notebook avant de l’executer (vous devrez respecter un timing précis pour executer les scripts).

Pensez à ajouter l’entête suivant dans la première celulle de chaque notebook :

%reload_ext sql
%sql postgresql://user_tp:ue_info_reseaux_db@localhost/ue_info_reseaux_db

La simulation nécessite une exécution précise et coordonnée des deux scripts. Voici la procédure à suivre pas à pas :

1. Lancez d’abord le script du Client A
2. Dans les 5 secondes qui suivent, exécutez le script du Client B
3. Observez attentivement le comportement des deux sessions pendant l’exécution
4. Patientez jusqu’à la fin complète de l’expérience (environ 30 secondes)

Code pour le Client A (Premier acheteur) :

Le script du Client A simule un acheteur qui initie une transaction longue : il commence par réserver le stock, effectue un traitement qui prend du temps (simulé par une pause de 30 secondes), puis finalise son achat en mettant à jour le statut de sa transaction.

# Notebook A (Premier Client)
import psycopg2
import time

# Connexion
conn_a = psycopg2.connect(
    host="localhost", database="ue_info_reseaux_db",
    user="user_tp", password="ue_info_reseaux_db"
)
cur_a = conn_a.cursor()

try:
    # Démarrage de la transaction du client A
    cur_a.execute("BEGIN;")
    print("Transaction A démarrée!")

    # Mise à jour du stock
    print("Mise à jour du stock...")
    cur_a.execute("""
        UPDATE merchant_products mp
        SET available_quantity = mp.available_quantity - td.quantity
        FROM transaction_details td
        WHERE mp.merchant_id = td.merchant_id 
        AND mp.product_id = td.product_id
        AND td.transaction_id = 738;
    """)
    
    print("Premier UPDATE effectué. Simulation d'un traitement long...")
    time.sleep(30)  

    # Mise à jour de la transaction
    cur_a.execute("""
        UPDATE transactions 
        SET status = 'COMPLETED'
        WHERE transaction_id = 738;
    """)
    
    cur_a.execute("COMMIT;")
    print("Transaction A terminée avec succès!")
    
except Exception as e:
    print("Erreur transaction A:", e)
    conn_a.rollback()
finally:
    cur_a.close()
    conn_a.close()

3. Code pour le Client B (Second acheteur) :

Le script du Client B représente un second acheteur qui tente d’acheter au même vendeur le même produit que le client A. Cependant il veut immédiatement acheter les 3 unités disponibles ce qui déclenche une nouvelle transaction mettant à jour le stock dans la foulée.

# Notebook B (Second Client)
import psycopg2

conn_b = psycopg2.connect(
    host="localhost", database="ue_info_reseaux_db",
    user="user_tp", password="ue_info_reseaux_db"
)
cur_b = conn_b.cursor()

try:
    cur_b.execute("BEGIN;")
    
    cur_b.execute("""
        UPDATE merchant_products
        SET available_quantity = available_quantity - 3
        WHERE merchant_id = 1 AND product_id = 1;
    """)
    
    cur_b.execute("""
        INSERT INTO transactions (customer_id, amount, ip_address, status, merchant_id, transaction_type)
        VALUES (115, 2599.98, '192.168.1.2', 'COMPLETED', 1, 'PURCHASE');
    """)
    
    cur_b.execute("COMMIT;")
    print("Transaction réussie")
        
except Exception as e:
    print(f"Erreur: {e}")
    conn_b.rollback()
finally:
    cur_b.close()
    conn_b.close()

Validez vos réponses