PHP Operacións BD Mysql
Sumario
- 1 Introdución
- 2 Activar a extensión mysqli
- 3 Creando as táboas en Mysql
- 4 Visualización de errores
- 5 Conexión
- 6 Cambiando de base de datos activa
- 7 Operacións contra a base de datos
- 8 Permisos mínimos
- 9 Optimización: Sentenzas Prepared
- 10 Gardando campos a protexer na base de datos
- 11 Subindo unha imaxe ao servidor
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:
- Interfaz orientada a obxectos
- Soporte para declaracións preparadas.
- Soporte para Transaccións
- 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:
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
- 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 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
- 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');
- 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:
- Versión con chamada por procedemento teremos que chamar á función mysqli_connect_error() desta forma:
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:
- 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:
- 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).
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:
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.
- 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">
- No caso que nos ocupa teremos que poñer:
- 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 }
- Cando conectemos coa base de datos teremos que indicarlle que imos utilizar o xogo de caracteres utf8mb4 con esta liña:
- 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
- Operacións de consulta
- Operacións de alta
- Operacións de baixa
- Operacións de modificación
- Exercicios propostos
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
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
- 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).