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.

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 / password que teña todos os permisos sobre a base de datos creada. 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 puntos ides atopar que algo se vai poder facer 'por procedementos', 'orientado a obxectos' ou por 'PDO'. NO EXAME PODEDES EMPREGAR PDO ou ORIENTADO A OBXECTOS'.

No que se refiere á versión de php que imos empregar nos exames vai ser php 8.5 ou superior, polo que teredes que empregar a forma indicada nos apuntes para esta versión de php.

Apertura da conexión

• 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.

• Utilizando PDO:

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

No caso de PDO, temos obrigatoriamente que enviar o parámetro dsn, no que indicamos o host, base de datos e xogo de caracteres.

• 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.

• Empregando PDO:

$dsn = 'mysql:host=localhost;dbname=php';
$usuario = 'user_php';
$password = 'user_php';
$conex = new PDO($dsn, $usuario, $password);
$conex->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Neste caso facemos uso de setAttribute para modificar o atributo ATTR_ERRMODE e darlle o valor ERRMODE_EXCEPTION.

O que indica dito atributo é o seguinte:

1 Silencioso	PDO::ERRMODE_SILENT	Non lanza erros. Simplemente devolve false se hai un fallo. Hai que comprobar manualmente cada operación.
2 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.
3 Excepción	PDO::ERRMODE_EXCEPTION	Lanza unha excepción PDOException cando ocorre un erro. Isto permite usar try-catch para capturalo e xestionalo de forma controlada.

Fixarse que a conexión a estamos a facer contra Mysql. 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

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:

• 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());
}

• 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());
}

• Versión con chamada empregando pdo:

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("Error de conexión PDO: (" . $e->getCode() . ") " . $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, 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 }

Dará como resultado:

• En versións de PHP inferior á 8.1

• En caso de ter unha versión PHP anterior á PHP 5.2.9 e 5.3.0 ou estamos a utilizar a versión de chamadas por procedementos, teremos que chamar á función mysqli_connect_error() desta forma:

1 $conex = mysqli_connect('localhost','user_php','user_php','php'); 
2 if (mysqli_connect_error()){
3    die('Erro de Conexión (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
4 }

• En caso de ter unha versión PHP posterior á PHP 5.2.9 e 5.3.0 e estar utilizando a forma orientada a obxectos:

$conex = new mysqli('localhost','user_php','user_php','php'); 
if ($conex->connect_error) {
   die('Erro de Conexión (' . $conex->connect_errno . ') ' . $conex->connect_error);
}

Como vemos mostramos por un lado a mensaxe de erro que devolve o xestor de bases de datos e o número de erro asociado a esa mensaxe.

Podemos consultar os número de erro e mensaxes asociadas neste enlace.

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 para o administrador e que poida ver cal foi o erro.

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 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);

• No caso de PDO:

 1 <?php 
 2 
 3 require ('/var/www/confBD/bd.php');
 4 
 5 
 6 try {
 7     $dsn = sprintf("mysql:host=%s;dbname=%s",SERVIDOR,BD);
 8     $conex = new PDO($dsn, USUARIO, PASSWORD);
 9     $conex->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
10   
11   }
12   catch (PDOException $e) {
13     die("Error de conexión PDO: (" . $e->getCode() . ") " . $e->getMessage());
14   }

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

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   $ruta = substr($_SERVER['DOCUMENT_ROOT'],0,strpos($_SERVER['DOCUMENT_ROOT'],'html')) . 'confBD'; // RUTA al sitio web sin /html pero con /confBD
7   require ($ruta.'/bd.inc.php');
8 
9   $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);

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:

• Versións inferiores a Html 5: <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
• Html 5: <meta charset="UTF-8">

Máis información en w3schools.

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

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.

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

• Versión procedimental

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 }

• Versión PDO:

 1 require ('/var/www/confBD/bd.php');
 2 
 3 try {
 4     $dsn = sprintf("mysql:host=%s;dbname=%s;charset=utf8mb4",SERVIDOR,BD);
 5     $conex = new PDO($dsn, USUARIO, PASSWORD);
 6     $conex->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 7   
 8   }
 9   catch (PDOException $e) {
10     die("Error de conexión PDO: (" . $e->getCode() . ") " . $e->getMessage());
11   }

• E FUNDAMENTAL QUE NOS TRES SITIOS ESTEA POSTO O MESMO TIPO DO XOGO DE CARACTERES.

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 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();
  }
}
?>

• Versión PDO:

<?php

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

try {
    $dsn = sprintf("mysql:host=%s;dbname=%s;charset=utf8mb4",SERVIDOR,BD);
    $conex = new PDO($dsn, USUARIO, PASSWORD);
    $conex->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

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

} catch (PDOException $e) {
    die("Error PDO: " . $e->getMessage());
} catch (Exception $e) {
    die("Error general: " . $e->getMessage());
} finally {
    // Cerrar la conexión asignando null
    if (!empty($conex)) {
        $conex = null;
    }
}
?>

• Versión PHP inferior á 8.1':

• Versión procedimental

  if(!empty($conex)) {
     mysqli_close($conex);
  }

• Versión orientada a obxectos

  if(!empty($conex)){
     $conex->close();
  }

Cambiando de base de datos activa

• Cando creamos a cadea de conexión, un dos parámetros que enviamos foi a base de datos na que iamos traballar.

Podemos non enviar ese parámetro ou querer cambiar de base de datos dentro dunha páxina php.

Se queremos escoller outra diferente teremos que facer uso da función mysqli::select_db na versión procedimental e orientada a obxectos.

No caso de PDO, necesitamos crear unha nova conexión á nova base de datos ou empregar o orde SQL use.

Por exemplo:

• Versión procedimental:

<?php
try {
    $conex = mysqli_connect('localhost', 'user_php', 'user_php');  // Non indicamos o nome da bd
    mysqli_set_charset($conex,'utf8mb4');
    mysqli_select_db($conex, "php");  // O segundo parámetro indica o nome da base de datos

?>
  ..............
  // 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
try {
    $conex = new mysqli('localhost', 'user_php', 'user_php');   // Non indicamos o nome da bd
    $conex->set_charset('utf8mb4');
    $conex->select_db('php'); // O parámetro indica o nome da base de datos

?>

  ..............
  // 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();
  }
}
?>

• Versión PDO:

<?php
try {
    // Conexión inicial á base de datos 'php'
    $dsn1 = 'mysql:host=localhost;dbname=php;charset=utf8mb4';
    $conex = new PDO($dsn1, 'user_php', 'user_php');
    $conex->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "Base de datos inicial: php\n";

    // Cambiar a base de datos activa a 'otra_db'
    $dsn2 = 'mysql:host=localhost;dbname=otra_db;charset=utf8mb4';
    $conex = new PDO($dsn2, 'user_php', 'user_php');
    $conex->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "Base de datos activa cambiada a: otra_db\n";

    // Cambiamos empregando use
    $conex->exec("USE otra_db2");

} catch (PDOException $e) {
    die("Error de conexión PDO: " . $e->getMessage());
}
?>

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'

Optimización: Sentenzas Prepared

• A idea das sentenzas 'prepared' é a de mandar previamente ao Mysql a sentenza que imos executar pero sen datos.

Despois asociaremos a dita sentenza 'prepared' os datos e a executaremos.

Por exemplo:

1 select col1 from TABOA1
2 where col2=?

Neste caso, a sentenza a executar leva un parámetro (o indicado pola interrogación).

Este parámetro se asociará posteriormente e se executará a sentenza. Podemos executar a sentenza moitas veces cambiando o valor do parámetro.

• Preparar as sentenzas ten as vantaxes:

• É máis rápido que enviar as sentenzas da forma 'tradicional' sobre todo cando repetimos a mesma sentenza (con valores diferentes) varias veces. Isto é debido a que cando 'preparamos' a sentenza a estamos a enviar ao xestor MYSQL o cal xa fai as operacións de análise da sentenza e outras optimizacións. Isto soamente o terá que facer unha vez.
• Maior seguridade, xa que fai imposible a inxección de código (o mesmo que pasa cos procedementos almacenados) como vimos anteriormente nesta wiki.

PHP

Enlace.

Un exemplo sinxelo:

 1 <?php
 2 try{
 3  // Conectar á base de datos
 4  $con = new mysqli("localhost", "usuario", "contrasinal", "miña_base_de_datos");
 5 
 6  // Crear a sentencia preparada
 7  $sql = "INSERT INTO alumnos (nome, apelido) VALUES (?, ?)";
 8  $stmt = $con->prepare($sql);
 9 
10  // Vincular parámetros
11  $stmt->bind_param("ss", $nome, $apelido);  // Con s indicamos que deben de ser string. Leva dous eses xa que son dous parámetros
12 
13  // Definir valores e executar. Isto o poderíamos repetir cambiando os valores a enviar.
14  $nome = "Ana";
15  $apelido = "López";
16  $stmt->execute();
17 
18 
19  $stmt->close();
20 
21 }   // Pecha o try da parte superior da páxina. Lembrar que aos usuarios non se lle deben amosar os erros. Isto está posto a nivel informativo para os programadores
22 catch (mysqli_sql_exception $e) {
23     printf("Erro mysql: %s",$e->getMessage());
24 }
25 catch (Expcetion $e) {
26     printf("Erro mysql: %s",$e->getMessage());
27 }
28 finally{
29     if(!empty($conex)){
30         $conex->close();
31     }
32 }
33 ?>

MySQL

Enlace.

• Dende o propio xestor tamén se poden 'executar' sentenzas 'prepared'.

• Esta forma pode sernos moi útil se queremos executar sentenzas SQL que se cren dinamicamente dentro dun procedemento almacenado.

• Por exemplo, se temos unha páxina de busca en base a varios criterios (por exemplo, nome e idade). A sentenza SQL terá unha parte WHERE que pode ser:

• nome=valor1
• idade=valor2
• nome=valor1 and idade=valor2

Vai depender se enviamos ou non o dato teremos que executar unha instrución ou outra.

En vez de ter 3 instrucións SQL dentro do procedemento almacenado en base a se enviamos ou non o dato, podemos 'construír' a sentenza SQL coma unha cadea e cando a teñamos executala con 'PREPARED'

 1 DELIMITER $$
 2 
 3 CREATE DEFINER=`root`@`localhost` PROCEDURE `proba`(_apelido varchar(100),_nome varchar(50))
 4 BEGIN
 5 	
 6 	set @cadea = 'select nome,apelido from AUTORES where 1=1';
 7 	if (_apelido is not null) then
 8 		set @cadea=concat(@cadea,' and apelido=','\'', _apelido,'\'');
 9 	end if;
10 	if (_nome is not null) then
11 		set @cadea=concat(@cadea,' and nome=','\'', _nome,'\'');
12 	end if;
13 
14 	prepare stmt from @cadea;
15 
16 	execute stmt;
17 
18 	deallocate prepare stmt;
19 
20 
21 END

• Neste caso o procedemento buscará nunha táboa AUTORES por un nome, apelido ou nome e apelido concreto. En caso de enviar null nos dous parámetros, seleccionará todas as filas.

Fixarse nos espazos en branco cando concatenedes xa que tedes que formar a frase: WHERE 1=1 and cond=valor. Entre o 1=1 e a condición ten que ir un espazo en branco e o mesmo se engadides máis condicións.

Gardando campos a protexer na base de datos

• En moitas ocasións imos necesitar gardar nunha base de datos información que non queremos que sexa 'visible' en caso de que alguén poida acceder a nosa base de datos.

• Un exemplo típico é o das contrasinais dos usuarios do noso sitio web, no caso de implantar un sitio web cunha validación de usuarios.

• Información sobre a seguridade das passwords.

Como normas, nunca debe estar baseada nun dicionario.

Debe levar caracteres alfanuméricos, maiúsculas e minúsculas e algún carácter non alfanumérico.

Debe ser de a lo menos 10 ou máis caracteres

• Para gardar a información dunha forma segura temos que aplicar un algoritmo de hash.

En PHP temos dispoñible a partires da versión 5, a función de hash.

1 <?php
2 echo hash('ripemd160', 'The quick brown fox jumped over the lazy dog.');
3 ?>

Dará como resultado:

1 ec457d0a974c48d5685a7efa03d137dc8bbde7e3

• En PHP temos funcións de hash específicas dun tipo de algoritmo. Por exemplo a función md5.

1 <?php
2 echo md5('The quick brown fox jumped over the lazy dog.')           // As dúas funcións devolven o mesmo
3 echo hash('md5', 'The quick brown fox jumped over the lazy dog.');  // As dúas funcións devolven o mesmo
4 ?>

• Pero esta forma de 'tratar' a información non chega, xa que se podería descubrir o valor orixinal pola forza bruta.

Para complicalo un pouco máis debemos facer uso de SALT'.

• Salt é unha cadea que se aplica no proceso de hash e fai que sexa practicamente imposible utilizar mecanismos de forza bruta ou Táboas Rainbow para obter a cadea orixinal.

• Exemplo de como utilizar salt na función md5.

1 <?php
2 $password='q121ahgsha..'; // Password introducida polo usuario. Viría dun formulario con $_POST['txtPassword'].
3 $cadeaSalt='unha cadea ao chou';
4 
5 echo md5($cadeaSalt,$password)
6 ?>

Cabe sinalar que se a contrasinal é débil, é posible chegar a rompela, polo que é moi importante que a contrasinal sexa 'forte'.

A cadea SALT pode ser calquera cadea e mellor que non estea baseada en ningún dicionario.

Opción recomendada

• A cadea SALT a podemos proporcionar nos ou podemos xerar unha por medio da función password_hash.

1 $password = password_hash('contrasinal_a_codificar', PASSWORD_DEFAULT);

Codifica a contrasinal utilizando o algoritmo bcrypt e o resultado é unha cadea de 60 caracteres de lonxitude. Seguramente en versións posteriores, o algoritmo vai devolve lonxitudes máis grandes, polo que é recomendable gardar a contrasinal nun campo de 256 caracteres de lonxitede.

Esta forma de codificar fai que o valor SALT cambie de cada vez.

O resultado será o valor que teremos que gardar na base de datos.

• Para realizar o proceso contrario, e comprobar se o password introducido por un usuario se corresponde con o gardado na base de datos, teremos que facer uso da función password_verify().

 1 $orixinal = 'password_introdicada_polo_usuario';
 2 $codificado = '2y$10$ETpCoo11Gd3w2kx4x1me1OQtx.p67c6eRzg6OSRgPxUsBGaQnff4W';
 3  
 4 $iguais = password_verify($orixinal, $codificado);
 5  
 6 if ($iguais) {
 7     echo 'Password correcto';
 8 } else {
 9     echo 'Password incorrecto';
10 }

• Máis información neste enlace.

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).