Monday, 19 July 2010

Usare le Connessioni permanenti


Nel connettersi ad un database, oltre alla classica modalità di temporary connection, spesso si ha necessità di utilizzare le connessioni permanenti, che l'Application server riunisce in un pool di persistent connections.

Le connessioni permanenti hanno la particolarità di non chiudersi quando termina l'esecuzione dello script. PHP verifica se c'è un'identica connessione (stesso host, username e password) che non sia stata chiusa: se c'è la utilizza, altrimenti la crea.

L'utilità delle connessioni permanenti risalta maggiormente quando ci sono molti utenti che utilizzano brevi connessioni. In questo caso, infatti, il server utilizza il pool invece di aprire e chiudere una connessione per ogni query/dataset (considerando che ogni apertura comporta un costo operativo).

Un server Web multiprocesso (tipicamente, Apache) ha un processo padre che coordina un set di processi figli: quando viene richiesta una pagina dal client, il server alloca la risorsa scegliendo il primo processo figlio libero. Il problema delle connessioni temporanee, è che uno stesso client che effettua una nuova richiesta al server può essere servito da un nuovo processo, andando a creare due (o più) connessioni per una stessa pagina. Invece, se la connessione è permanente ogni pagina che effettua richieste SQL può riutilizzare la stessa connessione.
La controindicazione è nel numero massimo di connessioni che il database supporta. Bisogna verificare la documentazione per parametrizzare correttamente le connessioni (comprese abandoned e idle connections)

Ecco un esempio di codice di connessione in MySQL:

$connect=mysql_pconnect($dbhost, $dbuser, $dbpass);
if (!$connect)
    die("Errore durante la connessione a MySQL ".mysql_error());
mysql_select_db($dbname ,$connect);
mysql_query('set names utf8');

Naturalmente questo frammento di codice va posto in un file separato chiamato a livello globale, e non inserito in ogni pagina (altrimenti verrebbe aperta una connessione permanente per ogni richiesta!). Questo consente anche di avere codice scalabile e modificabile sostituendo connessioni permanenti e non permanenti.

Tuesday, 13 July 2010

Impostazione veloce Variabili di Sessione e utente

Molto spesso si ha necessità di verificare se una variabile è impostata, e se lo è correttamente. Ecco uno snippet di codice utile a tal scopo, che include anche il controllo di sessione sull'utente:

// Controllo che l'utente abbia profilo 3
@session_start();
if ($_SESSION['id_profile'] != 3) {
$_SESSION['errore'] = 2;
}
$id_requested = $_SESSION['id_profile']; 

$date_from = isset($_REQUEST['datefrom']) ? $_REQUEST['datefrom'] : "";

Per comodità riporto anche questo codice utile per distinguere i diversi casi di variabili

PHP Superglobals

Prendi il parametro
$_GET["id"]

quello che viene passato in Get, Post e Cookie da una form
$_REQUEST["variabile"]

Per sapere se una pagina arriva in POST:
$_SERVER["REQUEST_METHOD"]
Per salvare un dato in sessione:
$_SESSION["datodasalvare"] = $miavar;
NOTE
Con gli apici singoli il "\n" non viene espanso. 

L'attenzione ai parametri

Quando si accettano dati da un utente dovrebbero essere sempre disinfettati prima di mandarli in esecuzione al database.
Una vignetta spiega più di mille parole.


Xkcd (Exploit of a mom). Qualche esempio qui.
Per il Bravo Programmatore MySQL, la  funzione da utilizzare è ad esempio mysql_real_escape_string

Monday, 12 July 2010

Selezione di un range di date

Nel caso in cui serva estrarre da una relazione delle tuple che contengano esattamente un range di date di nascita, può essere più efficiente utilizzare funzioni native SQL/MySQL invece di usare un codice ogni volta personalizzato (ovvero: scriverlo da zero).

Si suppone che si abbia bisogno di distinguere range di 5 anni. Il codice risulterà questo:

(SELECT
        SUM(CASE WHEN  data_nascita < NOW() THEN 1 END) AS tutti,
        SUM(CASE WHEN DATE_ADD(data_nascita, INTERVAL '5' YEAR) < NOW() AND DATE_ADD(data_nascita, INTERVAL '10' YEAR) >= NOW() THEN 1 ELSE 0 END) AS 5_9,
        SUM(CASE WHEN DATE_ADD(data_nascita, INTERVAL '10' YEAR) < NOW() AND DATE_ADD(data_nascita, INTERVAL '15' YEAR) >= NOW() THEN 1 ELSE 0 END) AS 10_14,
        SUM(CASE WHEN DATE_ADD(data_nascita, INTERVAL '15' YEAR) < NOW() AND DATE_ADD(data_nascita, INTERVAL '20' YEAR) >= NOW() THEN 1 ELSE 0 END) AS 15_19,
        SUM(CASE WHEN DATE_ADD(data_nascita, INTERVAL '20' YEAR) < NOW() AND DATE_ADD(data_nascita, INTERVAL '25' YEAR) >= NOW() THEN 1 ELSE 0 END) AS 20_24,
        SUM(CASE WHEN DATE_ADD(data_nascita, INTERVAL '25' YEAR) < NOW() AND DATE_ADD(data_nascita, INTERVAL '30' YEAR) >= NOW() THEN 1 ELSE 0 END) AS 25_29,
        SUM(CASE WHEN DATE_ADD(data_nascita, INTERVAL '30' YEAR) < NOW() AND DATE_ADD(data_nascita, INTERVAL '35' YEAR) >= NOW() THEN 1 ELSE 0 END) AS 30_34,
        SUM(CASE WHEN DATE_ADD(data_nascita, INTERVAL '35' YEAR) < NOW() AND DATE_ADD(data_nascita, INTERVAL '40' YEAR) >= NOW() THEN 1 ELSE 0 END) AS 35_39,
        SUM(CASE WHEN DATE_ADD(data_nascita, INTERVAL '40' YEAR) < NOW() AND DATE_ADD(data_nascita, INTERVAL '45' YEAR) >= NOW() THEN 1 ELSE 0 END) AS 40_44,
        SUM(CASE WHEN DATE_ADD(data_nascita, INTERVAL '45' YEAR) < NOW() AND DATE_ADD(data_nascita, INTERVAL '50' YEAR) >= NOW() THEN 1 ELSE 0 END) AS 45_49,
        SUM(CASE WHEN DATE_ADD(data_nascita, INTERVAL '50' YEAR) < NOW() AND DATE_ADD(data_nascita, INTERVAL '55' YEAR) >= NOW() THEN 1 ELSE 0 END) AS 50_54,
        SUM(CASE WHEN DATE_ADD(data_nascita, INTERVAL '55' YEAR) < NOW() AND DATE_ADD(data_nascita, INTERVAL '60' YEAR) >= NOW() THEN 1 ELSE 0 END) AS 55_59,
        SUM(CASE WHEN YEAR(data_nascita) <= YEAR(CURDATE())-60 THEN 1 ELSE 0 END) AS over_60
        FROM tab_utenti
        WHERE attivo='1')

Thursday, 8 July 2010

Tunnel SSH

Per aprire una connessione sicura verso un Server, il tunnel SSH è sicuramente la soluzione migliore.

Diversi software possono servire allo scopo. Sicuramente ad esempio PuTTY, con Pageant, per le parole chiave, e Plink per creare collegamenti. Questo ultimo è il comando da utilizzare con la seguente sintassi:
plink -ssh -l <<nomeutente>> -L <<porta>>:<<indirizzo_porta>> -T -X -C -N <<server_destinazione>> [opzioni]
Esempio per un sistema linux con vm Windows:
plink -L 15901:10.11.12.13:5900 username@210.211.112.113
In cui_> porta locale:IP remoto:porta remota  username@IP locale.


Un'altra possibilità è OpenSSH. Ne parla Andrea Beggi qui. Brevemente:
Installare OpenSSH per Windows da qui (lasciare tutto default)
Aprire prompt comandi sulla dir di OpenSSH
cd bin
mkgroup -l >> ..\etc\group
mkpasswd -l -u <username_Windows> >> ..\etc\passwd   
net start opensshd

Apri PuTTY
Ip del server: Salva
Nella sezione SSH metti:
    Source Port: 3389
    Destination: 127.0.0.1:3389
Add.
Open: Yes

Vediamo come utilizzare SQLYog. La versione da utilizzare per questo scopo non è quella gratuita (Community Edition) ma l'Enterprise a pagamento. Dopo averla scaricata possiamo partire a configurare la connessione SSH e quella al database.

New -> Connection

Linguetta SSH
Use SSH Tunneling
SSH Host: va scritto il server a cui si vuole accedere (porta 22)
Username e password: quelle che usate per accedere in remoto (comunicate dall'admin)
Local Port: 4407 (Tcp/Udp)

Linguetta MySQL
MySQL host address: localhost o 127.0.0.1 (percorso locale al server)
Username e password: quelle usate per accedere al database (comunicate dall'admin)
Port: 3306 (porta Tcp usata da MySQL)
Database: il nome del vostro db

Save.

Verificate che ci sia l'utente. Andate su "Tools"->"User Manager"->"Edit User" e aggiungete l'utente.

A questo punto "Test connection.." e poi "Connect".


VNC su Tunnel SSH

Il tunnel SSH può essere anche usato per la modalità VNC (Virtual Network Computer), molto comoda per amministrare o gestire remotamente computer (su cui gira un VNC Server). La connessione VNC, infatti di norma è criptata solo in fase di login, ma utilizzandola tramite un tunnel SSH, si cripta tutto il traffico tra i due computer. Tra l'altro non vengono aperte porte VNC su Internet (quindi un eventuale scanning non troverà nulla), tranne la porta 222 di SSH. Devono essere abilitate (LISTEN) la porta remota 5900 (se Windows), o 5901 (se linux), e la porta  locale (si verifica con netstat -ln sui sistemi unix/linux, e netstat -an sotto Windows).

Ci si collega alla macchina remota indicando localhost:<porta locale> VNC forwardata su SSH e la porta remota. Il server, se raggiungibile ed attivo, richiederà username e password per abilitare il controllo remoto.

Friday, 2 July 2010

Il campo dedicato all'email

Secondo la specifica contenuta nell'RFC 2821, la parte dedicata al nome (local name) non deve superare 64 caratteri mentre la restante parte (domain name) 255 caratteri. La lunghezza massima dell'email si deduce sia quindi al massimo di 320 caratteri, compreso il simbolo `@'.

ESEMPIO (MySQL)
CREATE TABLE  `dbname`.`tb_utenti` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  `nome` varchar(50) default NULL,
  `cognome` varchar(50) default NULL,
  `email` varchar(320) default NULL, 
  `indirizzo` text NOT NULL,
  `citta` varchar(255) NOT NULL,
  `data` datetime NOT NULL, 
  `telefono` varchar(50) NOT NULL,
  `note` text,
  PRIMARY KEY  (`id`,`username`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


Altre utili informazioni in questa pagina e alla RFC 2821 (che rende obsoleta la vetusta 821)

Thursday, 1 July 2010

Vincoli di integrità referenziali (foreign key)

Avendo ad es. queste due Relazioni:

tb_utenti
id_profilo: 1
tb_profili
id: 1
descrizione: normale

Codice (MySQL):

tb_utenti
KEY `fk_p` (`id_profilo`),
CONSTRAINT  `fk_p` FOREIGN KEY (`id_profilo`) REFERENCES `tb_profili` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
(ovvero: costringi fk_p, chiave referenziante di id_profilo a referenziare il campo id di tb_profili)


ESEMPIO DI IMPLEMENTAZIONE
DROP TABLE IF EXISTS `dbname`.`tb_profili`;
CREATE TABLE `dbname`.`tb_profili` (
`id` int(11) NOT NULL auto_increment,
`descrizione` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `dbname`.`tb_profili` (`id`,`descrizione`) VALUES
(1,'Amministratore'),
(2,'Utente'),
(3,'Ospite');

DROP TABLE IF EXISTS `dbname`.`tb_utenti`;
CREATE TABLE `dbname`.`tb_utenti` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(20) NOT NULL,
`password` varchar(50) NOT NULL,
`nome` varchar(30) default NULL,
`cognome` varchar(50) default NULL,
`email` varchar(50) default NULL,
`data` datetime NOT NULL,
`attivo` int(11) NOT NULL default '0',
`id_profilo` int(11) NOT NULL default '0',
`note` text,
PRIMARY KEY (`id`,`username`),
UNIQUE KEY `id` (`id`),
KEY `fk_profili` (`id_profilo`),
CONSTRAINT `fk_profili` FOREIGN KEY (`id_profilo`) REFERENCES `tb_profili` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;

(vedere manuale MySQL per il significato del valore AUTO_INCREMENT)