Prepared Statements

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:

<?php
$mysqli = new mysqli("localhost", "user", "password", "database");
$res = $mysqli->query("SELECT `firstname`, `lastname` FROM `users` WHERE user_id = 1");
?>

Con prepared statement diviene:

<?php
//Creao un prepared statement
$stmt = $mysqli->prepare("SELECT `firstname`, `lastname` FROM `users` WHERE user_id = ?");

// Associo la variabile per sostituire il ?
$stmt->bind_param('i', $id);

// Setto la variabile	
$id = 1;
 
// Eseguo la query
$stmt->execute();
?>

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:

<?php
$stmt = $mysqli->prepare("SELECT `firstname`, `lastname` FROM `users` WHERE email=? AND psw=SHA1(?)");
$stmt->bind_param('ss', $email, $psw);
?>

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.

Inserimento dei dati

<?php
if($stmt = $mysqli->prepare("INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES (?, ?, ?)")) {
    $stmt->bind_param('sss', $first, $last, $email);
 
    $first = 'Mario';
    $last  = 'Rossi';
    $email = 'mario.rossi@miaemail.com';
    
    $stmt->execute();
 
    $stmt->close();    
} else {
    echo 'Inserimento fallito!';
}
?>

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

<?php
$stmt = $mysqli->prepare("INSERT INTO `test` (id) VALUE (?)");
$stmt->bind_param('i', $i);

for ($i = 1; $i <= 100; $i++) {
    $stmt->execute();
}

$stmt->close();
?>

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

<?php
$mysqli->query("INSERT INTO `tbl_name` (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9)"));
?>

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:

<?php
$stmt = $mysqli->prepare("SELECT `user_id`, `firstname` FROM `users`");
$stmt->execute();
$stmt->bind_result($out_id, $out_name);
while ($stmt->fetch()) {
    printf("id= %s (%s) firstname= %s (%s)n", 
            $out_id, gettype($out_id), 
            $out_name, gettype($out_name)
            );
}

$stmt->close();
?>

Interfaccia mysqli_result

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

<?php
$stmt = $mysqli->prepare("SELECT `user_id`, `firstname` FROM `users`");
$stmt->execute();
$res = $stmt->get_result();

while ($row = $res->fetch_array(MYSQLI_ASSOC)) {
    var_dump($row);
}

$stmt->close();
?>

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:

<?php
$mysqli = new mysqli("host", "user", "psw", "db");

/* Prepared statement -> restituisce un intero*/
$stmt = $mysqli->prepare("SELECT `id` FROM `test` WHERE `id` = 2");
$res = $stmt->execute();
$res = $stmt->get_result();
$row = $res->fetch_assoc();
var_dump($row);

/* Non-prepared statement -> restituisce una stringa*/
$res = $mysqli->query("SELECT `id` FROM test WHERE id = 2");
$row = $res->fetch_assoc();
var_dump($row);
?>

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