Prepared Statement in PHP e altri linguaggi: teoria ed esempi
I sistemi di gestione dei database (DBMS o Database Management System) operanti con il linguaggio di interrogazione SQL, godono di grande apprezzamento, ma da sempre sono anche soggetti a manipolazioni al momento dell’immissione dei dati. I record dati non sufficientemente protetti e contenenti meta caratteri come le virgolette o il punto e virgola, sono una preda facile per i malintenzionati. Una possibile risposta a questo problema è l’utilizzo di Prepared Statement, ossia delle dichiarazioni predefinite ai database, dotate di valori che ne stabiliscono la durata.
Ma cosa rende questa tecnologia così unica e in che situazione viene principalmente adottata? Utilizzando esempi in MySQL vi mostriamo come funzionano i Prepared Statement e di come possono essere impiegati nella gestione delle basi di dati.
Che cosa sono i Prepared Statement
I Prepared Statement (letteralmente “dichiarazioni preparate”) sono modelli già pronti all’uso per le interrogazioni nei sistemi di database in SQL, che non contengono valori per i singoli parametri. Diversamente dalle immissioni di dati manuali, in cui i valori vengono assegnati già al momento dell’esecuzione di un comando, i Prepared Statement utilizzano variabili o metacaratteri che vengono poi sostituiti con valori reali all’interno del sistema.
Tutti i più grandi sistemi di database basati su SQL come MySQL, MariaDB, Oracle, il server SQL di Microsoft o PostgreSQL supportano i Prepared Statement, per quanto la maggior parte di questi programmi utilizzino a questo scopo un protocollo binario NoSQL. Alcuni sistemi, tra i quali MySQL, impiegano soprattutto anche la comune sintassi SQL per l’implementazione.
Inoltre, alcuni linguaggi di programmazione come Java, Perl, Python e PHP supportano i Prepared Statement grazie alle loro librerie standard o estensioni. Nel caso in cui utilizziate quest’ultimo linguaggio di scripting per l’accesso al database, potete scegliere di adoperare l’interfaccia orientata agli oggetti PHP Data Objects (PDO) o l’estensione PHP MySQLi, così da poter adottare i Prepared Statement.
Perché l’utilizzo dei Prepared Statement in MySQL e negli altri linguaggi è così importante
Il motivo decisivo che porta a lavorare con i Prepared Statement in sistemi di gestione dei database come MySQL è la sicurezza. Il problema in assoluto più grande in merito agli accessi ai database in linguaggio SQL è infatti la facilità con la quale questi possono essere manipolati. In questi casi si parla di SQL Injection, ovvero quando vengono aggiunti o modificati codici in modo tale da riuscire a mettere mano su dati sensibili o a ottenere controllo sull’intero sistema di basi di dati.
I Prepared Statement in PHP, così come anche in altri linguaggi, sono privi di queste falle di sicurezza, poiché i valori concreti vengono assegnati solamente all’interno del sistema.
Una condizione necessaria per garantire gli elevati standard di sicurezza dei Prepared Statement è che nessuna delle componenti di queste dichiarazioni predefinite sia generata da fonti esterne.
La protezione dalle SQL Injection non è però l’unica ragione per l’utilizzo dei Prepared Statement. Infatti, una volta analizzati e compilati, questi possono essere riutilizzati nei propri sistemi di database tutte le volte che si vuole (cambiando di volta in volta i valori). Così facendo, i Prepared Statement consumano un quantitativo significativo di risorse in meno e sono più rapidi rispetto alle interrogazioni manuali dei database, qualora si tratti di attività in SQL che devono essere ripetute.
Il funzionamento dei Prepared Statement spiegato nel dettaglio
Se non si considerano la sintassi del linguaggio di scripting alla base e le caratteristiche del sistema di database in questione, l’integrazione e l’utilizzo dei Prepared Statement si svolge generalmente nelle fasi qui in seguito illustrate:
Fase 1: preparazione del Prepared Statement
Il primo passaggio consisteste nella creazione di un template. In PHP a questo scopo viene utilizzata la funzione prepare(). Al posto di digitare valori concreti per i parametri rilevanti vengono inseriti i metacaratteri precedentemente menzionati, definiti anche variabili Bind. Negli esempi successivi queste sono contraddistinte da un “?”:
INSERT INTO Prodotto (Nome, Prezzo) VALUES (?, ?);I Prepared Statement completi saranno poi inoltrati al sistema di gestione dei database relativo.
Fase 2: elaborazione del modello di dichiarazione attraverso il DBMS
Il modello di dichiarazione viene dunque parsificato, ovvero analizzato, così da poter essere poi compilato, trasformandolo in pratica una dichiarazione eseguibile. Questo procedimento serve a ottimizzare il Prepared Statement.
Fase 3: esecuzione del Prepared Statement
In un secondo momento il modello elaborato può essere eseguito nel sistema di database tutte le volte che lo si desidera. L’unico requisito è l’input adeguato attraverso l’applicazione associata, ossia una fonte dati che fornisca i valori corrispondenti per il campo del metacarattere. Facendo riferimento al codice di esempio sopra riportato (Fase 1), potrebbe trattarsi di un valore quale “Libro” (Nome) e “10” (Prezzo), o anche “Computer” e “1000”.
Tutorial: come utilizzare i Prepared Statement in MySQL con MySQLi
Dopo aver descritto il funzionamento dei Prepared Statement in generale, il qui presente tutorial con tanto di esempi concreti dovrebbe servire a chiarirvi l’utilizzo di queste pratiche direttiva. Gli esempi fanno riferimento a:
- MySQL come sistema di gestione dei database
- PHP come linguaggio per i Prepared Statement
Le versioni attuali di MySQL supportano l’impiego lato server dei Prepared Statement basandosi su di un protocollo binario contenente i comandi SQL che aggiornano i dati e che inoltre protocollano tutti gli aggiornamenti dall’ultima messa in sicurezza dei dati. Da interfaccia d’accesso utilizziamo l’estensione PHP MySQLi per questo tutorial, che allo stesso modo supporta i Prepared Statement grazie al protocollo binario.
Una buona alternativa a MySQLi come API (Application Programming Interface) per i Prepared Statement è l’interfaccia orientata agli oggetti PDO (PHP Data Objects). Questa variante vale generalmente come soluzione adatta ai principianti.
PREPARE, EXECUTE e DEALLOCATE PREPARE: i tre comandi elementari di SQL per l’utilizzo dei Prepared Statement
Sono tre i comandi SQL a giocare un ruolo decisivo nell’utilizzo dei Prepared Statement nei database MySQL:
Il comando „PREPARE“ è necessario durante la fase di preparazione di un Prepared Statement e per assegnare a questo un nome univoco tramite il quale ritrovare lo statement anche in un secondo momento.
PREPARE stmt_name FROM preparable_stmtPer l’esecuzione di una direttiva SQL predefinita si ha invece bisogno del comando „EXECUTE“. Per ricorrere al relativo Prepared Statement dovete utilizzare il nome assegnatogli, generato con il comando “PREPARE”. La frequenza con la quale eseguite un Prepared Statement dipende da voi: potete definire diverse variabili o conferire illimitatamente nuovi valori alle variabili utilizzate.
EXECITE stmt_name
[USING @var_name [, @var_name] …]Per riattivare un Prepared Statement in PHP c’è il comando „DEALLOCATE PREPARE“. Poiché, altrimenti, gli Statement vengono terminati automaticamente allo scadere di una sessione. La riattivazione è perciò rilevante, in quanto sennò viene rapidamente raggiunto il limite massimo preimpostato dalla variabile di sistema max_prepared_stmt_count, che impedirebbe altrimenti di creare nuovi Prepared Statement.
{DEALLOCATE | DROP} PREPARE stmt_nameUtilizzare le dichiarazioni SQL come Prepared Statement MySQL
Praticamente tutti gli statement SQL supportati da MySQL possono essere preparati ed eseguiti come Prepared Statement. L'unica eccezione è data delle direttive di diagnosi, escluse dai Prepared Statement per il mantenimento degli standard SQL. Precisamente si tratta delle seguenti:
- SHO WARNINGS
- SHOW COUNT(*) WARNINGS
- SHOW ERRORS
- SHOW COUNT(*) ERRORS
Inoltre, non potete generale alcun modello di interrogazione SQL che faccia riferimento alle variabili di sistema warning_count ed error_count.
Possono altresì essere utilizzati i seguenti statement:
ALTER TABLE
ALTER USER
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
| LOGS | STATUS | MASTER | SLAVE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE}
REVOKE
SELECT
SET
SHOW {WARNINGS | ERRORS}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATEPeculiarità sintattiche della sintassi SQL dei Prepared Statement in PHP
In confronto alla sintassi SQL standard, quella relativa ai Prepared Statement presenta un paio di particolarità, alle quali bisogna però prestare attenzione. Prima di tutto si segnala l’utilizzo di metacaratteri per i valori parametro, che rendono le direttive predefinite così interessanti per l’accesso ai sistemi di gestione dei database. A partire da MySQL 8.0 è possibile utilizzare questi metacaratteri per i parametri “OUT” e “INOUT”, ad esempio, negli statement “PREPARE” ed “EXECUTE”, mentre per il parametro “IN” questi sono indipendenti dalla versione del sistema di database. Ulteriori caratteristiche specifiche della sintassi dei Prepared Statement sono le seguenti:
- La sintassi SQL per i Prepared Statement in PHP non può essere doppiata. Quello che si intende qui è che una direttiva alla quale sia stata assegnata la funzione di “PREPARE”, non può contemporaneamente essere un “PREPARE”, “EXECUTE” o “DEALLOCATE PREPARE” statement.
- I Prepared Statement possono essere utilizzati anche nelle procedure archiviate (funzione di richiamo di esecuzioni complete delle dichiarazioni).
- Non è possibile eseguire più dichiarazioni o direttive (Multi Statement) all’interno di uno stesso statement, ossia di una stringa di caratteri, separandolo per mezzo di un punto e virgola.
Prepared Statement in MySQL: esempio
In questo capitolo vi mostriamo che aspetto ha esattamente l’immissione di un Prepared Statement in PHP in MySQLi:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Connessione
$conn = new mysqli($servername, $username, $password, $dbname);
// Verifica della connessione
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Preparazione del Prepared Statements
$stmt = $conn->prepare("INSERT INTO Miocliente (Nome, Cognome, Email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $nome, $cognome, $email);
// Impostare i parametri e l’esecuzione
$Vorname = "Virgilio";
$Nachname = "Rossi";
$Mail = "m.rossi@esempio.it";
$stmt->execute();
$Vorname = "Rita";
$Nachname = "Bianchi";
$Mail = "r.bianchi@esempio.it";
$stmt->execute();
$Vorname = "Niccolò";
$Nachname = "Verdi";
$Mail = "n.verdi@esempio.it";
$stmt->execute();
echo "Nuovi records creati con successo";
$stmt->close();
$conn->close();
?>Questo codice di scripting in PHP stabilisce per prima cosa la connessione al database in MySQL con ($conn), all’interno del quale voi dovete indicare i relativi dati del server, quali il nome host, il nome utente, la password e il nome del database.
Con la riga “INSERT INTO Miocliente (Nome, Cognome, Email) VALUES (?, ?, ?)” inizia la parte relativa al Prepared Statement: il database cliente “Miocliente" riceve l’input (INSERT INTO), rispettivamente per le colonne “Nome”, “Cognome” e “Email”. Per i valori (VALUES) vengono utilizzati inizialmente dei metacaratteri, contrassegnati dal simbolo di domanda (?).
Infine, i parametri devono essere collegati tra loro (bind_param). Per fare ciò il database necessita informazioni riguardo al tipo di dati. L'argomento “sss” mostra ad esempio che, per tutti e tre i parametri, si tratta di una stringa di caratteri. Le alternative sarebbero le seguenti:
- i: INTEGER (valore intero)
- d: DOUBLE (valori numerici approssimati)
- b: BLOB (oggetto binario di grandi dimensioni, dall’inglese “Binary Large OBject”)
Per visualizzare questo video, sono necessari i cookie di terze parti. Puoi accedere e modificare le impostazioni dei cookie qui.