PHP Operacións BD Mysql

Introdución

IMPORTANTE – API empregada no módulo

Nestes apuntes explícanse diferentes formas de realizar operacións contra unha base de datos MySQL desde PHP, empregando principalmente **mysqli** e **PDO (PHP Data Objects)**.

As explicacións e exemplos realizados con **mysqli**, tanto na súa versión procedemental como orientada a obxectos, mantéñense nos apuntes con carácter **informativo e comparativo**, para coñecer as diferentes posibilidades que ofrece PHP para acceder a MySQL.

Non obstante, a partir deste tema a forma de acceso a bases de datos que se empregará como referencia no módulo será **PDO**, utilizando o controlador **PDO_MySQL**.

Polo tanto, **nos exercicios, prácticas e exames nos que se solicite realizar operacións contra unha base de datos desde PHP, deberá empregarse PDO**, salvo que no enunciado se indique expresamente o contrario.

Recoméndase, polo tanto, prestar especial atención aos exemplos identificados como **“Empregando PDO”**, xa que serán os que se tomarán como referencia para a avaliación.

• Estes apuntes están actualizados para PHP 8.5. PDO traballa por defecto en modo de excepcións, polo que os erros producidos nas operacións contra a base de datos poden xerar unha PDOException, que poderá ser xestionada mediante try-catch.

• Como comentamos no punto anterior, para conectarnos a un xestor de base de datos específico imos ter que ter instalados os drivers correspondentes.

Un driver é o software que nos permite comunicarnos cun xestor de base de datos, pero normalmente non imos facer dita comunicación utilizando o driver directamente se non que o usaremos a través dunha API, que son un conxunto de clases, funcións,... que nos permiten facer uso do driver e conectar a un xestor de base de datos.

• Existen dous tipos de API´s:

• Procedimentais: Facemos uso de funcións para realizar as operacións contra a base de datos.
• Orientadas a obxectos: Instanciamos clases e chamamos a métodos dos obxectos creados.

Normalmente faremos uso desta segunda opción.

• En PHP, ditas API´s se 'activan' facendo uso de Extensións.

Indicar que unha API non é o mesmo que unha extensión en PHP, xa que algunhas extensións non proporcionan ningunha API ao usuario (por exemplo a extensión do driver PDO MySQL, non proporciona ningunha API ó programador PHP).

• Existen tres APIs principais para conectar a un servidor de bases de datos MySQL:

• Extensión MySQL de PHP (eliminada a partires da versión PHP 7.0)
• Extensión mysqli de PHP
• Obxectos de Datos de PHP (PDO) => A QUE IMOS EMPREGAR

• A extensión mysqli ven incluída nas versiones PHP 5 e posteriores.

Algunha das melloras de dita extensión con respecto á extensión mysql:

• Interfaz orientada a obxectos
• Soporte para declaracións preparadas.
• Soporte para Transaccións

Activar a extensión mysqli e pdo

• Se instalamos XAMPP ou calquera das versións para outros S.O. normalmente a extensión xa ven habilitada por defecto.

Se temos unha instalación personalizada no que instalamos separadamente todo, teremos que habilitala.

Xa vimos anteriormente que PDO permite conexión a múltiples bases de datos, pero tamén engade unha capa más para a conexión de base de datos, aínda que hoxe en día, a diferenza de rendemento é mínima.

Windows

• En Windows, a extensión mysqli ven desactivada no php.ini.

Partindo de que xa tedes o arquivo php.ini no cartafol onde se atopa o intérprete de PHP (se non é así mirade a nota de Importante que se atopa neste punto da wiki) temos que:

• Descomentar a liña:

1 ;extension=php_mysqli.dll

Pasa a:

1 extension=php_mysqli.dll

• Descomentar a liña:

1 ;extension_dir = "ext"

Pasa a:

1 extension_dir = "ext"

O que fai dita liña é informar a PHP que busque as extensións no cartafol relativo ext, é dicir, nun cartafol chamado ext dentro do cartafol onde se atopa o executable de php.

Linux

• Necesitamos ter instalado o paquete: php-mysql.

Para instalalo:

1 sudo apt install php-mysql

Reiniciamos o apache: sudo systemctl restart apache2

• O anterior vai crear varios arquivos de configuración no cartafol: etc/php/8.1/apache2/conf.d para cargar as extensións mysqli e pdo.

Nota: O cartafol pode variar dependendo da versión php que teñades instalada.

Creando as táboas en Mysql

• Unha vez temos a base de datos teremos que facer o Modelo EE/R, pasalo ao Modelo Relacional e facer un Proceso de Normalización ata polo menos a forma normal de Boyce-Codd.

• Imos implementar unha base de datos para xestionar a venda de libros de segunda mano.

A orde SQL para crear unha base de datos de nome php é:

CREATE SCHEMA `php` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish2_ci ;

O modelo EE/R é o seguinte:

Nota:

• A táboa ESTADOS podería eliminarse e crear un campo estado de tipo enum en Mysql se ditos 'estados' van ser sempre os mesmos e non van modificarse...
• O campo 'imaxe' da táboa EXEMPLARES está nesta táboa e non na táboa LIBROS, xa que a idea é amosar ao comprador unha imaxe do exemplar para amosar o seu estado.

• O modelo relacional implementado nunha base de datos Mysql é o seguinte:

CREATE TABLE IF NOT EXISTS `php`.`libros` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `titulo` VARCHAR(300) NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE IF NOT EXISTS `php`.`estados` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `descripcion` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`));

 CREATE TABLE IF NOT EXISTS `php`.`exemplares` (
  `libro_id` INT NOT NULL,
  `id_exemplar` INT NOT NULL,
  `imaxe` VARCHAR(100) NULL,
  `prezo` DECIMAL(6,2) NOT NULL,
  `estado_id` INT NULL,
  INDEX `fk_EXEMPLARES_LIBROS_idx` (`libro_id` ASC),
  INDEX `fk_EXEMPLARES_ESTADOS_idx` (`estado_id` ASC),
  PRIMARY KEY ( `libro_id`,`id_exemplar`),
  CONSTRAINT `fk_EXEMPLARES_LIBROS`
    FOREIGN KEY (`libro_id`)
    REFERENCES `php`.`libros` (`id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_EXEMPLARES_ESTADOS`
    FOREIGN KEY (`estado_id`)
    REFERENCES `php`.`estados` (`id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS `php`.`autores` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `apelidos` VARCHAR(200) NOT NULL,
  `nome` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`));


CREATE TABLE IF NOT EXISTS `php`.`libro_autor` (
  `libro_id` INT NOT NULL,
  `autor_id` INT NOT NULL,
  PRIMARY KEY (`libro_id`, `autor_id`),
  INDEX `fk_LIBROAUTOR_AUTORES_idx` (`autor_id` ASC),
  CONSTRAINT `fk_LIBROAUTOR_LIBROS`
    FOREIGN KEY (`libro_id`)
    REFERENCES `php`.`libros` (`id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_LIBROAUTOR_AUTORES`
    FOREIGN KEY (`autor_id`)
    REFERENCES `php`.`autores` (`id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE);

• Para ter algún dato inicial imos a executar estas ordes:

INSERT INTO `php`.`estados` VALUES (1,'NOVO'),(2,'COMO NOVO'),(3,'DETERIODADO'),(4,'CON ANOTACIÓNS');


INSERT INTO `php`.`autores` (`apelidos`, `nome`) VALUES ('Drumont', 'Eduardo');

INSERT INTO `php`.`libros` (`titulo`) VALUES ('El caso Dreyfus, expresión del antisemitismo, temas, Gallica, BNF');
INSERT INTO `php`.`libros` (`titulo`) VALUES ('Les amis');

INSERT INTO `php`.`exemplares` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('1', '1', 'dreyfus.jpg', '20.52', '2');
INSERT INTO `php`.`exemplares` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('1', '2', 'dreyfus.jpg', '15.34', '3');
INSERT INTO `php`.`exemplares` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('2', '1', 'amis.jpg', '35.35', '1');
INSERT INTO `php`.`exemplares` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('2', '2', 'amis.jpg', '20.25', '4');
INSERT INTO `php`.`exemplares` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('2', '3', 'amis.jpg', '25.34', '3');

INSERT INTO `php`.`libro_autor` VALUES (1,1),(2,1);

• Agora faremos o seguinte:

• Crearemos dentro do noso sitio web (normalmente /var/www/html/) un cartafol de nome IMAXES
• Copiamos as imaxes que veñen a continuación a dito cartafol.
• Cambiaremos o propietario coa orde chown -R www-data:www-data IMAXES/

Nome: amis.jpg Nome: dreyfus.jpg

Regras de nomeado de táboas e columnas

• Aínda que non existen 'regras' únicas sobre como nomear as táboas e campos nos imos seguir as seguintes, para que cando vexamos Laravel, coincidan os Modelos de Laravel cós nomes das táboas asociadas:

• Nomes de táboas en PLURAL e con minúsculas. Por exemplo táboa: usuarios
• Nomes de campos en minúscula e singular.

• Se o campo é a clave primaria terá de nome: id
• Se o campo é unha clave foránea terá de nome: nometáboa_en_singular_id. Por exemplo, nunha táboa de nome pais, asociada á táboa usuarios cunha relación 1:N, tería unha clave foránea de nome usuario_id.
• Se o nome de campo está formado por varias palabras, estarán separadas por un guión baixo ( _ )

• Non usar til nin caracteres 'raros' como espazos en branco, interrogacións,...
• As claves foráneas irán ao final da lista de columnas da táboa.
• As táboas que se formen por unha relación N:M terán de nome: taboa1singular_taboa2singular. Por exemplo se temos unha táboa usuarios e unha táboa modulos e existe unha relación N:M entre elas, se crearía a táboa: usuario_modulo

Visualización de erros

• Ao principio é habitual cometer erros durante as probas de programación.

 : Ata que aprendamos a utilizar un depurador (debugger), cando se produce un erro en PHP pode aparecer unha páxina en branco, mostrarse só unha parte da páxina ou aparecer unha mensaxe de erro que indique o problema.

• Durante o desenvolvemento é recomendable configurar PHP para que mostre os erros. Isto pode facerse modificando as directivas display_errors e error_reporting do ficheiro php.ini.

Se non se quere modificar a configuración de PHP, tamén se poden engadir temporalmente ao principio do ficheiro PHP as seguintes instrucións:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

Desta forma PHP mostrará os erros, avisos e advertencias que se produzan durante a execución do programa.

IMPORTANTE: Estas opcións deben utilizarse unicamente durante o desenvolvemento e as probas. Nun servidor en produción non se deben mostrar os erros ao usuario.

Hai que ter en conta que estas instrucións non permiten detectar determinados erros que se producen antes de que o programa comece a executarse, como algúns erros de sintaxe. Nestes casos será necesario consultar o rexistro de erros do servidor ou activar a visualización de erros na configuración de PHP (php.ini).

• Outra ferramenta moi útil é o inspector ou as ferramentas para desenvolvedores do navegador. Pódense abrir normalmente coa tecla F12.

Desde estas ferramentas pódense revisar, entre outras cousas:

• O código HTML xerado.
• As peticións realizadas ao servidor.
• Os códigos de estado HTTP, por exemplo un erro 500 Internal Server Error.
• As mensaxes da consola de JavaScript.

Con todo, hai que ter en conta que o navegador non pode mostrar o código PHP orixinal, xa que este se executa no servidor. O navegador só recibe o resultado xerado polo programa PHP.

• Outra ferramenta é engadir ao VS Code a extensión que nos permita facer 'debuger' da aplicación. Tedes nesta misma wiki o procedemento para facelo na UD0 de instalación.

• E lembrar que tamén podedes consultar o arquivo de error.log que se atopa en /var/log/apache2/error.log coa orde: tail -n 100 /var/log/apache2/error.log (sendo 100 o número de liñas que queredes consultar do arquivo empezando polo final)

Conexión

• Partimos da base de que xa temos instalado:

• O xestor de bases de datos Mysql
• Unha base de datos creada. Neste manual vai ter de nome php. Facer que o 'default collation' sexa utf8mb4_spanish2_ci (máis información sobre o charset/collation neste enlace.
• Un usuario con permisos suficientes para realizar as operacións necesarias sobre a base de datos. Neste manual o usuario/password creado son user_php/user_php.

Todo o anterior xa está indicado no punto anterior da wiki.

NOTA IMPORTANTE:

Nos seguintes apartados explícanse distintas formas de realizar operacións contra unha base de datos MySQL desde PHP: utilizando mysqli de forma procedemental, mysqli orientado a obxectos e PDO.

Os exemplos realizados con mysqli mantéñense nos apuntes con carácter informativo e comparativo.

Nos exames, exercicios e prácticas avaliables deberase empregar PDO, salvo que no enunciado se indique expresamente o contrario.

A versión de PHP empregada como referencia nestes apuntes e nos exames será PHP 8.5.

Apertura da conexión

• Utilizando PDO:

public PDO::__construct(
    string $dsn,
    ?string $username = null,
    #[\SensitiveParameter] ?string $password = null,
    ?array $options = null
)

No caso de PDO é obrigatorio indicar o parámetro dsn (Data Source Name), que identifica o controlador e os datos necesarios para realizar a conexión. No noso caso, ao empregar MySQL, indicaremos o servidor, a base de datos e o xogo de caracteres.

Un exemplo:

$dsn = 'mysql:host=localhost;dbname=php;charset=utf8mb4';

$opciones = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false
];

$conex = new PDO($dsn, $usuario, $password, $opciones);

Como vemos modificamos varios atributos da conexión no array $opciones. Podemos consultar todas as opcións no seguinte enlace.

• Neste caso modificamos o atributo ATTR_ERRMODE e damos o valor ERRMODE_EXCEPTION.

O que indica dito atributo é o seguinte:

Excepción	PDO::ERRMODE_EXCEPTION	Fai que PDO lance unha PDOException cando se produce un erro. É o modo predeterminado desde PHP 8.0 e será o que empregaremos no módulo.

Silencioso	PDO::ERRMODE_SILENT	Non lanza erros. Simplemente devolve false se hai un fallo. Hai que comprobar manualmente cada operación.
Aviso (Warning)	PDO::ERRMODE_WARNING	Xera un warning de PHP cando ocorre un erro, pero o script continúa. Obsoleta a partires da versión 8.5. Non empregar.

• PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC: Indica que cando recollamos datos da base de datos os imos recoller nun array asociativo. Se sabemos empregar a POO podemos optar por FETCH_OBJECT.
• PDO::ATTR_EMULATE_PREPARES => false: Isto fai que as sentenzas prepared sexan xestionadas polo xestor de base de datos e non polo php.

Fixarse que a conexión a estamos a facer contra Mysql.

Unha das vantaxes de PDO é que proporciona unha interface común para traballar con diferentes xestores de bases de datos. No módulo utilizaremos exclusivamente PDO_MySQL, pero a modo informativo existen outros controladores.

Se queremos conectarnos con outra base de datos poderíamos poñer os seguintes valores:

mysql	mysql:host=localhost;dbname=php;charset=utf8mb4	Conecta a unha base de datos MySQL ou MariaDB
pgsql	pgsql:host=localhost;port=5432;dbname=instituto;user=user_pg;password=1234	Conecta a unha base de datos PostgreSQL
sqlite	sqlite:/var/www/basedatos.db	Conecta a unha base de datos SQLite (sen servidor)
sqlsrv	sqlsrv:Server=localhost;Database=instituto	Conecta a Microsoft SQL Server
oci	oci:dbname=xe;charset=UTF8	Conecta a Oracle
ibm	ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=db;HOSTNAME=host;PORT=50000;PROTOCOL=TCPIP;UID=user;PWD=pass;	Conecta a IBM DB2
firebird	firebird:dbname=localhost:/ruta/a/base.fdb	Conecta a Firebird
dblib	dblib:host=localhost;dbname=instituto	Conecta a Sybase ou Microsoft SQL Server antigo
informix	informix:host=host;service=puerto;database=db;server=servidor;protocol=onsoctcp;UID=user;PWD=pass;	Conecta a Informix
odbc	odbc:mi_dsn	Usa unha fonte de datos ODBC definida no sistema

NOTA: O indicado a continuación inclúese a nivel informativo. Nos exames empregarase PDO.

• Utilizando procedementos: mysqli_connect
• Utilizando obxectos: mysql::_construct

• Sintaxe (se indica a sintaxe do construtor e polo tanto será equivalente a facer un 'new' da clase mysqli):

• Orientada a obxectos:

mysqli::__construct ([ string $host = ini_get("mysqli.default_host") [, string $username = ini_get("mysqli.default_user") [, string $passwd = ini_get("mysqli.default_pw") [, string $dbname = "" [, int $port = ini_get("mysqli.default_port") [, string $socket = ini_get("mysqli.default_socket") ]]]]]] )

• Por procedementos:

mysqli mysqli_connect ([ string $host = ini_get("mysqli.default_host") [, string $username = ini_get("mysqli.default_user") [, string $passwd = ini_get("mysqli.default_pw") [, string $dbname = "" [, int $port = ini_get("mysqli.default_port") [, string $socket = ini_get("mysqli.default_socket") ]]]]]] )

Todos eles son optativos, pero normalmente indicaremos o nome de host, login, password e base de datos.

Os parámetros son:

• Host a conectar (cadea): Pode ser o nome ou a dirección IP. Normalmente imos poñer 'localhost' (se o xestor de bases de datos está instalado no mesmo equipo que o servidor web).
• Nome do usuario con acceso á base de datos (cadea): Nome do usuario que ten acceso á base de datos.
• Password (cadea): Password do usuario.
• Dbname (cadea): Nome da base de datos sobre a que imos traballar.
• Porto (numérico): Porto no que está escoitando o xestor de base de datos. No caso de mysql é o 3306.
• Socket (cadea): Socket ou tubería para conectar có xestor de bases de datos. En Linux é un tipo especial de arquivo que serve para 'transmitir' información, neste caso, cara o xestor de bases de datos.

• Aplicado ao noso caso, teremos a seguinte orde:

• Por procedementos:

1 $conex = mysqli_connect('localhost','user_php','user_php','php');

• Orientado a obxectos:

1 $conex = new mysqli('localhost','user_php','user_php','php');

$conex é un obxecto que represente a conexión á base de datos.

Control de errores

• En versións de PHP igual ou superior á 8.1: Neste caso, debemos empregar a forma try ... catch da programación orientada a obxectos. ESTE É A FORMA QUE IMOS EMPREGAR NOS EXAMES:

• Versión con chamada empregando pdo:

try {
  $dsn = 'mysql:host=localhost;dbname=php;charset=utf8mb4';
  
  $usuario = 'user_php';
  $password = 'user_php';

  $opciones = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false
  ];

  $conex = new PDO($dsn, $usuario, $password, $opciones);

}
catch (PDOException $e) {
    die("Error de conexión PDO: (" . $e->getCode() . ") " . $e->getMessage());
}
} catch (Throwable $e) {
    die('Erro xeral: ' . $e->getMessage());
}

Nota:

• Normalmente ao usuario non se lle vai amosar o erro que devolve o xestor de base de datos e dito erro sóese gardar nunha táboa interna ou nun arquivo de log para o administrador e que poida ver cal foi o erro.
• Podemos facer un único try-catch que envolva toda a páxina PHP ou facer un try-catch por cada bloque php que poida xerar unha excepción.
• Os atributos:

• PDO::ERRMODE_EXCEPTION: Valor predeterminado a partires da versión 8.0, pero o imos poñer de todas formas.
• PDO::FETCH_ASSOC: Cando consultemos datos devolverá os mesmos en forma de array asociativo. Se poñemos PDO::FETCH_OBJ obteremos os datos como propiedades de clase coma $row->nome.
• ATTR_EMULATE_PREPARES => false : Fai que as sentenzas prepared (o veremos posteriormente) as faga o xestor de base de datos.

Por exemplo:

try {

    $dsn = 'mysql:host=localhost;dbname=php;charset=utf8mb4';

    $opciones = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false
    ];

    $conex = new PDO($dsn, $usuario, $password, $opciones);

} catch (PDOException $e) {
    die("Erro de conexión: (" . $e->getCode() . ') ' . $e->getMessage());
}
} catch (Throwable $e) {
    die('Erro xeral: ' . $e->getMessage());
}

NotaLembrar que cando se produce un erro non se debe amosar ao usuario nin o getCode() nin o getMessage(). Isto soamente é necesario en depuración, non en producción.

Dará como resultado:

NOTA: O indicado a continuación inclúese a nivel informativo. Nos exames empregarase PDO.

• Por procedementos:

try {
    $conex = mysqli_connect('localhost', 'usuario_inexistente', 'contrasena_incorrecta', 'base_de_datos');
} catch (mysqli_sql_exception $e) {
    die("Erro de conexión: " . $e->getMessage());
}
} catch (Throwable $e) {
    die('Erro xeral: ' . $e->getMessage());
}

• Versión con chamada empregando obxectos:

try {
    $conex = new mysqli('localhost', 'usuario_inexistente', 'contrasena_incorrecta', 'base_de_datos');
} catch (mysqli_sql_exception $e) {
    die("Erro de conexión: " . $e->getMessage());
}
} catch (Throwable $e) {
    die('Erro xeral: ' . $e->getMessage());
}

• Por exemplo, imos modificar o password do usuario por un que non exista e veremos o resultado:

1 try {
2     $conex = mysqli_connect('localhost','user_php','password_incorrecta','php');
3 } catch (mysqli_sql_exception $e) {
4     die("Erro de conexión: (" . $e->getCode() . ') ' . $e->getMessage());
5 }
6 } catch (Throwable $e) {
7     die('Erro xeral: ' . $e->getMessage());
8 }

Gardando os datos de conexión nun lugar seguro

• Como é obvio os datos de base de datos, usuario e password son moi importantes e non debemos permitir que alguén poida descubrilos.

• O que faremos será o seguinte:

• Definiremos en forma de constantes ditos datos nun arquivo de include.
• Gardaremos dito arquivo fora do cartafol de acceso do noso sitio web.

• O primeiro punto, definir en forma de constante e nun arquivo de include os datos da conexión:

Arquivo: bd.php:

1 <?php
2 
3 define ('SERVIDOR','localhost');
4 define ('USUARIO','user_php');
5 define ('PASSWORD','user_php');
6 define ('BD','php');

• Faremos un require deste arquivo en todas as páxinas que necesiten unha conexión á base de datos. Desta forma, se necesitamos cambiar algo soamente o cambiaríamos nun arquivo.

• O segundo punto consiste en descubrir cal é a ruta física á que apunta o noso servidor web e colocar o arquivo de include 'fora' desa ruta.

• Temos neste punto da Wiki información sobre como funciona Apache e como saber a onde apunta o servidor web no seu arquivo de configuración.

• Temos varias opcións:

• Facendo uso da constante __DIR__.
• Facendo uso do array global $_SERVER e dentro deste a entrada $_SERVER['DOCUMENT_ROOT'].

Se facemos un echo destas variables dentro dunha páxina do noso sitio web, obteremos unha ruta, coma por exemplo:

1 /var/www/html/

Nota: Se o facemos coa constante __DIR__ e a páxina que está a facer o echo se atopa dentro dun cartafol, tamén aparecerá o cartafol (por exemplo /var/www/html/cartafol/).

• Isto quere dicir que o servidor web (e polo tanto calquera usuario de Internet) vai poder acceder as páxinas que se atopan no cartafol /var/www/html e calquera dentro del.

Unha medida de seguridade é colocar o arquivo de include 'fora' desta ruta. Por exemplo, podemos crear un arquivo no cartafol /var/www/confBD

A dito cartafol ten que poder acceder o usuario que utiliza o Apache para acceder aos cartafoles a nivel do S.O. (no caso de Apache soe ser www-data, podedes miralo no punto da wiki indicado anteriormente).

• Creando o cartafol onde se vai gardar o arquivo de configuración
• 

  Facemos un cd /var/www. Creamos o cartafol: sudo mkdir confBD. Movemos o arquivo de include creado no paso anterior ao cartafol: mv /cartafol_arquivo_include/bd.inc.php /var/www/confBD.

• 

  Cambiamos os permisos: sudo chmod -R 550 /var/www/confBD e cambiamos o propietario: sudo chown -R www-data:www-data /var/www/confBD. Nota: O usuario e grupo pode variar dependendo do S.O. En Linux podedes sabelo mirando este enlace da wiki. En Windows o podedes saber neste enlace.

• Agora, calquera páxina que faga uso destes datos terá que facer o seguinte require:

• No caso de PDO:

 1 <?php 
 2 
 3 require '/var/www/confBD/bd.php';
 4 
 5 try {
 6 
 7     $dsn = sprintf(
 8         'mysql:host=%s;dbname=%s;charset=utf8mb4',
 9         SERVIDOR,
10         BD
11     );
12 
13     $opciones = [
14         PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
15         PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
16         PDO::ATTR_EMULATE_PREPARES   => false
17     ];
18 
19     $conex = new PDO(
20         $dsn,
21         USUARIO,
22         PASSWORD,
23         $opciones
24     );
25 
26 } catch (PDOException $e) {
27     die('Erro de conexión coa base de datos.');
28 }

IMPORTANTE: Este será o modelo de conexión que se empregará como referencia nos exames.

• No caso de orientado a obxectos:

1 <?php
2 
3 //error_reporting(0);  // Inabilita os erros de Apache se temos activada a directiva display_erros no php.ini
4 
5 
6 require ('/var/www/confBD/bd.php');
7 $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);

• Se queremos facer o mesmo pero sen utilizar rutas absolutas (por exemplo se estamos nun aloxamento compartido):

1 <?php
2 
3   $ruta = substr($_SERVER['DOCUMENT_ROOT'],0,strpos($_SERVER['DOCUMENT_ROOT'],'html')) . 'confBD'; // RUTA al sitio web sin /html pero con /confBD
4   require ($ruta.'/bd.php');

O xogo de caracteres

• Debemos ter en conta que unha páxina web está 'aberta' a todo o mundo que se conecte a ela.

Cada persoa en diferentes países terá diferente xogos de caracteres no que cada carácter está relacionado cun código numérico.

• Se gardamos como dato dentro da base de datos unha cadea cun 'ñ' entre os seus datos, dito carácter estará asociado a un código nun xogo de caracteres. Se o usuario que vai cargar a páxina o carga cun xogo de caracteres diferente non vai poder visualizalo.

• Cando creamos aplicacións en PHP con acceso a base de datos teremos que ter en conta os seguintes xogos de caracteres:

• Xogo de caracteres da base de datos (no noso caso Mysql): É o que se coñece como Charset/Collation.

Se vos fixades cando creamos a base de datos en Mysql, escollemos o charset (primeira columna) e o collation (segunda columna).

Como indicamos antes, cando creamos a base de datos o charset/collation indicado foi utf8mb4/utf8mb4_spanish2_ci.

O charset é como mysql garda internamente os caracteres e os relaciona cun código numérico e o collation indica como comparar e ordenar o texto. Podedes consultar esta blog para obter máis información.

• Xogo de caracteres da páxina HTML.

Na páxina web indicamos coa etiqueta <meta> o xogo de caracteres para que o navegador web poida visualizalos correctamente.

No caso que nos ocupa teremos que poñer:

• Html 5 e posteriores: <meta charset="UTF-8">

Máis información en w3schools.

• No código PHP ao conectar coa base de datos.

Cando conectemos coa base de datos teremos que indicarlle que imos utilizar o xogo de caracteres utf8mb4 con esta liña:

• Versión PDO:

 1 require ('/var/www/confBD/bd.php');
 2 
 3 try {
 4     $dsn = sprintf(
 5         'mysql:host=%s;dbname=%s;charset=utf8mb4',
 6         SERVIDOR,
 7         BD
 8     );
 9 
10     $opciones = [
11         PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
12         PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
13         PDO::ATTR_EMULATE_PREPARES   => false
14     ];
15 
16     $conex = new PDO(
17         $dsn,
18         USUARIO,
19         PASSWORD,
20         $opciones
21     );

• É importante empregar unha configuración coherente de Unicode en toda a aplicación. Neste módulo empregaremos utf8mb4 en MySQL e na conexión PDO, e UTF-8 nas páxinas HTML..

• Versión procedimental

Para iso teremos que facer uso da función set_charset.

En caso de éxito devolve true ou false en caso de existir algún problema.

1 try {
2     require ('/var/www/confBD/bd.php');
3     $conex = mysqli_connect(SERVIDOR, USUARIO, PASSWORD, BD);
4      mysqli_set_charset($conex,'utf8mb4');
5 } catch (mysqli_sql_exception $e) {
6     die("Erro de conexión: " . $e->getMessage());
7 }

• Versión orientada a obxectos:

1 try {
2     require ('/var/www/confBD/bd.php');
3     $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
4     $conex->set_charset('utf8mb4');
5 } catch (mysqli_sql_exception $e) {
6     die("Erro de conexión: " . $e->getMessage());
7 }

Peche da conexión

• Despois de facer as diferentes operacións sobre a base de datos, teremos que pechar a conexión.

Para iso temos que chamar á función mysqil::close nas versión orientada a obxectos e procedimental. No caso de PDO chega con igual a null o obxecto $conex.

Nota: Neste exemplo partimos que estamos a empregar a verión PHP 8.1 ou superior. En todas as páxinas se opta por facer un único try-catch, pero dependendo de se se quere saber a sección onde foi o erro, se podería facer varios try-catch.

• Versión PHP 8.1 ou superior

• Versión PDO:

En PDO non existe un método close(). Para pechar explicitamente unha conexión podemos asignar null á variable que contén o obxecto PDO. Se non o facemos, PHP pechará automaticamente a conexión ao rematar a execución do script.

<?php

require ('/var/www/confBD/bd.php');

try {

    $dsn = sprintf(
        'mysql:host=%s;dbname=%s;charset=utf8mb4',
        SERVIDOR,
        BD
    );

    $opciones = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false
    ];

    $conex = new PDO(
        $dsn,
        USUARIO,
        PASSWORD,
        $opciones
    );

    // ..............
    // Operaciones contra a base de datos e amosar información ao usuario
    // ..............

} catch (PDOException $e) {
    die("Error PDO: " . $e->getMessage());
} catch (Throwable $e) {
    die("Error general: " . $e->getMessage());
} 

$conex = null;   // Poderíamos non poñer nada

• Versión procedimental

<?php

require ('/var/www/confBD/bd.php');

try {  // O try se pecha na sección catch do final
    $conex = mysqli_connect(SERVIDOR, USUARIO, PASSWORD, BD);
    mysqli_set_charset($conex,'utf8mb4');
?>
  ..............
  // Operacións contra a base de datos e amosar información ao usuario
  ..............

<?php
}   // Fin do try do principio da páxina
catch (mysqli_sql_exception $e) {
    die("Erro mysql: " . $e->getMessage());
}
catch (Expcetion $e) {
    die("Erro xeral: " . $e->getMessage());
}
finally{
  if(!empty($conex)) {
     mysqli_close($conex);
  }
}
?>

• Versión orientada a obxectos

<?php
require ('/var/www/confBD/bd.php');

try {
    $conex = new mysqli(SERVIDOR, USUARIO, PASSWORD, BD);
    $conex->set_charset('utf8mb4');
?>

  ..............
  // Operacións contra a base de datos e amosar información ao usuario
  ..............

<?php
}     // Fin do try do principio da páxina
catch (mysqli_sql_exception $e) {
    die("Erro mysql: " . $e->getMessage());
}
catch (Expcetion $e) {
    die("Erro xeral: " . $e->getMessage());
}
finally{
  if(!empty($conex)){
     $conex->close();
  }
}
?>

Cambiando de base de datos activa

• Cando realizamos unha conexión con PDO podemos indicar no DSN a base de datos coa que queremos traballar:

$dsn = 'mysql:host=localhost;dbname=php;charset=utf8mb4';

Neste caso, a base de datos seleccionada inicialmente será php.

• Se durante a execución do programa necesitamos traballar con outra base de datos temos, entre outras, dúas posibilidades:

 :* Crear unha nova conexión PDO indicando a nova base de datos no DSN.
 :* Cambiar a base de datos activa da conexión actual mediante a sentenza SQL USE.

Crear unha nova conexión PDO

• Podemos crear un novo obxecto PDO indicando outra base de datos no DSN:

<?php

try {

    $opciones = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false
    ];

    // Conexión inicial á base de datos php
    $dsn = 'mysql:host=localhost;dbname=php;charset=utf8mb4';

    $conex = new PDO(
        $dsn,
        'user_php',
        'user_php',
        $opciones
    );

    // Creamos unha nova conexión á base de datos outra_db
    $dsn = 'mysql:host=localhost;dbname=outra_db;charset=utf8mb4';

    $conex = new PDO(
        $dsn,
        'user_php',
        'user_php',
        $opciones
    );

} catch (PDOException $e) {

    die('Erro relacionado coa base de datos: ' . $e->getMessage());

} catch (Throwable $e) {

    die('Erro xeral: ' . $e->getMessage());

}
?>

Ao crear o segundo obxecto PDO estamos realizando unha nova conexión na que a base de datos seleccionada inicialmente será outra_db.

Cambiar a base de datos activa con USE

• Se queremos manter a mesma conexión e cambiar a base de datos activa podemos executar a sentenza SQL USE:

<?php

try {

    $dsn = 'mysql:host=localhost;dbname=php;charset=utf8mb4';

    $opciones = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false
    ];

    $conex = new PDO(
        $dsn,
        'user_php',
        'user_php',
        $opciones
    );

    // A base de datos activa inicialmente é php

    $conex->exec('USE outra_db');

    // A partir deste momento a base de datos activa é outra_db

} catch (PDOException $e) {

    die('Erro relacionado coa base de datos: ' . $e->getMessage());

} catch (Throwable $e) {

    die('Erro xeral: ' . $e->getMessage());

}
?>

A sentenza USE cambia a base de datos predeterminada da conexión actual. Esta seguirá sendo a base de datos activa ata que se execute outra sentenza USE ou se peche a conexión.

O usuario empregado para realizar a conexión deberá ter os permisos necesarios sobre a nova base de datos.

• Podemos comprobar cal é a base de datos activa empregando:

$stmt = $conex->query('SELECT DATABASE()');

echo $stmt->fetchColumn();

Neste caso, despois de executar:

$conex->exec('USE outra_db');

o código anterior devolvería:

outra_db

IMPORTANTE: O nome da base de datos empregado na sentenza `USE` non debería obterse directamente de datos introducidos polo usuario. Neste módulo empregaremos nomes de bases de datos definidos previamente no código.

Optimización e seguridade: Sentenzas preparadas

• NOTA: A explicación de como se fan consultas ven no seguinte punto, pero adianto agora a explicación de como en PDO se empregan sentenzas prepared.

• Unha sentenza preparada permite separar a instrución SQL dos datos que se van empregar nela.

Primeiro prepárase a sentenza SQL indicando mediante marcadores os valores que se proporcionarán posteriormente. Despois asócianse os datos a eses marcadores e execútase a sentenza.

Por exemplo:

SELECT col1
FROM TABOA1
WHERE col2 = ?

Neste caso a sentenza ten un parámetro, representado polo carácter ?. O valor correspondente proporcionarase posteriormente no momento de executar a sentenza.

Unha mesma sentenza preparada pode executarse varias veces empregando valores diferentes.

• PDO permite utilizar dous tipos de marcadores:

• Marcadores posicionais, representados mediante `?`:

SELECT nome, apelido
FROM alumnos
WHERE idade > ?

• Marcadores con nome, precedidos por `:`:

SELECT nome, apelido
FROM alumnos
WHERE idade > :idade

Nos exemplos dos apuntes empregaremos preferentemente marcadores con nome, xa que normalmente fan que o código sexa máis lexible.

Vantaxes das sentenzas preparadas

• As sentenzas preparadas presentan, entre outras, as seguintes vantaxes:

• Rendemento: cando unha mesma sentenza se executa repetidamente con distintos valores, a base de datos pode reutilizar parte do traballo realizado durante a preparación da sentenza, como a análise e optimización da consulta. Polo tanto, poden resultar especialmente útiles cando unha mesma operación se executa moitas veces.

• Seguridade: os datos enviados mediante parámetros son tratados como valores e non como parte da instrución SQL. Isto axuda a evitar ataques de inxección SQL.

IMPORTANTE: As sentenzas preparadas protexen os valores enviados mediante parámetros, pero isto non significa que calquera consulta sexa automaticamente segura.

Por exemplo, non debemos construír unha consulta concatenando directamente datos recibidos do usuario:

$sql = "SELECT * FROM alumnos WHERE nome = '" . $_POST['nome'] . "'";

A forma correcta será empregar un parámetro:

$sql = 'SELECT * FROM alumnos WHERE nome = :nome';

$stmt = $conex->prepare($sql);

$stmt->execute([
'nome' => $_POST['nome']
]);

Os parámetros dunha sentenza preparada representan valores. Non poden utilizarse directamente para substituír nomes de táboas, nomes de columnas, palabras reservadas SQL ou outras partes estruturais dunha consulta.

Máis información:

Sentenzas preparadas con PDO

PHP empregando PDO

IMPORTANTE: Nos exames, exercicios e prácticas avaliables empregaremos PDO para realizar as operacións contra a base de datos, salvo que no enunciado se indique expresamente o contrario.

• Para preparar unha sentenza empregamos o método:

PDO::prepare()

• Este método devolve un obxecto da clase `PDOStatement`, sobre o que posteriormente chamaremos ao método:

PDOStatement::execute()

Máis información:

• PDO::prepare()
• PDOStatement::execute()

Exemplo

• Supoñamos que queremos inserir datos na táboa `alumnos`:

INSERT INTO alumnos (nome, apelido)
VALUES (:nome, :apelido)

• O código PHP podería ser o seguinte:

 1 <?php
 2 
 3 $conex = null;
 4 
 5 try {
 6 
 7     $dsn = 'mysql:host=localhost;dbname=php;charset=utf8mb4';
 8 
 9     $opciones = [
10         PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
11         PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
12         PDO::ATTR_EMULATE_PREPARES   => false
13     ];
14 
15     $conex = new PDO(
16         $dsn,
17         'user_php',
18         'user_php',
19         $opciones
20     );
21 
22     // Preparamos a sentenza
23     $sql = 'INSERT INTO alumnos (nome, apelido)
24             VALUES (:nome, :apelido)';
25 
26     $stmt = $conex->prepare($sql);
27 
28     // Indicamos os datos e executamos a sentenza
29     $nome = 'Ana';
30     $apelido = 'López';
31 
32     $stmt->execute([
33         'nome'    => $nome,
34         'apelido' => $apelido
35     ]);
36 
37 } catch (PDOException $e) {
38 
39     // Durante o desenvolvemento podemos amosar a mensaxe de erro.
40     // Nunha aplicación en produción non se debería mostrar ao usuario.
41     printf('Erro PDO: %s', $e->getMessage());
42 
43 } catch (Throwable $e) {
44 
45     printf('Erro xeral: %s', $e->getMessage());
46 
47 } finally {
48 
49     $conex = null;
50 
51 }
52 ?>

Executar varias veces a mesma sentenza

• Unha das vantaxes das sentenzas preparadas é que podemos preparar a instrución unha vez e executala varias veces con valores diferentes.

$sql = 'INSERT INTO alumnos (nome, apelido)
VALUES (:nome, :apelido)';

$stmt = $conex->prepare($sql);

// Primeiro alumno
$stmt->execute([
'nome'    => 'Ana',
'apelido' => 'López'
]);

// Segundo alumno
$stmt->execute([
'nome'    => 'Brais',
'apelido' => 'Pérez'
]);

// Terceiro alumno
$stmt->execute([
'nome'    => 'Lucía',
'apelido' => 'García'
]);

A sentenza SQL prepárase unha vez, pero execútase tres veces con valores diferentes.

• Exercicio proposto: Intenta crear unha páxina PHP que engada á táboa 'autores' os seguintes datos:

• Benito Pérez Galdós
• Julio Verne

Solución exercicio:

<?php 
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require ('/var/www/confBD/bd.php');

try {

    $dsn = sprintf(
        'mysql:host=%s;dbname=%s;charset=utf8mb4',
        SERVIDOR,
        BD
    );

    $opciones = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false
    ];

    $conex = new PDO(
        $dsn,
        USUARIO,
        PASSWORD,
        $opciones
    );

    echo '<br />Conecta';

    $sql = 'INSERT INTO autores (nome, apelidos)
            VALUES (:nome, :apelidos)';

    $stmt = $conex->prepare($sql);
    $stmt->execute(['nome'  => 'Benito', 'apelidos' => 'Pérez Galdós']);
    $stmt->execute(['nome'  => 'Julio', 'apelidos' => 'Verne']);

    echo '<br />Datos engadidos';

} catch (PDOException $e) {
    die("Error PDO: " . $e->getMessage());
} catch (Throwable $e) {
    die("Error general: " . $e->getMessage());
} 

$conex = null;   // Poderíamos non poñer nada

Asociar explicitamente os parámetros

• Tamén podemos asociar os valores aos parámetros antes de executar a sentenza empregando, por exemplo, o método `bindValue()`.

$sql = 'SELECT nome, apelido
FROM alumnos
WHERE idade >= :idade';

$stmt = $conex->prepare($sql);

$stmt->bindValue(':idade', 18, PDO::PARAM_INT);

$stmt->execute();

Neste exemplo indicamos explicitamente que o valor asociado ao parámetro `:idade` é de tipo enteiro mediante:

PDO::PARAM_INT

Para datos de tipo cadea podemos empregar:

PDO::PARAM_STR

Nos exemplos máis sinxelos tamén podemos enviar directamente os valores ao método `execute()`:

$stmt->execute([
'idade' => 18
]);

• IMPORTANTE: Se enviamos os parámetros directamente mediante o array de execute(), PDO trátaos por defecto como cadeas (PDO::PARAM_STR). O xestor de base de datos poderá realizar as conversións de tipo necesarias segundo o contexto da consulta e o tipo das columnas.

Se queremos indicar explicitamente o tipo dun parámetro, podemos empregar bindValue() ou bindParam() xunto cunha constante como PDO::PARAM_INT ou PDO::PARAM_STR.

Pode darse o caso de que teñamos unha columna na base de datos definida como numérica o o valor enviado sexa cadea e o interprete de forma erronéa (por exemplo con Limit de SQL que espara un número)

• Exercicio Proposto: Empregando esta forma de paso de parámetros (indicando o tipo) agrega dos novos libros tendo en conta que a columna id é autonumérica e non tes que enviar o dato.

MySQL

NOTA: O seguinte apartado explica como crear sentenzas preparadas directamente desde MySQL. Inclúese a nivel informativo. Nas operacións realizadas desde PHP nos exames empregaremos PDO.

Máis información:

Prepared Statements en MySQL

• Desde o propio xestor MySQL tamén podemos crear e executar sentenzas preparadas mediante:

• `PREPARE`
• `EXECUTE`
• `DEALLOCATE PREPARE`

• Esta funcionalidade pode resultar útil, por exemplo, para construír SQL dinámico dentro dun procedemento almacenado.

As sentenzas `PREPARE`, `EXECUTE` e `DEALLOCATE PREPARE` poden empregarse dentro de procedementos almacenados, pero non dentro de funcións almacenadas ou triggers.

Exemplo de SQL dinámico

• Supoñamos que queremos buscar autores empregando dous criterios opcionais:

• Apelido.
• Nome.

Dependendo dos datos recibidos, a condición podería ser:

WHERE apelido = ?

ou:

WHERE nome = ?

ou:

WHERE apelido = ?
AND nome = ?

Se non se indica ningún criterio, seleccionaranse todas as filas.

• Podemos construír dinamicamente a sentenza mantendo os valores separados da instrución SQL:

DELIMITER $$

CREATE PROCEDURE proba(
IN _apelido VARCHAR(100),
IN _nome VARCHAR(50)
)
BEGIN

```
SET @cadea = 'SELECT nome, apelido
              FROM AUTORES
              WHERE 1 = 1';

-- Gardamos os valores en variables de usuario,
-- xa que EXECUTE ... USING traballa con este tipo de variables
SET @p_apelido = _apelido;
SET @p_nome = _nome;

IF _apelido IS NOT NULL THEN
    SET @cadea = CONCAT(
        @cadea,
        ' AND apelido = ?'
    );
END IF;

IF _nome IS NOT NULL THEN
    SET @cadea = CONCAT(
        @cadea,
        ' AND nome = ?'
    );
END IF;

PREPARE stmt FROM @cadea;

IF _apelido IS NOT NULL AND _nome IS NOT NULL THEN

    EXECUTE stmt USING @p_apelido, @p_nome;

ELSEIF _apelido IS NOT NULL THEN

    EXECUTE stmt USING @p_apelido;

ELSEIF _nome IS NOT NULL THEN

    EXECUTE stmt USING @p_nome;

ELSE

    EXECUTE stmt;

END IF;

DEALLOCATE PREPARE stmt;
```

END$$

DELIMITER ;

• Neste exemplo:

• `@cadea` almacena a sentenza SQL que se vai construíndo.
• Os valores de `_apelido` e `_nome` non se concatenan directamente dentro da instrución SQL.
• Cada valor que se necesita engádese mediante un marcador `?`.
• `PREPARE` prepara a sentenza.
• `EXECUTE ... USING` asocia os valores aos marcadores e executa a sentenza.
• `DEALLOCATE PREPARE` libera a sentenza preparada cando xa non se necesita.

IMPORTANTE: É necesario ter en conta os espazos en branco ao construír dinamicamente a sentenza mediante `CONCAT()`. Por exemplo:

WHERE 1 = 1 AND apelido = ?

debe conter un espazo entre `1 = 1` e `AND`.

Operacións contra a base de datos

• Operacións de consulta
• Operacións de alta
• Operacións de baixa
• Operacións de modificación
• Exercicios propostos

• Transaccións

Permisos mínimos

• Sempre debemos ter en mente a seguridade de acceso aos datos gardados nunha base de datos.

Ata o de agora, o usuario que usamos para acceder á base de datos tiña asinado todos os permisos sobre a mesma, pero esta práctica non é aconsellable.

O ideal é que teña asinado os permisos mínimos necesarios para que poida utilizar a aplicación.

• Cando creamos unha aplicación WEB podemos ter varios supostos no que se refire ao acceso á base de datos.

• Temos creado en MYSQL un usuario diferente por cada usuario, de tal forma que a cadea de conexión á base de datos a crearemos en base ao usuario/password introducido dende unha páxina PHP.
• Utilizamos o mesmo usuario MYSQL para acceder á base de datos e temos unha táboa de usuario/password. En función do usuario validado, este terá acceso a diferentes páxinas PHP con diferentes funcionalidades.
• Utilizamos o mesmo usuario MYSQL para acceder á base de datos e todos os usuarios do sitio web poden ter acceso ás mesmas páxinas.

• En calquera destes supostos, o usuario que empreguemos para acceder a MYSQL debe ter os permisos mínimos para poder funcionar no noso sitio web.

• Partimos de que temos creado un usuario en Mysql, que vai ser o usuario que imos utilizar no noso sitio web para conectarnos a MYSQL e queremos darlle os permisos mínimos.

• CASO 1: A nosa aplicación fai as operacións contra a base de datos empregando sentenzas SQL (SELECT/INSERT/UPDATE/DELETE) deberemos outorgar os permisos e so os permisos necesarios para facer as operacións sobre as táboas da nosa base de datos.

• Para facelo deberemos facer uso da orde grant e orde revoke.

A primeira (grant) serve para outorgar un permiso a un usuario.

A segunda (revoke) serve para quitar un permiso a un usuario.

• No caso de SELECT e UPDATE, podemos outorgar permisos a nivel de columnas, de tal forma que podemos especificar que columnas dunha táboa vai ter permiso para poder ser seleccionadas ou actualizadas.

Se estamos a utilizar o WorkBench, podemos asociar os permisos graficamente.

• Prememos na sección 'User and privileges' (1).
• Prememos o botón 'Add entry' e escollemos o schema (base de datos) sobre a que queremos outorgar os permisos (2).
• Aparecerá o schema na lista (3)^.
• Agora na parte baixa aparecerán os permisos que podemos outorgar. Fixarse que os marcados con (4) son os permisos de SELECT/INSERT/UPDATE/DELETE

• Se eliximos este 'tipo de permisos' estaremos dando o permiso sobre todas as táboas creadas na base de datos.

Se estamos a utilizar a ferramenta Phpmyadmin poderemos facer o mesmo seleccionado a base de datos e indo a opción Privilegios. Aparecerá o usuario creado e poderemos editar os seus permisos.

• Se queremos dar un permiso individual a unha táboa usaremos a orde grant. Esta será a opción a escoller se queremos otorgar un permisos mínimos a nosa base de datos:

Nota: A orde grant, cando fai referencia a un obxecto (coma unha táboa) pode indicarse o nome da base de datos e o nome do obxecto desta forma: bd.obxecto

Nos exemplos seguintes omitimos o nome da base de datos e supoñemos que xa está seleccionada (activa) a base de datos sobre a que imos a executar as ordes.

Permiso 'select' sobre unha táboa.

1 grant select on TABOA to 'novo_usuario';

Permiso 'select' sobre unha columna dunha táboa.

1 grant select (columna) on TABOA to 'novo_usuario';

Permiso 'update' sobre unha táboa.

1 grant update on TABOA to 'novo_usuario';

Permiso 'update' sobre unha columna dunha táboa.

1 grant update (columna) on TABOA to 'novo_usuario';

Para os permisos DELETE e INSERT soamente é necesario poñer na orde grant a operación igual que nos casos anteriores.

• CASO 2: A nosa aplicación fai as operacións contra a base de datos empregando procedementos almacenados.

• No caso anterior, aínda que melloramos a seguridade, o usuario aínda ten acceso ás táboas directamente e pode ver a estrutura das mesmas.

Se queremos aumentar a seguridade podemos utilizar procedementos almacenados.

• Xa vimos anteriormente que empregar ditos procedementos leva consigo:

• Mellor rendemento xa que as sentenzas xa están compiladas e analizadas polo xestor.
• Mellor seguridade xa que é imposible facer ataques de inxección de código.
• Mellor seguridade xa que o usuario non pode ver a estrutura de táboas.
• Mantemento máis doado, xa que podo cambiar a estrutura de táboas sempre que o procedemento continúe devolvendo o mesmo ao usuario.

• IMPORTANTE: Se utilizamos este tipo de 'seguridade' xa non necesitamos outorgar ningún tipo de permiso sobre as táboas.

• O permiso que debemos outorgar para que poida executar un procedemento almacenado é 'EXECUTE'.

• Podemos facelo como no punto anterior facendo uso do WORKBENCH (neste caso daremos permiso de execución sobre todos os procedementos almacenados).
• Usar a orde grant:

Dando permiso de execución a todos os procedementos almacenados.

1 grant execute on *.* to 'novo_usuario'

Dando permiso de execución a un procedemento almacenado.

1 grant execute on procedure NOME_PROCEDEMENTO to 'novo_usuario'

Gardando contrasinais de forma segura

• En moitas aplicacións necesitamos gardar na base de datos información relacionada coa autenticación dos usuarios, como por exemplo os seus contrasinais.

• Os contrasinais nunca deben gardarse directamente na base de datos en texto plano.

Se un atacante consegue acceder á base de datos podería coñecer inmediatamente os contrasinais de todos os usuarios.

• Tampouco debemos empregar algoritmos de hash xenéricos como `MD5`, `SHA1` ou `SHA256` directamente para almacenar contrasinais.

Estes algoritmos están deseñados para calcular hashes de forma moi rápida, característica que facilita que un atacante poida realizar grandes cantidades de intentos mediante ataques de forza bruta.

• Para gardar contrasinais PHP proporciona funcións específicas que empregan algoritmos deseñados para este propósito:

• password_hash()
• password_verify()
• password_needs_rehash()

Recomendacións para os contrasinais

• Actualmente considérase que a lonxitude do contrasinal é un dos factores máis importantes para aumentar a súa seguridade.

• Como recomendación xeral:

• Se o contrasinal é o único factor de autenticación, debería ter como mínimo 15 caracteres.
• Se se emprega como parte dun sistema de autenticación multifactor (MFA), poderá admitirse unha lonxitude mínima de 8 caracteres.
• Débense permitir contrasinais longos ou frases de paso (passphrases).
• Recoméndase permitir unha lonxitude máxima de polo menos 64 caracteres.
• Non é recomendable obrigar ao usuario a incluír necesariamente maiúsculas, minúsculas, números e caracteres especiais.
• Débese evitar o emprego de contrasinais moi coñecidos, previsibles ou que apareceran en filtracións de datos.

Por exemplo, unha frase de paso longa pode ser máis fácil de lembrar e proporcionar maior seguridade que un contrasinal curto construído unicamente para cumprir regras de complexidade.

Máis información:

• NIST SP 800-63B
• OWASP - Authentication Cheat Sheet

Función password_hash()

• Para gardar un contrasinal na base de datos empregaremos a función:

password_hash()

• Por exemplo:

 1 <?php
 2 
 3 $password = 'contrasinal_introducido_polo_usuario';
 4 
 5 $hash = password_hash(
 6     $password,
 7     PASSWORD_DEFAULT
 8 );
 9 
10 echo $hash;
11 
12 ?>

• A constante:

PASSWORD_DEFAULT

indica que PHP empregará o algoritmo considerado predeterminado para almacenar contrasinais.

En PHP 8.5, `PASSWORD_DEFAULT` emprega actualmente bcrypt. Non obstante, esta constante está deseñada para que PHP poida cambiar o algoritmo empregado en futuras versións se aparece unha alternativa máis adecuada.

Por este motivo, a lonxitude do hash podería cambiar no futuro e recoméndase gardalo nun campo suficientemente grande da base de datos, por exemplo:

password VARCHAR(255)

O salt

• Os algoritmos de almacenamento de contrasinais empregan un valor aleatorio denominado salt.

O salt fai, entre outras cousas, que dous usuarios que teñan o mesmo contrasinal obteñan normalmente hashes diferentes.

Tamén dificulta o emprego de táboas precalculadas de hashes, como as chamadas Rainbow Tables.

• Non debemos crear nin proporcionar manualmente o salt.

A función `password_hash()` xera automaticamente un salt seguro e diferente para cada contrasinal.

$hash = password_hash(
$password,
PASSWORD_DEFAULT
);

O algoritmo empregado, os seus parámetros e o salt necesario para comprobar posteriormente o contrasinal están incluídos dentro do propio hash devolto por `password_hash()`.

Polo tanto, non é necesario gardar o salt nun campo separado da base de datos.

IMPORTANTE: En versións antigas de PHP era posible proporcionar manualmente un salt a `password_hash()`. Esta opción está obsoleta desde PHP 7 e foi eliminada en PHP 8.0. Non debe empregarse.

Gardar o contrasinal na base de datos

• Supoñamos que recibimos o contrasinal desde un formulario:

$password = $_POST['password'];

• Primeiro calcularemos o hash:

$hash = password_hash(
$password,
PASSWORD_DEFAULT
);

• O valor que gardaremos na base de datos será $hash e nunca o contrasinal orixinal.

• Por exemplo, empregando PDO e unha sentenza preparada:

 1 $sql = 'INSERT INTO usuarios (nome, password)
 2 VALUES (:nome, :password)';
 3 
 4 $stmt = $conex->prepare($sql);
 5 
 6 $hash = password_hash(
 7 $_POST['password'],
 8 PASSWORD_DEFAULT
 9 );
10 
11 $stmt->execute([
12 'nome'     => $_POST['nome'],
13 'password' => $hash
14 ]);

Comprobar un contrasinal

• Cando un usuario inicia sesión non se calcula manualmente o hash do contrasinal para comparalo co gardado.

• Para comprobar se o contrasinal introducido corresponde co hash almacenado na base de datos empregaremos:

password_verify()

• Por exemplo:

 1 <?php
 2 
 3 $passwordIntroducido = $_POST['password'];
 4 
 5 // Este valor obteríase previamente da base de datos
 6 $hashGardado = $usuario['password'];
 7 
 8 if (password_verify($passwordIntroducido, $hashGardado)) {
 9 
10     echo 'Contrasinal correcto';
11 
12 } else {
13 
14     echo 'Contrasinal incorrecto';
15 
16 }
17 
18 ?>

• `password_verify()` recibe:

1. O contrasinal introducido polo usuario.
2. O hash que temos gardado na base de datos.

A función devolve `true` se o contrasinal se corresponde co hash almacenado ou `false` en caso contrario.

Toda a información necesaria para realizar a comprobación, incluído o algoritmo, os seus parámetros e o salt, está incluída no propio hash.

Actualización do hash

• Os algoritmos ou os seus parámetros poden cambiar co paso do tempo.

PHP proporciona a función:

password_needs_rehash()

que permite comprobar se un hash almacenado debería volver calcularse utilizando a configuración actual.

• Podemos facer esta comprobación despois de validar correctamente o contrasinal:

 1 if (password_verify($passwordIntroducido, $hashGardado)) {
 2 
 3 ```
 4 // O contrasinal é correcto
 5 
 6 if (password_needs_rehash(
 7     $hashGardado,
 8     PASSWORD_DEFAULT
 9 )) {
10 
11     $novoHash = password_hash(
12         $passwordIntroducido,
13         PASSWORD_DEFAULT
14     );
15 
16     // Actualizar $novoHash na base de datos
17 }
18 
19 // Continuar co proceso de autenticación
20 ```
21 
22 }

Desta forma podemos actualizar progresivamente os hashes almacenados cando cambie o algoritmo predeterminado de PHP ou os seus parámetros.

Hash e cifrado non son o mesmo

• Un hash é unha transformación deseñada para non poder obter novamente o valor orixinal.

Por este motivo é adecuada para almacenar contrasinais: non necesitamos coñecer o contrasinal orixinal, unicamente comprobar se o introducido polo usuario é correcto.

• Se necesitamos gardar un dato confidencial pero posteriormente temos que recuperar o seu valor orixinal, non debemos empregar `password_hash()`.

Nese caso necesitaríamos empregar técnicas de cifrado adecuadas para protexer os datos.

IMPORTANTE:

• Para almacenar contrasinais empregaremos `password_hash()`.
• Para comprobar contrasinais empregaremos `password_verify()`.
• Non empregaremos `MD5`, `SHA1` nin hashes xenéricos para almacenar contrasinais.
• Non xeraremos nin gardaremos manualmente o salt.
• Na base de datos gardaremos unicamente o hash xerado por `password_hash()`.
• Recoméndase utilizar un campo como `VARCHAR(255)` para almacenar o hash.

Subindo unha imaxe ao servidor

• Lembrar que normalmente o que subimos á base de datos é a ruta 'relativa' onde se gardan as imaxes no servidor.

• A forma para gardar 'fisicamente' a imaxe no servidor xa a vimos anteriormente neste manual.

-- Ángel D. Fernández González -- (2017).