PHP 8.5.0 Alpha 1 available for testing

Consultas preparadas y procedimientos almacenados

La mayoría de las bases de datos soportan el concepto de consultas preparadas. ¿Qué son? Pueden verse como una especie de plantilla compilada para el SQL que se desea ejecutar, que puede ser personalizada utilizando variables como parámetros. Las consultas preparadas ofrecen dos funcionalidades esenciales:

  • La consulta solo debe ser analizada (o preparada) una vez, pero puede ser ejecutada múltiples veces con parámetros idénticos o diferentes. Cuando la consulta es preparada, la base de datos analizará, compilará y optimizará su plan para ejecutar la consulta. Para consultas complejas, este proceso puede tomar bastante tiempo, lo que puede ralentizar las aplicaciones si se debe repetir la misma consulta múltiples veces con diferentes parámetros. Al utilizar consultas preparadas, se evita repetir el ciclo análisis/compilación/optimización. En resumen, las consultas preparadas utilizan menos recursos y se ejecutan más rápidamente.
  • Los parámetros para preparar las consultas no necesitan estar entre comillas; el controlador gestiona esto. Si la aplicación utiliza exclusivamente consultas preparadas, se puede estar seguro de que no es posible ninguna inyección SQL (Sin embargo, si se construyen otras partes de la consulta basándose en entradas de usuario, se sigue asumiendo un riesgo).

Las consultas preparadas son tan prácticas que es la única funcionalidad que PDO emula para los controladores que no las soportan. Esto asegura poder utilizar la misma técnica para acceder a los datos, sin preocuparse por las capacidades de la base de datos.

Ejemplo #1 Inserciones repetitivas utilizando consultas preparadas

Este ejemplo realiza una consulta INSERT sustituyendo un nombre y un valor para los marcadores nombrados.

<?php
$stmt
= $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// inserción de una fila
$name = 'one';
$value = 1;
$stmt->execute();

// inserción de otra fila con valores diferentes
$name = 'two';
$value = 2;
$stmt->execute();
?>

Ejemplo #2 Inserciones repetidas utilizando consultas preparadas

Este ejemplo realiza una consulta INSERT sustituyendo un nombre y un valor para los marcadores ?.

<?php
$stmt
= $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// inserción de una fila
$name = 'one';
$value = 1;
$stmt->execute();

// inserción de otra fila con diferentes valores
$name = 'two';
$value = 2;
$stmt->execute();
?>

Ejemplo #3 Recuperación de datos utilizando consultas preparadas

Este ejemplo recupera datos basados en el valor de una clave proporcionada por un formulario. La entrada del usuario es automáticamente escapada, por lo que no hay riesgo de ataque por inyección SQL.

<?php
$stmt
= $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
$stmt->execute([$_GET['name']]);
foreach (
$stmt as $row) {
print_r($row);
}
?>

Ejemplo #4 Llamada a un procedimiento almacenado con un parámetro de salida

Si el controlador de la base de datos lo soporta, también se pueden vincular parámetros tanto para entrada como para salida. Los parámetros de salida se utilizan típicamente para recuperar valores de un procedimiento almacenado. Los parámetros de salida son un poco más complejos de usar que los parámetros de entrada ya que se debe conocer la longitud que un parámetro dado podrá alcanzar cuando se vincule. Si el valor devuelto es más largo que el tamaño sugerido, se emitirá un error.

<?php
$stmt
= $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);

// Llamada al procedimiento almacenado
$stmt->execute();

print
"El procedimiento ha devuelto: $return_value\n";
?>

Ejemplo #5 Llamada a un procedimiento almacenado con un parámetro de entrada/salida

También se deben especificar los parámetros que gestionan valores tanto para entrada como para salida; la sintaxis es similar a los parámetros de salida. En el siguiente ejemplo, la cadena 'Hola' es pasada al procedimiento almacenado y cuando devuelve el valor, 'Hola' es reemplazada por el valor devuelto por el procedimiento.

<?php
$stmt
= $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);

// llamada al procedimiento almacenado
$stmt->execute();

print
"El procedimiento ha devuelto: $value\n";
?>

Ejemplo #6 Uso inválido de marcador

<?php
$stmt
= $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
$stmt->execute([$_GET['name']]);

// un marcador debe ser utilizado en lugar de un valor completo
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(["%$_GET[name]%"]);
?>

add a note

User Contributed Notes 3 notes

up
218
adam at pyramidpower dot com dot au
15 years ago
Note that when using name parameters with bindParam, the name itself, cannot contain a dash '-'.

example:
<?php
$stmt
= $dbh->prepare ("INSERT INTO user (firstname, surname) VALUES (:f-name, :s-name)");
$stmt -> bindParam(':f-name', 'John');
$stmt -> bindParam(':s-name', 'Smith');
$stmt -> execute();
?>

The dashes in 'f-name' and 's-name' should be replaced with an underscore or no dash at all.

See http://bugs.php.net/43130

Adam
up
3
theking2(at)king.ma
1 year ago
Example #5 gives an 1414 wenn tried on MariaDB. Use this function to call a stored procedure with the last parameter as INOUT returning a value like a (uu)id or a count;

<?php
/**
* call_sp Call the specified stored procedure with the given parameters.
* The first parameter is the name of the stored procedure.
* The remaining parameters are the (in) parameters to the stored procedure.
* the last (out) parameter should be an int like state or number of affected rows.
*
* @param mixed $sp_name The name of the stored procedure to call.
* @param mixed $params The parameters to pass to the stored procedure.
* @return int The number of affected rows.
*/
function call_sp( \PDO $db, string $sp_name, ...$params ): mixed
{
$placeholders = array_fill( 0, count( $params ), "?" );
$placeholders[] = "@new_id";

$sql = "CALL $sp_name( " . implode( ", ", $placeholders ) . " ); SELECT @new_id AS `new_id`";

try {
LOG->debug( "calling Stored Procedure", [ "sql" => $sql ] );

$stmt = $db->prepare( $sql );
$i = 0;
foreach(
$params as $param ) {
$stmt->bindValue( ++$i, $param );
}
$stmt->execute();
$new_id = $stmt->fetch( PDO::FETCH_ASSOC )['new_id'];

return
$new_id;

} catch (
\Exception $e ) {
LOG->error( "Error calling Stored Procedure", [ "sql" => $sql, "params" => $params, "error" => $e->getMessage() ] );
throw
$e;
}
up
9
w37090 at yandex dot ru
5 years ago
Insert a multidimensional array into the database through a prepared query:
We have an array to write the form:

$dataArr:
Array
(
[0] => Array
(
[0] => 2020
[1] => 23
[2] => 111111
)

[1] => Array
(
[0] => 2020
[1] => 24
[2] => 222222222
)
....

Task: prepare a request and pass through binds
$array = [];
foreach ($dataArr as $k=>$v) {
// $x = 2020, the variable is predetermined in advance, does not change the essence
$array[] = [$x, $k, $v];
}
$sql = ("INSERT INTO `table` (`field`,`field`,`field`) VALUES (?,?,?)");

$db->queryBindInsert($sql,$array);

public function queryBindInsert($sql,$bind) {
$stmt = $this->pdo->prepare($sql);

if(count($bind)) {
foreach($bind as $param => $value) {
$c = 1;
for ($i=0; $i<count($value); $i++) {
$stmt->bindValue($c++, $value[$i]);
}
$stmt->execute();
}
}
}
To Top