La formattazione SQL e la clausola IN che rompe la produzione

Perché la parametrizzazione risolve l'iniezione strutturalmente

Un database riceve un'istruzione come testo e la trasforma in un piano per stadi: tokenizza, analizza in un albero, lega i nomi agli oggetti, ottimizza e solo allora esegue. Quando incolli i valori dentro il testo, tutto questo accade dopo che i tuoi valori sono già stati fusi con la tua sintassi, quindi è l'analizzatore a decidere cosa significano i tuoi dati. È l'intera vulnerabilità. Non è questione di virgolette o apostrofi; è questione del confine fra codice e dati cancellato prima che il database veda l'istruzione.

Un'istruzione parametrizzata inverte l'ordine. Il testo che invii contiene segnaposto e nessun valore, quindi il database analizza e pianifica un'istruzione la cui forma è già definitiva. I segnaposto sono caselle in quel piano finito, ciascuna con un tipo dichiarato, e legare un valore significa riempire una casella tipizzata dentro un oggetto compilato — non aggiungere caratteri a una stringa che verrà analizzata dopo. Non c'è analisi successiva. Ecco perché la garanzia è strutturale: un valore legato non ha alcuna strada per diventare un operatore, un nome di tabella, un commento o una seconda istruzione, perché lo stadio che avrebbe potuto interpretarlo così è finito prima che il valore esista nel mondo del database.

L'escape prova a ottenere lo stesso risultato trasformando il valore invece di spostare il confine, e non è una difesa equivalente. Deve essere corretto in ogni contesto, e i contesti non sono uguali: un letterale di stringa, un letterale numerico, un identificatore, il pattern di un LIKE, un valore dentro un'espressione di percorso JSON e un valore dentro un ORDER BY costruito dinamicamente richiedono trattamenti diversi, e alcuni non si possono affatto sottoporre a escape in sicurezza. Deve essere corretto sotto ogni codifica di caratteri, perché il rapporto fra byte e caratteri è esattamente ciò su cui ragiona una funzione di escape. E deve essere applicato in ogni singolo punto, per sempre, incluso quello che un collega aggiungerà il prossimo trimestre sotto scadenza. La parametrizzazione deve essere giusta una volta, nella forma del codice; l'escape deve essere giusto ogni volta, nella disciplina del team. Dove il valore davvero non può essere un parametro — un nome di tabella, un nome di colonna, un verso di ordinamento — la risposta non è l'escape ma la validazione contro un elenco fisso di identificatori che il tuo schema contiene davvero.

I tetti che ogni database documenta davvero

I numeri che circolano nei post di blog su questo sono spesso scaduti: leggili nei manuali. Il limite di PostgreSQL viene dal protocollo di rete: il messaggio Bind codifica il numero di valori di parametri in un campo a sedici bit, e libpq rifiuta qualsiasi cosa sopra 65 535 con un messaggio che nomina l'intervallo esplicitamente. MySQL ha lo stesso tetto pratico per la stessa ragione — il conteggio dei segnaposto nel protocollo client-server occupa due byte — e superarlo produce l'errore di server 1390, il cui testo dice che l'istruzione preparata contiene troppi segnaposto. Il tracker di bug di MySQL porta una richiesta di miglioria in sospeso per alzare la soglia dei 64 k, il che indica bene che è un vincolo reale e non teorico.

SQL Server è assai più stretto, ed è quello che coglie di sorpresa i team. Le sue specifiche di capacità pubblicate indicano 2 100 parametri per stored procedure e 2 100 per funzione definita dall'utente. Un batch parametrizzato da un driver client viene eseguito tramite sp_executesql, che è una stored procedure, quindi eredita esattamente quel tetto — e una di quelle caselle è consumata dalla meccanica, ed è per questo che i professionisti citano di solito 2 099 o 2 098 come numero di valori davvero legabili. Duemila identificatori non sono un lotto grande per gli standard attuali: su SQL Server il limite non è un caso limite teorico, è un vincolo di progetto da prevedere fin dall'inizio.

Oracle limita la lista di espressioni stessa anziché il numero di parametri, e la cifra è cambiata abbastanza di recente perché gran parte di ciò che leggerai online sia sbagliato. Fino a 19c la SQL Language Reference indica che una lista di espressioni separate da virgole non può contenere più di 1 000 espressioni, e superarla solleva ORA-01795. Dal 23ai la stessa pagina indica 65 535. Una sfumatura sopravvive al cambiamento: una lista di insiemi di espressioni separati da virgole — la forma multicolonna di IN — può contenere un numero qualsiasi di insiemi, ma ogni singolo insieme resta limitato a 1 000 espressioni. Verifica la versione che stai davvero eseguendo prima di dimensionare un lotto attorno all'uno o all'altro numero.

L'inquinamento della cache dei piani, calcolato

Un database mette in cache i piani compilati per non ottimizzare due volte la stessa istruzione, e la chiave di cache deriva dal testo dell'istruzione. Una query con tre segnaposto e una con quattro sono testi diversi, quindi chiavi diverse, quindi voci diverse — benché siano la stessa query con una lunghezza di lista diversa. È tutto il meccanismo, e l'aritmetica ne discende direttamente: se la tua applicazione manda mai lunghezze da 1 a N, generi N piani distinti invece di uno.

Calcolalo per un intervallo realistico. Un endpoint che recupera gli ordini sopra 500 € per un insieme di identificatori cliente può vedere da un identificatore a qualche centinaio. Servire ingenuamente lunghezze da 1 a 100 produce 100 piani distinti; da 1 a 1 000 ne produce 1 000; da 1 a 5 000 ne produce 5 000. Dai a un piano una dimensione plausibile — 50 KB è una cifra mediana ragionevole per una query con un paio di join — e l'impronta della cache è circa 5 MB a 100 lunghezze, 49 MB a 1 000 e 244 MB a 5 000. A 200 KB per piano, cifra che una query analitica complessa raggiunge senza sforzo, 1 000 lunghezze costano 195 MB e 5 000 ne costano 977. Quella memoria non è gratis: viene dallo stesso serbatoio della tua cache dei buffer, e sui motori dove la cache dei piani è limitata sfratta i piani del resto del tuo carico, quindi il sintomo è che query non collegate diventano più lente.

Anche il testo dell'istruzione cresce, e viaggia a ogni richiesta. Una query nella forma SELECT ... WHERE id IN con segnaposto numerati si aggira sui 525 byte a 100 valori, 5 926 byte a 1 000, 68 927 byte a 10 000 e 513 207 byte a 65 535. Mezzo megabyte di testo SQL per richiesta, analizzato da zero ogni volta perché nessun piano in cache gli corrisponderà mai più, è la forma del dirupo. Vale la pena essere precisi sui due costi: il lavoro di analisi e ottimizzazione si paga a ogni singola chiamata perché la cache manca sempre, e la memoria è consumata da voci che non vengono mai riusate.

I rimedi che scalano: array, join VALUES, tabella temporanea

Il rimedio migliore collassa l'intera famiglia di istruzioni in una. Passa la lista come singolo parametro di tipo array o tabella e il testo dell'istruzione smette di dipendere dalla lunghezza: un solo testo distinto, un solo piano in cache, una sola analisi, a ogni chiamata. PostgreSQL lo offre direttamente con un parametro array confrontato tramite ANY, ed è il motivo per cui le applicazioni PostgreSQL raramente raggiungono il tetto dei 65 535 — un parametro array conta come un parametro, qualunque numero di elementi contenga. SQL Server ha i parametri con valori di tabella, che servono allo stesso scopo e sono la risposta standard al suo limite di 2 100. Oracle ha tipi collezione interrogabili come una tabella.

La versione portabile della stessa idea è un join contro una lista VALUES, supportata da ogni motore diffuso e che l'ottimizzatore può trattare come una piccola relazione anziché una lunga disgiunzione. Continua a far variare il testo dell'istruzione con il numero di righe, quindi non risolve da sola la cache dei piani, ma molto spesso produce un piano migliore di un OR a mille rami perché l'ottimizzatore può unirla per hash. Una tabella temporanea è la versione che scala senza limiti: inserisci gli identificatori a lotti, unisci contro la tabella, e il testo dell'istruzione è costante qualunque sia il numero di identificatori. Costa un giro di andata e ritorno e un po' di scrittura, quindi si guadagna il posto sopra qualche migliaio di valori e non sotto.

Se per ora sei bloccato con una lista IN letterale, il riempimento è un cerotto economico che elimina la maggior parte del danno alla cache. Arrotonda la lista a uno scaglione e riempi le caselle avanzate con un valore che non possa corrispondere — ripetere il primo identificatore è il trucco corretto più semplice, dato che non cambia nulla nel risultato. Arrotondare alla potenza di due successiva trasforma 1 000 lunghezze possibili in 11 istruzioni distinte, ma spreca in media 174 parametri per chiamata su quell'intervallo, un sovraccosto del 34,8 %. Arrotondare al multiplo di dieci successivo dà 100 istruzioni distinte e spreca solo 4,5 parametri in media, lo 0,9 %. Quel secondo compromesso è di solito quello giusto: cento piani in cache non sono nulla, e un uno per cento di sovraccosto sul conteggio dei parametri è invisibile.

Suddividere sopra il tetto

Quando la lista supera davvero il tetto, spezzala. Il numero di giri di andata e ritorno esce direttamente dai limiti: 100 000 identificatori richiedono 2 chiamate su PostgreSQL, MySQL od Oracle 23ai, 48 chiamate su SQL Server a 2 100 parametri, e 100 chiamate su Oracle 19c a 1 000 espressioni. Quella forbice è il motivo per cui una dimensione di lotto che funziona benissimo nel container PostgreSQL di uno sviluppatore crolla sul SQL Server del cliente, e conviene derivare la dimensione del blocco dal database anziché fissare un numero nel codice.

Due dettagli contano quando spezzi. Spezza in modo deterministico — ordina gli identificatori prima di dividere — così un nuovo tentativo manda gli stessi blocchi e i tuoi log sono confrontabili fra esecuzioni. E decidi esplicitamente se la lettura debba essere coerente: più istruzioni separate vedono più istantanee separate, quindi se i dati possono cambiare sotto di te, avvolgi il lotto in una sola transazione o accetta che l'unione dei risultati non sia l'istantanea di nulla. Quel secondo punto produce il bug che nessuno riesce a riprodurre, perché si manifesta solo sotto scritture concorrenti.

La formattazione non è cosmetica

Dal fatto che la cache dei piani è indicizzata sul testo dell'istruzione discendono due cose. La prima: la formattazione conta dove la scrivi, non dove la mandi. Un'istruzione leggibile nel tuo file sorgente, con le clausole sulle loro righe e la lista distribuita su più righe, è la differenza fra una revisione che intercetta un errore e una che ci scivola sopra. La seconda: non riformattare il SQL in uscita. Se un involucro abbellisce, minifica o normalizza gli spazi a runtime, e lo fa in modo incoerente — poniamo, diversamente sotto carico o dopo un cambio di configurazione — produce nuove voci di cache per un'istruzione di cui il motore aveva già un piano. Formatta per gli umani, nel repository; manda una stringa stabile.

C'è un'altra ragione per tenere leggibile l'istruzione, ed è la ragione per cui questo articolo esiste. Una lunga lista IN resa come un'unica riga enorme nasconde tutto: se i valori sono legati o interpolati, se il conteggio è quello che ti aspettavi, se un valore estraneo si è infilato da un'altra query. Distribuita su più righe con i segnaposto visibili, tutte e tre sono ovvie a colpo d'occhio, e lo è anche il momento in cui la lista ha smesso di essere una manciata di identificatori ed è diventata silenziosamente un migliaio.

Domande frequenti

L'escape è mai un sostituto accettabile del binding?

No, e la ragione non è che le funzioni di escape siano scritte male: è che risolvono un problema diverso. Il binding sposta il confine fra codice e dati in modo che un valore non abbia alcuna strada verso la sintassi. L'escape lascia il confine dov'è e prova a neutralizzare ogni valore che potrebbe attraversarlo, il che lo obbliga a essere corretto in ogni contesto, sotto ogni codifica, in ogni punto di chiamata, per sempre. I contesti differiscono davvero: un letterale di stringa, un letterale numerico, un identificatore, un pattern di LIKE e un valore dentro una clausola assemblata dinamicamente richiedono ciascuno un trattamento diverso, e alcuni non hanno alcun escape sicuro. Un solo punto dimenticato ripristina l'intero buco, e i punti dimenticati sono il normale esito di una base di codice con più di un autore. C'è un caso in cui un valore davvero non può essere un parametro, perché il database non accetta lì un segnaposto: un nome di tabella, un nome di colonna, un verso di ordinamento, un LIMIT in alcuni motori. Anche lì la risposta non è l'escape. Convalida il valore contro un elenco fisso di identificatori che il tuo schema contiene davvero, e mappa l'input dell'utente su una costante invece di lasciarlo passare.

Cosa faccio quando la lista è più lunga di quanto il mio database consenta?

Tre opzioni, in ordine crescente di lavoro e di capacità di scalare. Primo, un parametro array o con valori di tabella, se il tuo motore ne ha uno: l'intera lista viaggia come singolo parametro, quindi il tetto smette di applicarsi e il testo dell'istruzione diventa costante. È la risposta giusta su PostgreSQL e su SQL Server, dove il limite dei 2 100 morde presto. Secondo, una tabella temporanea: inserisci gli identificatori a lotti, poi unisci contro la tabella. Il testo dell'istruzione è costante qualunque sia la dimensione della lista, l'ottimizzatore ottiene informazioni reali di cardinalità, e non c'è alcun tetto — paghi un giro di andata e ritorno e un po' di scrittura. Terzo, la suddivisione: spezza la lista, esegui la query una volta per blocco e fondi i risultati nella tua applicazione. Deriva la dimensione del blocco dal limite del database stesso anziché fissare un numero, perché la stessa lista richiede 2 giri su PostgreSQL e 48 su SQL Server. Ordina prima di dividere così i nuovi tentativi sono riproducibili, e metti l'intero lotto in una transazione se il risultato fuso deve rappresentare un unico momento coerente — altrimenti più istruzioni separate vedono più istantanee separate, che è l'origine del bug intermittente che nessuno riesce a riprodurre.

Perché la query rallenta al crescere della lista, anche ben sotto il limite?

Si sommano tre effetti, e conviene separarli perché hanno rimedi diversi. Il primo è la compilazione: il testo dell'istruzione cambia a ogni lunghezza di lista, quindi la cache dei piani manca sempre e l'ottimizzatore rianalizza e ripianifica da zero a ogni chiamata. A 1 000 valori il testo è già di circa 5 926 byte; a 10 000, 68 927. Il tempo di ottimizzazione cresce con il numero di predicati, quindi non è un sovraccosto costante. Il secondo è la strategia dell'ottimizzatore. Una lista IN è logicamente una catena di OR, e superata una certa lunghezza l'ottimizzatore smette di trattarla come un insieme di ricerche su indice e considera una scansione — MySQL lo documenta esplicitamente per l'ottimizzatore di intervalli, che traccia la memoria che un metodo di accesso a intervalli consumerebbe e lo abbandona se un limite configurato stesse per essere superato, ripiegando su una scansione completa della tabella. Attraversare quella soglia è un dirupo, non un pendio. Il terzo è la pressione sulla cache descritta sopra: i tuoi mille piani monouso sfrattano quelli usati da altre query, quindi il rallentamento compare in query che non hai toccato. Un parametro array o una tabella temporanea affronta tutti e tre in una volta, perché rende costante il testo dell'istruzione e dà all'ottimizzatore una relazione che può unire.

Usare un ORM significa che non devo pensare a niente di tutto questo?

Gestisce la metà di sicurezza e di solito non quella operativa. Ogni ORM diffuso lega i valori invece di interpolarli, quindi il rischio di iniezione sparisce davvero per i costruttori di query e le istruzioni generate — con l'eccezione di ogni via d'uscita verso SQL grezzo, dove torni a scrivere tu i parametri e dove vivono in massa le vulnerabilità delle basi di codice con ORM. La metà prestazionale è un'altra storia. La maggior parte degli ORM rende una clausola WHERE su una collezione come una lista IN letterale con un segnaposto per elemento, che è esattamente il pattern che cambia il testo dell'istruzione a ogni lunghezza e inonda la cache dei piani. Molti generano anche volentieri una lista più lunga di quanto il tuo database consenta e falliscono solo all'esecuzione, ed è per questo che il tetto dei 2 100 di SQL Server compare come incidente di produzione e non come fallimento di test. Le cose da verificare nel tuo stack sono concrete: se il tuo ORM sa emettere un parametro array o con valori di tabella invece di una lista IN, se suddivide automaticamente e con quale dimensione di blocco, e se espone il SQL generato in un log leggibile. Se non fa nulla di tutto ciò, una chiamata di caricamento anticipato su una collezione grande è un problema di cache dei piani in attesa del suo lunedì.

Riformattare il mio SQL cambia le sue prestazioni?

Il piano no, ma forse la ricerca in cache, e quella distinzione vale la pena tenerla. L'ottimizzatore lavora sull'albero di analisi, quindi spazi e interruzioni di riga non hanno alcun effetto sul piano scelto — un'istruzione impaginata su venti righe e la stessa minificata su una sola si eseguono in modo identico. Ciò che non condividono necessariamente è una voce di cache, perché la cache dei piani è indicizzata sul testo dell'istruzione e i motori differiscono su quanto lo normalizzano prima. La regola pratica è dunque semplice: formatta il SQL nel tuo repository, dove lo legge una persona, e manda ciò che il tuo driver produce senza un abbellitore o un minificatore nel percorso. Un involucro che riformatta in modo incoerente — diversamente sotto carico, o dopo che qualcuno ha spostato un flag di configurazione — può creare una seconda voce di cache per un'istruzione di cui il motore aveva già un piano: una regressione piccola, silenziosa e assai fastidiosa. Formattare nel repository ha un secondo vantaggio che conta di più: una lunga lista IN distribuita su più righe rende visibile a colpo d'occhio se i valori sono legati o interpolati, e se il conteggio è quello che ti aspettavi.