Lausunto - Prepared statement

Vuonna DBMS (DBMS), joka on valmistettu lausuman tai parameterized selvitys on ominaisuus, jota käytetään ennalta koota SQL-koodia , joka erottaa sen tietoja. Valmistettujen lausuntojen edut ovat:

• tehokkuutta, koska niitä voidaan käyttää toistuvasti ilman uudelleen kääntämistä
• turvallisuus, vähentämällä tai poistamalla SQL-injektio -iskut

Valmis lausunto on esimääritetyn mallin muodossa, johon korvataan vakioarvot jokaisen suorituksen aikana ja käytetään tyypillisesti SQL DML -lausekkeita , kuten INSERT , SELECT tai UPDATE .

Valmiiden lausuntojen yleinen työnkulku on:

1. Valmistele : Sovellus luo lausuntomallin ja lähettää sen DBMS -järjestelmään. Tietyt arvot jätetään määrittämättä, nimeltään parametrit , paikkamerkit tai sidosmuuttujat (merkitty "?" Alla):

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

2. Käännä : DBMS kääntää (jäsentää, optimoi ja kääntää) lausepohjan ja tallentaa tuloksen suorittamatta sitä.
3. Suorita : Sovellus toimittaa (tai sitoo ) arvot lausekemallin parametreille ja DBMS suorittaa käskyn (mahdollisesti palauttaa tuloksen). Sovellus voi pyytää DBMS: ää suorittamaan lauseen monta kertaa eri arvoilla. Yllä olevassa esimerkissä sovellus saattaa antaa arvot "pyörä" ensimmäiselle parametrille ja "10900" toiselle parametrille ja myöhemmin arvot "kengät" ja "7400".

Vaihtoehto valmiille lausunnolle on kutsua SQL suoraan sovelluksen lähdekoodista tavalla, joka yhdistää koodin ja tiedot. Edellisen esimerkin suora vastine on:

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

Kaikkia optimointeja ei voida suorittaa lausekemallin kokoamishetkellä kahdesta syystä: paras suunnitelma voi riippua parametrien erityisarvoista ja paras suunnitelma voi muuttua, kun taulukot ja indeksit muuttuvat ajan myötä.

Toisaalta, jos kysely suoritetaan vain kerran, palvelinpuolen valmistellut lausumat voivat olla hitaampia palvelimelle suunnatun edestakaisen matkan vuoksi. Toteutusrajoitukset voivat myös johtaa suoritusrangaistuksiin; esimerkiksi jotkin MySQL -versiot eivät välimuistittaneet valmiiden kyselyiden tuloksia. Tallennettu menettely , joka on myös esikäännetty ja tallennetaan palvelimelle myöhempää suoritusta varten, on samat edut. Toisin kuin tallennettu proseduuri, valmisteltua lausuntoa ei yleensä kirjoiteta prosessikielellä, eikä se voi käyttää tai muokata muuttujia tai käyttää ohjausvirtarakenteita sen sijaan, että se perustuu deklaratiiviseen tietokannan kyselykieleen. Yksinkertaisuuden ja asiakaspuolen emuloinnin vuoksi valmistellut lausunnot ovat kannettavampia eri toimittajien välillä.

Ohjelmistotuki

Suurimmat DBMS -järjestelmät , kuten SQLite, MySQL , Oracle , DB2 , Microsoft SQL Server ja PostgreSQL, tukevat valmiita lausuntoja. Valmistetut käskyt suoritetaan tavallisesti ei-SQL-binääriprotokollan avulla tehokkuuden ja suojauksen estämiseksi SQL: ltä, mutta joissakin DBMS-järjestelmissä, kuten MySQL: ssä, myös valmiita lausuntoja on saatavana käyttämällä SQL-syntaksia virheenkorjausta varten. The

Useat ohjelmointikielet tukevat valmis lausuntoja niiden standardikirjastot ja jäljitellä niitä asiakkaan puolelta, vaikka taustalla DBMS ei tue niitä, mukaan lukien Java : n JDBC , Perl n DBI , PHP : n SAN ja Python n DB -API. Asiakaspuolen emulointi voi olla nopeampi kyselyille, jotka suoritetaan vain kerran, vähentämällä palvelimelle suuntautuvien edestakaisten matkojen määrää, mutta yleensä hitaampi useasti suoritettujen kyselyiden yhteydessä. Se vastustaa SQL -injektiohyökkäyksiä yhtä tehokkaasti.

Monet SQL -ruiskutushyökkäysten tyypit voidaan poistaa käytöstä poistamalla literaalit , mikä edellyttää tehokkaasti valmiiden lausuntojen käyttöä; vuodesta 2007 alkaen vain H2 tukee tätä ominaisuutta.

Esimerkkejä

Java JDBC

Tässä esimerkissä käytetään Javaa ja JDBC : tä:

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 PreparedStatementtarjoaa "asetuksia" ( setInt(int), setString(String), setDouble(double),jne.) Kaikille tärkeimmille sisäänrakennetuille tietotyypeille.

PHP SAN

Tässä esimerkissä käytetään PHP: tä ja PDO : ta:

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

Tässä esimerkissä käytetään Perliä ja DBI : tä:

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

Tässä esimerkissä käytetään C# ja 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 SqlCommandhyväksyy minkä tahansa tyypin valueparametrille AddWithValue, ja tyyppimuunnos tapahtuu automaattisesti. Huomaa, että "nimettyjen parametrien" (eli "@username") käytön sijaan "?"- tämän avulla voit käyttää parametria useita kertoja ja missä tahansa mielivaltaisessa järjestyksessä kyselykomennon tekstissä.

AddWithValue -menetelmää ei kuitenkaan pitäisi käyttää vaihtelevan pituisten tietotyyppien kanssa, kuten varchar ja nvarchar. Tämä johtuu siitä, että .NET olettaa, että parametrin pituus on annetun arvon pituus sen sijaan, että saisi todellisen pituuden tietokannasta heijastumisen kautta. Tästä seuraa, että jokaiselle eri pituudelle kootaan ja tallennetaan eri kyselysuunnitelma. Yleensä "päällekkäisten" suunnitelmien enimmäismäärä on tietokannassa määritettyjen muuttuvan pituisten sarakkeiden pituuksien tulos. Tästä syystä on tärkeää käyttää tavallista Add -menetelmää vaihtelevan pituisille sarakkeille:

command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue, jossa ParamLength on tietokannassa määritetty pituus.

Koska muuttuvan pituisille tietotyypeille on käytettävä tavallista Lisää -menetelmää, on hyvä tapa käyttää sitä kaikissa parametrityypeissä.

Python DB-API

Tässä esimerkissä käytetään Pythonia ja DB-API: ta:

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

Tässä esimerkissä käytetään suoraa SQL: ää neljännen sukupolven kieleltä, kuten eDeveloper, uniPaaS ja Magic XPA Magic Software Enterprisesilta

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 (v5.40 LTS: stä lähtien) voi hallita 7 tyyppistä linkkiä seuraavilla komennoilla

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

Tietokannan tyypistä riippuen on 2 eri menetelmää

For SQLite , ODBC , MariaDB / MySQL käyttää:?

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

For PostgreSQL käyttö: 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

Viitteet