19 – 20 – 21 Stored procedures
Abbiamo visto che nelle views posso inserire un comando SQL, ma solo una query può essere impartita per views, inoltre la views non può accettare dei parametri, tipo una vista a cui dire un valore di soglia per la selezione (es: estrarre i dati dei clienti solo se il fatturato e maggiore di un valore generato dinamicamente).
Le stored procedures permettono questo, completando le views. Le possiamo definire come batch di istruzioni SQL memorizzati sul server, infatti
- Possono ricevere parametri dall’esterno (input parameters)
- Possono restituire valori all’esterno (output parameters)
Si può pensare alle stored procedures come dei veri e propri sottoprogrammi SQL memorizzati sul server e richiamabili a piacere.
Vantaggi
- Velocità (precompilazione)
L’interprete potrebbe impiegare del tempo a controllare e ricompilare lunghe sequenze di comandi SQL che ogni volta incontra. Con le stored procedures questo avviene solo la prima volta al momento della creazione. - Minor Traffico di rete
Molti comandi SQL vengono richiesti ed eseguiti da molti utenti, le stored procedures sono memorizzate sul server - Uniformità
si mettono a disposizione gli stessi batch con le stesse politiche aziendali per tutti, invece di lasciare libero ogni programmatore - Condivisione delle migliori tecniche a tutti gli sviluppatori
si vengono a creare librerie SQL a disposizione di tutti i programmatori - Facilita la manutenzione
avendo una gestione centralizzata - Sicurezza
se ogni operazione possibile può essere svolta tramite le stored procedures si può impedire l’uso diretto delle tabelle; più difficile sfruttare tecniche di sql injection
Vediamo un esempio classico di sql injection nella compilazione di una form di login:
sql = "select * from users" + "where userName =' "userName.Text +" ' and userPass=' "+userPass.Text +" '
Immaginiamo che come user venga inserita questa SQL INJECT: ‘or 1=1 —
Il primo apice chiuderebbe quello dello userName e otterrei: userName=’ ‘ che di per se farebbe fallire il confronto, ma con l’or 1=1 andrei in situazione di true, permettendo il log. Le 2 linee (–) mettono a momento tutto il susseguirsi. Ecco il comando che otterrei con dopo la sql injection:
select from users where userName='' or 1=1 -- and userPass='...'
Il controllo a livello di store procedures è una barriera in più.
Vediamo come creare una store procedures chiamata procedura1 che selezione tutto da atleti
per eseguire la store procedures basta usare la keywords CALL
call procedura1();
Esempio pratico
Abbiamo una tabella piante con i campi: idPianta , nomePianta , ksClima , durata che esprime gli anni attesi per quel tipo di pianta.
Vorrei passare come parametro un valore intero da usare nel where della select.
I parametri vengono passati tra parentesi e se più di 1, separati da una virgola. In fondo si dichiara il tipo (INT , VARCHAR, DATE …)
Un parametro può essere usato per portare dall’esterno un valore alla store procedure che sarà utilizzata nel comando SQL. Parleremo di parametro di INPUT (IN), diversamente se il parametro serve a portare all’esterno un valore calcolato, si parlerà di OUTPUT (OUT). Potrebbe essere si di in entra che uscita e sarà INPUT/OUTPUT. Vediamo il comando che crea una stored procedure che usa un parametro per la durata delle piante e crea una select che estrae tale durata:
Con questa select ci esponiamo in minor modo a sql inject , perché il comando SQL non viene concatenato a pezzi come stringa, ma il motore interno utilizza il parametro inserendolo come criterio del where già costruito.
In SQL server è obbligatorio che il nome di un parametro cominci con chiocciola (@), in MySQL è libero.
La stored procedure potrà essere richiamata interattivamente come andremo a fare nel comando che segue, oppure essere eseguita da un altra stored procedure, o da un’applicazione esterna (php, c#, python…).
call piante_con_durata(2);
Passiamo ora invece che il numero 2 direttamente come parametro, una variabile cha avrà valore 2. La variabile viene dichiarata col comando SET
set @durata = 2; call piante_con_durata(@durata);
Questo tipo di variabile è chiamata variabile di sessione e resterà attiva fino a quando non staccheremo la connessione col database. davanti al nome della variabile, in MySQL, viene anteposto il carattere @.
Ricordarsi che il valore di una variabile dichiarata nella creazione della stored procedure:
rimane solo finchè chiamo tale stored procedure
call piante_con_durata(@durata);
al di fuori il suo valore è ignoto. Da qui deriva l’importanza dei valori di input (in)
b
a
b
a
b
a
b
a
b
a
b
a
b
a
b
a
b
a
b
a
b
a
b
a
b
a
b