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]%"]);
?>