¿Cómo y porqué usar PDO para consultar bases de datos en PHP?

Con muy contadas excepciones (si las hay) prácticamente toda aplicación Web interactúa en mayor o menor grado con algún tipo de repositorio de información, usualmente en la forma de una base de datos. Que mejor forma de comenzar este Año Nuevo que explorando cómo podemos conectarnos a una de estas desde un script en PHP y consultar su contenido.

Una rápida introducción al concepto de bases de datos

Las bases de datos son por definición, repositorios de datos. Las hay de diferentes tipos, sabores y colores, pero vamos a centrarnos en las llamadas bases de datos relacionales, que según Wikipedia, cumplen con las siguientes características:

Se componen de varias tablas.
No pueden existir dos tablas con el mismo nombre.
Cada tabla se define como un conjunto de campos (columnas) y registros (filas).
Las llaves primarias son la clave principal de un registro dentro de una tabla y estas deben cumplir con la integridad de datos, esto es, no debe ni puede existir el mismo valor en dos llaves primarias de la misma tabla.
La relación entre una tabla padre y una hija se lleva a cabo por medio de las llaves primarias y llaves foráneas.
Las llaves foráneas se colocan en la tabla hija y contienen el mismo valor que la llave primaria del registro en la tabla padre. En consecuencia, una tabla hija puede estar relacionada con muchas “tablas padre”.

Simple, ¿verdad? Por si las dudas, veámoslo con un ejemplo. Supongamos que tenemos dos tablas, la primera es la tabla “gender”, que cuenta con dos columnas:

La columna “id” es su llave primaria y contiene por tanto un valor numérico único por registro.
La columna “name” tiene por valor el texto con los posibles géneros asociados a una persona.

Y segundo, tenemos la tabla “person”, que contiene los nombres de personajes ilustres de la historia y que entre otras, define las siguientes columnas:

La columna “id” es su llave primaria y contiene por tanto un valor numérico único por registro.
La columna “name” tiene por valor un texto con los nombres de los personajes.
La columna “gender_id”, almacena el valor de referencia al género del personaje según esté definido en la columna “id” de la tabla “gender”.

Así, podemos encontrar datos como los siguientes:

gender            |   person
---------------   |   --------------------------------------
id  name          |   id  name                 gender_id
--  -----------   |   --  -------------------  -------------
1   Masculino     |   1   Albert Einstein      1 (=Masculino)
2   Femenino      |   4   Ada Lovelace         2 (=Femenino)

¿Qué ventaja tiene hacer algo así? ¿Por qué simplemente no se define “gender_id” como una columna de tipo texto y se registra allí “Masculino” o “Femenino”? Bueno, algunas razones me vienen a la cabeza.

Antes de continuar, una breve observación: Como se aprecia en este ejemplo se han usado los nombres en inglés tanto para las tablas como las columnas, esto obedece a que usualmente las bases de datos no permiten el uso de tildes o letras especiales (como la “ñ”). Igualmente puede ignorarse esta limitante y usar nombres en español, pero que no digan después que no quedaron advertidos, cuando tengan que nombrar una columna llamada “año”.

Razones para usar una base de datos relacional

Facilita las búsquedas. Por ejemplo si quieres listar todos los personajes femeninos, el motor de base de datos solo busca el texto entre 2 posibles valores en la tabla “gender” y luego (por el relacionamiento establecido) busca su valor numérico en la tabla “person”. Aquí entre nos y según tengo entendido, para el motor de base de datos es siempre más rápido realizar una búsqueda sobre valores numéricos que sobre textos.

Reduce el espacio ocupado. Imagina que la tabla “person” contenga 1 millón de registros. Esto significa que para almacenar el genero del personaje necesitaría en promedio 9 bytes por registro (asumiendo que cada carácter texto corresponde a 1 byte) en tanto que un campo numérico puede tomar 4 bytes en promedio. Solo con este cambio ya hemos reducido el espacio consumido y el tamaño de la tabla en 4MB. Para los estándares de hoy esa cantidad de espacio puede no parecer mucho, pero ten presente que en este ejemplo usamos una tabla muy sencilla comparada con las que se pueden encontrar en el mundo real, donde una misma tabla puede tener relaciones con muchas otras tablas y almacenar varios millones de registros.

Integridad de los datos. Por definición, si una columna puede tomar una serie limitada de valores, debería en lo posible definirse una relación padre/hija con otra tabla que contenga dichos valores.

Reduce la complejidad en la administración de los datos. Se centraliza la ubicación de datos, garantiza un manejo seguro de los mismos, facilita la realización de copias de seguridad y (casi siempre) el motor de base de datos previene conflictos cuando muchos usuarios realizan consultas al mismo tiempo.

Toda esta teoría es muy interesante (seguro), pero cómo se ve esto reflejado en el mundo real. ¿Cómo se implementan las bases de datos? Bueno, para eso tenemos que referirnos a los motores de bases de datos.

El mundo de los motores de bases de datos

Cómo bien dice el adagio, “para gustos, sabores”. Allí afuera existen muchos diferentes motores de bases de datos relacionales, tanto de dominio público como privadas (es decir, aquellas que requieren pago de licencia). Algunos de los más usados son:

MySQL (y su derivada MariaDB). Quizás la más popular. Actualmente propiedad de Oracle, es todavía considerada como de “dominio público”.
PostgreSQL. Una de las más completas de dominio público.
Microsoft SQL Server. Aunque es privada, Microsoft provee una versión en “licencia libre” bajo ciertas condiciones y limitaciones.
Oracle (privada).
SQLite. Una base de datos ligera, de dominio publico y por defecto integrada en PHP. El framework Laravel a partir de la versión 11 la usa como el motor por defecto para bases de datos.

Para cada uno de estos grupos, existen funciones PHP diferentes para conectar y consultar datos. Es así como, para cada uno de los ejemplos listados arriba, tenemos que la conexión puede realizarse usando estas diferentes opciones:

// MySQL
$link = mysqli_connect($hots, $username, $password, $database);
// PostgreSQL
$dbconn = pg_connect($connection_string, $flags)
// Microsoft SQL Server
$conn = sqlsrv_connect($serverName, $connectionInfo);
// Oracle
$conn = oci_connect($username, $password, $connection_string);
// SQLite
$bd = new SQLite3($database);

Como desarrollador, imagina que tienes no uno, sino varios proyectos que interactúan con un mismo motor de base de datos (digamos MySQL) y que un buen día, por las razones que fueren, necesitas migrarlos a PostgreSQL u otras. Tendrías que entrar a cada proyecto y modificar cada script para que en lugar de usar las funciones mysqli_xxx haga uso de funciones pg_xxx y revisar cada cambio para garantizar su integridad. Eso es mucho, pero mucho trabajo, especialmente si no fuiste juicioso y no centralizaste las consultas para hacerlas desde una única librería. Así las cosas, ¿existe una mejor alternativa?

Afortunadamente la hay y su nombre es PDO.

La magia del PDO

PDO (PHP Data Objects) es una librería PHP para manejo de datos usando Objetos, que nos facilita el realizar consultas a diferentes motores de bases de datos usando el mismo conjunto de métodos, aunque no todos los motores están soportados. Por supuesto, existirán algunas funcionalidades muy propias de cada motor para los que posiblemente PDO no pueda cubrir o garantizar compatibilidad, pero para la mayoría de las cosas que haremos con bases de datos en aplicaciones web de complejidad media o baja (consultas, inserciones y/o actualizaciones), PDO puede resultar una alternativa bastante efectiva.

Para comenzar, lo primero que necesitamos es asegurarnos de cargar los drivers respectivos tanto del motor como de PDO. Para consultas SQLite por ejemplo, esto significa que nuestro PHP debe tener habilitadas las siguientes librerías (drivers) en su archivo de configuración php.ini:

extension=pdo_sqlite
extension=sqlite3

Si en cambio, nuestro proyecto requiere MySQL o MariaDB, se deberían tener habilitadas estas extensiones:

extension=pdo_mysql
extension=mysqli

Y así para cada diferente motor de base de datos que necesitemos usar.

¿Pueden habilitarse drivers para diferentes motores de bases de datos al tiempo? Si. PHP permite habilitar estas librerías simultáneamente, no son excluyentes. Ten presente eso si, que aunque en Desarrollo sea más que normal tener habilitadas múltiples motores de bases de datos, en Producción mientras más cosas tenga que cargar PHP al arrancar, es posible que responda más lento y que más recursos consuma en el servidor.

Una vez habilitados los drivers necesarios, ¿qué otra cosa necesitamos para conectar una base de datos?

En casi todos los escenarios se requieren los siguientes elementos:

Nombre del motor de base de datos (driver).
Identificación del servidor o host donde está operando el motor de base de datos. Puede incluir o no el puerto de conexión a usar.
Nombre del usuario.
Contraseña.
Nombre de la Base de datos a conectar.

Los nombres de driver soportados por PDO son, entre otros, los siguientes (puedes consultar el listado completo en el manual de PHP):

mysql. Soporta MySQL 3.x/4.x/5.x.
pgsql. PostgreSQL.
sqlsrv. Soporte para Microsoft SQL Server y SQL Azure.
oci. Oracle Call Interface.
sqlite. Soporte para SQLite 3 y SQLite 2.

El caso particular de SQLite

Por tratarse de un sistema de bases de datos simplificado, SQLite almacena toda su data en un único archivo. Por tanto, en lugar de indicar un servidor host y/o el nombre de una base de datos, se debe indicar solamente el path completo del archivo que contiene la base de datos.

Es importante recalcar que si el archivo indicado no existe, el controlador lo creará automáticamente. Por esta razón se deben dar los permisos necesarios a la aplicación web para poder crear archivos en el servidor, particularmente en el directorio donde queremos tener el archivo de base de datos.

Tip para configuración de SQLite usando Apache en Windows: SQLite probablemente requiera el uso del archivo libsqlite3.dll que no se encuentra en el directorio apache\bin y deberá copiarse allí usando el archivo que exista en el directorio de PHP.

Consultas SQL, inyección de código y sentencias preparadas

Respecto a las consultas de datos, estas se realizan mediante sentencias SQL. SQL (Structured Query Language o lenguaje de consultas estructuradas) es el “lenguaje” usado para interactuar con bases de datos relacionales, con sus propias reglas y sintaxis. Por ejemplo, para recuperar de nuestra base de datos el listado con los nombres y géneros de personajes, podemos usar esta sentencia:

select person.id, person.name, gender.name as gender 
from person
    left join gender on gender.id = person.gender_id

Para ejecutar esta sentencia o query, usamos un objeto PDO de la siguiente manera:

$result = $pdo->query($query);

Si ejecutáramos esta instrucción correctamente, algunos de los registros a recuperar serían:

id  name             gender
--- ---------------- ---------
1   Albert Einstein  Masculino
4   Ada Lovelace     Femenino
    ...

Iguales pero diferentes

Muchas de las reglas que gobiernan al SQL son comunes a todos los motores, pero existen diferencias en el uso de ciertas funcionalidades. Un ejemplo podemos encontrarlo en la forma de recuperar el primer registro de una consulta. Podemos hacerlo en cualquier motor de la forma regular, recuperando todos los registros en un arreglo y extrayendo luego el primer elemento. Pero este acercamiento no resulta práctico en término de manejo de recursos (tiempo, memoria y acceso a los datos) especialmente si la consulta retorna millones de registros. ¿Recuperar millones cuando solamente necesitamos uno? Definitivamente no es nada práctico.

De nuevo, como dijo Obi-Wan mientras eran jalados sin remedio hacia la Estrella de la Muerte: “existen alternativas”.

En MySQL puede obtenerse el primer registro de una consulta limitando la respuesta en el SQL, así:

select person.id, person.name, gender.name as gender 
from person
    left join gender on gender.id = person.gender_id
limit 0,1

En SQLite, esta misma funcionalidad existe pero se indica de forma ligeramente diferente:

select person.id, person.name, gender.name as gender 
from person
    left join gender on gender.id = person.gender_id
limit 1 offset 0

En tanto que en SQL Server podemos hacerlo de la siguiente forma:

select top 1 
    person.id, person.name, gender.name as gender 
from person
    left join gender on gender.id = person.gender_id

Estas diferencias deberán ser abordadas directamente por el desarrollador, toda vez que PDO directamente no provee un medio para hacerlo. Y como este ejemplo, existen muchas otras funciones que pueden variar de motor en motor, tanto en sintaxis como en ejecución. Esto será algo que habremos de abordar con más cuidado en una próxima entrega.

Sentencias preparadas

El poder de SQL reside en la posibilidad de poder indicar atributos específicos que queremos que estén presentes en una o varias columnas de los datos recuperados, mediante el uso del condicional where. Por ejemplo, si solamente queremos recuperar el listado de mujeres en nuestra base de datos de ejemplo, podemos recurrir a una sentencia SQL como la siguiente:

select person.id, person.name, gender.name as gender 
from person
    left join gender on gender.id = person.gender_id
where gender.name = 'Femenino'

Yendo un poco más allá, podemos permitir que sea el usuario quien decida qué género quiere obtener del listado, lo que podemos lograr en PHP a través de un elemento “gender” recuperado en la variable global $_GET. Por ejemplo:

select person.id, person.name, gender.name as gender 
from person
    left join gender on gender.id = person.gender_id
where gender.name = '{$_GET['gender']}'

Atentos, porque en este ejemplo estamos inyectando (deliberadamente) un valor externo al query, lo que abre la posibilidad a que un “usuario malintencionado” aproveche nuestra falta de precaución e incluya código que puede causar daños en nuestro sistema. ¿Cómo puede eso ser posible? Bueno, si en lugar de un valor de “Masculino” o “Femenino”, que sería lo esperado, el usuario envía algo como “' or person.id > '0”, podemos, al incluirlo directamente en el query, quedar con algo como esto:

select person.id, person.name, gender.name as gender 
from person
    left join gender on gender.id = person.gender_id
where gender.name = '' or person.id > '0'

Este query aparentemente inocente, está solicitando que se recuperen todos los registros y que posiblemente los muestre en pantalla (esto último ya depende por supuesto de cada aplicación). ¿Y qué daño puede hacer eso? Imagina que nuestra tabla no fuera una de personajes históricos sino de usuarios del sistema, tendríamos entonces una violación de seguridad en toda regla. Por supuesto que esto puede prevenirse validando los posibles valores a ingresar y/o previniendo el uso de comillas, pero existe una forma adicional de proteger nuestro query. PDO provee un mecanismo de “sentencias preparadas” con el que automáticamente protege los valores a incluir en el query, previniendo inyecciones de código malicioso como el mostrado y asegurando una sintaxis segura para el uso del SQL.

Para usar estas sentencias preparadas, se remplaza en el query cada valor de búsqueda por un “?” (denominado placeholder) y aparte se indica un arreglo con dichos valores, en el mismo orden de los placeholders dispuestos en el query. De esta forma, el query a usar sería:

select person.id, person.name, gender.name as gender 
from person
    left join gender on gender.id = person.gender_id
where gender.name = ?

Y para ejecutarlo en PHP haríamos uso de las siguientes instrucciones:

$values = [$_GET['gender']];
$result = $pdo->prepare($query);
if ($result !== false) {
	if ($result->execute($values) !== false) {
		. . .
	}
}

Para más información sobre las sentencias preparadas y otras formas de uso, puedes consultar el manual de PHP.

La clase PDOController

Con toda esta teoría ya procesada, es hora de que procedamos a plantear nuestra solución. Para tal fin, definimos una clase PDOController para interactuar con PDO. Para determinar qué propiedades necesitaremos, veamos qué parámetros requiere el objeto PDO para su creación. De acuerdo con el manual de PHP, el objeto se crea de la siguiente manera:

$pdo = new PDO($dsn, $user, $password, $options);

El parámetro $dsn se construye con base en las características del motor de base de datos deseado, que como hemos visto, consta del nombre del driver y el host o path del archivo de base de datos, según sea el caso. Algunos atributos adicionales pueden aplicarse, como por ejemplo el conjunto de caracteres a usar por defecto (charset). En cuanto al valor del parámetro $options, corresponde a características que deseamos en el objeto PDO, tales como:

ATTR_EMULATE_PREPARES = false
Usar “sentencias preparadas” de forma nativa donde esté disponible (a la fecha, están disponibles en los drivers para Oracle, Firebird y MySQL). En los demás drivers, simulará este comportamiento.
ATTR_ERRMODE = ERRMODE_EXCEPTION
Reportar errores en la forma de PHP Exceptions, esto permite capturarlos usando try/catch. Deberá declararse el uso de la clase PDOException para poder hacer uso efectivo de las sentencias try/catch de PHP.
ATTR_DEFAULT_FETCH_MODE = FETCH_ASSOC
Recuperar los datos en un arreglo asociativo. Por defecto, el arreglo de datos retornado contiene tanto las referencias asociativas con el nombre de las columnas, como por la posición, de forma que se duplican los datos recuperados. Al escoger solo uno de los métodos, reducimos la memoria a usar por nuestra aplicación.

La clase podemos entonces definirla así:

use PDO;
use PDOException;

class PDOController {
    // Atributos a usar para crear el objeto PDO
    public string $host = '';
    public string $filename = '';
    public string $user = '';
    public string $password = '';
    // Propiedades privadas
    private string $driverName = '';
    private ?PDO $pdo = null;

    public function __construct(string $driver)
    {
        // Registra nombre del driver deseado
        $this->driverName = $driver;
    }

    /**
     * Abre conexión al motor de base de datos.
     */
    public function connect(string $database = ''): bool
    {
            $options = [
                PDO::ATTR_EMULATE_PREPARES   => false, 
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, 
              ];
            // Construye cadena de conexión
            $elements = [];
            if ($this->filename !== '') {
                // A usar con sqlite y bases de datos similares
                $elements[] = $this->filename;
            }
            if ($this->host !== '') {
                $elements[] = 'host=' . $this->host;
            }
            if ($database !== '') {
                $elements[] = 'dbname=' . $database;
            }
            if ($this->charset !== '') {
                $elements[] = 'charset=' . $this->charset;
            }
            $dsn = $this->driverName . ':' . implode(';', $elements);
            $this->pdo = new PDO($dsn, $this->user, $this->password, $options);        
    }

    /**
     * Realiza consulta SQL.
     */
    public function query(string $query, array $values = []): array
    {
        $data = [];
        if ($query !== '') {
            if (count($values) <= 0 || strpos($query, '?') === false) {
                // No está formateada para usar prepare()
                $result = $this->pdo->query($query);
            }
            else {
                // El arreglo de valores no puede tener llaves asociativas
                $values = array_values($values);
                $result = $this->pdo->prepare($query);
                if ($result !== false) {
                    if ($result->execute($values) === false) {
                        $result = false;
                    }
                }
            }
        }
        if ($result !== false) {
            // Recupera todos los datos
            $data = $result->fetchAll();
        }
        return $data;
    }
}

Nótese que el método query() siempre retorna un arreglo, esto para agilizar el uso de la respuesta. Se recomienda sin embargo implementar un manejo de errores para poder depurar las consultas en caso que fallen, al menos mientras se está en etapa de Desarrollo.

Para usar la clase bastará con configurar primero la conexión y luego ejecutar las consultas SQL sobre nuestra base de datos (que llamaremos “historical_demo”):

$dbo = new PDOController('sqlite');
$dbo->filename = __DIR__ . DIRECTORY_SEPARATOR . 'historical_demo.db';
$dbo->connect();
$query = 'select person.id, person.name, gender.name as gender 
    from person
    left join gender on gender.id = person.gender_id';
$data = $dbo->query($query);
print_r($data);

Y eso es todo, ya tenemos acceso a los datos de nuestra base de datos.

Esta primer versión de nuestra clase da un ejemplo claro de uso de PDO y aunque funciona, puede mejorarse para incluir características tales como:

Validación de errores (captura de Exceptions ocurridas).
Captura de información estadística que sirva para depuración, tal como los tiempos de ejecución. Esto puede servir para mejorar las consultas que toman mucho tiempo, detectar consultas repetidas o incluso, consultas innecesarias.
Permitir la recuperación parcial de registros, por ejemplo para casos donde no sea posible limitar la consulta a través del SQL o donde la cantidad de datos no sea tal que requiera un proceso más “refinado”.
Finalmente, permitir la recuperación manual de registros, para consultas que retornen demasiadas filas y almacenarlas directamente en memoria pueda comprometer la estabilidad de la aplicación. En ese caso, lo recomendado es procesar cada fila y guardar solamente la información necesaria (usando técnicas de caché de ser necesario).

Una versión con características como las descritas puedes consultarla y estudiarla en el repositorio de Github dispuesto en:

miframe/commons/core/PDOController.php

También puedes encontrar una demo funcional de esta librería en:

lekosdev.com:demo-database-pdo.php

¿Qué te ha parecido el desarrollo de este recurso?¿Crees que pueda servirte para tus propias aplicaciones? Habrá más sobre base de datos en próximos artículos, así que date una vuelta por acá para que no te los pierdas.

Quedo atento a tus comentarios y sugerencias en la sección de Comentarios.

¡Hasta una próxima ocasión!

MiCode Manager

Buscar este blog