Declaração preparada - Prepared statement

Em sistemas de gerenciamento de banco de dados (DBMS), uma instrução preparada ou instrução parametrizada é um recurso usado para pré-compilar o código SQL , separando-o dos dados. Os benefícios das declarações preparadas são:

• eficiência, porque eles podem ser usados ​​repetidamente sem recompilar
• segurança, reduzindo ou eliminando ataques de injeção de SQL

Uma instrução preparada assume a forma de um modelo pré-compilado no qual os valores constantes são substituídos durante cada execução e, normalmente, usam instruções SQL DML como INSERT , SELECT ou UPDATE .

Um fluxo de trabalho comum para instruções preparadas é:

1. Prepare : O aplicativo cria o modelo de instrução e o envia ao DBMS. Certos valores não são especificados, chamados de parâmetros , marcadores de posição ou variáveis ​​de ligação (marcados com "?" Abaixo):

   INSERT INTO products (name, price) VALUES (?, ?);

2. Compilar : O SGBD compila (analisa, otimiza e traduz) o modelo de instrução e armazena o resultado sem executá-lo.
3. Executar : O aplicativo fornece (ou vincula ) valores para os parâmetros do modelo de instrução e o SGBD executa a instrução (possivelmente retornando um resultado). O aplicativo pode solicitar que o SGBD execute a instrução muitas vezes com valores diferentes. No exemplo acima, o aplicativo pode fornecer os valores "bike" para o primeiro parâmetro e "10900" para o segundo parâmetro e, posteriormente, os valores "shoes" e "7400".

A alternativa para uma instrução preparada é chamar SQL diretamente do código-fonte do aplicativo de uma forma que combine código e dados. O equivalente direto ao exemplo acima é:

INSERT INTO products (name, price) VALUES ("bike", "10900");

Nem toda otimização pode ser executada no momento em que o modelo de instrução é compilado, por dois motivos: o melhor plano pode depender dos valores específicos dos parâmetros e o melhor plano pode mudar conforme as tabelas e índices mudam com o tempo.

Por outro lado, se uma consulta for executada apenas uma vez, as instruções preparadas do lado do servidor podem ser mais lentas por causa da viagem de ida e volta adicional para o servidor. As limitações de implementação também podem levar a penalidades de desempenho; por exemplo, algumas versões do MySQL não armazenavam em cache os resultados de consultas preparadas. Um procedimento armazenado , que também é pré-compilado e armazenado no servidor para execução posterior, tem vantagens semelhantes. Ao contrário de um procedimento armazenado, uma instrução preparada não é normalmente escrita em uma linguagem procedural e não pode usar ou modificar variáveis ​​ou usar estruturas de fluxo de controle, dependendo, em vez disso, da linguagem de consulta de banco de dados declarativa. Devido à sua simplicidade e emulação do lado do cliente, as instruções preparadas são mais portáteis entre os fornecedores.

Suporte de software

Os principais DBMSs , incluindo SQLite, MySQL , Oracle , DB2 , Microsoft SQL Server e PostgreSQL, oferecem suporte a declarações preparadas. As instruções preparadas são normalmente executadas por meio de um protocolo binário não SQL para eficiência e proteção contra injeção de SQL, mas com alguns SGBDs, como o MySQL, as instruções preparadas também estão disponíveis usando uma sintaxe SQL para fins de depuração. o

Uma série de linguagens de programação suportam instruções preparadas em suas bibliotecas padrão e irão emulá-las no lado do cliente, mesmo se o DBMS subjacente não as suportar, incluindo Java 's JDBC , Perl 's DBI , PHP 's PDO e Python 's DB -API. A emulação do lado do cliente pode ser mais rápida para consultas que são executadas apenas uma vez, reduzindo o número de idas e voltas ao servidor, mas geralmente é mais lenta para consultas executadas muitas vezes. Ele resiste a ataques de injeção de SQL de forma igualmente eficaz.

Muitos tipos de ataques de injeção de SQL podem ser eliminados desativando literais , exigindo efetivamente o uso de instruções preparadas; a partir de 2007, apenas H2 oferece suporte a esse recurso.

Exemplos

Java JDBC

Este exemplo usa Java e JDBC :

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

  public static void main(String[] args) throws SQLException {
    MysqlDataSource ds = new MysqlDataSource();
    ds.setDatabaseName("mysql");
    ds.setUser("root");

    try (Connection conn = ds.getConnection()) {
      try (Statement stmt = conn.createStatement()) {
        stmt.executeUpdate("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)");
      }

      try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO products VALUES (?, ?)")) {
        stmt.setString(1, "bike");
        stmt.setInt(2, 10900);
        stmt.executeUpdate();
        stmt.setString(1, "shoes");
        stmt.setInt(2, 7400);
        stmt.executeUpdate();
        stmt.setString(1, "phone");
        stmt.setInt(2, 29500);
        stmt.executeUpdate();
      }

      try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM products WHERE name = ?")) {
        stmt.setString(1, "shoes");
        ResultSet rs = stmt.executeQuery();
        rs.next();
        System.out.println(rs.getInt(2));
      }
    }
  }
}

Java PreparedStatementfornece "configuradores" ( setInt(int), setString(String), setDouble(double),etc.) para todos os principais tipos de dados integrados.

PHP PDO

Este exemplo usa PHP e PDO :

<?php

try {
    // Connect to a database named "mysql", with the password "root"
    $connection = new PDO('mysql:dbname=mysql', 'root');

    // Execute a request on the connection, which will create
    // a table "products" with two columns, "name" and "price"
    $connection->exec('CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)');

    // Prepare a query to insert multiple products into the table
    $statement = $connection->prepare('INSERT INTO products VALUES (?, ?)');
    $products  = [
        ['bike', 10900],
        ['shoes', 7400],
        ['phone', 29500],
    ];

    // Iterate through the products in the "products" array, and
    // execute the prepared statement for each product
    foreach ($products as $product) {
        $statement->execute($product);
    }

    // Prepare a new statement with a named parameter
    $statement = $connection->prepare('SELECT * FROM products WHERE name = :name');
    $statement->execute([
        ':name' => 'shoes',
    ]);

    // Use array destructuring to assign the product name and its price
    // to corresponding variables
    [ $product, $price ] = $statement->fetch();

    // Display the result to the user
    echo "The price of the product {$product} is \${$price}.";

    // Close the cursor so `fetch` can eventually be used again
    $statement->closeCursor();
} catch (\Exception $e) {
    echo 'An error has occurred: ' . $e->getMessage();
}

Perl DBI

Este exemplo usa Perl e DBI :

#!/usr/bin/perl -w
use strict;
use DBI;

my ($db_name, $db_user, $db_password) = ('my_database', 'moi', 'Passw0rD');
my $dbh = DBI->connect("DBI:mysql:database=$db_name", $db_user, $db_password,
    { RaiseError => 1, AutoCommit => 1})
    or die "ERROR (main:DBI->connect) while connecting to database $db_name: " .
        $DBI::errstr . "\n";

$dbh->do('CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)');

my $sth = $dbh->prepare('INSERT INTO products VALUES (?, ?)');
$sth->execute(@$_) foreach ['bike', 10900], ['shoes', 7400], ['phone', 29500];

$sth = $dbh->prepare("SELECT * FROM products WHERE name = ?");
$sth->execute('shoes');
print "$$_[1]\n" foreach $sth->fetchrow_arrayref;
$sth->finish;

$dbh->disconnect;

C # ADO.NET

Este exemplo usa C # e ADO.NET :

using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room";
    command.Parameters.AddWithValue("@username", username);
    command.Parameters.AddWithValue("@room", room);

    using (SqlDataReader dataReader = command.ExecuteReader())
    {
        // ...
    }
}

ADO.NET SqlCommandaceitará qualquer tipo para o valueparâmetro de AddWithValue, e a conversão de tipo ocorre automaticamente. Observe o uso de "parâmetros nomeados" (ou seja, "@username") em vez de "?"- isso permite que você use um parâmetro várias vezes e em qualquer ordem arbitrária no texto do comando de consulta.

No entanto, o método AddWithValue não deve ser usado com tipos de dados de comprimento variável, como varchar e nvarchar. Isso ocorre porque o .NET assume que o comprimento do parâmetro é o comprimento do valor fornecido, em vez de obter o comprimento real do banco de dados por meio de reflexão. A consequência disso é que um plano de consulta diferente é compilado e armazenado para cada comprimento diferente. Em geral, o número máximo de planos "duplicados" é o produto dos comprimentos das colunas de comprimento variável conforme especificado no banco de dados. Por esse motivo, é importante usar o método Add padrão para colunas de comprimento variável:

command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue, em que ParamLength é o comprimento conforme especificado no banco de dados.

Como o método Add padrão precisa ser usado para tipos de dados de comprimento variável, é um bom hábito usá-lo para todos os tipos de parâmetro.

Python DB-API

Este exemplo usa Python e DB-API:

import mysql.connector

with mysql.connector.connect(database="mysql", user="root") as conn:
    with conn.cursor(prepared=True) as cursor:
        cursor.execute("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)")
        params = [("bike", 10900),
                  ("shoes", 7400),
                  ("phone", 29500)]
        cursor.executemany("INSERT INTO products VALUES (%s, %s)", params)
        params = ("shoes",)
        cursor.execute("SELECT * FROM products WHERE name = %s", params)
        print(cursor.fetchall()[0][1])

Magic Direct SQL

Este exemplo usa Direct SQL da linguagem de quarta geração como eDeveloper, uniPaaS e magic XPA da Magic Software Enterprises

Virtual username  Alpha 20   init: 'sister'
Virtual password  Alpha 20   init: 'yellow'

SQL Command:   SELECT * FROM users WHERE USERNAME=:1 AND PASSWORD=:2

Input Arguments: 
1:  username
2:  password

PureBasic

PureBasic (desde v5.40 LTS) pode gerenciar 7 tipos de link com os seguintes comandos

SetDatabaseBlob, SetDatabaseDouble, SetDatabaseFloat, SetDatabaseLong, SetDatabaseNull, SetDatabaseQuad, SetDatabaseString

Existem 2 métodos diferentes, dependendo do tipo de banco de dados

Para SQLite , ODBC , MariaDB / Mysql use:?

  SetDatabaseString(#Database, 0, "test")  
  If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=?")    
    ; ...
  EndIf

Para PostgreSQL, use: $ 1, $ 2, $ 3, ...

  SetDatabaseString(#Database, 0, "Smith") ; -> $1 
  SetDatabaseString(#Database, 1, "Yes")   ; -> $2
  SetDatabaseLong  (#Database, 2, 50)      ; -> $3
 
  If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=$1 AND active=$2 AND years>$3")    
    ; ...
  EndIf

Referências