PHP Operacións BD Mysql

De MediaWiki
Ir a la navegación Ir a la búsqueda

Introdución

  • Aclaración importante: A partires da versión 8.1 de PHP, as ordes que imos ver a continuación lanzan excepcións. Os exercicios resoltos están feitos con dita versión. Cando cheguedes a sección de Visualización de Errores se indicarán as dúas formas de xestionar os erros.



  • 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
  • Extensión mysqli de PHP
  • Obxectos de Datos de PHP (PDO)


  • Para conectarnos imos utilizar as funcións mysqli (mysql improved) que foron introducidas a partires de Mysql 4.1
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:


Dita extensión tamén ten unha versión procedural, de tal forma que imos poder chamar ás funcións de dúas maneiras diferentes:

Exemplo de función cun estilo procedimental que pecha unha conexión a unha base de datos ($link é un identificador cando se fai a conexión contra a base de datos):

1 mysqli_close($link);

Exemplo de función cun estilo obxectual que pecha unha conexión a unha base de datos:

1 mysqli::close();


  • Aquí tedes un cadro comparativo obtido de php.net onde se ve o comentado anteriormente:
Php BD 2.jpg


Nota: Máis información neste enlace.

Activar a extensión mysqli

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



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"



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


  • 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:
Php BD 6.jpg

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/

Amis.jpg Nome: amis.jpg Dreyfus.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 errores

  • Ao principio ides cometer moitos erros durante as probas de programación.
PHP non vai a amosar nada e simplemente vai amosar unha páxina en branco.


  • Para saber onde está o erro, pódense cambiar as directivas display_errors e error_reporting do php.ini, pero se non queredes cambiar ditos arquivos, podedes poñer ao principio de cada páxina PHP o seguinte:
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);


IMPORTANTE:Lembrar quitar ditas liñas cando rematedes de programar a páxina.




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.

Apertura da conexión

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


  • En caso de erro, $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:
1 try {
2     $conex = mysqli_connect('localhost', 'usuario_inexistente', 'contrasena_incorrecta', 'base_de_datos');
3 } catch (mysqli_sql_exception $e) {
4     die("Erro de conexión: " . $e->getMessage());
5 }
  • Versión con chamada empregando obxectos:
1 try {
2     $conex = new mysqli('localhost', 'usuario_inexistente', 'contrasena_incorrecta', 'base_de_datos');
3 } catch (mysqli_sql_exception $e) {
4     die("Erro de conexión: " . $e->getMessage());
5 }
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.
  • 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.




  • 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:
Php BD 3.jpg



  • 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:
1 $conex = new mysqli('localhost','user_php','user_php','php'); 
2 if ($conex->connect_error) {
3    die('Erro de Conexión (' . $conex->connect_errno . ') ' . $conex->connect_error);
4 }



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


  • Agora, calquera páxina que faga uso destes datos terá que facer o seguinte require:
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.inc.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   //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.
Php BD 7.jpg
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     $conex = mysqli_connect('localhost', 'user_php', 'user_php', 'php');
3      mysqli_set_charset($conex,'utf8mb4');
4 } catch (mysqli_sql_exception $e) {
5     die("Erro de conexión: " . $e->getMessage());
6 }


  • Versión orientada a obxectos
1 try {
2     $conex = new mysqli('localhost', 'user_php', 'user_php', 'php');
3     $conex->set_charset('utf8mb4');
4 } catch (mysqli_sql_exception $e) {
5     die("Erro de conexión: " . $e->getMessage());
6 }



  • 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.
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
try {  // O try se pecha na sección catch do final
    $conex = mysqli_connect('localhost', 'user_php', 'user_php', 'php');
    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
try {
    $conex = new mysqli('localhost', 'user_php', 'user_php', 'php');
    $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 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.
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();
  }
}
?>



Operacións contra a base de datos




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.
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.
Php Perm Minimos 1.jpg
  • 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.
Drupal instalacion mysql 7.jpg



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

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 }






Subindo unha imaxe ao servidor

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






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