Připravené prohlášení - Prepared statement

V systémech pro správu databází (DBMS) je připravený příkaz nebo parametrizovaný příkaz funkcí používanou k předkompilaci kódu SQL , která jej odděluje od dat. Přínosy připravených prohlášení jsou:

• efektivitu, protože je lze použít opakovaně bez opětovného kompilace
• zabezpečení, snížením nebo odstraněním útoků s injekcí SQL

Připravený příkaz má formu předkompilované šablony, do které se při každém provádění nahrazují konstantní hodnoty, a obvykle používá příkazy SQL DML, jako je INSERT , SELECT nebo UPDATE .

Běžný pracovní postup pro připravené příkazy je:

1. Příprava : Aplikace vytvoří šablonu výpisu a odešle ji do systému DBMS. Některé hodnoty zůstávají nespecifikovány, nazývají se parametry , zástupné symboly nebo proměnné vazby (níže označeny „?“):

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

2. Kompilovat : DBMS kompiluje (analyzuje, optimalizuje a překládá) šablonu příkazu a ukládá výsledek bez jeho spuštění.
3. Provést : Aplikace dodává (nebo váže ) hodnoty pro parametry šablony příkazu a DBMS provede příkaz (případně vrátí výsledek). Aplikace může požadovat, aby DBMS provedl příkaz mnohokrát s různými hodnotami. Ve výše uvedeném příkladu může aplikace zadat hodnoty „kolo“ pro první parametr a „10900“ pro druhý parametr a později hodnoty „boty“ a „7400“.

Alternativou k připravenému příkazu je volání SQL přímo ze zdrojového kódu aplikace způsobem, který kombinuje kód a data. Přímý ekvivalent výše uvedeného příkladu je:

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

V době kompilace šablony příkazu nelze provést veškerou optimalizaci, a to ze dvou důvodů: nejlepší plán může záviset na konkrétních hodnotách parametrů a nejlepší plán se může měnit, protože tabulky a indexy se v průběhu času mění.

Na druhou stranu, pokud je dotaz spuštěn pouze jednou, příkazy připravené na straně serveru mohou být pomalejší kvůli dalšímu zpáteční cestě na server. Implementační omezení mohou také vést k výkonnostním sankcím; například některé verze MySQL neukládaly výsledky připravených dotazů do mezipaměti. Uložené procedury , která je rovněž předkompilována a uloženy na serveru pro pozdější provedení, má podobné výhody. Na rozdíl od uložené procedury není připravený příkaz běžně napsán v procedurálním jazyce a nemůže používat ani upravovat proměnné ani používat struktury toku řízení, místo toho se spoléhá na deklarativní databázový dotazovací jazyk. Díky své jednoduchosti a emulaci na straně klienta jsou připravené příkazy přenositelnější mezi dodavateli.

Softwarová podpora

Hlavní DBMS , včetně SQLite, MySQL , Oracle , DB2 , Microsoft SQL Server a PostgreSQL, podporují připravená prohlášení. Připravené příkazy se běžně provádějí prostřednictvím binárního protokolu jiného než SQL, aby byla zajištěna účinnost a ochrana před injekcí SQL, ale u některých databázových systémů, jako je MySQL, jsou připravené příkazy k dispozici také pomocí syntaxe SQL pro účely ladění. The

Řada programovací jazyky podporují připravené prohlášení ve svých standardních knihoven a bude emulovat je na straně klienta i v případě, že podkladové DBMS je nepodporuje, včetně Javy je JDBC , Perl je DBI , PHP ‚s chráněným označením původu a Python ‘ s DB -API. Emulace na straně klienta může být rychlejší u dotazů, které jsou provedeny pouze jednou, snížením počtu zpáteční cesty na server, ale u dotazů spuštěných mnohokrát je obvykle pomalejší. Stejně efektivně odolává útokům na SQL injekci.

Mnoho typů útoků typu SQL injection lze eliminovat deaktivací doslovných znaků , což v podstatě vyžaduje použití připravených příkazů; od roku 2007 tuto funkci podporuje pouze H2 .

Příklady

Java JDBC

Tento příklad používá Javu a 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 PreparedStatementposkytuje "settery" ( setInt(int), setString(String), setDouble(double),atd.) Pro všechny hlavní vestavěné datové typy.

PHP PDO

Tento příklad používá PHP a 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

Tento příklad používá Perl a 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

Tento příklad používá C# a 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 SqlCommandpřijme jakýkoli typ pro valueparametr AddWithValuea převod typu proběhne automaticky. Všimněte si použití "pojmenovaných parametrů" (tj. "@username") Spíše než "?"- to vám umožňuje použít parametr vícekrát a v libovolném pořadí v textu příkazu dotazu.

Metoda AddWithValue by se však neměla používat s datovými typy s proměnnou délkou, jako jsou varchar a nvarchar. Důvodem je, že .NET předpokládá, že délka parametru je délka dané hodnoty, místo aby získávala skutečnou délku z databáze prostřednictvím reflexe. Důsledkem toho je, že je pro každou jinou délku sestaven a uložen jiný plán dotazů. Obecně platí, že maximální počet „duplicitních“ plánů je součin délek sloupců s proměnnou délkou, jak je uvedeno v databázi. Z tohoto důvodu je důležité použít standardní metodu Add pro sloupce s proměnnou délkou:

command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue, kde ParamLength je délka uvedená v databázi.

Protože pro datové typy s proměnnou délkou je třeba použít standardní metodu Add, je dobrým zvykem používat ji pro všechny typy parametrů.

Python DB-API

Tento příklad používá Python a 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

Tento příklad používá Direct SQL z jazyka čtvrté generace jako eDeveloper, uniPaaS a magic XPA od 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 (od v5.40 LTS) může spravovat 7 typů odkazů pomocí následujících příkazů

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

V závislosti na typu databáze existují 2 různé metody

Pro SQLite , ODBC , MariaDB/Mysql použijte :?

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

Pro PostgreSQL použijte: $ 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

Reference