Le formatage SQL et la clause IN qui casse la production

Pourquoi la paramétrisation corrige l'injection structurellement

Une base reçoit une instruction sous forme de texte et la transforme en plan par étapes : elle tokenise, elle analyse en arbre, elle lie les noms aux objets, elle optimise, et alors seulement elle exécute. Quand tu colles des valeurs dans le texte, tout cela se produit après que tes valeurs ont déjà été fusionnées avec ta syntaxe : c'est donc l'analyseur qui décide de ce que signifient tes données. Voilà l'intégralité de la vulnérabilité. Il n'est pas question de guillemets ou d'apostrophes ; il est question de la frontière entre code et données effacée avant même que la base voie l'instruction.

Une instruction paramétrée inverse l'ordre. Le texte que tu envoies contient des marqueurs et aucune valeur : la base analyse et planifie donc une instruction dont la forme est déjà définitive. Les marqueurs sont des emplacements dans ce plan fini, chacun avec un type déclaré, et lier une valeur revient à remplir un emplacement typé dans un objet compilé — pas à ajouter des caractères à une chaîne qui sera analysée plus tard. Il n'y a pas d'analyse ultérieure. C'est pourquoi la garantie est structurelle : une valeur liée n'a aucun chemin pour devenir un opérateur, un nom de table, un commentaire ou une seconde instruction, car l'étape qui aurait pu l'interpréter ainsi est terminée avant que la valeur n'existe dans le monde de la base.

L'échappement tente d'obtenir le même résultat en transformant la valeur plutôt qu'en déplaçant la frontière, et ce n'est pas une défense équivalente. Il doit être correct dans chaque contexte, et les contextes diffèrent : un littéral chaîne, un littéral numérique, un identifiant, le motif d'un LIKE, une valeur dans une expression de chemin JSON et une valeur dans un ORDER BY construit dynamiquement exigent tous un traitement différent, et certains ne peuvent pas être échappés sûrement du tout. Il doit être correct sous chaque encodage de caractères, car la relation entre octets et caractères est précisément ce sur quoi raisonne une fonction d'échappement. Et il doit être appliqué sur chaque site, pour toujours, y compris celui qu'un collègue ajoutera au trimestre prochain sous contrainte de délai. La paramétrisation doit être juste une fois, dans la forme du code ; l'échappement doit être juste à chaque fois, dans la discipline de l'équipe. Là où la valeur ne peut vraiment pas être un paramètre — nom de table, nom de colonne, sens de tri — la réponse n'est pas de l'échapper mais de la valider contre une liste blanche fixe des identifiants que ton schéma contient réellement.

Les plafonds que chaque base documente réellement

Les nombres qui circulent dans les billets de blog à ce sujet sont souvent périmés : lis-les dans les manuels. La limite de PostgreSQL vient du protocole réseau : le message Bind encode le nombre de valeurs de paramètres dans un champ de seize bits, et libpq refuse tout ce qui dépasse 65 535 avec un message nommant explicitement l'intervalle. MySQL a le même plafond pratique pour la même raison — le compte de marqueurs dans le protocole client-serveur tient sur deux octets — et le dépasser produit l'erreur serveur 1390, dont le texte dit que l'instruction préparée contient trop de marqueurs. Le suivi de bogues de MySQL porte une demande d'évolution en attente pour relever la limite de 64 k, ce qui indique bien qu'il s'agit d'une contrainte réelle et non théorique.

SQL Server est bien plus étroit, et c'est celui qui surprend les équipes. Ses spécifications de capacité publiées indiquent 2 100 paramètres par procédure stockée et 2 100 par fonction définie par l'utilisateur. Un lot paramétré venu d'un pilote client s'exécute via sp_executesql, qui est une procédure stockée : il hérite donc exactement de ce plafond — et l'un de ces emplacements est consommé par la mécanique, d'où les 2 099 ou 2 098 que les praticiens citent souvent comme nombre de valeurs réellement liables. Deux mille identifiants ne sont pas un gros lot selon les standards actuels : sur SQL Server, la limite n'est donc pas un cas limite théorique, c'est une contrainte de conception à anticiper dès le départ.

Oracle limite la liste d'expressions elle-même plutôt que le nombre de paramètres, et le chiffre a changé assez récemment pour que la plupart de ce qu'on lit en ligne soit faux. Jusqu'à 19c, le SQL Language Reference indique qu'une liste d'expressions séparées par des virgules ne peut pas contenir plus de 1 000 expressions, et la dépasser lève ORA-01795. À partir de 23ai, la même page indique 65 535. Une nuance survit au changement : une liste de jeux d'expressions séparés par des virgules — la forme multi-colonnes de IN — peut contenir un nombre quelconque de jeux, mais chaque jeu individuel reste plafonné à 1 000 expressions. Vérifie la version que tu exécutes réellement avant de dimensionner un lot autour de l'un ou l'autre nombre.

La pollution du cache de plans, calculée

Une base met en cache les plans compilés pour ne pas optimiser deux fois la même instruction, et la clé de cache dérive du texte de l'instruction. Une requête à trois marqueurs et une requête à quatre marqueurs sont des textes différents, donc des clés différentes, donc des entrées différentes — bien qu'il s'agisse de la même requête avec une longueur de liste différente. Voilà tout le mécanisme, et l'arithmétique en découle directement : si ton application envoie un jour des longueurs de 1 à N, tu produises N plans distincts au lieu d'un.

Calcule-le sur une plage réaliste. Un point d'entrée qui récupère les commandes de plus de 500 € pour un ensemble d'identifiants clients peut voir de un identifiant à quelques centaines. Servir naïvement les longueurs 1 à 100 produit 100 plans distincts ; 1 à 1 000 en produit 1 000 ; 1 à 5 000 en produit 5 000. Attribue une taille plausible à un plan — 50 Ko est un chiffre médian raisonnable pour une requête avec quelques jointures — et l'empreinte du cache vaut environ 5 Mo à 100 longueurs, 49 Mo à 1 000 et 244 Mo à 5 000. À 200 Ko par plan, ce qu'une requête analytique complexe atteint sans peine, 1 000 longueurs coûtent 195 Mo et 5 000 en coûtent 977. Cette mémoire n'est pas gratuite : elle est prise sur le même pool que ton cache de tampons, et sur les moteurs où le cache de plans est borné, elle évince les plans du reste de ta charge — le symptôme est donc que des requêtes sans rapport ralentissent.

Le texte de l'instruction grossit lui aussi, et il voyage à chaque requête. Une requête de la forme SELECT ... WHERE id IN avec des marqueurs numérotés fait environ 525 octets à 100 valeurs, 5 926 octets à 1 000, 68 927 octets à 10 000 et 513 207 octets à 65 535. Un demi-mégaoctet de texte SQL par requête, analysé de zéro à chaque fois parce qu'aucun plan en cache ne lui correspondra jamais, voilà la forme de la falaise. Il vaut la peine d'être précis sur les deux coûts : le travail d'analyse et d'optimisation est payé à chaque appel car le cache manque toujours, et la mémoire est consommée par des entrées jamais réutilisées.

Les correctifs qui passent à l'échelle : tableau, jointure VALUES, table temporaire

Le meilleur correctif réduit toute la famille d'instructions à une seule. Passe la liste comme un unique paramètre de type tableau ou table, et le texte de l'instruction cesse de dépendre de la longueur : un seul texte distinct, un seul plan en cache, une seule analyse, à chaque appel. PostgreSQL l'offre directement avec un paramètre tableau comparé via ANY, et c'est pourquoi les applications PostgreSQL rencontrent rarement le plafond de 65 535 — un paramètre tableau compte pour un paramètre, quel que soit le nombre d'éléments qu'il contient. SQL Server dispose des paramètres à valeurs de table, qui servent le même but et constituent la réponse standard à sa limite de 2 100. Oracle a des types collection interrogeables comme des tables.

La version portable de la même idée est une jointure sur une liste VALUES, prise en charge par tous les moteurs courants et que l'optimiseur peut traiter comme une petite relation plutôt qu'une longue disjonction. Elle fait toujours varier le texte de l'instruction avec le nombre de lignes : elle ne corrige donc pas le cache de plans à elle seule, mais elle produit très souvent un meilleur plan qu'un OR à mille branches, car l'optimiseur peut la joindre par hachage. Une table temporaire est la version qui passe à l'échelle sans limite : insère les identifiants par lots, joins sur la table, et le texte de l'instruction reste constant quel que soit leur nombre. Cela coûte un aller-retour et un peu d'écriture : elle gagne donc sa place au-dessus de quelques milliers de valeurs plutôt qu'en dessous.

Si tu es coincé avec une liste IN littérale pour l'instant, le remplissage est une parade bon marché qui supprime l'essentiel des dégâts sur le cache. Arrondis la liste à un palier et remplis les emplacements libres avec une valeur qui ne peut pas correspondre — répéter le premier identifiant est l'astuce correcte la plus simple, puisqu'elle ne change rien au résultat. Arrondir à la puissance de deux supérieure transforme 1 000 longueurs possibles en 11 instructions distinctes, mais gaspille en moyenne 174 paramètres par appel sur cette plage, soit 34,8 % de surcoût. Arrondir au multiple de dix supérieur donne 100 instructions distinctes et ne gaspille que 4,5 paramètres en moyenne, 0,9 %. Ce second compromis est généralement le bon : cent plans en cache ne sont rien, et un surcoût de un pour cent sur le nombre de paramètres est invisible.

Découper au-dessus du plafond

Quand la liste dépasse réellement le plafond, découpe-la. Le nombre d'allers-retours découle directement des limites : 100 000 identifiants demandent 2 appels sous PostgreSQL, MySQL ou Oracle 23ai, 48 appels sous SQL Server à 2 100 paramètres, et 100 appels sous Oracle 19c à 1 000 expressions. Cet écart explique qu'une taille de lot qui marche très bien sur le conteneur PostgreSQL d'un développeur s'effondre sur le SQL Server du client, et il vaut mieux dériver la taille de découpage de la base plutôt que de coder un nombre en dur.

Deux détails comptent quand tu découpes. Découpe de façon déterministe — trie les identifiants avant de scinder — pour qu'un réessai envoie les mêmes tranches et que tes journaux soient comparables d'une exécution à l'autre. Et décide explicitement si la lecture doit être cohérente : plusieurs instructions séparées voient plusieurs instantanés séparés, donc si les données peuvent changer sous tes pieds, englobe le lot dans une seule transaction ou accepte que l'union des résultats ne soit l'instantané de rien. Ce second point produit le bogue que personne n'arrive à reproduire, car il n'apparaît que sous écritures concurrentes.

Le formatage n'est pas cosmétique

Deux conséquences découlent du fait que le cache de plans est indexé sur le texte de l'instruction. La première : le formatage compte là où tu l'écris, pas là où tu l'envoies. Une instruction lisible dans ton fichier source, avec les clauses sur leurs propres lignes et la liste répartie sur plusieurs, fait la différence entre une relecture qui attrape une erreur et une qui glisse dessus. La seconde : ne reformate pas le SQL à la sortie. Si un enrobage embellit, minifie ou normalise les espaces à l'exécution, et le fait de manière incohérente — disons différemment sous charge, ou après un changement de configuration — il produit de nouvelles entrées de cache pour une instruction dont le moteur avait déjà un plan. Formate pour les humains, dans le dépôt ; envoie une chaîne stable.

Il y a encore une raison de garder l'instruction lisible, et c'est celle qui justifie cet article. Une longue liste IN rendue sur une seule ligne énorme cache tout : si les valeurs sont liées ou interpolées, si le compte est celui que tu attendais, si une valeur parasite s'est glissée depuis une autre requête. Répartie sur plusieurs lignes avec les marqueurs visibles, les trois sautent aux yeux, tout comme le moment où la liste a cessé d'être une poignée d'identifiants pour devenir discrètement un millier.

Questions fréquentes

L'échappement est-il parfois un substitut acceptable à la liaison ?

Non, et la raison n'est pas que les fonctions d'échappement sont mal écrites : c'est qu'elles résolvent un autre problème. La liaison déplace la frontière entre code et données de sorte qu'une valeur n'a aucune route vers la syntaxe. L'échappement laisse la frontière en place et tente de neutraliser chaque valeur susceptible de la franchir, ce qui l'oblige à être correct dans chaque contexte, sous chaque encodage, à chaque site d'appel, pour toujours. Les contextes diffèrent réellement : un littéral chaîne, un littéral numérique, un identifiant, un motif de LIKE et une valeur dans une clause assemblée dynamiquement demandent chacun un traitement différent, et certains n'ont aucun échappement sûr. Un seul site oublié rouvre tout le trou, et les sites oubliés sont le résultat normal d'une base de code à plusieurs auteurs. Il existe un cas où une valeur ne peut vraiment pas être un paramètre, parce que la base n'accepte pas de marqueur à cet endroit : un nom de table, un nom de colonne, un sens de tri, un LIMIT dans certains moteurs. La réponse n'est toujours pas l'échappement. Valide la valeur contre une liste blanche fixe des identifiants que ton schéma contient réellement, et fais correspondre l'entrée utilisateur à une constante plutôt que de la laisser passer.

Que faire quand la liste dépasse ce que ma base autorise ?

Trois options, par ordre croissant de travail et de capacité à passer à l'échelle. D'abord, un paramètre tableau ou à valeurs de table si ton moteur en a un : toute la liste voyage comme un seul paramètre, le plafond cesse donc de s'appliquer et le texte de l'instruction devient constant. C'est la bonne réponse sous PostgreSQL et sous SQL Server, où la limite de 2 100 mord sinon très tôt. Ensuite, une table temporaire : insère les identifiants par lots, puis joins sur la table. Le texte de l'instruction est constant quelle que soit la taille de la liste, l'optimiseur obtient de vraies informations de cardinalité, et il n'y a plus aucun plafond — cela coûte un aller-retour et un peu d'écriture. Enfin, le découpage : scinde la liste, exécute la requête une fois par tranche et fusionne les résultats dans ton application. Dérive la taille de tranche de la limite propre à la base plutôt que de coder un nombre en dur, car la même liste demande 2 allers-retours sous PostgreSQL et 48 sous SQL Server. Trie avant de scinder pour que les réessais soient reproductibles, et place tout le lot dans une seule transaction si le résultat fusionné doit représenter un instant cohérent — sinon plusieurs instructions séparées voient plusieurs instantanés séparés, source du bogue intermittent que personne n'arrive à reproduire.

Pourquoi la requête ralentit-elle quand la liste grandit, même bien en dessous de la limite ?

Trois effets se cumulent, et il vaut la peine de les séparer car les correctifs diffèrent. Le premier est la compilation : le texte de l'instruction change à chaque longueur de liste, le cache de plans manque donc à chaque fois et l'optimiseur ré-analyse et replanifie de zéro à chaque appel. À 1 000 valeurs, le texte fait déjà environ 5 926 octets ; à 10 000, 68 927. Le temps d'optimisation croît avec le nombre de prédicats : ce n'est donc pas un surcoût constant. Le deuxième est la stratégie de l'optimiseur. Une liste IN est logiquement une chaîne de OR, et passé une certaine longueur l'optimiseur cesse de la traiter comme un ensemble de recherches par index et envisage un balayage — MySQL le documente explicitement pour l'optimiseur d'intervalles, qui suit la mémoire qu'une méthode d'accès par intervalle consommerait et l'abandonne si une limite configurée devait être dépassée, en repliant sur un balayage complet de table. Le franchissement de ce seuil est une falaise, pas une pente. Le troisième est la pression sur le cache décrite plus haut : tes mille plans à usage unique évincent ceux qu'utilisaient d'autres requêtes, si bien que le ralentissement apparaît dans des requêtes que tu n'as pas touchées. Un paramètre tableau ou une table temporaire traite les trois d'un coup, car il rend le texte de l'instruction constant et donne à l'optimiseur une relation qu'il peut joindre.

Utiliser un ORM me dispense-t-il de penser à tout cela ?

Il gère la moitié sécurité et généralement pas la moitié opérationnelle. Tous les ORM courants lient les valeurs au lieu de les interpoler : le risque d'injection disparaît donc réellement pour les constructeurs de requêtes et les instructions générées — à l'exception de toute échappatoire SQL brut, où tu reviens à écrire les paramètres toi-même et où vivent massivement les vulnérabilités des bases de code à ORM. La moitié performance est une autre histoire. La plupart des ORM rendent une clause WHERE sur une collection comme une liste IN littérale avec un marqueur par élément, soit exactement le motif qui fait changer le texte de l'instruction à chaque longueur et inonde le cache de plans. Beaucoup génèrent aussi volontiers une liste plus longue que ce que ta base autorise et n'échouent qu'à l'exécution, d'où le plafond de 2 100 de SQL Server qui se manifeste en incident de production plutôt qu'en échec de test. Les points à vérifier dans ta propre pile sont concrets : ton ORM peut-il émettre un paramètre tableau ou à valeurs de table au lieu d'une liste IN, découpe-t-il automatiquement et avec quelle taille de tranche, et expose-t-il le SQL généré dans un journal lisible ? S'il ne fait rien de tout cela, un appel de chargement anticipé sur une grande collection est un problème de cache de plans qui attend son lundi.

Reformater mon SQL change-t-il ses performances ?

Pas le plan, mais possiblement la recherche dans le cache, et cette distinction mérite d'être retenue. L'optimiseur travaille sur l'arbre d'analyse : les espaces et les sauts de ligne n'ont donc aucun effet sur le plan choisi — une instruction mise en forme sur vingt lignes et la même minifiée sur une seule s'exécutent à l'identique. Ce qu'elles ne partagent pas nécessairement, c'est une entrée de cache, car le cache de plans est indexé sur le texte de l'instruction et les moteurs diffèrent dans la normalisation qu'ils appliquent d'abord. La règle pratique est donc simple : formate le SQL dans ton dépôt, là où un humain le lit, et envoie ce que produit ton pilote sans embellisseur ni minifieur à l'exécution. Un enrobage qui reformate de façon incohérente — différemment sous charge, ou après qu'on a basculé un indicateur de configuration — peut créer une seconde entrée de cache pour une instruction dont le moteur avait déjà un plan : régression petite, silencieuse et très agaçante. Formater dans le dépôt a un second bénéfice plus important : une longue liste IN répartie sur plusieurs lignes rend visible d'un coup d'œil si les valeurs sont liées ou interpolées, et si le compte est celui que tu attendais.