Sentencias SQL básicas: SELECT, INSERT, UPDATE, DELETE

En el desarrollo de aplicaciones web con PHP, es fundamental interactuar con bases de datos para almacenar y recuperar información. Las sentencias SQL básicas que permiten realizar operaciones CRUD (Crear, Leer, Actualizar y Borrar) son esenciales para manipular datos en una base de datos relacional.

SELECT: Recuperación de datos

La sentencia SELECT se utiliza para consultar y recuperar datos de una o varias tablas en la base de datos. A continuación se muestra un ejemplo de cómo realizar una consulta básica utilizando PDO:

<?php
// Conexión a la base de datos
$dsn = 'mysql:host=localhost;dbname=tienda';
$usuario = 'usuario';
$contraseña = 'contraseña';

try {
    $pdo = new PDO($dsn, $usuario, $contraseña);

    // Consulta SELECT
    $sql = 'SELECT nombre, precio FROM productos';
    $stmt = $pdo->query($sql);

    // Recorrer los resultados
    while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo 'Producto: ' . $fila['nombre'] . ' - Precio: ' . $fila['precio'] . "\n";
    }
} catch (PDOException $e) {
    echo 'Error de conexión: ' . $e->getMessage() . "\n";
}

En este ejemplo, se realiza una consulta para obtener los nombres y precios de los productos de la tabla productos. Se utiliza el método query para ejecutar la sentencia y fetch para recorrer los resultados.

INSERT: Inserción de datos

La sentencia INSERT permite agregar nuevos registros a una tabla. El siguiente ejemplo muestra cómo insertar un nuevo producto en la tabla productos:

<?php
// Datos del nuevo producto
$nombre = 'Nuevo Producto';
$precio = 19.99;

try {
    // Sentencia INSERT
    $sql = "INSERT INTO productos (nombre, precio) VALUES ('$nombre', $precio)";
    $filasAfectadas = $pdo->exec($sql);
    echo "Se insertaron $filasAfectadas registro(s).\n";
} catch (PDOException $e) {
    echo 'Error al insertar: ' . $e->getMessage() . "\n";
}

En este caso, se utiliza el método exec para ejecutar la sentencia INSERT. Se debe tener cuidado con la inyección SQL, y aunque este ejemplo usa valores directos para ilustrar el concepto, es recomendable utilizar sentencias preparadas para mayor seguridad.

UPDATE: Actualización de datos

La sentencia UPDATE sirve para modificar registros existentes en una tabla. A continuación, se muestra cómo actualizar el precio de un producto específico:

<?php
// ID del producto a actualizar
$idProducto = 1;
$nuevoPrecio = 24.99;

try {
    // Sentencia UPDATE
    $sql = "UPDATE productos SET precio = $nuevoPrecio WHERE id = $idProducto";
    $filasAfectadas = $pdo->exec($sql);
    echo "Se actualizaron $filasAfectadas registro(s).\n";
} catch (PDOException $e) {
    echo 'Error al actualizar: ' . $e->getMessage() . "\n";
}

Aquí, el método exec se utiliza nuevamente para ejecutar la sentencia UPDATE. Es importante especificar una condición WHERE para evitar modificar todos los registros de la tabla.

DELETE: Eliminación de datos

La sentencia DELETE se utiliza para eliminar registros de una tabla. El siguiente ejemplo muestra cómo eliminar un producto dado su ID:

<?php
// ID del producto a eliminar
$idProducto = 2;

try {
    // Sentencia DELETE
    $sql = "DELETE FROM productos WHERE id = $idProducto";
    $filasAfectadas = $pdo->exec($sql);
    echo "Se eliminaron $filasAfectadas registro(s).\n";
} catch (PDOException $e) {
    echo 'Error al eliminar: ' . $e->getMessage() . "\n";
}

Al igual que con UPDATE, se debe tener precaución al usar DELETE y siempre incluir una condición WHERE para evitar borrar todos los registros.

Uso de prepare y execute para consultas seguras (Prepared Statements)

El uso de sentencias preparadas es una práctica esencial para garantizar la seguridad y eficiencia en las operaciones con bases de datos en PHP. Las sentencias preparadas permiten separar la lógica SQL de los datos, evitando riesgos como la inyección SQL y mejorando el rendimiento en consultas repetitivas.

En PDO, se utilizan los métodos prepare y execute para trabajar con sentencias preparadas. A continuación, se muestra cómo utilizar estas funciones para realizar operaciones seguras en la base de datos.

Preparación de una sentencia SQL

El método prepare se utiliza para preparar una sentencia SQL con marcadores de posición (placeholders) en lugar de valores reales. Existen dos tipos de marcadores de posición: posicionales (uso de signos de interrogación ?) y nombrados (uso de nombres precedidos por dos puntos :).

Ejemplo de preparación con marcadores posicionales:

<?php
// Conexión a la base de datos
$dsn = 'mysql:host=localhost;dbname=tienda';
$usuario = 'usuario';
$contraseña = 'contraseña';

try {
    $pdo = new PDO($dsn, $usuario, $contraseña);

    // Sentencia preparada con marcadores posicionales
    $sql = 'SELECT nombre, precio FROM productos WHERE categoria = ?';
    $stmt = $pdo->prepare($sql);
} catch (PDOException $e) {
    echo 'Error de conexión: ' . $e->getMessage() . "\n";
}

En este ejemplo, la sentencia SQL incluye un marcador de posición ? que será reemplazado con un valor real al momento de la ejecución.

Ejecución de la sentencia con parámetros

Una vez preparada la sentencia, se utiliza el método execute para ejecutarla pasando los valores correspondientes a los marcadores de posición.

Ejemplo de ejecución con un marcador posicional:

<?php
// Valor del parámetro
$categoria = 'Electrónica';

// Ejecución de la sentencia preparada
$stmt->execute([$categoria]);

// Recorrer los resultados
while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo 'Producto: ' . $fila['nombre'] . ' - Precio: ' . $fila['precio'] . "\n";
}

En este caso, el valor de la variable $categoria reemplaza al marcador ? en la sentencia SQL de forma segura.

Uso de marcadores nombrados

Los marcadores nombrados permiten una mayor legibilidad y flexibilidad al asociar valores a nombres específicos. La sintaxis utiliza dos puntos seguidos de un nombre (:nombreParametro).

Ejemplo de preparación y ejecución con marcadores nombrados:

<?php
// Sentencia preparada con marcadores nombrados
$sql = 'INSERT INTO productos (nombre, precio, categoria) VALUES (:nombre, :precio, :categoria)';
$stmt = $pdo->prepare($sql);

// Valores de los parámetros
$datos = [
    ':nombre' => 'Teclado Mecánico',
    ':precio' => 59.99,
    ':categoria' => 'Informática'
];

// Ejecución de la sentencia preparada
$stmt->execute($datos);
echo "Producto insertado correctamente.\n";

Aquí, los nombres de los marcadores (:nombre, :precio, :categoria) se asocian a los valores correspondientes en el array $datos. Esto facilita la asignación de múltiples parámetros y mejora la claridad del código.

Operaciones de actualización y eliminación

Las sentencias preparadas también se utilizan en operaciones de UPDATE y DELETE para mantener la seguridad y eficiencia.

Ejemplo de actualización de un registro:

<?php
// Sentencia preparada para UPDATE con marcadores nombrados
$sql = 'UPDATE productos SET precio = :precio WHERE id = :id';
$stmt = $pdo->prepare($sql);

// Valores de los parámetros
$datos = [
    ':precio' => 49.99,
    ':id' => 3
];

// Ejecución de la sentencia preparada
$stmt->execute($datos);
echo "Producto actualizado correctamente.\n";

Ejemplo de eliminación de un registro:

<?php
// Sentencia preparada para DELETE con marcadores posicionales
$sql = 'DELETE FROM productos WHERE id = ?';
$stmt = $pdo->prepare($sql);

// Ejecución de la sentencia preparada con valor del parámetro
$stmt->execute([5]);
echo "Producto eliminado correctamente.\n";

En ambos casos, el uso de sentencias preparadas garantiza que los valores se tratan de manera segura, evitando posibles vulnerabilidades.

Ventajas de las sentencias preparadas

• Seguridad: Protegen contra inyección SQL al separar el código SQL de los datos proporcionados por el usuario.
• Rendimiento: Las sentencias se analizan y compilan una sola vez, lo que mejora la eficiencia en consultas repetitivas.
• Flexibilidad: Facilitan la reutilización de código y la asignación dinámica de valores en las consultas.

Implementar sentencias preparadas con prepare y execute es una práctica recomendada para desarrollar aplicaciones PHP robustas y seguras. Adoptar este enfoque mejora significativamente la calidad y confiabilidad de la interacción con la base de datos.

Vinculación de parámetros para evitar inyección SQL

La inyección SQL es una amenaza seria para la seguridad de las aplicaciones web que interactúan con bases de datos. Ocurre cuando un atacante inserta código SQL malicioso en los campos de entrada, alterando la ejecución de las consultas y obteniendo acceso no autorizado a los datos. Para mitigar este riesgo, es fundamental utilizar la vinculación de parámetros en las sentencias preparadas.

La vinculación de parámetros garantiza que los datos proporcionados por el usuario se traten como valores literales y no como parte del código SQL. En PDO, esto se logra mediante los métodos bindParam y bindValue, que asocian los valores a los marcadores de posición de la consulta de forma segura.

Por ejemplo, una consulta vulnerable podría ser:

<?php
// Valor recibido del usuario
$username = $_GET['username'];

// Consulta insegura propensa a inyección SQL
$sql = "SELECT * FROM usuarios WHERE username = '$username'";
$stmt = $pdo->query($sql);

// Procesar resultados
while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo 'Usuario: ' . $fila['username'] . ' - Email: ' . $fila['email'] . "\n";
}

En este caso, si el usuario introduce código malicioso en $username, puede manipular la consulta. Para evitarlo, utilizamos una sentencia preparada y vinculamos el parámetro:

<?php
// Valor recibido del usuario
$username = $_GET['username'];

// Sentencia preparada con marcador de posición
$sql = 'SELECT * FROM usuarios WHERE username = :username';
$stmt = $pdo->prepare($sql);

// Vinculación del parámetro de forma segura
$stmt->bindParam(':username', $username, PDO::PARAM_STR);

// Ejecución de la sentencia
$stmt->execute();

// Procesar resultados
while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo 'Usuario: ' . $fila['username'] . ' - Email: ' . $fila['email'] . "\n";
}

Mediante bindParam, asociamos el marcador :username con la variable $username, asegurando que cualquier contenido se trate como un valor de cadena y no como código SQL.

La diferencia entre bindParam y bindValue radica en cómo manejan las variables:

• bindParam: Vincula una variable al parámetro de la sentencia. Si la variable cambia después de la vinculación pero antes de la ejecución, el nuevo valor se utilizará al ejecutar execute().
• bindValue: Vincula un valor (no una variable) al parámetro. El valor se fija en el momento de la vinculación y no cambia.

Uso de bindValue:

<?php
// Valor recibido del usuario
$username = $_GET['username'];

// Sentencia preparada
$sql = 'SELECT * FROM usuarios WHERE username = :username';
$stmt = $pdo->prepare($sql);

// Vinculación del valor directamente
$stmt->bindValue(':username', $username, PDO::PARAM_STR);

// Ejecución de la sentencia
$stmt->execute();

// Procesar resultados
while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo 'Usuario: ' . $fila['username'] . ' - Email: ' . $fila['email'] . "\n";
}

Ambos métodos protegen contra inyección SQL, pero bindValue es útil cuando el valor no cambiará antes de la ejecución.

También es posible pasar los parámetros directamente en el método execute, lo que simplifica el código:

<?php
// Valor recibido del usuario
$username = $_GET['username'];

// Sentencia preparada
$sql = 'SELECT * FROM usuarios WHERE username = :username';
$stmt = $pdo->prepare($sql);

// Ejecución de la sentencia con parámetros
$stmt->execute([':username' => $username]);

// Procesar resultados
while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo 'Usuario: ' . $fila['username'] . ' - Email: ' . $fila['email'] . "\n";
}

Al proporcionar un array asociativo a execute, los valores se vinculan de manera segura a sus marcadores correspondientes, evitando la inyección SQL.

La vinculación de parámetros también permite especificar el tipo de dato, lo que facilita el manejo correcto de los valores. Por ejemplo, al trabajar con números enteros:

<?php
// ID recibido del usuario
$userId = $_GET['id'];

// Sentencia preparada
$sql = 'SELECT * FROM usuarios WHERE id = :id';
$stmt = $pdo->prepare($sql);

// Vinculación del parámetro como entero
$stmt->bindValue(':id', $userId, PDO::PARAM_INT);

// Ejecución de la sentencia
$stmt->execute();

// Procesar resultado
$fila = $stmt->fetch(PDO::FETCH_ASSOC);
if ($fila) {
    echo 'Usuario: ' . $fila['username'] . "\n";
} else {
    echo "Usuario no encontrado.\n";
}

Al utilizar PDO::PARAM_INT, nos aseguramos de que el valor de $userId se trate estrictamente como un número entero, añadiendo una capa extra de seguridad y reduciendo errores potenciales.

La vinculación de parámetros es especialmente útil en operaciones de inserción y actualización. Por ejemplo, al insertar datos en la base de datos:

<?php
// Datos recibidos del usuario
$username = $_POST['username'];
$email = $_POST['email'];

// Sentencia preparada
$sql = 'INSERT INTO usuarios (username, email) VALUES (:username, :email)';
$stmt = $pdo->prepare($sql);

// Vinculación de parámetros
$stmt->bindValue(':username', $username, PDO::PARAM_STR);
$stmt->bindValue(':email', $email, PDO::PARAM_STR);

// Ejecución de la sentencia
$stmt->execute();

echo "Usuario registrado correctamente.\n";

Aquí, garantizamos que los datos se inserten de manera segura, sin riesgo de que contenido malicioso afecte la consulta.

Es importante recordar que la vinculación de parámetros debe utilizarse siempre que se incorporen datos externos en las consultas SQL. Esta práctica no solo protege contra inyección SQL, sino que también mejora la legibilidad y mantenimiento del código.

Algunas recomendaciones adicionales:

• Siempre validar y sanitizar los datos de entrada, incluso al utilizar vinculación de parámetros.
• Evitar concatenar variables directamente en las consultas SQL.
• Utilizar marcadores de posición nombrados (:parametro) para mayor claridad, especialmente en consultas con múltiples parámetros.
• Especificar el tipo de dato adecuado al vincular parámetros, lo que ayuda al gestor de la base de datos a optimizar la consulta.

Recorrido de resultados (fetch, fetchAll)

Una vez ejecutadas las consultas SQL en PHP utilizando PDO, es necesario recorrer los resultados obtenidos para procesarlos o mostrarlos al usuario. Las funciones clave para este propósito son fetch y fetchAll, las cuales permiten acceder a los registros devueltos por la consulta de manera eficiente y flexible.

La función fetch recupera la siguiente fila del conjunto de resultados, mientras que fetchAll obtiene todas las filas en un solo paso. Ambas ofrecen diferentes modos de extracción que determinan cómo se accede a los datos.

Uso de fetch para recorrer resultados uno a uno

El método fetch es útil cuando se desea procesar los resultados registro a registro, especialmente en conjuntos de datos grandes donde recuperar todos los registros a la vez podría consumir demasiada memoria.

Ejemplo de uso de fetch:

<?php
// Supongamos que $stmt es un objeto PDOStatement ya ejecutado
while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo 'Nombre: ' . $fila['nombre'] . ' - Edad: ' . $fila['edad'] . "\n";
}

En este caso, se utiliza PDO::FETCH_ASSOC para obtener cada fila como un array asociativo, donde las claves son los nombres de las columnas. El bucle while continúa hasta que fetch devuelve false, indicando que no hay más filas.

Modos de extracción con fetch

El método fetch admite varios modos de extracción, entre ellos:

• PDO::FETCH_ASSOC: Devuelve un array asociativo.
• PDO::FETCH_NUM: Devuelve un array indexado numéricamente.
• PDO::FETCH_BOTH: Devuelve un array con índices numéricos y asociativos.
• PDO::FETCH_OBJ: Devuelve un objeto anónimo con propiedades correspondientes a los nombres de las columnas.
• PDO::FETCH_CLASS: Devuelve una instancia de una clase especificada.

Por ejemplo, para obtener los resultados como objetos:

<?php
while ($fila = $stmt->fetch(PDO::FETCH_OBJ)) {
    echo "Nombre: $fila->nombre - Edad: $fila->edad\n";
}

Uso de fetchAll para obtener todos los resultados

El método fetchAll permite recuperar todas las filas del conjunto de resultados en un solo array. Es útil cuando se sabe que la cantidad de datos no es excesiva y se desea manipularlos de forma conjunta.

Ejemplo de uso de fetchAll:

<?php
// Obtener todas las filas como arrays asociativos
$resultados = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($resultados as $fila) {
    echo 'Nombre: ' . $fila['nombre'] . ' - Edad: ' . $fila['edad'] . "\n";
}

En este ejemplo, fetchAll devuelve un array de arrays asociativos, donde cada elemento representa una fila del conjunto de resultados.

Consideraciones sobre el rendimiento

Es importante ser consciente del impacto en rendimiento al usar fetchAll con conjuntos de datos muy grandes, ya que puede consumir una cantidad significativa de memoria al cargar todos los registros en un solo array. En tales casos, es preferible utilizar fetch dentro de un bucle para procesar los resultados uno a uno.

Recuperar una sola columna con fetchColumn

Cuando solo se necesita una columna específica de los resultados, fetchColumn es una opción eficiente. Este método devuelve el valor de una única columna de la siguiente fila del conjunto de resultados.

Ejemplo de uso de fetchColumn:

<?php
// Obtener la primera columna de la siguiente fila
$nombre = $stmt->fetchColumn();
echo "Nombre: $nombre\n";

También es posible especificar el índice de la columna que se desea recuperar:

<?php
// Obtener la segunda columna (índice 1)
$edad = $stmt->fetchColumn(1);
echo "Edad: $edad\n";

Obtención de resultados como objetos con fetchObject

El método fetchObject devuelve la siguiente fila como una instancia de una clase. Por defecto, devuelve un objeto de la clase stdClass, pero se puede especificar una clase personalizada.

Ejemplo de uso de fetchObject:

<?php
while ($persona = $stmt->fetchObject()) {
    echo "Nombre: $persona->nombre - Edad: $persona->edad\n";
}

Para usar una clase personalizada:

<?php
class Persona
{
    public $nombre;
    public $edad;
}

while ($persona = $stmt->fetchObject('Persona')) {
    echo "Nombre: $persona->nombre - Edad: $persona->edad\n";
}

Este enfoque es útil cuando se trabaja con objetos y se desea aprovechar la programación orientada a objetos en el manejo de los datos.

Uso de cursores para un mejor manejo de los resultados

PDO ofrece la posibilidad de utilizar cursores para controlar la forma en que se recuperan los resultados. Esto es especialmente útil en situaciones en las que se necesita un mayor control sobre la interacción con la base de datos.

Por ejemplo, para cerrar un cursor y liberar la conexión para su reutilización:

<?php
$stmt->closeCursor();

Esto es relevante en aplicaciones donde se mantienen múltiples conexiones o se ejecutan varias consultas de forma secuencial.

Personalización de la obtención de datos con fetch

El método fetch acepta opciones adicionales que permiten personalizar el comportamiento de la recuperación de datos. Por ejemplo, se pueden combinar modos de extracción o especificar argumentos adicionales.

Ejemplo de obtención de resultados como pares clave-valor:

<?php
while ($fila = $stmt->fetch(PDO::FETCH_KEY_PAIR)) {
    echo "Clave: $fila[0] - Valor: $fila[1]\n";
}

Limitaciones y errores comunes

Orden de llamada a fetch o fetchAll: Estos métodos deben llamarse después de ejecutar la sentencia con execute. Olvidar esto puede resultar en errores o resultados inesperados.

Uso incorrecto de índices: Al usar PDO::FETCH_NUM, los índices son numéricos y empezarían desde 0. Confundirlos puede llevar a accesos incorrectos a los datos.

Consumo de memoria: Como se mencionó, fetchAll puede consumir mucha memoria con grandes conjuntos de datos. Es importante evaluar el tamaño de los resultados antes de utilizarlo.

Ejemplo completo

Un ejemplo más completo que muestra cómo utilizar diferentes métodos de obtención de resultados:

<?php
// Conectar a la base de datos
$dsn = 'mysql:host=localhost;dbname=empresa';
$usuario = 'usuario';
$contraseña = 'contraseña';

try {
    $pdo = new PDO($dsn, $usuario, $contraseña);

    // Preparar y ejecutar la consulta
    $sql = 'SELECT id, nombre, puesto FROM empleados';
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    // Obtener todos los resultados como objetos
    $empleados = $stmt->fetchAll(PDO::FETCH_OBJ);

    foreach ($empleados as $empleado) {
        echo "ID: $empleado->id - Nombre: $empleado->nombre - Puesto: $empleado->puesto\n";
    }

    // Obtener una sola columna
    $stmt->execute(); // Reejecutar la consulta
    while ($nombre = $stmt->fetchColumn(1)) {
        echo "Nombre: $nombre\n";
    }

    // Procesar resultados uno a uno
    $stmt->execute(); // Reejecutar la consulta
    while ($fila = $stmt->fetch(PDO::FETCH_BOTH)) {
        echo "ID: {$fila['id']} - Nombre: {$fila['nombre']}\n";
    }
} catch (PDOException $e) {
    echo "Error de conexión: {$e->getMessage()}\n";
}

En este ejemplo, se muestran diferentes formas de recorrer los resultados, reejecutando la misma consulta para demostrar distintas técnicas.