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:
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]%"]);
?>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
AdamExample #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;
}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();
}
}
}