Подготовленное заявление - Prepared statement

В системах управления базами данных (СУБД) подготовленный оператор или параметризованный оператор - это функция, используемая для предварительной компиляции кода SQL , отделяя его от данных. Преимущества подготовленных отчетов:

  • эффективность, потому что их можно использовать многократно без повторной компиляции
  • безопасность за счет уменьшения или устранения атак с использованием SQL-инъекций

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

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

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

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

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

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

С другой стороны, если запрос выполняется только один раз, операторы, подготовленные на стороне сервера, могут работать медленнее из-за дополнительных циклов обращения к серверу. Ограничения реализации также могут привести к снижению производительности; например, некоторые версии MySQL не кэшировали результаты подготовленных запросов. Хранимая процедура , которая также прекомпилирована и хранится на сервере для последующего выполнения, имеет такое же преимущество. В отличие от хранимой процедуры подготовленный оператор обычно не пишется на процедурном языке и не может использовать или изменять переменные или использовать структуры потока управления, вместо этого полагаясь на декларативный язык запросов к базе данных. Благодаря своей простоте и эмуляции на стороне клиента подготовленные операторы более переносимы между поставщиками.

Программная поддержка

Основные СУБД , включая SQLite, MySQL , Oracle , DB2 , Microsoft SQL Server и PostgreSQL, поддерживают подготовленные операторы. Подготовленные операторы обычно выполняются через двоичный протокол, отличный от SQL, для эффективности и защиты от SQL-инъекций, но в некоторых СУБД, таких как MySQL, подготовленные операторы также доступны с использованием синтаксиса SQL для целей отладки. В

Ряд языков программирования поддерживают подготовленные заявления в их стандартных библиотек и будет эмулировать их на стороне клиента , даже если основной СУБД не поддерживает их, в том числе Java «s JDBC , Perl » s DBI , PHP «S PDO и Python » S DB -API. Эмуляция на стороне клиента может быть быстрее для запросов, которые выполняются только один раз, за ​​счет уменьшения количества циклических обращений к серверу, но обычно медленнее для запросов, выполняемых много раз. Он одинаково эффективно противостоит атакам с использованием SQL-инъекций.

Многие типы атак с использованием SQL-инъекций могут быть устранены путем отключения литералов , что фактически требует использования подготовленных операторов; с 2007 только H2 поддерживает эту функцию.

Примеры

Java JDBC

В этом примере используются 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предоставляет "сеттеры" ( setInt(int), setString(String), setDouble(double),и т. Д.) Для всех основных встроенных типов данных.

PHP PDO

В этом примере используются 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

В этом примере используются 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

В этом примере используются 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())
    {
        // ...
    }
}

ADO.NET SqlCommandпринимает любой тип в качестве valueпараметра AddWithValue, и преобразование типа происходит автоматически. Обратите внимание на использование «именованных параметров» (т.е. "@username"), а не "?"- это позволяет вам использовать параметр несколько раз и в любом произвольном порядке в тексте команды запроса.

Однако метод AddWithValue не следует использовать с типами данных переменной длины, такими как varchar и nvarchar. Это связано с тем, что .NET предполагает, что длина параметра равна длине данного значения, а не получает фактическую длину из базы данных посредством отражения. Следствием этого является то, что для каждой длины компилируется и сохраняется отдельный план запроса. Как правило, максимальное количество «повторяющихся» планов - это произведение длин столбцов переменной длины, указанных в базе данных. По этой причине важно использовать стандартный метод Add для столбцов переменной длины:

command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue, где ParamLength - длина, указанная в базе данных.

Поскольку для типов данных переменной длины необходимо использовать стандартный метод Add, рекомендуется использовать его для всех типов параметров.

Python DB-API

В этом примере используются 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])

Magic Direct SQL

В этом примере используется Direct SQL от языка четвертого поколения, такого как eDeveloper, uniPaaS и magic XPA от 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 (начиная с v5.40 LTS) может управлять 7 типами ссылок с помощью следующих команд

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

В зависимости от типа базы данных существует 2 разных метода.

Для SQLite , ODBC , MariaDB / Mysql используйте :?

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

Для PostgreSQL используйте: $ 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

использованная литература

  1. ^ a b Группа документации PHP. «Подготовленные отчеты и хранимые процедуры» . Руководство по PHP . Проверено 25 сентября 2011 года .
  2. ^ Petrunia, Сергей (28 апреля 2007). «Оптимизатор MySQL и подготовленные операторы» . Блог Сергея Петруни . Проверено 25 сентября 2011 года .
  3. Зайцев, Петр (2 августа 2006 г.). «Подготовленные операторы MySQL» . Блог о производительности MySQL . Проверено 25 сентября 2011 года .
  4. ^ «7.6.3.1. Как работает кэш запросов» . Справочное руководство по MySQL 5.1 . Oracle . Проверено 26 сентября 2011 года .
  5. ^ «Подготовленные объекты утверждения» . SQLite . 18 октября 2021 г.
  6. ^ Оракул. «20.9.4. Подготовленные операторы C API» . Справочное руководство по MySQL 5.5 . Проверено 27 марта 2012 года .
  7. ^ «13 Oracle Dynamic SQL» . Руководство программиста прекомпилятора Pro * C / C ++, выпуск 9.2 . Oracle . Проверено 25 сентября 2011 года .
  8. ^ «Использование операторов PREPARE и EXECUTE» . Информационный центр i5 / OS, версия 5, выпуск 4 . IBM . Проверено 25 сентября 2011 года .
  9. ^ «SQL Server 2008 R2: Подготовка отчетов SQL» . Библиотека MSDN . Microsoft . Проверено 25 сентября 2011 года .
  10. ^ «ПОДГОТОВИТЬ» . Документация по PostgreSQL 9.5.1 . Группа глобального развития PostgreSQL . Проверено 27 февраля +2016 .
  11. ^ Оракул. «12.6. Синтаксис SQL для подготовленных операторов» . Справочное руководство по MySQL 5.5 . Проверено 27 марта 2012 года .
  12. ^ «Использование подготовленных заявлений» . Учебники по Java . Oracle . Проверено 25 сентября 2011 года .
  13. ^ Банс, Тим. «Спецификация DBI-1.616» . CPAN . Проверено 26 сентября 2011 года .
  14. ^ «Python PEP 289: спецификация API базы данных Python v2.0» .
  15. ^ «SQL-инъекции: как не застрять» . Кодист. 8 мая 2007 . Проверено 1 февраля 2010 года .