Declarações preparadas e procedimentos armazenados

Muitos dos bancos de dados mais maduros suportam o conceito de declarações preparadas. O que são elas? Elas podem ser consideradas como uma espécie de modelo compilado para o SQL que uma aplicação deseja executar, que pode ser personalizado usando parâmetros variáveis. As declarações preparadas oferecem dois benefícios principais:

  • A consulta só precisa ser analisada (ou preparada) uma vez, mas pode ser executada várias vezes com os mesmos ou diferentes parâmetros. Quando a consulta é preparada, o banco de dados irá analisar, compilar e otimizar seu plano para executar a consulta. Para consultas complexas, esse processo pode levar tempo suficiente para diminuir perceptivelmente o desempenho de uma aplicação se houver necessidade de repetir a mesma consulta muitas vezes com diferentes parâmetros. Ao usar uma declaração preparada, a aplicação evita repetir o ciclo de análise/compilação/otimização. Isso significa que declarações preparadas usam menos recursos e, portanto, são mais rápidas.
  • Os parâmetros das declarações preparadas não precisam ser citados; o driver lida automaticamente com isso. Se uma aplicação usar exclusivamente declarações preparadas, o desenvolvedor pode ter certeza de que nenhuma injeção de SQL ocorrerá (no entanto, se outras partes da consulta estiverem sendo construídas com entrada não escapada, a injeção de SQL ainda é possível).

As declarações preparadas são tão úteis que são a única funcionalidade que o PDO emula para drivers que não as suportam. Isso garante que uma aplicação poderá usar o mesmo paradigma de acesso a dados, independentemente das capacidades do banco de dados.

Exemplo #1 Inserções repetidas usando declarações preparadas

Este exemplo executa uma consulta INSERT substituindo um name e um value pelos marcadores de posição nomeados.

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

// insere uma linha
$name = 'one';
$value = 1;
$stmt->execute();

// insere outra linha com valores diferentes
$name = 'two';
$value = 2;
$stmt->execute();
?>

Exemplo #2 Inserções repetidas usando declarações preparadas

Este exemplo executa uma consulta INSERT substituindo um name e um value pelos marcadores de posição posicionais ?.

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

// insere uma linha
$name = 'one';
$value = 1;
$stmt->execute();

// insere outra linha com valores diferentes
$name = 'two';
$value = 2;
$stmt->execute();
?>

Exemplo #3 Buscando dados usando declarações preparadas

Este exemplo busca dados com base em um valor de chave fornecido por um formulário. A entrada do usuário é automaticamente citada, portanto, não há risco de um ataque de injeção de SQL.

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

Exemplo #4 Chamando um procedimento armazenado com um parâmetro de saída

Se o driver do banco de dados suportar, uma aplicação também pode vincular parâmetros de saída além de entrada. Os parâmetros de saída são normalmente usados para recuperar valores de procedimentos armazenados. Os parâmetros de saída são um pouco mais complexos de usar do que os parâmetros de entrada, pois um desenvolvedor deve saber o quão grande um determinado parâmetro pode ser quando o vincula. Se o valor for maior do que o tamanho sugerido, um erro será gerado.

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

// chamar o procedimento armazenado
$stmt->execute();

print
"procedure returned $return_value\n";
?>

Exemplo #5 Chamando um procedimento armazenado com um parâmetro de entrada/saída

Os desenvolvedores também podem especificar parâmetros que contêm valores tanto de entrada quanto de saída; a sintaxe é semelhante aos parâmetros de saída. No próximo exemplo, a string 'hello' é passada para o procedimento armazenado e, quando ele retorna, hello é substituído pelo valor de retorno do procedimento.

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

// chamar o procedimento armazenado
$stmt->execute();

print
"procedure returned $value\n";
?>

Exemplo #6 Uso inválido de marcador de posição

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

// o marcador de posição deve ser usado no lugar do valor inteiro
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(["%$_GET[name]%"]);
?>

add a note

User Contributed Notes 3 notes

up
219
adam at pyramidpower dot com dot au
14 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
10
w37090 at yandex dot ru
4 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();
}
}
}
up
-1
theking2(at)king.ma
7 months 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;
}
To Top