Query preparate e SQL injection

Nella lezione precedente abbiamo eseguito solo query scritte interamente da noi. Ma un'applicazione vera mette nelle query i dati dell'utente: il testo da cercare, l'id del libro da mostrare, lo username del login. Se lo si fa nel modo sbagliato, si apre la porta all'attacco più famoso (e ancora oggi tra i più diffusi) del web: la SQL injection.

L'attacco

Ecco una ricerca scritta nel modo sbagliato: il testo del form viene incollato dentro la stringa SQL.

<?php
// ❌ NON FARE MAI COSÌ
require_once 'db.php';
$q = $_GET['q'] ?? '';
$sql = "SELECT titolo, anno FROM libri WHERE titolo LIKE '%$q%'";
$libri = mysqli_fetch_all(mysqli_query($conn, $sql), MYSQLI_ASSOC);

Da dove arriva $q? Dal form di ricerca della lezione 9. Il campo si chiama q (da query, "domanda": è lo stesso nome che usa Google, google.com/search?q=…):

<form action="cerca.php" method="get">
  <input type="search" name="q">
  <button>Cerca</button>
</form>

Chi scrive robot e preme Cerca apre l'indirizzo cerca.php?q=robot. PHP mette i parametri dell'indirizzo nell'array $_GET, con il name del campo come chiave: $_GET['q'] vale 'robot'. La prima riga dello script lo copia in una variabile normale, $q:

• ?? '' evita l'errore quando la pagina si apre senza ricerca (cerca.php e basta: la chiave q non c'è);
• con un nome corto il resto del codice si legge meglio, e il valore si "pulisce" una volta sola (nella versione corretta, più sotto, con trim);
• la stessa variabile serve più volte: nella query e per rimettere il testo cercato nel campo (value="<?= h($q) ?>").

Il punto importante per questa lezione: $q contiene quello che ha scritto l'utente, qualunque cosa sia.

Con una ricerca normale, robot, la query diventa:

SELECT titolo, anno FROM libri WHERE titolo LIKE '%robot%'

e trova Io, robot. Ma l'utente può scrivere quello che vuole. Se cerca ' OR 1=1 -- (con uno spazio finale), la query diventa:

SELECT titolo, anno FROM libri WHERE titolo LIKE '%' OR 1=1 -- %'

L'apice dell'utente ha chiuso la stringa: ora OR 1=1 è un pezzo di SQL, sempre vero, e -- trasforma in commento il resto. La query restituisce tutti i libri. Fin qui poco male. Ma se cerca:

x' UNION SELECT username, password_hash FROM utenti -- 

la query diventa una UNION che aggiunge ai risultati le righe di un'altra tabella: nella pagina, al posto dei titoli, compaiono i nomi degli utenti e le impronte delle loro password. Con la stessa tecnica si possono leggere tutte le tabelle del database e, a seconda della configurazione, modificarle o cancellarle. Su un form di login, lo stesso trucco permette di entrare senza password.

Prova l'attacco sul tuo XAMPP, sulla tua copia del database: è il modo migliore per capirlo. Provarlo su siti di altri è un reato (accesso abusivo a un sistema informatico, art. 615-ter del Codice penale), anche "solo per vedere se funziona".

Il problema non è l'apice: è che dati e comandi viaggiano mescolati nella stessa stringa, e il database non può sapere quale parte ha scritto il programmatore e quale l'utente.

La soluzione: le query preparate

Con una query preparata (prepared statement) la query e i dati viaggiano separati:

1. prepare: si manda a MySQL la query con dei segnaposto ? al posto dei valori. MySQL la analizza e la "congela": da questo momento la sua struttura non può più cambiare;
2. bind: si legano ai segnaposto le variabili PHP, dichiarando di che tipo sono;
3. execute: si mandano i valori. MySQL li tratta sempre e solo come dati, qualunque cosa contengano.

<?php
// ✅ COSÌ
require_once 'db.php';
$q = trim($_GET['q'] ?? '');
$cerca = "%$q%";

$stmt = mysqli_prepare($conn, 'SELECT titolo, anno FROM libri WHERE titolo LIKE ? ORDER BY titolo');
mysqli_stmt_bind_param($stmt, 's', $cerca);
mysqli_stmt_execute($stmt);
$risultato = mysqli_stmt_get_result($stmt);
$libri = mysqli_fetch_all($risultato, MYSQLI_ASSOC);

Ora se l'utente cerca ' OR 1=1 -- , MySQL cerca un titolo che contiene letteralmente quei caratteri, apice compreso. Non lo trova, e la ricerca restituisce zero righe. L'attacco non funziona più, qualunque cosa venga scritta.

Nota anche che non serve più raddoppiare gli apici: un titolo come L'isola di Arturo si cerca senza problemi.

Le quattro righe si ripetono sempre uguali:

I tipi di bind_param. La stringa 's' dice che il primo (e unico) valore è una stringa. Serve una lettera per ogni ?, nell'ordine:

mysqli_stmt_bind_param vuole variabili, non valori scritti direttamente: mysqli_stmt_bind_param($stmt, 's', "%$q%") dà errore. Per questo prima si prepara $cerca = "%$q%" e poi si lega $cerca.

La regola del corso. Ogni volta che in una query entra un valore che arriva da fuori ($_GET, $_POST, $_COOKIE, un file caricato, persino il database stesso), si usa una query preparata. Sempre, anche quando "tanto è un numero". mysqli_query() si usa solo per le query scritte tutte da noi.

<?php
$cerca = "%$q%";
$stmt = $conn->prepare('SELECT titolo, anno FROM libri WHERE titolo LIKE ? ORDER BY titolo');
$stmt->bind_param('s', $cerca);
$stmt->execute();
$libri = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

<?php
$stmt = $pdo->prepare('SELECT titolo, anno FROM libri WHERE titolo LIKE ? ORDER BY titolo');
$stmt->execute(["%$q%"]);
$libri = $stmt->fetchAll(PDO::FETCH_ASSOC);

<?php
$stmt = $pdo->prepare('INSERT INTO studenti (nome, cognome, classe, email)
                       VALUES (:nome, :cognome, :classe, :email)');
$stmt->execute([':nome' => 'Davide', ':cognome' => 'Conti', ':classe' => '5B', ':email' => 'davide.conti@scuola.it']);
echo 'Nuovo studente con id ' . $pdo->lastInsertId();

Più valori

I punti interrogativi si riempiono nell'ordine delle variabili, e i tipi seguono lo stesso ordine:

<?php
$genere = 'Fantascienza';
$anno = 1900;

$stmt = mysqli_prepare($conn, 'SELECT titolo FROM libri WHERE genere = ? AND anno > ? ORDER BY anno');
mysqli_stmt_bind_param($stmt, 'si', $genere, $anno);     // s = stringa, i = intero
mysqli_stmt_execute($stmt);
$libri = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);   // 1984, Io, robot, Fondazione

INSERT, UPDATE, DELETE

Per le istruzioni che modificano i dati non c'è un risultato da leggere: bastano i primi tre passi.

<?php
$nome = 'Davide';
$cognome = 'Conti';
$classe = '5B';
$email = 'davide.conti@scuola.it';

$stmt = mysqli_prepare($conn, 'INSERT INTO studenti (nome, cognome, classe, email) VALUES (?, ?, ?, ?)');
mysqli_stmt_bind_param($stmt, 'ssss', $nome, $cognome, $classe, $email);
mysqli_stmt_execute($stmt);

echo 'Nuovo studente con id ' . mysqli_insert_id($conn);
echo ' (righe inserite: ' . mysqli_stmt_affected_rows($stmt) . ')';

• mysqli_insert_id($conn) restituisce l'id AUTO_INCREMENT appena assegnato: serve, per esempio, per andare alla scheda del record appena creato.
• mysqli_stmt_affected_rows($stmt) dice quante righe sono state inserite, modificate o cancellate: con un UPDATE … WHERE id = ?, 0 significa che quell'id non esiste (o che i valori erano già uguali).

Una riga sola: il dettaglio

Il caso più comune in assoluto: la pagina libro.php?id=7 mostra un libro.

<?php
require_once 'db.php';

$id = (int) ($_GET['id'] ?? 0);
$stmt = mysqli_prepare($conn, 'SELECT l.*, a.nome, a.cognome
                               FROM libri l JOIN autori a ON a.id = l.autore_id
                               WHERE l.id = ?');
mysqli_stmt_bind_param($stmt, 'i', $id);
mysqli_stmt_execute($stmt);
$libro = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));

if (!$libro) {
    http_response_code(404);
    exit('Libro non trovato.');
}
echo htmlspecialchars($libro['titolo'] . ' di ' . $libro['nome'] . ' ' . $libro['cognome']);

• (int) trasforma il parametro in intero ('7abc' diventa 7, 'ciao' diventa 0): non è la difesa contro l'injection (quella è la query preparata), ma evita valori senza senso.
• mysqli_fetch_assoc restituisce null se il libro non esiste: in quel caso si risponde con il codice HTTP 404 e un messaggio, invece di una pagina piena di errori.

Una scorciatoia (PHP 8.2 e successivi). mysqli_execute_query($conn, $sql, [$valore1, $valore2]) fa prepare, bind ed execute in una riga sola, senza le lettere dei tipi: $libro = mysqli_fetch_assoc(mysqli_execute_query($conn, 'SELECT * FROM libri WHERE id = ?', [$id]));. È comoda, ma nei manuali e nelle tracce trovi quasi sempre la forma lunga: impara prima quella.

Casi particolari

LIKE. I caratteri jolly % si mettono nel valore, non nella query: LIKE ? con $cerca = "%$q%". Scrivere LIKE '%?%' non funziona: il punto interrogativo tra apici è un normale carattere.

LIMIT e OFFSET. Si legano come interi, con la lettera i:

<?php
$quanti = 5;
$salta = 10;
$stmt = mysqli_prepare($conn, 'SELECT titolo FROM libri ORDER BY titolo LIMIT ? OFFSET ?');
mysqli_stmt_bind_param($stmt, 'ii', $quanti, $salta);
mysqli_stmt_execute($stmt);

NULL. Per salvare NULL (per esempio un anno non indicato) si lega una variabile che vale null: $anno = $_POST['anno'] === '' ? null : (int) $_POST['anno']; e poi bind_param('…i…', …, $anno, …). MySQL riceve NULL, qualunque sia la lettera del tipo.

Nomi di colonne e tabelle. I segnaposto sostituiscono solo valori, non pezzi di SQL. ORDER BY ? non ordina per la colonna scelta dall'utente (ordina per una costante, cioè non ordina). Quando l'utente sceglie l'ordinamento, si usa una lista bianca: si accettano solo i valori previsti e si mette nella query il testo scritto da noi.

<?php
$ordinamenti = [
    'titolo' => 'l.titolo',
    'anno' => 'l.anno DESC',
    'autore' => 'a.cognome, l.titolo',
];
$scelta = $_GET['ordine'] ?? 'titolo';
$order_by = $ordinamenti[$scelta] ?? $ordinamenti['titolo'];   // valore sconosciuto → predefinito

$risultato = mysqli_query($conn, "SELECT l.titolo, l.anno, a.cognome
                                  FROM libri l JOIN autori a ON a.id = l.autore_id
                                  ORDER BY $order_by");
$libri = mysqli_fetch_all($risultato, MYSQLI_ASSOC);

Qui mysqli_query() è corretto: nella stringa entra solo testo scelto da noi, mai quello dell'utente.

Riepilogo: tre modi, la stessa idea

Su vecchi codici troverai anche mysqli_real_escape_string($conn, $q), che "protegge" gli apici prima di incollare il valore nella query. Funziona solo se usata ovunque e nel modo giusto (e non protegge i numeri non tra apici): basta dimenticarla una volta. Le query preparate sono la soluzione corretta.

Esercizi

<?php
// cerca.php
require_once 'db.php';
$q = trim($_GET['q'] ?? '');
$libri = [];
if ($q !== '') {
    $cerca = "%$q%";
    $stmt = mysqli_prepare($conn, 'SELECT titolo, anno FROM libri WHERE titolo LIKE ? ORDER BY titolo');
    mysqli_stmt_bind_param($stmt, 's', $cerca);
    mysqli_stmt_execute($stmt);
    $libri = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
}
?>
<form method="get">
  <input type="search" name="q" value="<?= htmlspecialchars($q) ?>">
  <button>Cerca</button>
</form>
<?php foreach ($libri as $l): ?>
  <p><?= htmlspecialchars($l['titolo']) ?> (<?= $l['anno'] ?>)</p>
<?php endforeach; ?>

<?php
require_once 'db.php';

$id = (int) ($_GET['id'] ?? 0);

$stmt = mysqli_prepare($conn, 'SELECT nome, cognome FROM autori WHERE id = ?');
mysqli_stmt_bind_param($stmt, 'i', $id);
mysqli_stmt_execute($stmt);
$autore = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));
if (!$autore) {
    http_response_code(404);
    exit('Autore non trovato.');
}

$stmt = mysqli_prepare($conn, 'SELECT titolo, anno FROM libri WHERE autore_id = ? ORDER BY anno');
mysqli_stmt_bind_param($stmt, 'i', $id);
mysqli_stmt_execute($stmt);
$libri = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
?>
<h1><?= htmlspecialchars($autore['nome'] . ' ' . $autore['cognome']) ?></h1>
<ul>
  <?php foreach ($libri as $l): ?>
  <li><?= htmlspecialchars($l['titolo']) ?> (<?= $l['anno'] ?>)</li>
  <?php endforeach; ?>
</ul>

<td><a href="autore.php?id=<?= $a['id'] ?>"><?= htmlspecialchars($a['cognome']) ?></a></td>

<?php
$u = $_POST['username'];
$p = $_POST['password'];
$riga = mysqli_fetch_assoc(mysqli_query($conn, "SELECT * FROM utenti WHERE username = '$u' AND password = '$p'"));
if ($riga) { echo 'Benvenuto!'; }

SELECT * FROM utenti WHERE username = 'admin' -- ' AND password = '...'

<?php
$u = $_POST['username'] ?? '';
$p = $_POST['password'] ?? '';
$stmt = mysqli_prepare($conn, 'SELECT * FROM utenti WHERE username = ? AND password = ?');
mysqli_stmt_bind_param($stmt, 'ss', $u, $p);
mysqli_stmt_execute($stmt);
$riga = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));

<?php
$cerca = "%$q%";
$stmt = mysqli_prepare($conn, "SELECT l.titolo, l.anno, a.cognome
                               FROM libri l JOIN autori a ON a.id = l.autore_id
                               WHERE l.titolo LIKE ?
                               ORDER BY $order_by");
mysqli_stmt_bind_param($stmt, 's', $cerca);
mysqli_stmt_execute($stmt);