Sentencias preparadas PHP y MYSQL
Las bases de datos MySQL soportan sentencias preparadas. Una sentencia preparada o una sentencia parametrizada se usa para ejecutar la misma sentencia repetidamente con gran eficiencia.
Las sentencias preparadas son muy últiles frente a Inyecciones SQL, ya que los valores de los parámetros, que son transmitidos después usando un protocolo diferente, no necesitan ser escapados.
![](https://www.adaweb.es/wp-content/uploads/2022/08/php_mysqli-1024x796.jpg)
Tabla de ejemplo
En este ejemplo partimos de los siguientes datos en nuestra tabla:
id | firstname | lastname | |
---|---|---|---|
1 | John | Deere | jdeere@mycompany.com |
2 | Adam | Perry | aperry@mycompany.com |
3 | Noé | Bosch | nbosch@mycompany.com |
Parámetros
Una cadena que contiene uno o más caracteres que especifican los tipos para el correspondiente enlazado de variables:
Carácter | Descripción |
---|---|
i | la variable correspondiente es de tipo entero |
d | la variable correspondiente es de tipo double |
s | la variable correspondiente es de tipo string |
b | la variable correspondiente es un blob y se envía en paquetes |
El número de variables y la longitud de la cadena types
debe coincidir con los parámetros en la sentencia.
Mysqli procedimental
Ejemplo de sentencia SELECT
Ejecutaremos una sentencia de tipo SELECT en a que filtraremos por el campo firstname.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Set charset
mysqli_set_charset($conn, "utf8");
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$search = "John";
$sql = "SELECT * FROM employee WHERE firstname = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "s", $search);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
}
} else {
echo "0 results";
}
// Close connection
mysqli_close($conn);
El resultado será:
id: 1 - Name: John Deere
Otro ejemplo sería utilizar LIKE en la consulta, en cuyo caso sería de esta forma:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Set charset
mysqli_set_charset($conn, "utf8");
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$search = "%o%";
$sql = "SELECT * FROM employee WHERE firstname LIKE ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "s", $search);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
}
} else {
echo "0 results";
}
// Close connection
mysqli_close($conn);
El resultado será:
id: 1 - Name: John Deere
id: 3 - Name: Noé Bosch
Ejemplo de sentencia UPDATE
Realizaremos un UPDATE sobre un campo de tipo texto.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Set charset
mysqli_set_charset($conn, "utf8");
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$old_value = "John";
$new_value = "John 2";
$sql = "UPDATE employee SET firstname = ? WHERE firstname = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ss", $new_value, $old_value);
mysqli_stmt_execute($stmt);
// Close connection
mysqli_close($conn);
Ejemplo de sentencia DELETE
Eliminaremos un registro filtrando por su campo firstname.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Set charset
mysqli_set_charset($conn, "utf8");
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$old_value = "John";
$sql = "DELETE FROM employee WHERE firstname = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "s", $old_value);
mysqli_stmt_execute($stmt);
// Close connection
mysqli_close($conn);
Mysqli orientada a objetos
Ejemplo de sentencia SELECT
Ejecutaremos una sentencia de tipo SELECT en a que filtraremos por el campo firstname.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Set charset
$conn->set_charset("utf8");
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$search = "John";
$sql = "SELECT * FROM employee WHERE firstname LIKE ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $search);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "id: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
}
} else {
echo "0 results";
}
// Close connection
$conn->close();
El resultado será:
id: 1 - Name: John Deere
Otro ejemplo sería utilizar LIKE en la consulta, en cuyo caso sería de esta forma:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Set charset
$conn->set_charset("utf8");
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$search = "%o%";
$sql = "SELECT * FROM employee WHERE firstname LIKE ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $search);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "id: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
}
} else {
echo "0 results";
}
// Close connection
$conn->close();
Y el resultado:
id: 1 - Name: John Deere
id: 3 - Name: Noé Bosch
Ejemplo de sentencia UPDATE
Realizaremos un UPDATE sobre un campo de tipo texto.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Set charset
$conn->set_charset("utf8");
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$old_value = "John";
$new_value = "John 2";
$sql = "UPDATE employee SET firstname = ? WHERE firstname = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $new_value, $old_value );
$stmt->execute();
// Close connection
$conn->close();
Ejemplo de sentencia DELETE
Eliminaremos un registro filtrando por su campo firstname.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Set charset
$conn->set_charset("utf8");
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$old_value = "John";
$sql = "DELETE FROM employee WHERE firstname = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $old_value );
$stmt->execute();
// Close connection
$conn->close();
PDO
Ejemplo de sentencia SELECT
Ejecutaremos una sentencia de tipo SELECT en a que filtraremos por el campo firstname.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
try {
// Create connection
$conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
// Set charset
$conn->exec("set names utf8mb4");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$search = "John";
$sql = "SELECT * FROM employee WHERE firstname = ?";
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $search);
$stmt->execute();
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach ($stmt->fetchAll() as $row) {
echo "id: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
El resultado será:
id: 1 - Name: John Deere
Otro ejemplo sería utilizar LIKE en la consulta, en cuyo caso sería de esta forma:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
try {
// Create connection
$conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
// Set charset
$conn->exec("set names utf8mb4");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$search = "%o%";
$sql = "SELECT * FROM employee WHERE firstname LIKE ?";
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $search);
$stmt->execute();
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach ($stmt->fetchAll() as $row) {
echo "id: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
Y el resultado:
id: 1 - Name: John Deere
id: 3 - Name: Noé Bosch
Ejemplo de sentencia UPDATE
Realizaremos un UPDATE sobre un campo de tipo texto.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
try {
// Create connection
$conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
// Set charset
$conn->exec("set names utf8mb4");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$old_value = "John";
$new_value = "John 2";
$sql = "UPDATE employee SET firstname = ? WHERE firstname = ?";
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $new_value);
$stmt->bindParam(2, $old_value);
$stmt->execute();
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
Ejemplo de sentencia DELETE
Eliminaremos un registro filtrando por su campo firstname.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company_info";
try {
// Create connection
$conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
// Set charset
$conn->exec("set names utf8mb4");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$old_value = "John";
$sql = "DELETE FROM employee WHERE firstname = ?";
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $old_value);
$stmt->execute();
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
Base de datos de ejemplo
Si quieres realizar estas pruebas, a continuación te facilitamos la base de datos que hemos utilizado nosotros.
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`firstname` varchar(50) NOT NULL,
`lastname` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `employee` (`id`, `firstname`, `lastname`, `email`) VALUES
(1, 'John', 'Deere', 'jdeere@mycompany.com'),
(2, 'Adam', 'Perry', 'aperry@mycompany.com'),
(3, 'Noé', 'Bosch', 'nbosch@mycompany.com');
ALTER TABLE `employee`
ADD PRIMARY KEY (`id`);
ALTER TABLE `employee`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
Leer más: Conectar PHP y base de datos