Hazırlanan beyan - Prepared statement

Gelen veri tabanı yönetim sistemleri (DBMS), bir hazır deyimi veya parametreli deyim -derleme öncesi kullanılan bir özelliktir SQL kodunu verilerinden ayıran. Hazırlanan ifadelerin faydaları şunlardır:

• verimlilik, çünkü yeniden derlemeden tekrar tekrar kullanılabilirler
• SQL enjeksiyon saldırılarını azaltarak veya ortadan kaldırarak güvenlik

Hazırlanan bir ifade, her yürütme sırasında sabit değerlerin değiştirildiği önceden derlenmiş bir şablon şeklini alır ve genellikle INSERT , SELECT veya UPDATE gibi SQL DML ifadelerini kullanır .

Hazırlanan ifadeler için yaygın bir iş akışı:

1. Hazırla : Uygulama deyim şablonunu oluşturur ve DBMS'ye gönderir. Parametreler , yer tutucular veya bağlama değişkenleri (aşağıda "?" etiketli) olarak adlandırılan belirli değerler belirtilmeden bırakılır :

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

2. Derleme : DBMS , ifade şablonunu derler (çözümler, optimize eder ve çevirir) ve sonucu çalıştırmadan saklar.
3. Yürüt : Uygulama , ifade şablonunun parametreleri için değerler sağlar (veya bağlar ) ve VTYS, ifadeyi yürütür (muhtemelen bir sonuç döndürür). Uygulama, DBMS'den ifadeyi farklı değerlerle birçok kez yürütmesini isteyebilir. Yukarıdaki örnekte, uygulama birinci parametre için "bisiklet" ve ikinci parametre için "10900" değerlerini ve daha sonra "ayakkabılar" ve "7400" değerlerini sağlayabilir.

Hazırlanmış bir deyimin alternatifi, SQL'i doğrudan uygulama kaynak kodundan kod ve verileri birleştirecek şekilde çağırmaktır. Yukarıdaki örneğe doğrudan eşdeğerdir:

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

Tüm optimizasyon, iki nedenden dolayı, ifade şablonu derlendiğinde gerçekleştirilemez: en iyi plan, parametrelerin belirli değerlerine bağlı olabilir ve tablolar ve dizinler zamanla değiştikçe en iyi plan değişebilir.

Öte yandan, bir sorgu yalnızca bir kez yürütülürse, sunucuya yapılan ek gidiş-dönüş nedeniyle sunucu tarafında hazırlanan ifadeler daha yavaş olabilir. Uygulama sınırlamaları ayrıca performans cezalarına yol açabilir; örneğin, MySQL'in bazı sürümleri, hazırlanan sorguların sonuçlarını önbelleğe almadı. Bir depolanmış prosedür aynı zamanda daha sonra uygulanması için sunucuda önceden derlenmiş ve depolanır, benzer avantajlara sahiptir. Saklı bir yordamın aksine, hazırlanmış bir ifade normalde prosedürel bir dilde yazılmaz ve değişkenleri kullanamaz veya değiştiremez veya bunun yerine bildirimsel veritabanı sorgu diline dayanarak kontrol akışı yapılarını kullanamaz. Basitlikleri ve istemci tarafı öykünmesi nedeniyle, hazırlanan ifadeler satıcılar arasında daha taşınabilirdir.

Yazılım desteği

SQLite, MySQL , Oracle , DB2 , Microsoft SQL Server ve PostgreSQL dahil olmak üzere başlıca DBMS'ler hazırlanmış ifadeleri destekler. Hazırlanan ifadeler normalde verimlilik ve SQL enjeksiyonundan korunma için SQL olmayan bir ikili protokol aracılığıyla yürütülür, ancak MySQL gibi bazı DBMS'lerde hata ayıklama amacıyla bir SQL sözdizimi kullanılarak hazırlanan ifadeler de mevcuttur. NS

Programlama dilleri bir dizi standart kütüphanelerinde hazırlanan açıklamaları desteklemek ve altta yatan DBMS dahil olmak üzere bunları desteklemiyorsa bile istemci tarafında onları taklit edecek Java 'nın JDBC , Perl ' ın DBII , PHP 'nin PDO ve Python ' ın DB -API. İstemci tarafı öykünmesi, sunucuya gidiş dönüş sayısını azaltarak yalnızca bir kez yürütülen sorgular için daha hızlı olabilir, ancak birçok kez yürütülen sorgular için genellikle daha yavaştır. SQL enjeksiyon saldırılarına eşit derecede etkili bir şekilde direnir.

Pek çok SQL enjeksiyon saldırısı türü , hazır ifadelerin etkili bir şekilde kullanılmasını gerektirerek, hazır değerleri devre dışı bırakarak ortadan kaldırılabilir ; 2007 itibariyle sadece H2 bu özelliği desteklemektedir.

Örnekler

Java JDBC'si

Bu örnek Java ve JDBC kullanır :

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 PreparedStatement, setInt(int), setString(String), setDouble(double),tüm büyük yerleşik veri türleri için "ayarlayıcılar" ( vb.) sağlar.

PHP PDO'su

Bu örnek PHP ve PDO kullanır :

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

Bu örnek, Perl ve DBI kullanır :

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

Bu örnek, C# ve ADO.NET kullanır :

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 SqlCommand, valueparametresi için herhangi bir türü kabul eder AddWithValueve tür dönüştürmesi otomatik olarak gerçekleşir. "adlandırılmış parametreler" (yani "@username") yerine "?"—bu, bir parametreyi sorgu komut metni içinde birden çok kez ve herhangi bir rastgele sırada kullanmanıza izin verir.

Ancak, AddWithValue yöntemi, varchar ve nvarchar gibi değişken uzunluklu veri türleriyle kullanılmamalıdır. Bunun nedeni, .NET'in veri tabanından yansıma yoluyla gerçek uzunluğu almak yerine parametrenin uzunluğunu verilen değerin uzunluğu olarak varsaymasıdır. Bunun sonucu, her farklı uzunluk için farklı bir sorgu planının derlenmesi ve saklanmasıdır. Genel olarak, maksimum "yinelenen" plan sayısı, veritabanında belirtilen değişken uzunluklu sütunların uzunluklarının çarpımıdır. Bu nedenle, değişken uzunluklu sütunlar için standart Add yöntemini kullanmak önemlidir:

command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue, burada ParamLength, veritabanında belirtilen uzunluktur.

Değişken uzunluklu veri türleri için standart Add yönteminin kullanılması gerektiğinden, tüm parametre türleri için kullanılması iyi bir alışkanlıktır.

Python DB-API

Bu örnek Python ve DB-API kullanır :

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])

Sihirli Doğrudan SQL

Bu örnek , Magic Software Enterprises'dan eDeveloper, uniPaaS ve magic XPA gibi Dördüncü nesil dilden Direct SQL'i kullanır.

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'den beri) aşağıdaki komutlarla 7 tür bağlantıyı yönetebilir

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

Veritabanı türüne bağlı olarak 2 farklı yöntem vardır.

İçin SQLite , ODBC , mariadb / MySQL kullanımı:?

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

For PostgreSQL kullanımı: $ 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

Referanslar