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