Les quatre garde-fous SQL

Le compilateur ne sait produire que des SELECT sur quatre tables. Pourquoi ajouter des contrôles ? Parce que le compilateur évoluera. Dans six mois, quelqu'un ajoutera une métrique « avancée » avec une sous-requête vers events (384 Mo), ou un mode « SQL expert » pour les analystes. Les garde-fous sont écrits pour ce jour-là : ils ne font pas confiance à l'origine du SQL, seulement à ce que BigQuery dit qu'il va lire.

Le mécanisme : du moins cher au plus sûr

flowchart TD
  S["SQL + paramètres"] --> A{"1. Statique (local, 0 ms)<br/>une instruction, SELECT/WITH,<br/>aucun mot-clé d'écriture, aucune colonne sensible"}
  A -->|"refus"| R["RequeteRefusee : message renvoyé au modèle"]
  A --> B{"2. Dry-run BigQuery (gratuit)<br/>SQL valide ? type ? tables ? octets ?"}
  B -->|"SQL invalide"| R
  B --> C{"3. Politique<br/>type = SELECT, tables en liste blanche,<br/>octets facturés prévus ≤ 1 Go"}
  C -->|"refus"| R
  C --> D["4. Exécution<br/>maximum_bytes_billed, délai, lignes bornées, labels"]
  D -->|"bytesBilledLimitExceeded"| R
  D --> OK["Lignes + octets facturés + coût + durée"]

Chaque couche couvre un angle mort de la précédente :

Le dry-run est une exécution à blanc : BigQuery analyse la requête, résout les tables, estime les octets lus et ne facture rien. Il répond en quelques centaines de millisecondes.

La mise en œuvre

assistant/garde_fous.py enchaîne les quatre étapes dans executer(). La partie qui compte le plus est la prévision de facturation :

def octets_factures_prevus(estimation: dict) -> int:
    """Ce que BigQuery facturera : arrondi au Mo, minimum 10 Mo par table lue et par requête."""
    arrondi = -(-estimation["octets"] // MO) * MO
    return max(arrondi, 10 * MO * max(1, len(estimation["tables"])))


def verifier_estimation(estimation: dict, autorisees: set[str], max_octets: int) -> None:
    if estimation["type"] != "SELECT":
        raise RequeteRefusee(f"Instruction {estimation['type']} refusée : lecture seule.")
    hors = sorted(set(estimation["tables"]) - autorisees)
    if hors:
        raise RequeteRefusee(f"Tables hors liste blanche : {', '.join(hors)}.")
    prevus = octets_factures_prevus(estimation)
    if prevus > max_octets:
        raise RequeteRefusee(...)

La tarification à la demande de BigQuery arrondit au Mo et applique un minimum de 10 Mo par table référencée (tarifs BigQuery). Sur ce jeu, une requête qui lit 1,9 Mo est facturée 10,5 Mo ; une question qui joint les produits et les clients en coûte 31,5. Un contrôle fondé sur les octets lus sous-estime donc la facture des petites requêtes, et le plafond de BigQuery (qui porte sur les octets facturés) refuserait une requête que votre contrôle a laissé passer.

L'exécution réutilise les mêmes paramètres et étiquette chaque job (formation-lab=assistant-data) pour retrouver la dépense dans la facturation :

job = client(emplacement).query(sql, job_config=bigquery.QueryJobConfig(
    maximum_bytes_billed=config.MAX_OCTETS, labels=config.LABELS,
    query_parameters=_parametres(params)))

Le module s'utilise aussi seul, ce qui permet de tester les garde-fous sur du SQL écrit à la main. Résultats obtenus le 2026-10-15 :

$ python -m assistant.garde_fous "SELECT department, ROUND(AVG(retail_price), 2) AS prix_moyen FROM `...products` GROUP BY department"
OK : 0.4 Mo estimés, 10.5 Mo facturés, 0.000060 $, 897 ms
[{'department': 'Women', 'prix_moyen': 55.94}, {'department': 'Men', 'prix_moyen': 63.21}]

$ python -m assistant.garde_fous "SELECT status, COUNT(*) AS n FROM `...order_items` GROUP BY status ORDER BY n DESC"
OK : 1.9 Mo estimés, 0.0 Mo facturés, 0.000000 $, 764 ms        <- 2e passage : servi par le cache, gratuit

$ python -m assistant.garde_fous "SELECT email FROM `...users` LIMIT 5"
REFUS : Colonne sensible ou sélection globale interdite : email.

$ python -m assistant.garde_fous "SELECT uri, COUNT(*) AS n FROM `...events` GROUP BY uri"
REFUS : Tables hors liste blanche : bigquery-public-data.thelook_ecommerce.events.

$ python -m assistant.garde_fous "DELETE FROM `...orders` WHERE TRUE"
REFUS : Seules les requêtes de lecture (SELECT) sont permises.

$ python -m assistant.garde_fous "SELECT statut FROM `...order_items`"
REFUS : SQL rejeté au dry-run : Unrecognized name: statut; Did you mean status? at [1:8]

$ MAX_OCTETS=5000000 python -m assistant.garde_fous "SELECT SUM(sale_price) AS ca FROM `...order_items`"
REFUS : Requête trop coûteuse : 10 Mo facturés prévus (1.4 Mo lus) pour un plafond de 5 Mo.

Le dernier cas vient d'un vrai défaut de la première version : elle comparait les 1,4 Mo lus au plafond de 5 Mo, laissait partir la requête, et BigQuery la rejetait à l'exécution avec bytesBilledLimitExceeded ... 10485760 or higher required. Le contrôle mentait, le filet final a tenu.

Ce qui casse en production

La règle de décision

• Dry-run systématique avant toute requête générée : il coûte 0 $ et 300 à 900 ms, et il donne au modèle un message d'erreur précis (« Did you mean status? ») pour se corriger.
• maximum_bytes_billed toujours, même avec un dry-run : c'est le seul contrôle appliqué côté serveur.
• Plafond : 1 Go par requête couvre ce jeu cent fois ; sur vos données, fixez-le à quelques fois la plus grosse requête légitime observée, pas à « ce qui ne bloque jamais ».

Pratique

Exercice. Les analystes demandent un mode « SQL expert » : ils tapent du SQL, l'assistant l'exécute avec les mêmes garde-fous. Listez ce qui manque à garde_fous.py pour l'ouvrir sans risque, puis écrivez le test qui prouve que SELECT u.email FROM users AS u reste bloqué.

def test_alias_de_colonne_sensible():
    with pytest.raises(gf.RequeteRefusee, match="sensible"):
        gf.controle_statique("SELECT u.email FROM users AS u")

@pytest.mark.xfail(reason="liste noire par nom : utiliser une vue autorisée")
def test_serialisation_de_ligne():
    with pytest.raises(gf.RequeteRefusee):
        gf.controle_statique("SELECT TO_JSON_STRING(u) FROM users AS u")

• Je sais ce que le dry-run vérifie et ce qu'il ne voit pas
• Je sais calculer les octets facturés d'une petite requête
• Je sais pourquoi une liste blanche de tables ne protège pas une colonne
• J'ai lancé python -m assistant.garde_fous sur une requête acceptée et une requête refusée