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

Melhore Esta PƔgina

Aprenda Como Melhorar Esta PĆ”gina ā€¢ Envie uma SolicitaĆ§Ć£o de ModificaĆ§Ć£o ā€¢ Reporte um Problema
ļ¼‹adicionar nota

Notas de UsuƔrios 3 notes

up
219
adam at pyramidpower dot com dot au Ā¶
16 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
3
theking2(at)king.ma Ā¶
2 years 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;
  }
up
7
w37090 at yandex dot ru Ā¶
6 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();
            }
        }
    }
ļ¼‹adicionar nota