Подготовленный запрос (Hk;ikmkflyuudw [ghjkv)

Перейти к навигации Перейти к поиску

В системах управления базами данных подготовленный запрос или параметризованный запрос — это предоставляемая СУБД возможность предварительной компиляции кода SQL, отделённого от данных[1]. Преимущества подготовленных запросов:

  • эффективность, вытекающая из того, что их можно использовать повторно без повторной компиляции[2];
  • безопасность. Их использование уменьшает или устраняет возможность атаки путём SQL-инъекции[3].

Подготовленный оператор фактически является предварительно скомпилированным шаблоном, в который подставляются постоянные значения во время каждого выполнения, и обычно используются такие операторы SQL DML, такие как INSERT, SELECT или UPDATE.

Обычная последовательность использования подготовленных операторов:

  • Подготовка: приложение создает шаблон запроса и отправляет его в СУБД. Некоторые значения остаются неуказанными, они называются параметрами, заполнителями или переменными связывания (обозначены ниже как «?»):
INSERT INTO products (name, price) VALUES (?, ?);
  • Компиляция: СУБД компилирует (анализирует, оптимизирует и транслирует) шаблон запроса и сохраняет результат, не выполняя его.
  • Выполнение: приложение предоставляет (или привязывает) значения для параметров шаблона оператора, а СУБД выполняет оператор (возможно, возвращая результат). Приложение может запросить у СУБД многократное выполнение оператора с разными значениями. В приведенном выше примере приложение может предоставить значения «велосипед» для первого параметра и «10900» для второго параметра, а затем значения «обувь» и «7400».

Альтернативой подготовленному запросу является вызов SQL непосредственно из исходного кода приложения таким образом, чтобы сочетались код и данные. Прямой эквивалент приведенному выше примеру:

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

Не все оптимизации могут быть выполнены во время компиляции шаблона оператора по двум причинам: лучший план запроса может зависеть от конкретных значений параметров, и лучший план запроса может меняться с течением времени из-за изменения таблиц и индексов[4]. Когда и если подготовленный запрос выполняется только один раз, он будет выполняться медленнее из-за дополнительного обращения к серверу[5]. Ограничения реализации также могут привести к снижению производительности; например, некоторые версии MySQL не кэшировали результаты подготовленных запросов[6]. Хранимые процедуры, которые также предварительно компилируются и сохраняются на сервере для последующего выполнения, обладают аналогичными преимуществами. В отличие от хранимых процедур, подготовленный запрос обычно не пишется на процедурном языке и не может использовать или изменять переменные или использовать структуры потока управления, полагаясь вместо этого на декларативный язык запросов к базе данных. Благодаря своей простоте и возможности эмуляции на стороне клиента (если целевая СУБД их не поддерживает) подготовленные запросы более переносимы между различными СУБД, чем хранимые процедуры.

Поддержка в программном обеспечении

[править | править код]

Почти все распространённые СУБД, включая SQLite,[7] MySQL,[8] Oracle,[9] DB2,[10] Microsoft SQL Server[11] and PostgreSQL[12] поддерживают подготовленные запросы. Подготовленные запросы обычно вызываются с использованием специального двоичного протокола, который, увеличивает скорость передачи данных и, как предполагается, дополнительно защищает от SQL-инъекции, но некоторые СУБД, включая, например, MySQL, позволяют, в отладочных целях, вызывать подготовленные запросы с использованием синтаксиса запросов SQL[13].

Многие языки программирования поддерживают подготовленные запросы в своих стандартных библиотеках и эмулируют их для случаев, когда целевая СУБД не поддерживает такую возможность. Среди этих языков - Java (с использованием JDBC[14]), Perl (с использованием DBI (perl)[англ.][15]), PHP (с использованием PDO[1]), и Python (с использованием DB-API[16]). Эмуляция на стороне клиента может быть эффективнее с точки зрения производительности для однократных запросов и менее эффективной для многократных. Она также помогает против SQL-инъекций, как и прямая реализация подготовленных запросов на стороне СУБД[17].

Этот примеры использует Java и 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 PreparedStatement provides "setters" (setInt(int), setString(String), setDouble(double), etc.) for all major built-in data types.

Этот пример использует PHP и 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:

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

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

Этот примеры использует Python и 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])

Примечания

[править | править код]
  1. 1 2 The PHP Documentation Group Prepared statements and stored procedures. PHP Manual. Дата обращения: 25 сентября 2011. Архивировано 8 апреля 2022 года.
  2. Shuping Ran, Doug Palmer, Paul Brebner, Shiping Chen, Ian Gorton , Jeffrey Gosper, Lei Hu, Anna Liu and Phong Tran. J2EE TECHNOLOGY PERFORMANCE EVALUATION METHODOLOGY. citeseerx.ist.psu.edu. Дата обращения: 15 апреля 2022. Архивировано 15 апреля 2022 года.
  3. Stephen Thomas, Laurie Williams, Tao Xie. On automated prepared statement generation to remove SQL injection vulnerabilities (англ.) // Information and Software Technology. — 2009-03-01. — Vol. 51, iss. 3. — P. 589–598. — ISSN 0950-5849. — doi:10.1016/j.infsof.2008.08.002. Архивировано 9 мая 2012 года.
  4. Petrunia, Sergey MySQL Optimizer and Prepared Statements. Sergey Petrunia's blog (28 апреля 2007). Дата обращения: 25 сентября 2011. Архивировано 5 февраля 2018 года.
  5. Zaitsev, Peter MySQL Prepared Statements. MySQL Performance Blog (2 августа 2006). Дата обращения: 25 сентября 2011. Архивировано 23 марта 2014 года.
  6. 7.6.3.1. How the Query Cache Operates. MySQL 5.1 Reference Manual. Oracle. Дата обращения: 26 сентября 2011. Архивировано 25 сентября 2011 года.
  7. Prepared Statement Objects. SQLite (18 октября 2021). Дата обращения: 9 апреля 2022. Архивировано 7 мая 2022 года.
  8. Oracle 20.9.4. C API Prepared Statements. MySQL 5.5 Reference Manual. Дата обращения: 27 марта 2012. Архивировано 30 июня 2017 года.
  9. 13 Oracle Dynamic SQL. Pro*C/C++ Precompiler Programmer's Guide, Release 9.2. Oracle. Дата обращения: 25 сентября 2011. Архивировано 26 октября 2011 года.
  10. Using the PREPARE and EXECUTE statements. i5/OS Information Center, Version 5 Release 4. IBM. Дата обращения: 25 сентября 2011. (недоступная ссылка)
  11. SQL Server 2008 R2: Preparing SQL Statements. MSDN Library. Microsoft. Дата обращения: 25 сентября 2011. Архивировано 5 июля 2017 года.
  12. PREPARE. PostgreSQL 9.5.1 Documentation. PostgreSQL Global Development Group. Дата обращения: 27 февраля 2016. Архивировано 9 марта 2018 года.
  13. Oracle 12.6. SQL Syntax for Prepared Statements. MySQL 5.5 Reference Manual. Дата обращения: 27 марта 2012. Архивировано 16 июля 2019 года.
  14. Using Prepared Statements. The Java Tutorials. Oracle. Дата обращения: 25 сентября 2011. Архивировано 12 ноября 2011 года.
  15. Bunce, Tim DBI-1.616 specification. CPAN. Дата обращения: 26 сентября 2011.
  16. Python PEP 289: Python Database API Specification v2.0. Дата обращения: 9 апреля 2022. Архивировано 3 марта 2022 года.
  17. Аникин Евгений Александрович. SQL-инъекция и как защититься от несанкционированного доступа // CONTINUUM. МАТЕМАТИКА. ИНФОРМАТИКА. ОБРАЗОВАНИЕ. — 2016. — № 4. — ISSN 2500-1957.