SQL: interrogazioni, raggruppamenti, valori nulli e iniezione

SQL è il linguaggio con cui si parla a tutte le basi di dati relazionali, da quella di un telefono a quelle delle banche. È nato all’IBM negli anni Settanta ed è uno standard internazionale. In questa lezione vediamo le interrogazioni con selezione, giunzione e ordinamento, i raggruppamenti, le sottointerrogazioni, il valore nullo e la logica a tre valori con la dimostrazione di un errore classico, le viste, e come difendersi dall’iniezione SQL. Gli esempi usano lo schema Studenti, Corsi ed Esami della lezione sulla normalizzazione.

Definire e modificare i dati

Le tabelle si creano dichiarando colonne, tipi e vincoli:

CREATE TABLE Esami (
  matricola INT REFERENCES Studenti(matricola),
  codice    CHAR(6) REFERENCES Corsi(codice),
  voto      INT CHECK (voto BETWEEN 18 AND 30),
  data      DATE NOT NULL,
  PRIMARY KEY (matricola, codice)
);

I dati si modificano con INSERT, UPDATE e DELETE, e il DBMS rifiuta le operazioni che violano i vincoli (lezione sul modello relazionale).

Le interrogazioni

La forma di base è SELECT (che cosa mostrare) FROM (da quali tabelle) WHERE (con quali condizioni), che corrisponde a proiezione, giunzione e selezione dell’algebra relazionale:

SELECT s.cognome, c.titolo, e.voto
FROM Esami e
JOIN Studenti s ON s.matricola = e.matricola
JOIN Corsi c    ON c.codice = e.codice
WHERE e.voto >= 28
ORDER BY e.voto DESC, s.cognome;

La giunzione interna (JOIN) tiene solo le righe che trovano un corrispondente. La giunzione esterna (LEFT JOIN) tiene tutte le righe della tabella di sinistra, riempiendo con valori nulli le colonne dell’altra quando non c’è corrispondenza: serve, per esempio, a elencare anche gli studenti senza esami.

I raggruppamenti

Le funzioni di aggregazione (COUNT, SUM, AVG, MIN, MAX) riassumono gruppi di righe. Con GROUP BY si formano i gruppi, con HAVING si filtrano dopo l’aggregazione:

SELECT s.matricola, s.cognome, AVG(e.voto) AS media, COUNT(*) AS esami
FROM Studenti s JOIN Esami e ON e.matricola = s.matricola
GROUP BY s.matricola, s.cognome
HAVING COUNT(*) >= 5
ORDER BY media DESC;

WHERE filtra le righe prima di raggrupparle, HAVING i gruppi dopo: la condizione sulla media va in HAVING, perché la media esiste solo dopo il raggruppamento.

Le sottointerrogazioni

Una query può contenerne un’altra: “gli studenti che hanno un voto superiore alla media di tutti i voti”.

SELECT DISTINCT s.cognome
FROM Studenti s JOIN Esami e ON e.matricola = s.matricola
WHERE e.voto > (SELECT AVG(voto) FROM Esami);

Con IN, EXISTS e NOT EXISTS si esprimono appartenenza ed esistenza, per esempio gli studenti che non hanno mai sostenuto un certo esame.

Il valore nullo e la logica a tre valori

NULL rappresenta un valore sconosciuto o inesistente: un voto non ancora registrato, un secondo nome che non c’è. Un confronto con un valore nullo non è né vero né falso, ma sconosciuto (U): SQL usa una logica a tre valori.

La clausola WHERE tiene solo le righe per cui la condizione è vera: quelle con risultato falso o sconosciuto vengono scartate.

Teorema. Se la sottointerrogazione contiene almeno un valore nullo, la condizione x NOT IN (sottointerrogazione) non è mai vera, e la query non restituisce alcuna riga.

Dimostrazione. x NOT IN (v1, ..., vn) equivale a x <> v1 AND ... AND x <> vn. Se uno dei valori è nullo, il corrispondente confronto x <> NULL vale U. Un AND che contiene un termine U può valere F (se un altro termine è falso, cioè è nella lista) o U (se tutti gli altri sono veri), ma mai T, come mostra la tavola. Quindi nessuna riga soddisfa la condizione. ∎

È uno degli errori più insidiosi di SQL: una query che “non trova niente” senza dare errori. Si evita usando NOT EXISTS, che non ha questo problema, o escludendo esplicitamente i nulli. Per lo stesso motivo il test di nullità si scrive IS NULL, non = NULL, che vale sempre U. Anche le funzioni di aggregazione ignorano i nulli: COUNT(*) conta le righe, COUNT(voto) solo quelle con voto non nullo, e AVG calcola la media dei soli valori presenti.

Le viste

Una vista è una query a cui si dà un nome e che si usa come una tabella:

CREATE VIEW Medie AS
SELECT matricola, AVG(voto) AS media FROM Esami GROUP BY matricola;

Le viste semplificano le query ripetute, nascondono la complessità dello schema e servono a dare permessi limitati: un utente può avere accesso alla vista delle medie senza vedere i singoli voti.

L’iniezione SQL

Costruire una query incollando testo scritto dall’utente è l’errore di sicurezza più classico del web. Se un programma esegue "SELECT * FROM Utenti WHERE nome = '" + nome + "'" e qualcuno scrive come nome ' OR '1'='1, la condizione diventa sempre vera e restituisce tutti gli utenti; con varianti si possono leggere o cancellare intere tabelle. La difesa è usare sempre le query preparate con parametri:

SELECT * FROM Utenti WHERE nome = ?

il valore viene passato separatamente e il DBMS lo tratta come un dato, mai come codice. A questa si aggiungono il principio del minimo privilegio (l’applicazione si collega con un utente che può fare solo ciò che le serve) e il controllo dei dati in ingresso.