Elkészített nyilatkozat - Prepared statement

Az adatbázis-kezelő rendszerekben (DBMS) az előkészített utasítás vagy paraméterezett utasítás az SQL-kód előzetes fordítására szolgáló szolgáltatás , amely elválasztja azt az adatoktól. Az elkészített nyilatkozatok előnyei:

• hatékonyságot, mert többször is használhatók újrafordítás nélkül
• biztonságát azáltal, hogy csökkenti vagy megszünteti az SQL -befecskendezési támadásokat

Az előkészített utasítás egy előre összeállított sablon formájában jelenik meg , amelybe minden egyes végrehajtás során állandó értékeket cserélnek, és általában SQL DML utasításokat használnak , például INSERT , SELECT vagy UPDATE .

Az elkészített nyilatkozatok általános munkafolyamata:

1. Előkészítés : Az alkalmazás létrehozza a kimutatássablont, és elküldi a DBMS -nek. Bizonyos értékek nem adhatók meg, úgynevezett paraméterek , helyőrzők vagy kötési változók (lent "?" Címkével):

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

2. Fordítás : A DBMS lefordítja (elemzi, optimalizálja és lefordítja) a kimutatássablont, és végrehajtása nélkül tárolja az eredményt.
3. Végrehajtás : Az alkalmazás értékeket szolgáltat (vagy köt ) az utasítássablon paramétereihez, a DBMS pedig végrehajtja a nyilatkozatot (esetleg eredményt ad vissza). Az alkalmazás kérheti a DBMS -t, hogy sokszor hajtsa végre az utasítást különböző értékekkel. A fenti példában az alkalmazás megadhatja a "bike" értékeket az első paraméterhez és a "10900" értéket a második paraméterhez, majd később a "shoes" és a "7400" értékeket.

Az elkészített utasítás alternatívája az SQL közvetlen hívása az alkalmazás forráskódjából, kód és adatok kombinálásával. A fenti példa közvetlen megfelelője:

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

A kimutatássablon összeállításakor nem minden optimalizálás hajtható végre, két okból: a legjobb terv függhet a paraméterek konkrét értékeitől, és a legjobb terv változhat, ahogy a táblázatok és az indexek idővel változnak.

Másrészről, ha a lekérdezést csak egyszer hajtják végre, akkor a szerveroldali előkészületek lassabbak lehetnek a szerver felé irányuló további oda-visszaút miatt. A végrehajtási korlátozások teljesítménybüntetésekhez is vezethetnek; például a MySQL egyes verziói nem mentették gyorsítótárba az előkészített lekérdezéseket. Hasonló előnyökkel jár egy tárolt eljárás , amelyet szintén előre lefordítunk, és a szerveren tárolunk a későbbi végrehajtáshoz. A tárolt eljárásokkal ellentétben az előkészített utasításokat rendszerint nem írják le eljárási nyelven, és nem használhatnak vagy módosíthatnak változókat, illetve nem használhatnak vezérlőfolyamat -struktúrákat, helyette a deklaratív adatbázis lekérdezési nyelvére támaszkodva. Egyszerűségük és ügyféloldali emulációjuk miatt az elkészített nyilatkozatok hordozhatóbbak a szállítók között.

Szoftver támogatás

A főbb DBMS -ek , köztük az SQLite, a MySQL , az Oracle , a DB2 , a Microsoft SQL Server és a PostgreSQL támogatják az elkészített utasításokat. Az előkészített utasításokat rendszerint nem SQL bináris protokollon keresztül hajtják végre a hatékonyság és az SQL-befecskendezés elleni védelem érdekében, de néhány DBMS-sel, például a MySQL-vel, az előkészített állítások SQL szintaxis segítségével is elérhetők hibakeresési célokra. Az

Számos programozási nyelv támogatja elkészített kimutatások azok szabványos könyvtárakat, és utánozni őket a kliens oldalon is, ha a mögöttes adatbázis-kezelő nem támogatja, köztük a Java „s JDBC , Perl ” s DBI , PHP „s OEM és Python ” s DB -API. Az ügyféloldali emuláció gyorsabb lehet a csak egyszer végrehajtott lekérdezéseknél, csökkentve a szerverre irányuló oda-vissza utak számát, de általában lassabb a többször végrehajtott lekérdezéseknél. Egyformán hatékonyan ellenáll az SQL injekciós támadásoknak.

Az SQL befecskendezési támadások sok típusa kiküszöbölhető a literálok letiltásával , ami ténylegesen megköveteli az előkészített utasítások használatát; 2007 -től csak a H2 támogatja ezt a funkciót.

Példák

Java JDBC

Ez a példa Java -t és JDBC -t használ :

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));
      }
    }
  }
}

A Java minden fontosabb beépített adattípushoz PreparedStatement"beállítókat" ( setInt(int), setString(String), setDouble(double),stb.) Biztosít .

PHP OEM

Ez a példa PHP -t és PDO -t használ :

<?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

Ez a példa Perl és DBI -t használ :

#!/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

Ez a példa a C# és az ADO.NET verziót használja :

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())
    {
        // ...
    }
}

Az ADO.NET SqlCommandbármilyen típusú valueparamétert elfogad AddWithValue, és a típuskonverzió automatikusan megtörténik. Vegye figyelembe, hogy a "megnevezett paraméterek" (azaz "@username") használata helyett "?"- ez lehetővé teszi, hogy a paramétert többször és tetszőleges sorrendben használja a lekérdezési parancs szövegében.

Az AddWithValue metódus azonban nem használható változó hosszúságú adattípusokkal, például varchar és nvarchar. Ennek az az oka, hogy a .NET feltételezi, hogy a paraméter hossza az adott érték hossza, ahelyett, hogy a tényleges hosszúságot reflexió útján kapná meg az adatbázisból. Ennek az a következménye, hogy minden lekérdezési tervet összeállítanak és tárolnak minden különböző hosszúságra. Általában a "duplikált" tervek maximális száma az adatbázisban meghatározott változó hosszúságú oszlopok hosszának szorzata. Emiatt fontos a szabványos Hozzáadás módszer használata a változó hosszúságú oszlopokhoz:

command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue, ahol a ParamLength az adatbázisban megadott hosszúság.

Mivel a változó hosszúságú adattípusokhoz a szabványos Hozzáadási módszert kell használni, jó szokás minden paramétertípusnál használni.

Python DB-API

Ez a példa Python -t és DB-API-t használ :

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

Ez a példa a közvetlen SQL -t használja a negyedik generációs nyelvből, például az eDeveloperből, az uniPaaS -ból és a Magic XPA -ból

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

A PureBasic (v5.40 LTS óta) 7 típusú kapcsolatot képes kezelni a következő parancsokkal

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

Az adatbázis típusától függően 2 különböző módszer létezik

Mert SQLite , ODBC , MariaDB / Mysql használat:?

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

Mert PostgreSQL használata: $ 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

Hivatkozások