SQL: Formas Normales

Recuperados ya del fin de semana de rock and roll vamos a volver al código con algo de SQL teórico: la normalización de datos. El diseño lógico de las tablas y sus relaciones es fundamental para la optimización de una base de datos. Existen cinco reglas de normalización que debemos cumplir para aseverar que nuestra base de datos SQL está normalizada, aunque es cierto que hay quien considera que la cuarta y la quinta son rizar el rizo y que con cumplir la tres primeras generalmente ya tenemos una base de datos normalizada. Estas son las cinco formas normales:

  1. Primera Forma Normal: La primera forma requiere la eliminación de todas las columnas duplicadas de una tabla, la separación en otras tablas de esos datos que se duplicarían y la identificación de cada tabla con una atributo de clave primaria. Por ejemplo, si tenemos una tabla donde registramos ventas no guardamos el nombre y el precio del producto vendido en ella varias veces, sino que tendremos una tabla de productos con esos datos separado de la tabla de ventas, y ambas tendrían claves primarias que identifican a cada fila.
  2. Segunda forma Normal: Implica que se cumpla lo dicho en la primera forma normal y que, además, se creen relaciones entre tablas a través de claves externas. Es decir, la tabla Ventas del ejemplo anterior incluye como Clave Externa un valor único que lo relaciona con la tabla Productos, generalmente su clave primaria.
  3. Tercera Forma Normal: Esta norma implica que se cumplan las dos anteriores y que, además, todas las columnas de un registro deben hacer referencia únicamente a la clave primaria, y además elimina todos los atributos derivados. Volvemos al ejemplo: la tabla Productos nos dará el nombre del producto, su precio, en qué almacén se guarda y su fecha de caducidad. No tendremos, por ejemplo, un registro para decir en qué piso del almacén se guarda, ya que ese dato sería de la tabla Almacén. Tampoco tendríamos una columna con los días que faltan hasta que caduque, ya que ese sería un atributo derivado que podemos calcular con la fecha de caducidad.
  4. Cuarta Forma Normal: Agrega un requisito adicional, que es la eliminación de cualquier dependencia multivaluada en las relaciones. Una tabla con una dependencia multivaluada es una donde la existencia de dos o más relaciones independientes muchos a muchos causa redundancia.
  5. Quinta Forma Normal: Rizando el rizo, vendría a decir que sólo se podrían realizar relaciones entre tablas utilizando claves candidatas, con la idea de reducir la redundancia de datos entre múltiples tablas.

A la hora de normalizar bases de datos hay una frase en inglés que lo resume todo: “The key, the whole key, and nothing but the key.” En la propia web de Microsoft la primera recomendación que dan para mejorar el rendimiento de SQL-Server y tener un diseño eficiente de la base de datos es usar un índice autonumérico como clave primaria de cada tabla, identificando así de forma unívoca cada registro y facilitando la relación entre ellos.

PostgreSQL: Obtener todas las fechas que existen entre dos fechas dadas

El otro día veíamos cómo crear en SQL-Server una tabla con todas las fechas entre dos fechas dadas. Hoy vamos a ver cómo hacer un simple select valiéndonos de la función generate_series() para obtener todas las fechas comprendidas entre dos fechas dadas con PostgreSQL

select i::date from generate_series('2017-06-01', 
  '2017-07-31', '1 day'::interval) i

PostgreSQL: Consulta para saber en qué tabla está una columna

Lo hicimos en Oracle, en SQL-Server y en MySQL. No podíamos dejarnos PostgreSQL ¿cómo podemos saber en qué tabla está una columna sabiendo sólo el nombre de dicha columna?

Pues con una consulta sobre la vista columns, que contiene información sobre las columnas de todas las tablas y vistas. Veamos cómo sabríamos en qué tabla está la columna “NombrePaciente” (por poner un ejemplo práctico):

Select table_name
from columns
where column_name like 'NombrePaciente'

En vuestro caso cambiáis NombrePaciente por el nombre de columna que corresponda.

PostgeSQL: Calcular una edad a partir de una fecha.

No hace mucho veíamos cómo calcular una edad en SQL-Server. Vamos ahora con otro SGBD ¿Cómo lo hago en Postgres? Pues con mucha menos dificultad, porque este gestor incluye ya una función integrada para el cálculo de edades, presente al menos desde la versión 9 (no se si antes) llamada age().

La función puede ser llamada con uno o con dos argumentos, siempre del tipo timestamp, y devuelve el interváluo entre ambos. Para el ejemplo imaginemos una tabla llamada clientes donde tenemos un campo FechaNac con la fecha de nacimiento guardada.

/*Con el primer ejemplo
pasando sólo una fecha
cogería la edad a día de hoy
de alguien nacido en esa fecha*/
Select age(timestamp FechaNac) from clientes

/*Con el segundo ejemplo
pasando dos fechas
cogería la edad que una
persona nacida en la fecha 2
tenía en la fecha 1*/

Select age(timestamp '2014-01-01',timestamp FechaNac) from clientes

Script para gestión de backups de PostgreSQL en FreeBSD

No recuerdo si había comentado que estaba en plena migración tecnológica laboral, trabajando con el SGBD libre PostgreSQL y el sistema operativo (libre, of course) FreeBSD. Y el primero de los trasteos era simple ¿cómo automatizo los backups? Ya habíamos hablado de este tipo de cuitas alguna vez por aquí, así que vamos a ver cómo con un script, y haciendo uso de Cron, podemos lograrlo:

#!/bin/sh
# pgsql backups --datosde conexion
db_user="tu_usuario"
db_name="tu_base_de_datos"
db_dumpdir="/var/backups/carpeta"
date_format="`date +%Y-%m-%d.%H:%M`"

#ARRIBA CAMBIA LOS EJEMPLOS POR LOS DATOS QUE CORRESPONDAN. 
 
# BORRAMOS LOS BACKUPS MÁS VIEJOS
# PARA NO SATURAR
# EN ESTE CASO, LOS QUE TENGAN MÁS DE
# 4 SEMANAS

expire_time="+4w"
 
# Begin Script #
/usr/local/bin/pg_dump -U $db_user $db_name -f "$db_dumpdir/$db_name-$date_format.pgsql"
/usr/bin/find $db_dumpdir -d 1 -Btime $expire_time -delete

Con este ya tenemos un script que nos genera un backup de la base de datos en nuestra carpeta de copias de seguridad y además borra los que tengan más de cuatro semanas, para que no saturemos el disco duro. Y ahora, como dijimos antes, necesitamos Crontab para automatizar la copia. Asumamos que guardamos el script en la dirección /usr/local/scripts/pg_backup.sh, porque somos gente ordenadita (al menos a la hora de organizar discos duros). En nuestro caso ejecutaremos el script cada 6 horas:

# crontab -e
* /6 * * * /usr/local/scripts/pg_backup.sh > /dev/null 2>&1

Y con esto ya tenemos la cuestión de automatizar las copias de seguridad solventada.

Consulta SQL para conocer el número de campos de una tabla

Por avatares del destino hoy estaba modificando una página en ASP clásico y necesitaba saber el número de campos que tenía una tabla. Basta con una consulta para tener estos datos:

SELECT Table_Name, COUNT(*) As NumeroCampos
FROM Information_Schema.Columns
WHERE Table_Name = 'nombre_de_la_tabla'
GROUP BY Table_Name;

Si queréis saber el de todas las tablas de la base de datos podéis hacer lo siguiente:

SELECT Table_Name, COUNT(*) As NumeroCampos
FROM Information_Schema.Columns
GROUP BY Table_Name
ORDER BY Table_Name

Evitar SQL-injection en PHP

Los ataques SQL-Injection son unos de los más habituales en el mundo web. Aquí vamos a ver una serie de consejos para evitar estos agujeros en la seguridad:

Lo primero es seguir una serie de consejos a nivel de administración, como limitar los permisos del usuario a nivel de base de datos. Por ejemplo, en la mayoría de aplicaciones web lo habitual es que no tenga que utilizarse DROP, por lo que por seguridad sería mejor no permitir ya al usuario hacerlo. También sería interesante no dar información extra al atacante evitando sacar en pantalla los errores de la base de datos. En ese caso lo ideal es capturar el error haciendo uso de exception. Tampoco hay que confiarlo todo a las validaciones por javascript, porque el atacante puede saltárselas desactivándolo desde el navegador, por lo que mejor hacer las validaciones del lado del servidor. Y finalmente escapar las comillas con msqli_real_scape_string (en caso de que usemos MySQL), pg_scape_string (en caso de PostgreSQL) o addslashes (por si utilizamos otro SGBD). También podemos utilizar htmlentities para convertir los textos en entidades html, como un plus a la seguridad. Abajo un simple ejemplo:


<?php

try{
  $query = sprintf("SELECT * FROM users WHERE id=%d", mysqli_real_escape_string($id));
  $query = htmlentities($query);
  mysqli_query($query);
}catch(Exception $e){
    echo('Lo sentimos, ha habido un error en la conexión');
}
?>

Como medida extra se podrían utilizar expresiones regulares para evitar la inserción de ciertas palabras (SELECT, DROP, UNION…), si bien puede resultar poco práctico, sobre todo si tu software está destinado al mercado británico.

Pero si todo esto te parece lioso, hay una alternativa: la clase PDO. Dicha clase nos facilitará mucho la vida a la hora de trabajar con bases de datos, ya que nos permite abstraernos del SGDB que estemos utilizando. Si por ejemplo, en una página donde no utilices PDO o algo similar sino el mysql_connect simple, decides migrar tu web de MySQL a PostgreSQL tendrías que cambiar todos los métodos del conector de MySQL por los métodos de PostgreSQL. Con PDO bastaría con que cambiaras una sola línea de código, concretamente la de la creación del objeto PDO, y el resto de la aplicación seguiría funcionando. PDO además te permitirá usar consultas parametrizadas (como los Prepared Statements de java) o realizar transacciones. En fin, en el enlace de arriba tenéis todo el manual de PDO para estudiarlo si queréis. Ahora vamos con un simple ejemplo de Prepared Statement, para que veáis lo sencillo que es (dando por sentado para el ejemplo que ya hemos creado el objeto PDO, tal cual está explicado en el manual del enlace).


<?php
  $prepared_statement = $pdo->prepare("SELECT name FROM usuarios WHERE id = :id");/*preparamos la consulta*/
  $prepared_statement->bindParam(':id', $_GET['id'], PDO::PARAM_INT); /*Le pasamos el parámetro, asociado al parámetro de la consulta y definiendo su tipo (si no, por defecto lo trata como string)*/
  $prepared_statement->execute(); /*ejecutamos*/
  $prepared_statement = $statement->fetch(); /*recogemos los resultados, como un array. Se pueden utilizar parámetros para especificar otro tipo de respuesta, como por ejemplo PDO::FETCH_ASSOC para obtener un array asociativo*/
?>

Y con estos breves consejos lograrás que tu página sea más segura. El consejo: utiliza PDO, por ahorrarte comeduras de cabeza, por seguridad y por portabilidad de tu código.