Declarație pregătită - Prepared statement

În sistemele de gestionare a bazelor de date (SGBD), o instrucțiune pregătită sau o instrucțiune parametrizată este o caracteristică utilizată pentru precompilarea codului SQL , separându-l de date. Avantajele declarațiilor pregătite sunt:

• eficiență, deoarece pot fi utilizate în mod repetat fără recompilare
• securitate, prin reducerea sau eliminarea atacurilor de injecție SQL

O instrucțiune pregătită ia forma unui șablon precompilat în care valorile constante sunt substituite în timpul fiecărei execuții și utilizează de obicei instrucțiuni SQL DML precum INSERT , SELECT sau UPDATE .

Un flux de lucru comun pentru declarațiile pregătite este:

1. Pregătiți : aplicația creează șablonul de declarație și îl trimite la SGBD. Anumite valori sunt lăsate nespecificate, numite parametri , substituenți sau variabile de legare (etichetate „?” Mai jos):

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

2. Compilați : SGBD compilează (analizează, optimizează și traduce) șablonul de instrucțiuni și stochează rezultatul fără a-l executa.
3. Executați : aplicația furnizează (sau leagă ) valori pentru parametrii șablonului de instrucțiuni, iar SGBD execută instrucțiunea (eventual returnând un rezultat). Aplicația poate solicita SGBD să execute declarația de multe ori cu valori diferite. În exemplul de mai sus, aplicația ar putea furniza valorile „bicicletă” pentru primul parametru și „10900” pentru al doilea parametru, apoi valorile „pantofi” și „7400”.

Alternativa la o declarație pregătită este apelarea SQL direct din codul sursă al aplicației într-un mod care combina codul și datele. Echivalentul direct al exemplului de mai sus este:

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

Nu toate optimizările pot fi realizate în momentul compilării șablonului de instrucțiuni, din două motive: cel mai bun plan poate depinde de valorile specifice ale parametrilor și cel mai bun plan se poate modifica pe măsură ce tabelele și indexurile se schimbă în timp.

Pe de altă parte, dacă o interogare este executată o singură dată, instrucțiunile pregătite de server pot fi mai lente din cauza călătoriei suplimentare către server. Limitările de implementare pot duce, de asemenea, la sancțiuni de performanță; de exemplu, unele versiuni ale MySQL nu au ascuns în cache rezultatele interogărilor pregătite. O procedură stocată , care este, de asemenea, precompilată și stocată pe server pentru o execuție ulterioară, are avantaje similare. Spre deosebire de o procedură stocată, o declarație pregătită nu este scrisă în mod normal într-un limbaj procedural și nu poate utiliza sau modifica variabile sau utiliza structuri de flux de control, bazându-se în schimb pe limbajul de interogare a bazei de date declarative. Datorită simplității și emulației clientului, declarațiile pregătite sunt mai portabile de la furnizori.

Suport software

SGBD-urile majore , inclusiv SQLite, MySQL , Oracle , DB2 , Microsoft SQL Server și PostgreSQL acceptă declarații pregătite. Instrucțiunile pregătite sunt executate în mod normal printr-un protocol binar non-SQL pentru eficiență și protecție împotriva injecției SQL, dar cu unele DBMS-uri, precum MySQL, instrucțiunile pregătite sunt, de asemenea, disponibile utilizând o sintaxă SQL în scopuri de depanare. The

O serie de limbaje de programare acceptă declarații pregătite în bibliotecile lor standard și le vor emula pe partea clientului chiar dacă SGBD-ul de bază nu le acceptă, inclusiv JDBC- ul Java , DBI- ul Perl , PDO- ul PHP și DB-ul Python -API. Emularea din partea clientului poate fi mai rapidă pentru interogările care sunt executate o singură dată, prin reducerea numărului de călătorii dus-întors către server, dar de obicei este mai lentă pentru interogările executate de mai multe ori. Rezistă la fel de eficient la atacurile de injecție SQL.

Multe tipuri de atacuri de injecție SQL pot fi eliminate prin dezactivarea literelor , necesitând în mod eficient utilizarea instrucțiunilor pregătite; începând din 2007 doar H2 acceptă această caracteristică.

Exemple

Java JDBC

Acest exemplu folosește Java și 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 PreparedStatementoferă „setere” ( setInt(int), setString(String), setDouble(double),etc.) pentru toate tipurile majore de date încorporate.

PHP DOP

Acest exemplu folosește PHP și 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

Acest exemplu folosește Perl și 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

Acest exemplu folosește C # și 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 SqlCommandva accepta orice tip pentru valueparametrul AddWithValue, iar conversia de tip are loc automat. Rețineți utilizarea „parametrilor denumiți” (adică "@username"), mai degrabă decât "?"— acest lucru vă permite să utilizați un parametru de mai multe ori și în orice ordine arbitrară în textul comenzii de interogare.

Cu toate acestea, metoda AddWithValue nu trebuie utilizată cu tipuri de date cu lungime variabilă, cum ar fi varchar și nvarchar. Acest lucru se datorează faptului că .NET presupune ca lungimea parametrului să fie lungimea valorii date, mai degrabă decât să obțină lungimea reală din baza de date prin reflecție. Consecința acestui fapt este că un plan de interogare diferit este compilat și stocat pentru fiecare lungime diferită. În general, numărul maxim de planuri „duplicate” este produsul lungimilor coloanelor cu lungime variabilă, așa cum se specifică în baza de date. Din acest motiv, este important să folosiți metoda standard Add pentru coloanele cu lungime variabilă:

command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue, unde ParamLength este lungimea specificată în baza de date.

Deoarece metoda standard Add trebuie utilizată pentru tipurile de date cu lungime variabilă, este o obișnuință bună să o utilizați pentru toate tipurile de parametri.

Python DB-API

Acest exemplu folosește Python și 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

Acest exemplu folosește Direct SQL din limbajul de a patra generație precum eDeveloper, uniPaaS și magic XPA de la 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 (de la v5.40 LTS) poate gestiona 7 tipuri de legături cu următoarele comenzi

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

Există 2 metode diferite în funcție de tipul bazei de date

Pentru SQLite , ODBC , MariaDB / Mysql utilizați:?

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

Pentru utilizarea PostgreSQL : 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

Referințe