Prepared Statements

9 Feb

Out Of Date Warning

Questo post è stato pubblicato più di 2 anni fa (il 9 febbraio 2012). Le idee vanno avanti velocemente, le prospettive cambiano quindi i contenuti potrebbero non essere aggiornati. Ti prego di tenere in considerazione questo, e di verificare le informazioni tecniche presenti nell'articolo prima di farne affidamento per i tuoi scopi.

Ormai i moderni database supportano tutti il concetto di prepared statement. Ma di cosa si tratta? Possono pensarli come ad una sorta di modello compilato per l’SQL, che può essere personalizzato utilizzando parametri variabili.

I prepared statements sono stati aggiunti nella versione 4.1 di MySQL, e PHP li può utilizzare a partire dalla versione 5 (grazie ad Improved MySQL extension). Quando non si utilizza le istruzioni preparate, l’intera query, tra cui la sintassi SQL ed i valori specifici, sono inviati a MySQL come una lunga stringa. MySQL quindi la analizza e la esegue.
Con una query preparata, la sintassi SQL viene prima inviata a MySQL, dove viene analizzata, assicurandosi che sia sintatticamente valida. Quindi i valori specifici vengono inviati separatamente; MySQL assembla la query utilizzando tali valori, e la esegue. I vantaggi delle istruzioni preparate sono fondamentalmente due: una maggiore sicurezza e prestazioni potenzialmente migliori.

Teoria

Con i prepared statements le queries hanno bisogno di essere analizzate (o preparate) una sola volta anche se vengono eseguite più volte con parametri uguali o diversi. In una situazione normale quando una query viene preparata, il database dovrà eseguire delle operazioni di analisi, compilazione ed ottimizzazione prima dell’esecuzione. Per queries complesse questo processo può richiedere diverso tempo in più, se vi è la necessità di ripetere la stessa operazione molte volte con parametri diversi.
Utilizzando una dichiarazione preparata l’applicazione evita di ripetere queste operazioni. Ciò significa che con i prepared statements utilizzeremo meno risorse e saremo più veloci.
Inoltre i parametri non hanno bisogno di essere citati, il driver gestisce automaticamente questo. Se un’applicazione utilizza esclusivamente le istruzioni preparate, lo sviluppatore può essere sicuro che non vi sarà l’SQL injection (sempre che non vi siano anche le altre parti della query costruite attraverso parametri di ingresso in questo caso l’SQL injection sarà ancora possibile).

Con i prepared statements il processo si divide in due fasi, preparazione ed esecuzione.

  1. Preparazione: Nella prima fase viene inviato un template dello statement al database server, il quale procede a controllarne la sintassi ed ad inizializzare delle risorse interne per un utilizzo successivo.
  2. Esecuzione: in questa fase il client lega dei valori ai parametri e li invia al database server. Il server crea uno statement dal template e dai valori e lo esegue utilizzando le risorse interne precedentemente create.

Un prepared statements può essere eseguito ripetutamente. Su ogni esecuzione il valore corrente della variabile viene valutato ed inviato al server.
I prepared statements occupano risorse sul server, quindi è buon uso chiuderli quando non servono più.

Pratica

Recupero dei dati

Prendiamo una semplice query SQL:

Con prepared statement diviene:

Il parametro ‘i’ del metodo bind_param() indica il tipo di valore che dovrebbe essere atteso. In questo caso, la query si aspetta di ricevere un numero intero. Altro esempio per rendere il concetto:

Grazie a questo esempio possiamo notare un’altra differenza rispetto ai non-prepared statements, email e password sono passati senza virgolette, pur essendo valori stringa. Una volta che lo statement è stato “legato”, è possibile assegnare i valori alle variabili PHP (se non è già stato fatto) e poi eseguire l’istruzione.

Lettera Tipo di dato
i Integer
d Decimal
b Blob (dato bianario)
s Qualsiasi altro tipo

Inserimento dei dati

Ma come abbiamo detto, è eseguendo lo stesso statement più volte con parametri diversi che troviamo il vantaggio in termini prestazionali, consideriamo questo esempio:

Anche se la query viene eseguita 100 volte, il testo integrale viene trasferito, ed analizzato, da MySQL una volta sola.

Nota: se un prepared statement viene eseguito una sola volta potrebbe risultare meno performante di una query normale visto che genera delle andate-ritorno addizionali con il server (round-trip). Inoltre, generalmente, l’utilizzo di multi-INSERT SQL richiede meno tempo di un prepared statement.

Recupero dei risultati

Possiamo recuperare i risultati di un prepared statement in due modi, legando delle variabili di output o richiedendo un oggetto mysqli_result.

Variabili legate

Questa modalità consiste nel legare delle variabili di output (una per ogni colonna del set) dopo l’esecuzione dello statement:

Nota: dobbiamo legare tutte le colonne della SELECT con variabili di output altrimenti sarà generato uno warning: Number of bind variables doesn’t match number of fields in prepared statement.

Interfaccia mysqli_result

Utilizzando questa modalità (più comoda e flessibile per la navigazione dei risultati) sarà restituito un set di risultati bufferizzato:

Protocollo di trasferimento dati

I prepared statements utilizzano un protocollo diverso (protocollo binario) per il trasferimento dei dati, rispetto ai non-prepared statements. I risultati di una query saranno inviati dal server in formato binario e non saranno precedentemente serializzati in stringhe. I clients cercheranno poi i convertire i dati nel formato appropriato, per esempio i risultati di una colonna INT saranno restituiti come variabili PHP intere:

Il fatto che i non-prepared statemens restituiscono sempre stringhe può tuttavia essere modificato attraverso le opzioni di connessione: $mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);.

Riepilogo

Riassumendo un prepared statement è un template SQL che può essere riutilizzato con valori diversi. Pro e contro:

  • Pro:

    1. Consente di eseguire la solita istruzione senza rieseguire l’analisi dell’SQL.
    2. Consente di inviare dati binari.
    3. Previene dall’SQL injection, alleviandoci dalla necessità di eseguire l’escape dei dati.
    4. Il codice, può risultare più semplice da leggere visto che viene separata la logica SQL dai dati.
  • Contro:

    1. Può essere più lento quando dobbiamo effettuare un’unica query visto che richiede delle richieste addizionali con il server.
    2. E’ limitato alle seguenti queries: SELECT, INSERT, REPLACE, UPDATE, DELETE, e CREATE TABLE.

Conclusioni

Questa è solo una panoramica di base dei prepared statements, tuttavia chi è abituato a lavorare con MSQLI non dovrebbe avere grossi problemi ad utilizzarli nei propri scripts.

Dimenticavo… naturalmente oltre alla modalità ad oggetti, è contemplato anche il paradigma procedurale. 😉

Risorse

www.php.net/manual/en/mysqli.quickstart.prepared-statements.php
http://php.net/manual/en/book.mysqli.php

Un Commento su “Prepared Statements”

  1. Jimmy 2 febbraio 2013 at 02:08 #

    Ciao, questa guida è fatta molto bene, mi hai aiutato a comprendere il funzionamento delle istruzioni preparte di Mysql. grazie e bel blog 😀

Lascia un commento