Instrução Pré-processada MySQL PHP

Neste tutorial, você aprenderá como usar declarações preparadas no MySQL com PHP.

O que são declarações preparadas

As declarações preparadas (também conhecidas como instruções parametrizadas) são apenas um template de consulta SQL que contém placeholders em vez de valores de parâmetros reais. Durante a execução da instrução, esses placeholders são substituídos pelos valores reais.

O MySQLi suporta o uso de placeholders de posição anônimos (?), conforme mostrado a seguir:

INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?);

O PDO suporta placeholders de posição anônimos (?), bem como placeholders nomeados. Os placeholders nomeados começam com dois pontos (:) e são seguidos por um identificador, conforme mostrado a seguir:

INSERT INTO persons (first_name, last_name, email)
VALUES (:first_name, :last_name, :email);

A execução das declarações preparadas inclui duas fases: preparação e execução.

• Preparar - Durante a fase de preparação, será criado um template de instrução SQL e enviado para o servidor do banco de dados. O servidor analisa o template da instrução, realiza a verificação de sintaxe e otimização da consulta e o armazena para uso posterior.

• Executar - Durante a execução, os valores dos parâmetros serão enviados para o servidor. O servidor cria uma instrução a partir do template da instrução e desses valores para executá-la.

As declarações preparadas são muito úteis, especialmente quando você usa diferentes valores (por exemplo, uma série de instruções) para executar uma instrução INSERT específica várias vezes. A seguir, descrevemos alguns dos principais benefícios de usá-las.

Vantagens de usar declarações preparadas

Uma declaração preparada pode ser executada repetidamente de forma eficiente, pois a instrução é analisada apenas uma vez e pode ser executada várias vezes. Além disso, ao transmitir apenas os valores dos placeholders para o servidor do banco de dados em vez da instrução SQL completa, também pode reduzir ao máximo o uso de largura de banda.

As declarações preparadas também oferecem uma proteção poderosa para evitarInjeção SQLIsso é porque os valores de parâmetros não estão diretamente incorporados na string de consulta SQL. Ao usar diferentes protocolos, os valores de parâmetros são enviados separadamente da consulta para o servidor do banco de dados, portanto, não interferem nele. Após a análise da template da instrução, o servidor usa diretamente esses valores durante a execução. É por isso que as declarações preparadas são menos propensas a erros e são consideradas um dos elementos mais críticos da segurança do banco de dados.

O exemplo a seguir mostrará como a declaração preparada realmente funciona:

<?php
/*　Tentar conectar ao servidor MySQL.　Supondo que você esteja executando MySQL.
Servidor com configurações padrão (usuário sem senha "root")　*/
$link = mysqli_connect("localhost", "root", "", "demo");
　
//Verificar conexão
if($link === false){
　　　　die("Erro: Não foi possível conectar. ". mysqli_connect_error());
}
　
//Usar declaração preparada
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
　
if($stmt = mysqli_prepare($link, $sql)){
　　　　//Ligar variáveis como parâmetros à sentença preparada
　　　　mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
　　　　
　　　　/*　Definir valores de parâmetros e executar, a instrução novamente insere outra linha.　*/
　　　　$first_name = "Hermione";
　　　　$last_name = "Granger";
　　　　$email = "[email protected]";
　　　　mysqli_stmt_execute($stmt);
　　　　
　　　　/*　Definir valores de parâmetros e executar a instrução de inserção de linha.　*/
　　　　$first_name = "Ron";
　　　　$last_name = "Weasley";
　　　　$email = "[email protected]";
　　　　mysqli_stmt_execute($stmt);
　　　　
　　　　echo　"Inserção de registro bem-sucedida.";
}　else{
　　　　echo "Erro: Não foi possível preparar a consulta: $sql. " . mysqli_error($link);
}
　
//Fechar declaração
mysqli_stmt_close($stmt);
　
//Fechar conexão
mysqli_close($link);
?>

<?php
/*　Tentar conectar ao servidor MySQL.　Supondo que você esteja executando MySQL.
Servidor com configurações padrão (usuário sem senha "root")　*/
$mysqli = new mysqli("localhost", "root", "", "demo");
　
//Verificar conexão
if($mysqli === false){
　　　　die("Erro: Não foi possível conectar. ". $mysqli->connect_error);
}
　
//　Usar declaração preparada
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
　
if($stmt = $mysqli->prepare($sql)){
　　　　//　Ligar variáveis como parâmetros à sentença preparada
　　　　$stmt->bind_param("sss", $first_name, $last_name, $email);
　　　　
　　　　/*　Definir valores de parâmetros e executar.
　　　　Executar a instrução novamente para inserir outra linha　*/
　　　　$first_name = "Hermione";
　　　　$last_name = "Granger";
　　　　$email = "[email protected]";
　　　　$stmt->execute();
　　　　
　　　　/*　Definir valores de parâmetros e executar
　　　　　　　　A instrução para inserir a linha　*/
　　　　$first_name = "Ron";
　　　　$last_name = "Weasley";
　　　　$email = "[email protected]";
　　　　$stmt->execute();
　　　　
　　　　echo "Inserção de registro bem-sucedida.";
}　else{
　　　　echo "Erro: Não foi possível preparar a consulta: $sql. " . $mysqli"->error;
}
　
//Fechar declaração
$stmt->close();
　
//Fechar conexão
$mysqli->close();
?>

<?php
/*　Tentar conectar ao servidor MySQL.　Supondo que você esteja executando MySQL.
Servidor com configurações padrão (usuário sem senha "root")　*/
try{
　　　　$pdo　=　new　PDO("mysql:host=localhost;dbname=demo",　"root",　"");
　　　　//　Definir o modo de erro PDO como exceção
　　　　$pdo->setAttribute(PDO::ATTR_ERRMODE,　PDO::ERRMODE_EXCEPTION);
}　catch(PDOException　$e){
　　　　die("Erro: Não foi possível conectar.　"　.　$e->getMessage());
}
　
//Tentar executar consulta de inserção
try{
　　　　//Usar declaração preparada
　　　　$sql　=　"INSERT　INTO　persons　(first_name,　last_name,　email)　VALUES　(:first_name,　:last_name,　:email)";
　　　　$stmt　=　$pdo->prepare($sql);
　　　　
　　　　//Ligar parâmetros à declaração
　　　　$stmt-bindParam(':first_name', $first_name, PDO::PARAM_STR);
　　　　$stmt-bindParam(':last_name', $last_name, PDO::PARAM_STR);
　　　　$stmt-bindParam(':email', $email, PDO::PARAM_STR);
　　　　
　　　　/*　Definir valores de parâmetros e executar,
　　　　　　Executar a instrução novamente para inserir outra linha　*/
　　　　$first_name = "Hermione";
　　　　$last_name = "Granger";
　　　　$email = "[email protected]";
　　　　$stmt->execute();
　　　　
　　　　/*　Definir valores de parâmetros e executar
　　　　　　　　A instrução para inserir a linha　*/
　　　　$first_name = "Ron";
　　　　$last_name = "Weasley";
　　　　$email = "[email protected]";
　　　　$stmt->execute();
　　　　
　　　　echo　"Inserção de registro bem-sucedida.";
}　catch(PDOException　$e){
　　　　die("Erro: Não foi possível preparar/Executar consulta: $sql. " . $e->getMessage());
}
　
//　Fechar declaração
unset($stmt);
　
//Fechar conexão
unset($pdo);
?>

Como você pode ver no exemplo acima, preparamos a instrução INSERT apenas uma vez, mas executamos a instrução várias vezes passando conjuntos de parâmetros diferentes.

Uso do código (estilo de programação)

No exemplo da instrução SQL INSERT mencionado acima, o interrogativo é usado comofirst_name,last_nameeemailMarcador de posição para valor do campo.

A função mysqli_stmt_bind_param() liga variáveis às marcadores de posição (? no modelo de instrução SQL. Os marcadores de posição são substituídos pelos valores reais armazenados nas variáveis ao executar. O tipo de definição de string fornecido como segundo parâmetro, 'sss', especifica que cada variável de ligação tem o tipo de dados string (string).

A string de definição do tipo especifica o tipo de dados da variável de ligação correspondente, e os parâmetros têm os seguintes quatro tipos:

• i - integer (inteiro)

• d - double (dois pontos flutuantes de precisão dupla)

• s - string (string)

• b - BLOB (binary large object: objeto grande binário)

Os tipos de definição de string devem coincidir com o número de marcadores de posição no modelo de instrução SQL.

Usar as entradas recebidas pelo formulário da Web

Se você lembrar do capítulo anterior, já criamos um formulário HTML paraInserir dados no banco de dadosAqui, vamos expandir o exemplo executando instruções de pré-processamento. Você pode usar o mesmo formulário HTML para testar o seguinte exemplo de script de inserção, mas certifique-se de que o atributo action do formulário use o nome de arquivo correto.

Este é o código PHP atualizado para inserção de dados. Se você analisar o exemplo, perceberá que não usamos mysqli_real_escape_string() como no exemplo do capítulo anterior. Pois nas sentenças preparadas, a entrada do usuário nunca é substituída diretamente na string de consulta, então não há necessidade de escapar corretamente.

<?php
/*　Tentar conectar ao servidor MySQL.　Supondo que você esteja executando MySQL.
Servidor com configurações padrão (usuário sem senha "root")　*/
$link = mysqli_connect("localhost", "root", "", "demo");
　
//Verificar conexão
if($link === false){
　　　　die("Erro: Não foi possível conectar. ". mysqli_connect_error());
}
　
//Usar declaração preparada
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
　
if($stmt = mysqli_prepare($link, $sql)){
　　　　//Ligar variáveis à sentença preparada como parâmetros
　　　　mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
　　　　
　　　　//Definir parâmetros
　　　　$first_name = $_REQUEST['first_name'];
　　　　$last_name = $_REQUEST['last_name'];
　　　　$email = $_REQUEST['email'];
　　　　
　　　　//Tentar executar declaração preparada
　　　　if(mysqli_stmt_execute($stmt)){
　　　　　　　　echo　"Inserção de registro bem-sucedida.";
　　　　}　else{
　　　　　　　　echo "Erro: Não foi possível executar a consulta: $sql ". $mysqli_error($link);
　　　　}
}　else{
　　　　echo "Erro: Não foi possível executar a consulta: $sql ". $mysqli_error($link);
}
　
//　Fechar declaração
mysqli_stmt_close($stmt);
　
//Fechar conexão
mysqli_close($link);
?>

<?php
/*　Tentar conectar ao servidor MySQL.　Supondo que você esteja executando MySQL.
Servidor com configurações padrão (usuário sem senha "root")　*/
$mysqli = new mysqli("localhost", "root", "", "demo");
　
//Verificar conexão
if($mysqli === false){
　　　　die("Erro: Não foi possível conectar. ". $mysqli->connect_error);
}
　
//Usar declaração preparada
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
　
if($stmt = $mysqli->prepare($sql)){
　　　　//Ligar variáveis como parâmetros à sentença preparada
　　　　$stmt->bind_param("sss", $first_name, $last_name, $email);
　　　　
　　　　//Configurar parâmetros
　　　　$first_name = $_REQUEST['first_name'];
　　　　$last_name = $_REQUEST['last_name'];
　　　　$email = $_REQUEST['email'];
　　　　
　　　　//Tentar executar declaração preparada
　　　　if($stmt->execute()){
　　　　　　　　echo　"Inserção de registro bem-sucedida.";
　　　　}　else{
　　　　　　　　echo　"Erro: Não foi possível executar consulta:　$sql.　"　.　$mysqli->error;
　　　　}
}　else{
　　　　echo　"Erro: Não foi possível executar consulta:　$sql.　"　.　$mysqli->error;
}
　
//Fechar declaração
$stmt->close();
　
//Fechar conexão
$mysqli->close();
?>

<?php
/*　Tentar conectar ao servidor MySQL.　Supondo que você esteja executando MySQL.
Servidor com configurações padrão (usuário sem senha "root")　*/
try{
　　　　$pdo　=　new　PDO("mysql:host=localhost;dbname=demo",　"root",　"");
　　　　//Definir o modo de erro PDO como exceção
　　　　$pdo->setAttribute(PDO::ATTR_ERRMODE,　PDO::ERRMODE_EXCEPTION);
}　catch(PDOException　$e){
　　　　die("Erro: Não foi possível conectar.　"　.　$e->getMessage());
}
　
//Tentar executar consulta de inserção
try{
　　　　//Usar declaração preparada
　　　　$sql　=　"INSERT　INTO　persons　(first_name,　last_name,　email)　VALUES　(:first_name,　:last_name,　:email)";
　　　　$stmt　=　$pdo->prepare($sql);
　　　　
　　　　//　Ligar parâmetros à declaração
　　　　$stmt->bindParam(':first_name',　$_REQUEST['first_name'],　PDO::PARAM_STR);
　　　　$stmt->bindParam(':last_name',　$_REQUEST['last_name'],　PDO::PARAM_STR);
　　　　$stmt->bindParam(':email',　$_REQUEST['email'],　PDO::PARAM_STR);
　　　　
　　　　//　Executar declaração preparada
　　　　$stmt->execute();
　　　　echo　"Inserção de registro bem-sucedida.";
}　catch(PDOException　$e){
　　　　die("Erro: Não foi possível preparar/Executar consulta　$sql.　"　.　$e->getMessage());
}
　
//Fechar declaração
unset($stmt);
　
//Fechar conexão
unset($pdo);
?>