PHP Data Objects (PDO)

La extensión PDO (PHP Data Objects) permite acceder a distintas bases de datos utilizando las misma funciones, lo que facilita la portabilidad. En PHP 5 existen drivers para acceder a las bases de datos más populares (MySQL, Oracle, MS SQL Server, PostgreSQL, SQLite, Firebird, DB2, Informix, etc). La extensión PDO no evalúa la correción de las consultas SQL, aunque sí implementa algunas medidas de seguridad mediante las consultas preparadas.

En esta lección se explica el acceso a MySQL y SQLite mediante PDO.

Conexión con la base de datos

Para conectar con la base de datos hay que crear una instancia de la clase PDO, que se utiliza en todas las consultas posteriores. En cada página php que incluya consultas a la base de datos es necesario conectar primero con la base de datos.

Si no se puede establecer la conexión con la base de datos, puede deberse a que la base de datos no esté funcionando, a que los datos de usuario no sean correctos, a que no esté activada la extensión pdo o (en el caso de SQLite) que no exista el camino donde se guarda la base de datos.

Conexión con MySQL

En el caso de MySQL, para crear el objeto PDO se necesita proporcionar el nombre del servidor, el nombre de usuario y la contraseña.

Para poder acceder a MySQL mediante PDO, debe estar activada la extensión php_pdo_mysql en el archivo de configuración php.ini (véase el apartado extensión pdo_mysql en la lección de configuración de Apache y PHP).

// FUNCIÓN DE CONEXIÓN CON LA BASE DE DATOS MYSQL
function conectaDb()
{
    try {
        $tmp = new PDO(MYSQL_HOST, MYSQL_USUARIO, MYSQL_PASSWORD);
        $tmp->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
        $tmp->exec("set names utf8mb4");
        return($tmp);
    } catch(PDOException $e) {
        cabecera("Error grave", MENU_PRINCIPAL);
        print "  <p>Error: No puede conectarse con la base de datos.</p>\n\n";
        print "  <p>Error: " . $e->getMessage() . "</p>\n";
        pie();
        exit();
    }
}

// EJEMPLO DE USO DE LA FUNCIÓN ANTERIOR
// La conexión se debe realizar en cada página que acceda a la base de datos
$db = conectaDB();

Conexión con SQLite 3

En SQLite, no se hace una conexión a un servidor, sino que simplemente se indica el archivo que va a contener la base de datos. En SQLite no hay un servidor que gestiona todas las bases de datos, sino que cada base de datos es un archivo independiente (que debe estar situado en un directorio que exista y en el que el servidor web tenga permisos de escritura).

Para poder utilizar SQLite mediante PDO, debe estar activada la extensión php_pdo_sqlite en el archivo de configuración php.ini (véase el apartado extensión pdo_sqlite en la lección de configuración de Apache y PHP).

// FUNCIÓN DE CONEXIÓN CON LA BASE DE DATOS SQLITE
function conectaDb()
{
    global $dbDb;

    try {
        $tmp = new PDO("sqlite:" . $dbDb);
        return($tmp);
    } catch(PDOException $e) {
        cabecera("Error grave", MENU_PRINCIPAL);
        print "  <p>Error: No puede conectarse con la base de datos.</p>\n";
        print "  <p>Error: " . $e->getMessage() . "</p>\n";
        pie();
        exit();
    }
}

// EJEMPLO DE USO DE LA FUNCIÓN ANTERIOR
// La conexión se debe realizar en cada página que acceda a la base de datos
$db = conectaDB();

Notas:

Conexión configurable

Si se incluyen ambas conexiones en el mismo programa, cada usuario puede elegir la base de datos más conveniente en cada caso.

En los ejercicios en este curso se propone al alumno organizar los programas de manera que puedan trabajar tanto con SQLite como con MySQL y hacerlo de forma organizada, para que se puedan añadir fácilmente otras bases de datos.

Para ello se crearán dos bibliotecas, una dedicada a MySQL y otra a SQLite, que contengan las funciones específicas de cada base de datos. Además habrá una biblioteca general en la que se pueda seleccionar la biblioteca a utilizar (MySQL o SQLite). Así, cada página llamará a la biblioteca general y esta llamará a la biblioteca específica.

Por ejemplo, para el caso de la función de conexión, el resultado sería:

Desconexión con la base de datos

Para desconectar con la base de datos hay que destruir el objeto PDO. Si no se destruye el objeto PDO, PHP lo destruye al terminar la página.

$db = null;

Consultas a la base de datos

Una vez realizada la conexión a la base de datos, las operaciones se realizan a través de consultas.

El método para efectuar consultas es PDO->query($consulta), que devuelve el resultado de la consulta. Dependiendo del tipo de consulta, el dato devuelto debe tratarse de formas distintas.


En los ejemplos, se define una variable $consulta que contiene la consulta y a continuación se ejecuta la consulta, pero podría estar en una sola:

// En dos líneas
$consulta = "SELECT * FROM $dbTabla";
$result = $db->query($consulta);

// En una sola línea
$result = $db->query("SELECT * FROM $dbTabla");

Se recomienda utilizar la primera versión, que permite por ejemplo imprimir la consulta mientras se está programando para comprobar que no tiene errores:

$consulta = "SELECT * FROM $dbTabla";
print "<p>Consulta: $consulta</p>\n";
$result = $db->query($consulta);

Seguridad en las consultas: consultas preparadas

Para evitar ataques de inyección SQL (en la lección Inyecciones SQL se comentan los ataques más elementales), se recomienda el uso de sentencias preparadas, en las que PHP se encarga de "desinfectar" los datos en caso necesario. En general, cualquier consulta que incluya datos introducidos por el usuario debe realizarse mediante consultas preparadas.

Consultas preparadas

El método para efectuar consultas es primero preparar la consulta con PDO->prepare($consulta) y después ejecutarla con PDO->execute(array(parámetros)), que devuelve el resultado de la consulta.

// Consulta preparada
$consulta = "SELECT * FROM $dbTabla";
$result = $db->prepare($consulta);
$result->execute();

Dependiendo del tipo de consulta, el dato devuelto debe tratarse de formas distintas, como se ha explicado en el apartado anterior.


Si la consulta incluye datos introducidos por el usuario, los datos pueden incluirse directamente en la consulta, pero en ese caso, PHP no realiza ninguna "desinfección" de los datos, por lo que estaríamos corriendo riesgos de ataques:

$nombre    = $_REQUEST["nombre"];
$apellidos = $_REQUEST["apellidos"];

$consulta = "SELECT COUNT(*) FROM $dbTabla
    WHERE nombre=$nombre
    AND apellidos=$apellidos";                 // DESACONSEJADO: PHP NO DESINFECTA LOS DATOS
$result = $db->prepare($consulta);
$result->execute();
if (!$result) {
    print "  <p>Error en la consulta.</p>\n";
 ...
 

Para que PHP desinfecte los datos, estos deben enviarse al ejecutar la consulta, no al prepararla. Para ello es necesario indicar en la consulta la posición de los datos. Esto se puede hacer de dos maneras, mediante parámetros o mediante interrogantes, aunque se aconseja la utilización de parámetros:


Aunque no vayan a causar problermas en las consultas, sigue siendo conveniente tratar los datos recibidos para eliminar los espacios en blanco iniciales y finales, tratar los caracteres especiales del html, etc., como se comenta en la lección de Recogida de datos.

Restricciones en los parámetros de consultas preparadas

Debido a que las consultas preparadas se idearon para optimizar el rendimiento de las consultas, el uso de parámetros tiene algunas restricciones. Por ejemplo

Si no podemos usar parámetros, no queda más remedio que incluir los datos en la consulta. Como en ese caso PHP no hace ninguna desinfección de los datos, la tenemos que hacer nosotros previamente.

Como en estos casos los valores introducidos por el usuario suelen tener unos valores restringidos (por ejemplo, si el usuario puede elegir una columna de una tabla, los nombres de las columnas están determinadas y el usuario sólo puede elegir uno de ellos). Podemos crear una función de recogida de datos específica que impida cualquier tipo de ataque de inyección por parte del usuario, como muestra el siguiente ejemplo

// FUNCIÓN DE RECOGIDA DE UN DATO QUE SÓLO PUEDE TOMAR DETERMINADOS VALORES
$campos = array(
    "nombre",
    "apellidos");

function recogeCampo($var, $var2)
{
    global $campos;

    foreach($campos as $campo) {
        if (isset($_REQUEST[$var]) && $_REQUEST[$var] == $campo) {
            return $campo;
        }
    }
    return $var2;
}

// EJEMPLO DE USO DE LA FUNCIÓN ANTERIOR
$campo  = recogeCampo("campo", "apellidos");
$nombre = $_REQUEST["nombre"];

$consulta = "SELECT * FROM $dbTabla
    WHERE nombre=:nombre
    ORDER BY $campo ASC";
$result = $db->prepare($consulta);
$result->execute(array(":nombre" => $nombre));
if (!$result) {
    print "  <p>Error en la consulta.</p>\n";
 ...
 

Ejemplos de consultas

En los ejemplos de este apartado, se han utilizado sentencias preparadas en los casos en los que las consultas incluyen datos proporcionados por el usuario y consultas no preparadas cuando no incluyan datos proporcionados por el usuario. En la mayoría de los casos se podrían haber utilizado sentencias preparadas aunque no haya datos proporcionados por el usuario.

Consultas CREATE DATABASE, DROP DATABASE, CREATE TABLE

Estas consultas no son iguales en MySQL y SQLite. En los ejercicios propuestos para que se pueda utilizar una u otra base de datos, estas consultas se incluyen en las bibliotecas específicas.

Consultas en MySQL

Para crear una base de datos, se utiliza la consulta CREATE DATABASE.

// EJEMPLO DE CONSULTA DE CREACIÓN DE BASE DE DATOS EN MYSQL
$consultaCreaDb = "CREATE DATABASE $dbDb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci";
if ($db->query($consulta)) {
    print "  <p>Base de datos creada correctamente.</p>\n";
} else {
    print "  <p>Error al crear la base de datos.</p>\n";
}

Nota: El juego de caracteres utilizado en este curso es UTF-8, por lo que en la base de datos MySQL se utiliza el juego de caracteres utf8mb4 (que permite almacenar cualquier carácter Unicode) y el cotejamiento utf8mb4_unicode_ci (que implementa todos los criterios de ordenación de Unicode). Para una explicación más detallada se puede consultar el blog de Mathias Bynens.

Para borrar una base de datos, se utiliza la consulta DROP DATABASE.

// EJEMPLO DE CONSULTA DE BORRADO DE BASE DE DATOS EN MYSQL
$consulta = "DROP DATABASE $dbDb";
if ($db->query($consulta)) {
    print "  <p>Base de datos borrada correctamente.</p>\n";
} else {
    print "  <p>Error al borrar la base de datos.</p>\n";
}

Para crear una tabla, se utiliza la consulta CREATE TABLE. Las consultas de creación de tabla suelen ser específicas de cada base de datos. El ejemplo no utiliza sentencias preparadas (en caso de utilizarse sentencias preparadas, las variables no podrían ir como parámetros por tratarse de sentencias DDL).

// EJEMPLO DE CONSULTA DE CREACIÓN DE TABLA EN MYSQL
$consulta = "CREATE TABLE $dbTabla (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    nombre VARCHAR($tamNombre),
    apellidos VARCHAR($tamApellidos),
    PRIMARY KEY(id)
    )";
if ($db->query($consulta)) {
    print "  <p>Tabla creada correctamente.</p>\n";
} else {
    print "  <p>Error al crear la tabla.</p>\n";
}

Consultas en SQLite

En el caso de utiliza SQLite, no tiene sentido crear o borrar la base de datos ya que con SQLite cada base de datos es un fichero distinto y al conectar con la base de datos ya se dice con qué archivo se va a trabajar y se crea en caso necesario. Es suficiente borrar y crear las tablas.

// EJEMPLO DE CONSULTA DE BORRADO DE TABLA EN SQLITE
$consulta = "DROP TABLE $dbTabla";
if ($db->query($consulta)) {
    print "  <p>Tabla borrada correctamente.</p>\n";
} else {
    print "  <p>Error al borrar la tabla.</p>\n";
}

Para crear una tabla, se utiliza la consulta CREATE TABLE. Las consultas de creación de tabla suelen ser específicas de cada base de datos. El ejemplo no utiliza sentencias preparadas (en caso de utilizarse sentencias preparadas, las variables no podrían ir como parámetros por tratarse de sentencias DDL).

// EJEMPLO DE CONSULTA DE CREACIÓN DE TABLA EN SQLite
$consulta = "CREATE TABLE $dbTabla (
    id INTEGER PRIMARY KEY,
    nombre VARCHAR($tamNombre),
    apellidos VARCHAR($tamApellidos)
    )";
if ($db->query($consulta)) {
    print "  <p>Tabla creada correctamente.</p>\n";
} else {
    print "  <p>Error al crear la tabla.</p>\n";
}

Solución configurable

El resultado sería

  • en el fichero que quiera reiniciar la base de datos se llamaría a la biblioteca general y se llamaría a la función genérica borraTodo():
    // EJEMPLO DE USO DE CONEXIÓN CONFIGURABLE// La conexión se debe realizar en cada página que acceda a la base de datos
    require_once "biblioteca.php";
    $db = conectaDb();
    borraTodo($db);
    $db = null;
    
  • biblioteca_mysql.php: contiene la definición de la función borraTodo() específica para trabajar con MySQL y que borra la base de datos, la crea y crea la tabla:
        // biblioteca_mysql.php
    $consultaCreaTabla = "CREATE TABLE $dbTabla (
        id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
        nombre VARCHAR($tamNombre),
        apellidos VARCHAR($tamApellidos),
        PRIMARY KEY(id)
        )";
    
    function borraTodo($db)
    {
        global $dbDb, $consultaCreaTabla;
    
        $consulta = "DROP DATABASE $dbDb";
        if ($db->query($consulta)) {
            print "  <p>Base de datos borrada correctamente.</p>\n\n";
        } else {
            print "  <p>Error al borrar la base de datos.</p>\n\n";
        }
        $consulta = "CREATE DATABASE $dbDb";
        if ($db->query($consulta)) {
            print "  <p>Base de datos creada correctamente.</p>\n\n";
            $consulta = $consultaCreaTabla;
            if ($db->query($consulta)) {
                print "  <p>Tabla creada correctamente.</p>\n";
            } else {
                print "  <p>Error al crear la tabla.</p>\n";
            }
        } else {
            print "  <p>Error al crear la base de datos.</p>\n";
        }
    }
    
  • biblioteca_sqlite.php: contiene la definición de la función borraTodo() específica para trabajar con SQLite y que borra la tabla y la crea:
    // biblioteca_sqlite.php
    $consultaCreaTabla = "CREATE TABLE $dbTabla (
        id INTEGER PRIMARY KEY,
        nombre VARCHAR($tamNombre),
        apellidos VARCHAR($tamApellidos)
        )";
    
    function borraTodo($db)
    {
        global $dbTabla, $consultaCreaTabla;
    
        $consulta = "DROP TABLE $dbTabla";
        if ($db->query($consulta)) {
            print "  <p>Tabla borrada correctamente.</p>\n\n";
        } else {
            print "  <p>Error al borrar la tabla.</p>\n\n";
        }
        $consulta = $consultaCreaTabla;
        if ($db->query($consulta)) {
            print "  <p>Tabla creada correctamente.</p>\n";
        } else {
            print "  <p>Error al crear la tabla.</p>\n";
        }
    }
    

Consultas DROP TABLE, INSERT INTO, UPDATE, DELETE FROM

Para borrar una tabla, se utiliza la consulta DROP TABLE.

// EJEMPLO DE CONSULTA DE BORRADO DE TABLA
$consulta = "DROP TABLE $dbTabla";
if ($db->query($consulta)) {
    print "  <p>Tabla borrada correctamente.</p>\n";
} else {
    print "  <p>Error al borrar la tabla.</p>\n";
}

Para añadir un registro a una tabla, se utiliza la consulta INSERT INTO.

// EJEMPLO DE CONSULTA DE INSERCIÓN DE REGISTRO
$nombre    = recoge("nombre");
$apellidos = recoge("apellidos");

$consulta = "INSERT INTO $dbTabla
    (nombre, apellidos)
    VALUES (:nombre, :apellidos)";
$result = $db->prepare($consulta);
if ($result->execute(array(":nombre" => $nombre, ":apellidos" => $apellidos))) {
    print "  <p>Registro creado correctamente.</p>\n";
} else {
    print "  <p>Error al crear el registro.</p>\n";
}

Para modificar un registro a una tabla, se utiliza la consulta UPDATE.

// EJEMPLO DE CONSULTA DE MODIFICACIÓN DE REGISTRO
$nombre    = recoge("nombre");
$apellidos = recoge("apellidos");
$id        = recoge("id");

$consulta = "UPDATE $dbTabla
    SET nombre=:nombre, apellidos=:apellidos
    WHERE id=:id";
$result = $db->prepare($consulta);
if ($result->execute(array(":nombre" => $nombre, ":apellidos" => $apellidos, ":id" => $id))) {
    print "  <p>Registro modificado correctamente.</p>\n";
} else {
    print "  <p>Error al modificar el registro.</p>\n";
}

Para borrar un registro de una tabla, se utiliza la consulta DELETE FROM.

Nota: En el ejemplo, los registros a borrar se reciben en forma de matriz y se recorre la matriz borrando un elemento en cada iteración.

// EJEMPLO DE CONSULTA DE BORRADO DE REGISTRO
$id = recogeMatriz("id");

foreach ($id as $indice => $valor) {
    $consulta = "DELETE FROM $dbTabla
        WHERE id=:indice";
    $result = $db->prepare($consulta);
    if ($result->execute(array(":indice" => $indice))) {
        print "  <p>Registro borrado correctamente.</p>\n";
    } else {
        print "  <p>Error al borrar el registro.</p>\n";
    }
}

Consulta SELECT

Para obtener registros que cumplan determinados criterios se utiliza una consulta SELECT.

Consulta SELECT LIKE

La consulta SELECT permite efectuar búsquedas en cadenas utilizando el condicional LIKE o NOT LIKE y los comodines _ (cualquier carácter) o % (cualquier número de caracteres).

Ejemplos de consultas:

Consultas de unión de tablas

Se pueden también realizar consultas de unión entre varias tablas (el ejemplo está sacado del ejercicio de Biblioteca):

Nota: Escribir como consulta preparada.

// EJEMPLO DE CONSULTA DE UNIÓN DE TABLAS
$consulta = "SELECT $dbPrestamos.id AS id, $dbUsuarios.nombre as nombre,
    $dbUsuarios.apellidos as apellidos, $dbObras.titulo as titulo,
    $dbPrestamos.prestado as prestado, $dbPrestamos.devuelto as devuelto
    FROM $dbPrestamos, $dbUsuarios, $dbObras
    WHERE $dbPrestamos.id_usuario=$dbUsuarios.id AND
    $dbPrestamos.id_obra=$dbObras.id and $dbPrestamos.devuelto='0000-00-00'
    ORDER BY $campo $orden";
$result = $db->query($consulta);
if (!$result) {
    print "  <p>Error en la consulta.</p>\n";
} else {
   ...