Instruções Preparadas do PHP MySQLi para Evitar Injeção de SQL

Introdução

Antes de começar, se você quiser ver uma maneira ainda mais fácil de usar as instruções preparadas do MySQLi, confira minha classe de wrappers . Além disso, aqui está um ótimo recurso para aprender as instruções preparadas pela PDO , que é a melhor escolha para iniciantes e para a maioria das pessoas em geral.

Uma tentativa de invasão foi descoberta recentemente e parece que eles estão tentando derrubar o banco de dados inteiro. Uma reunião improvisada foi marcada às 2 da manhã e todos na empresa estão em pânico. Ironicamente, como gerente de banco de dados, você permanece mais calmo. Por quê? Você sabe que esses scrubs não são páreo para aquelas declarações preparadas que você codificou! Na verdade, você acha isso engraçado, já que esses hackers provavelmente ficarão aborrecidos por desperdiçar seu tempo com tentativas inúteis.

Espero que este cenário nunca aconteça com o seu site. No entanto, é sem dúvida uma boa ideia tomar as devidas precauções. Se implementadas corretamente, as instruções preparadas (também conhecidas como consultas parametrizadas) oferecem proteção superior contra injeção de SQL. Basicamente, basta criar o modelo de consulta com valores de espaço reservado e, em seguida, substituir as entradas fictícias pelas reais. Escapar não é necessário, pois tratará os valores como literais; todas as tentativas de injetar consultas SQL serão interpretadas como tal.

Declarações preparadas podem parecer intimidantes no começo, mas quando você se deparar com isso, vai parecer uma segunda natureza para você. O objetivo deste tutorial é transformar alguém com pouco ou nenhum conhecimento de declarações preparadas, em um especialista.

Disclaimer: Na verdade, não seja tão descontraído quanto este gerenciador de banco de dados. Quando se trata de segurança, você nunca deve ser complacente, não importa o quão seguro você acha que seu sistema é.

Como funciona a injeção de SQL

Os seguintes quadrinhos icônicos, conhecidos como Bobby Tables , são um excelente retrato de como um ataque de injeção de SQL pode funcionar. Todo o crédito pela imagem vai para este sitepara esta peça clássica de retórica.

Agora que terminamos com a teoria, vamos praticar. Antes de começar, se você está se perguntando exatamente como o " Bobby Tables Attack" funciona, confira esta explicação .

Em uma chamada normal do MySQL, você faria algo como:

$name = $_POST['name'];
$mysqli->query("Select * FROM myTable WHERE name='$name'");
cópia de

O problema com isso é que, se for baseado na entrada do usuário, como no exemplo, um usuário mal-intencionado poderia fazer ' OR '1'='1. Agora esta afirmação será sempre avaliada como verdadeira, desde 1=1. Nesse caso, o usuário mal-intencionado agora tem acesso à sua tabela inteira. Imagine o que poderia acontecer se fosse uma DELETEconsulta. Dê uma olhada no que realmente está acontecendo com a afirmação.

Select * FROM myTable WHERE name='' OR '1'='1' 
cópia de

Um hacker pode causar muitos danos ao seu site se as suas consultas estiverem configuradas dessa maneira. Uma solução fácil para isso seria fazer:

$name = $mysqli->real_escape_string($_POST['name']);
$mysqli->query("Select * FROM myTable WHERE name='$name'");
cópia de

Observe como semelhante ao primeiro exemplo, ainda adicionei aspas ao valor da coluna. Sem aspas, as seqüências de caracteres ainda são igualmente suscetíveis à injeção de SQL . Se você estiver usando uma cláusula LIKE, então você também deve fazer addcslashes($escaped, '%_'), desde mysqli::real_escape_stringque não faça isso como indicado aqui .

Isto cobre strings, como o nome da função implica, mas e os números? Você poderia fazer (int)$mysqli->real_escape_string($_POST['name']), o que certamente funcionaria, mas isso é redundante. Se você está lançando a variável para um int, você não precisa escapar de nada. Você já está dizendo para garantir essencialmente que o valor seja um inteiro. Fazendo (int)$_POST['name']seria suficiente. Como é um número inteiro, obviamente você não precisa adicionar aspas à coluna sql name.

Na realidade, se você seguir estas instruções perfeitamente, deve ser o suficiente para usar mysqli::real_escape_stringseqüências de caracteres e (int)$varnúmeros inteiros. Apenas não esqueça de definir o conjunto de caracteres padrão. Isso pode ser definido no php.ini (deve ser o valor padrão) como default_charset = "utf-8"e usando $mysqli->set_charset('utf8mb4')em cada página que usa $mysqli->real_escape_string(). Mas apenas para coisas que são legais em instruções preparadas, que são valores em uma declaração WHERE ou valores de coluna ; não use isso para nomes de tabelas / colunas ou palavras-chave SQL .

Independentemente disso, ainda sugiro fortemente o uso de instruções preparadas, pois elas são claramente mais adequadas para proteger contra injeção de SQL e menos propensas a erros, já que você não precisa se preocupar com formatação manual - em vez disso, basta substituir os espaços reservados falsos por seus valores . É claro que você ainda desejará filtrar e limpar suas entradas para evitar o XSS. Com declarações de instruções preparadas, há menos aspectos a serem considerados, juntamente com alguns casos de borda a serem quebrados $mysqli->real_escape_string() (não configurar corretamente o conjunto de caracteres é uma das causas). Em suma, não há absolutamente nenhuma boa razão para usar real_escape_string()declarações preparadas.Apenas mostrei como formatar manualmente suas consultas com ele, para mostrar que é possível. Na realidade, seria tolice não usar instruções preparadas para impedir a injeção de SQL.

Como funcionam as instruções preparadas do MySQLi

Em linguagem simples, é assim que as instruções preparadas pelo MySQLi funcionam em PHP:

1. Prepare uma consulta SQL com valores vazios como espaços reservados (com um ponto de interrogação para cada valor).
2. Vincule variáveis ??aos espaços reservados, informando cada variável, junto com seu tipo.
3. Execute a consulta.

Os quatro tipos de variáveis ??permitiram:

• i - Integer
• d - duplo
• s - String
• b - Blob

Uma declaração preparada, como o próprio nome indica, é uma maneira de preparar a chamada do MySQL, sem armazenar as variáveis. Você diz que as variáveis ??irão lá eventualmente - só que ainda não. A melhor maneira de demonstrar isso é por exemplo.

$stmt = $mysqli->prepare("SELECT * FROM myTable WHERE name = ? AND age = ?");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
//fetching result would go here, but will be covered later
$stmt->close();
cópia de

Se você nunca viu declarações preparadas antes, isso pode parecer um pouco estranho. Basicamente, o que está acontecendo é que você está criando um modelo para o que a instrução SQL será. Neste caso, estamos selecionando tudo de myTable, onde namee ageigual ?. O ponto de interrogação é apenas um espaço reservado para onde os valores irão.

O bind_param()método é onde você anexa variáveis ??aos valores fictícios no modelo preparado. Observe como existem duas letras entre aspas antes das variáveis. Isso informa ao banco de dados os tipos de variáveis. O sespecifica que o nome será um valor de string, enquanto as iforças envelhecem para ser um inteiro. É precisamente por isso que não adicionei aspas em torno do ponto de interrogação para o nome, como normalmente faria para uma string em uma chamada SQL. Você provavelmente pensou que eu tinha esquecido, mas a realidade é que simplesmente não há necessidade de (na verdade, ele realmente não funcionará se você colocar aspas ao redor do ?, já que ele será tratado como uma string literal, ao invés de um espaço reservado fictício. Você já está dizendo que será uma string literal quando você ligarbind_param(), portanto, mesmo que um usuário mal-intencionado tente inserir o SQL nas entradas do usuário, ele ainda será tratado como uma string. $stmt->execute()então, na verdade, executa o código; a última linha simplesmente fecha a declaração preparada. Vamos cobrir a busca de resultados na seção Select.

Criando uma nova conexão MySQLi

Criar um novo MySQLi é bem simples. Sugiro nomear um arquivo chamado mysqli_connect.phpe colocar esse arquivo fora de sua raiz diretamente (html, public_html) para que suas credenciais estejam seguras.

$mysqli = new mysqli("localhost", "username", "password", "databaseName");
if($mysqli->connect_error) {
  exit('Error connecting to database'); //Should be a message a typical user could understand in production
}
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli->set_charset("utf8mb4");
cópia de

Muitos tutoriais, incluindo o manual do PHP, mostram como usar $mysqli->connect_error()imprimindo em exit()ou die(). Mas isso não é realmente necessário (para não mencionar incrivelmente estúpido, já que você estará imprimindo esta informação para o mundo), já que se você estiver em desenvolvimento, ele irá imprimir o erro de qualquer maneira; na produção, a mensagem de erro será anexada ao seu log de erros. A mensagem exit()deve ser algo que um usuário normal poderia entender exit('Something weird happened').

Você poderia pensar que definir o charset utf-8em seu php.ini seria suficiente, juntamente com utf8mb4todo o seu banco de dados, mas às vezes erros estranhos acontecem se você não configurá-lo em seu arquivo php também, como observado aqui .

Você pode, alternativamente, instanciá-lo em um bloco try / catch se habilitar o relatório interno, que menciono na seção de tratamento de erros. Por favor, nunca relate erros diretamente no seu site em produção. Você estará chutando-se por um erro tão bobo, uma vez que irá imprimir as informações confidenciais do banco de dados (nome de usuário, senha e nome do banco de dados). Aqui está como seu arquivo php.ini deve se parecer em produção: faça ambos display_errors = Offe log_errors = On. Além disso, não faça eco do erro na produção.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
  $mysqli = new mysqli("localhost", "username", "password", "databaseName");
  $mysqli->set_charset("utf8mb4");
} catch(Exception $e) {
  error_log($e->getMessage());
  exit('Error connecting to database'); //Should be a message a typical user could understand
}
cópia de

Se você preferir usar set_exception_handler () ao invés de try/catch, você pode fazer o seguinte para evitar aninhamento. Se você estiver usando este método, você precisa entender que ele afetará todas as páginas em que ele está incluído. Portanto, você deve reutilizar a função novamente com uma mensagem personalizada para cada página ou usar restore_exception_handler () para reverter para o construído em PHP um. Se você fez vários, ele irá para o anterior que você fez.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
set_exception_handler(function($e) {
  error_log($e->getMessage());
  exit('Error connecting to database'); //Should be a message a typical user could understand
});
$mysqli = new mysqli("localhost", "username", "password", "databaseName");
$mysqli->set_charset("utf8mb4");
cópia de

Há uma séria repercussão do uso mysqli_report(), que é o fato de ele reportar suas informações confidenciais do banco de dados. Você tem três opções para usá-lo, mas não informar sua senha.

1. Você também pode usar mysqli_report()estritamente tudo, exceto para criar a conexão, se você fizer isso, o primeiro método que eu mostrei com $mysqli->connect_error(senha não mostrada) e apenas colocar mysqli_report() depois new mysqli() .
2. Se você chamar mysqli_report() antes de criar uma conexão, então você precisa para garantir que ele está em um bloco try / catch e você especificamente imprimir em seu log de erro $e->getMessage(), não $e, que ainda contém suas informações confidenciais.
3. Use set_exception_handler()da mesma maneira que 2 e use $e->getMessage().

Eu recomendo fortemente fazer um desses métodos. Mesmo se você for diligente e garantir que todos os seus erros apareçam apenas no seu log de erros, eu pessoalmente não vejo por que alguém precisaria registrar sua senha. Você já saberá qual é o problema.

Inserir, atualizar e excluir

Inserindo, atualizando e excluindo tem uma sintaxe idêntica, então eles serão combinados.

Inserir

$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
$stmt->close();
cópia de

Atualizar

$stmt = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?");
$stmt->bind_param("si", $_POST['name'], $_SESSION['id']);
$stmt->execute();
$stmt->close();
cópia de

Excluir

$stmt = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
$stmt->bind_param("i", $_SESSION['id']);
$stmt->execute();
$stmt->close();
cópia de

Obter o número de linhas afetadas

Você também pode querer verificar o status de uma linha inserida, atualizada ou excluída. Veja como você faria se estivesse atualizando uma linha.

$stmt = $mysqli->prepare("UPDATE myTable SET name = ?");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
if($stmt->affected_rows === 0) echo 'No rows updated';
$stmt->close();
cópia de

Nesse caso, verificamos se alguma linha foi atualizada. Para referência, aqui está o uso dos mysqli::$affected_rowsvalores de retorno.

-1 - consulta retornou um erro; redundante se já houver tratamento de erro paraexecute()

0 - nenhum registro foi atualizado UPDATE, nenhuma linha corresponde à WHEREcláusula ou nenhuma consulta foi executada

Maior que 0 - retorna o número de linhas afetadas; comparável a mysqli_result::$num_rowsparaSELECT

Obter chave primária mais recente inserida

$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
echo $mysqli->insert_id;
$stmt->close();
cópia de

Verifique se a entrada duplicada

Isso é útil se você criar uma restrição exclusiva em uma tabela, para que as duplicatas não sejam permitidas. Você pode até fazer isso para várias colunas, então terá que ser essa exata permutação. Se o tratamento de exceções estivesse desativado, você verificaria o código de erro com $mysqli->errno. Com o tratamento de exceções ativado, você pode escolher entre isso ou o método de exceção genérico $e->getCode(). Observe que isso é diferente de PDOException, que imprimirá o SQLSTATE, em vez do código de erro.

Aqui está uma lista de mensagens de erro . O código de erro para uma entrada de linha duplicada de uma atualização ou inserção é 1062 e SQLSTATE é 23000. Para verificar especificamente SQLSTATE, você deve usar $mysqli->sqlstate.

try {
  $stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  $stmt->bind_param("si", $_POST['name'], $_POST['age']);
  $stmt->execute();
  $stmt->close();
} catch(Exception $e) {
  if($mysqli->errno === 1062) echo 'Duplicate entry';
}
cópia de

É assim que você definiria uma restrição exclusiva:

ALTER TABLE myTable ADD CONSTRAINT unique_person UNIQUE (name, age)
cópia de

Selecione

Todas as instruções selecionadas em consultas parametrizadas começarão do mesmo modo. No entanto, existe uma diferença fundamental para realmente armazenar e buscar os resultados. Os dois métodos que existem são get_result()e bind_result().

get_result ()

Este é o mais versátil dos dois, já que pode ser usado para qualquer cenário. Deve ser notado que isto requer mysqlnd, o qual foi incluído no PHP desde 5.3 e tem sido o driver nativo padrão desde 5.4, como declarado aqui . Eu duvido que muitas pessoas estão usando versões mais antigas do que isso, então você deve ficar com elas get_result().

Isso essencialmente expõe a API de resultado mysqli regular e não preparada. Significa que, uma vez que você faz $result = get_result(), você pode usá-lo exatamente da mesma maneira que você usaria $result = $mysqli->query().

Agora você pode usar os métodos a seguir para buscar uma linha de cada vez ou todas de uma vez. Aqui estão alguns dos mais comuns, mas você pode dar uma olhada em toda a classe para todos os seus métodos.

Uma linha

• $result->fetch_assoc() - Buscar um array associativo
• $result->fetch_row() - Buscar um array numérico
• $result->fetch_object() - Buscar uma matriz de objetos

Todos

• $result->fetch_all(MYSQLI_ASSOC) - Buscar um array associativo
• $result->fetch_all(MYSQLI_NUM) - Buscar um array numérico

$stmt = $mysqli->prepare("SELECT * FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
while($row = $result->fetch_assoc()) {
  $ids[] = $row['id'];
  $names[] = $row['name'];
  $ages[] = $row['age'];
}
var_export($ages);
$stmt->close();
cópia de

Saída:

[22, 18, 19, 27, 36, 7]
cópia de

bind_result ()

Você pode estar se perguntando, por que usar mesmo bind_result()? Pessoalmente, acho que é muito inferior get_result()em todos os cenários, exceto quando se busca uma única linha em variáveis ??separadas. Além disso, antes de get_result()existir e o mysqlnd ser construído em PHP, esta era sua única opção, e é por isso que muitos códigos legados podem estar sendo usados.

A parte mais irritante sobre o uso bind_result()é que você deve ligar cada coluna que você selecionar e, em seguida, percorrer os valores em um loop. Isso obviamente não é ideal para uma infinidade de valores ou para usar com *. O seletor de estrelas é especialmente irritante de usar bind_result(), já que você nem sabe quais são esses valores sem procurar no banco de dados. Além disso, isso torna o seu código excessivamente insustentável com alterações na tabela. Isso normalmente não importa, já que você não deveria estar usando o seletor de caractere curinga no modo de produção de qualquer maneira (mas você sabe que está).

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows === 0) exit('No rows');
$stmt->bind_result($idRow, $nameRow, $ageRow); 
while ($stmt->fetch()) {
  $ids[] = $idRow;
  $names[] = $nameRow;
  $ages[] = $ageRow;
}
var_export($ids);
$stmt->close();
cópia de

Saída:

[106, 221, 3, 55, 583, 72]
cópia de

Buscar Matriz Associativa

Eu acho que este é o caso de uso mais comum normalmente. Eu também utilizarei o encadeamento no seguinte, embora isso obviamente não seja necessário.

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
cópia de

Se você precisar modificar o conjunto de resultados, provavelmente deverá usar um loop while fetch_assoc()e buscar cada linha, uma de cada vez.

$arr = [];
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
if(!$arr) exit('No rows');
while($row = $result->fetch_assoc()) {
  $arr[] = $row;
}
var_export($arr);
$stmt->close();
cópia de

Saída:

[
  ['id' => 27, 'name' => 'Jessica', 'age' => 27], 
  ['id' => 432, 'name' => 'Jimmy', 'age' => 19]
]
cópia de

Você pode fazer isso usando bind_result()também, embora claramente não tenha sido projetado para isso. Aqui está uma solução inteligente , embora eu pessoalmente ache que é algo legal saber que é possível, mas realisticamente não deve ser usado.

Buscar Matriz Numérica

Isso segue o mesmo formato de um array associativo. Para obter a matriz inteira em um comando, sem um loop, você usaria mysqli_result->fetch_all(MYSQLI_NUM). Se você precisa buscar os resultados em um loop, você deve usar mysqli_result->fetch_row().

$stmt = $mysqli->prepare("SELECT location, favorite_color, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_NUM);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
cópia de

E, claro, a adaptação do loop while.

$arr = [];
$stmt = $mysqli->prepare("SELECT location, favorite_color, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
if(!$arr) exit('No rows');
while($row = $result->fetch_row()) {
  $arr[] = $row;
}
var_export($arr);
$stmt->close();
cópia de

Saída:

[
  ['Boston', 'green', 28], 
  ['Seattle', 'blue', 49],
  ['Atlanta', 'pink', 24]
]
cópia de

Buscar fileira única

Eu pessoalmente acho mais simples de usar bind_result()quando eu sei que só vou buscar uma linha, já que posso acessar as variáveis ??de uma maneira mais limpa.

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows === 0) exit('No rows');
$stmt->bind_result($id, $name, $age);
$stmt->fetch();
echo $name; //Output: 'Ryan'
$stmt->close();
cópia de

Agora você pode simplesmente usar as variáveis bind_result(), como $namese soubesse que elas só conteriam um valor, não um array.

Aqui está a get_result()versão:

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_assoc();
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
cópia de

Você usaria então a variável como $arr['id']por exemplo.

Saída:

['id' => 36, 'name' => 'Kevin', 'age' => 39]
cópia de

Buscar Matriz de Objetos

Isso é muito semelhante a buscar um array associativo. A única diferença principal é que você estará acessando como $arr[0]->age. Além disso, caso você não saiba, os objetos são por valor, enquanto os arrays são por referência.

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
while($row = $result->fetch_object()) {
  $arr[] = $row;
}
var_export($arr);
$stmt->close();
cópia de

Saída:

[
  stdClass Object ['id' => 27, 'name' => 'Jessica', 'age' => 27], 
  stdClass Object ['id' => 432, 'name' => 'Jimmy', 'age' => 19]
]
cópia de

Você também pode adicionar valores de propriedade a uma classe existente. No entanto, deve-se notar que há uma pegadinha em potencial, de acordo com este comentário nos documentos do PHP. O problema é que, se você tiver um valor padrão em seu construtor com um nome de variável duplicado, ele buscará o objeto primeiro e, em seguida, definirá o valor do construtor, sobrescrevendo o resultado buscado. Estranhamente, havia um "bug" do PHP 5.6.21 para o 7.0.6, onde isso não aconteceria. Mesmo que isso viole os princípios da POO, algumas pessoas gostariam desse recurso, apesar de ter sido bug em certas versões. Algo como PDO::FETCH_PROPS_LATEno PDO deve ser implementado no MySQLi para lhe dar a opção de escolher.

class myClass {}
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
while($row = $result->fetch_object('myClass')) {
  $arr[] = $row;
}
var_export($arr);
$stmt->close();
cópia de

Como o comentário afirma, é assim que você faria corretamente. Tudo o que você precisa é de uma condição if simples para verificar se a variável é igual ao valor do construtor - se não, simplesmente não a defina no construtor. Isso é essencialmente o mesmo que usar PDO::FETCH_PROPS_LATEno PDO.

class myClass {
  private $id;
  public function __construct($id = 0) {
    if($this->id === 0) $this->id = $id;
  }
}
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
while($row = $result->fetch_object('myClass')) {
  $arr[] = $row;
}
var_export($arr);
$stmt->close();
cópia de

Outro comportamento inesperado, mas potencialmente útil, de usar fetch_object('myClass')has é que você pode modificar variáveis ??privadas. Eu realmente não tenho certeza de como me sinto sobre isso, pois isso parece violar princípios de encapsulamento.

Conclusão

bind_result () - melhor usado para buscar linha única sem muitas colunas ou *; extremamente deselegante para matrizes associativas.

get_result () - é o preferido para quase todos os casos de uso.

Gostar

Você provavelmente pensaria que poderia fazer algo como:

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE Name LIKE %?%"); 
cópia de

Mas isso não é permitido. O ?espaço reservado deve ser a cadeia inteira ou valor literal inteiro. É assim que você faria corretamente.

$search = "%{$_POST['search']}%";
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name LIKE ?"); 
$stmt->bind_param("s", $search);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
cópia de

Onde na matriz

Isso é definitivamente algo que eu gostaria de ver melhorado no MySQLi. Por enquanto, usar as instruções preparadas do MySQLi WHERE INé possível, mas parece um pouco longo.

Nota: Os dois exemplos a seguir usam o operador splat para descompactação de argumentos, que requer o PHP 5.6+. Se você estiver usando uma versão inferior a essa, poderá substituí-la por call_user_func_array().

$inArr = [12, 23, 44];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$types = str_repeat('i', count($inArr)); //create 3 ints for bind_param
$stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause)");
$stmt->bind_param($types, ...$inArr);
$stmt->execute();
$resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt->close();
cópia de

Com outros espaços reservados

O primeiro exemplo mostrou como usar a WHERE INcláusula com espaço reservado fictício somente dentro dela. E se você quisesse usar outros marcadores em lugares diferentes?

$inArr = [12, 23, 44];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$types = str_repeat('i', count($inArr)); //create 3 ints for bind_param
$types .= 'i'; //add 1 more int type
$fullArr = array_merge($inArr, [26]); //merge WHERE IN array with other value(s)
$stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause) AND age > ?");
$stmt->bind_param($types, ...$fullArr); //4 placeholders to bind
$stmt->execute();
$resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt->close();
cópia de

Múltiplas instruções preparadas em transações 

Isso pode parecer estranho porque justificaria sua própria seção, já que você pode literalmente usar declarações preparadas uma após a outra. Embora isso certamente funcione, isso não garante que suas consultas sejam atômicas. Isso significa que, se você executasse dez consultas e uma falhas, as outras nove ainda seriam bem-sucedidas. Se você quiser que suas consultas SQL sejam executadas somente se todas tiverem êxito, você deverá usar as transações.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
  $mysqli->autocommit(FALSE); //turn on transactions
  $stmt1 = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  $stmt2 = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?");
  $stmt1->bind_param("si", $_POST['name'], $_POST['age']);
  $stmt2->bind_param("si", $_POST['name'], $_SESSION['id']);
  $stmt1->execute();
  $stmt2->execute();
  $stmt1->close();
  $stmt2->close();
  $mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
} catch(Exception $e) {
  $mysqli->rollback(); //remove all queries from queue if error (undo)
  error_log($e);
}
cópia de

Reutilizar o mesmo modelo, valores diferentes

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
  $mysqli->autocommit(FALSE); //turn on transactions
  $stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  $stmt->bind_param("si", $name, $age);
  $name = 'John';
  $age = 21;
  $stmt->execute();  
  $name = 'Rick';
  $age = 24;
  $stmt->execute();
  $stmt->close();
  $mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
} catch(Exception $e) {
  $mysqli->rollback(); //remove all queries from queue if error (undo)
  error_log($e);
}
cópia de

Tratamento de erros

Erro fatal: erro não detectado: chamar a uma função de membro bind_param () em boolean

Qualquer um que tenha usado instruções preparadas do MySQLi viu essa mensagem em algum momento, mas o que isso significa? Quase nada. Então, como você conserta isso, você pode perguntar?

Manipulação de exceção

Todas as funções mysqli retornam false em caso de falha, então você pode facilmente verificar a veracidade de cada função e reportar erros $mysqli->error. No entanto, isso é muito tedioso e há uma maneira mais elegante de fazer isso se você ativar o relatório interno. Eu recomendo fazê-lo desta maneira, pois é muito mais portátil do desenvolvimento para a produção.

Isso também pode ser usado na produção, desde que você tenha um log de erros configurado para todos os erros; isso precisa ser definido no php.ini. Por favor, nunca relate erros diretamente no seu site em produção. Você estará se chutando por um erro tão bobo. A colocação de mysqli_report()assuntos também. se você colocá-lo antes de criar uma nova conexão, ele também emitirá sua senha; caso contrário, ele apenas relatará tudo depois, como suas consultas.

Aqui está como seu arquivo php.ini deve se parecer em produção: faça ambos display_errors = Offe log_errors = On. Além disso, lembre-se de que cada página deve estar usando apenas um único try/catchbloco global , em vez de agrupar cada consulta individualmente. A única exceção a isso é com transações, que seriam aninhadas, mas lançam sua própria exceção, portanto, o global try/catchpode "capturá-lo".

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
  $stmt = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
  $stmt->bind_param("i", $_SESSION['id']);
  $stmt->execute();
  $stmt->close();
} catch (Exception $e) {
  error_log($e);
  exit('Error deleting');
}
cópia de

Manipulador de exceção personalizado

Como afirmado anteriormente , você pode alternativamente usar set_exception_handler()em cada página (ou um redirecionamento global). Isso se livrar da camada de aninhamento de chaves. Se você estiver usando transações, você ainda deve usar um try catch com isso, mas depois lançar sua própria exceção.

//include mysqli_connect.php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
set_exception_handler(function($e) {
  error_log($e);
  exit('Error deleting');
});
$stmt = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
$stmt->bind_param("i", $_SESSION['id']);
$stmt->execute();
$stmt->close();
cópia de

Gotcha com tratamento de exceção

Você esperaria que todos os erros do MySQLi fossem convertidos em exceções com mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT). Curiosamente, notei que ainda me dava um erro de advertência quando bind_param()havia muitas ou poucas variáveis ??ou tipos ligados. A mensagem emitida é a seguinte:

Aviso: mysqli_stmt :: bind_param (): Número de variáveis ??não corresponde ao número de parâmetros na instrução preparada

Uma solução para isso é usar um manipulador de erro global para acionar uma exceção. Um exemplo disso poderia ser:

set_error_handler(function($errno, $errstr, $errfile, $errline) {
  throw new Exception("$errstr on line $errline in file $errfile");
});
cópia de

Isso só aconteceu em avisos de tempo de execução, mas converti todos os erros em exceções. Não vejo problema em fazer isso, mas há algumas pessoas que são fortemente contra isso.

Alguns extras

Preciso de $ stmt-> close ()?

Ótima pergunta. Ambos $mysqli->close()e $stmt->close()essencialmente têm o mesmo efeito. O primeiro fecha a conexão do MySQLi, enquanto o segundo fecha a instrução preparada. TLDR; ambos na verdade não são necessários na maioria dos casos, já que ambos serão fechados assim que a execução do script estiver completa. Há também uma função para simplesmente liberar a memória associada ao resultado do MySQLi e a declaração preparada, respectivamente: $result->free()e $stmt->free(). Eu mesmo, provavelmente, nunca o usarei, mas se você estiver interessado, aqui está o do resultado e da consulta parametrizada . O seguinte também deve ser observado: ambos $stmt->close()e o final da execução do script liberarão a memória de qualquer maneira.

Veredicto final: Eu costumo fazer $mysqli->close()e $stmt->close(), embora possa ser argumentado que é um pouco supérfluo. Se você estiver planejando usar a mesma variável $stmtnovamente para outras instruções preparadas, feche-a ou use um nome de variável diferente, como $stmt2. Por fim, nunca encontrei a necessidade de simplesmente libertá-los, sem fechá-los.

Devo sempre estar usando instruções preparadas?

Eu posso ver a confusão para isso - a resposta é absolutamente não. Quero dizer, você pode, se quiser, mas não parece haver necessidade de escrever código extra e ser menos eficiente (embora eu tenha certeza de que é insignificante para a maioria das pessoas, a menos que você seja chamado de Mark Zuckerberg). De acordo com os documentos do PHP :

Usar uma instrução preparada nem sempre é a maneira mais eficiente de executar uma instrução. Uma instrução preparada executada apenas uma vez causa mais viagens de ida e volta cliente-servidor do que uma declaração não preparada.

Meu exemplo favorito de mostrar isso é quando você só precisa selecionar um escalar (um valor) da sua tabela. Confira este forro enfermo para selecionar a pessoa mais velha da sua mesa.

$oldest = $mysqli->query("SELECT MAX(age) AS oldest FROM myTable")->fetch_object()->oldest;
cópia de

Muito legal, certo? Então, se você não tem nenhum valor inserido pelo usuário, por que não apenas utilizar essa grandiosidade, se uma declaração preparada não for necessária.

O único argumento que posso ver para usar uma instrução preparada quando não é necessário, é se você está antecipando que você acabará adicionando valores inseridos pelo usuário. Isso pode ser considerado excelente pensamento preventivo na minha opinião.

Devo usar mysqli :: $ insert_id ou mysqli_stmt :: $ insert_id?

Parece quase como se fossem aliases, o que é verdade em quase todos os casos de uso. Ambos receberão a chave primária da última linha inserida. A única diferença é que isso será incorreto se você fizer $stmt->insert_iduma declaração de declaração preparada que é usada várias vezes. Eu não tinha ideia sobre esse comportamento, até que li o seguinte comentário sobre os documentos do PHP.

Deve-se observar que o uso mysqli_stmt->insert_idnão resultará em um ID exclusivo sendo retornado para cada execução de uma instrução de inserção preparada. Na prática, parece que o primeiro ID de inserção é retornado. Se você estiver executando várias inserções com a mesma instrução preparada (uma chamada mysqli_stmt::preparee várias invocações mysqli_stmt::execute()para uma determinada instrução) e precisar manter o ID exclusivo para cada inserção, use mysqli_connection->insert_id.

Assim, você provavelmente deve ficar com $mysqli->insert_id, já que executa a mesma tarefa, sem essa pegadinha.

Então, usando instruções preparadas significa que estou seguro contra invasores?

Enquanto você está seguro de injeção de SQL, você ainda precisa validar e higienizar seus dados inseridos pelo usuário. Você pode usar uma função como filter_var () para validar antes de inseri-lo no banco de dados e htmlspecialchars () para limpar depois de recuperá-lo.