Подготовленное заявление
В системах управления базами данных (СУБД) подготовленный оператор , параметризованный оператор или параметризованный запрос — это функция, при которой база данных предварительно компилирует код SQL и сохраняет результаты, отделяя их от данных. Преимущества подготовленных отчетов: [1]
- эффективность, поскольку их можно использовать неоднократно без повторной компиляции.
- безопасность за счет уменьшения или устранения с использованием SQL-инъекций атак
Подготовленный оператор принимает форму предварительно скомпилированного шаблона , в который во время каждого выполнения подставляются постоянные значения, и обычно использует операторы SQL DML, такие как INSERT , SELECT или UPDATE .
Общий рабочий процесс для подготовленных операторов:
- Подготовка : приложение создает шаблон заявления и отправляет его в СУБД. Определенные значения остаются неуказанными и называются параметрами , заполнителями или переменными привязки (помечены знаком «?» ниже):
INSERT INTO products (name, price) VALUES (?, ?);
- Компиляция : СУБД компилирует (анализирует, оптимизирует и транслирует) шаблон оператора и сохраняет результат, не выполняя его.
- Выполнить : приложение предоставляет (или связывает ) значения для параметров шаблона оператора, а СУБД выполняет оператор (возможно, возвращая результат). Приложение может много раз запрашивать у СУБД выполнение оператора с разными значениями. В приведенном выше примере приложение может предоставить значения «велосипед» для первого параметра и «10900» для второго параметра, а затем значения «обувь» и «7400».
Альтернативой подготовленному оператору является вызов SQL непосредственно из исходного кода приложения таким образом, чтобы объединить код и данные. Прямой эквивалент приведенному выше примеру:
INSERT INTO products (name, price) VALUES ('bike', '10900');
Не вся оптимизация может быть выполнена во время компиляции шаблона инструкции по двум причинам: лучший план может зависеть от конкретных значений параметров, а лучший план может меняться по мере изменения таблиц и индексов с течением времени. [2]
С другой стороны, если запрос выполняется только один раз, подготовленные на стороне сервера операторы могут работать медленнее из-за дополнительного обращения к серверу. [3] Ограничения реализации также могут привести к снижению производительности; например, некоторые версии MySQL не кэшировали результаты подготовленных запросов. [4] , Аналогичными преимуществами обладает хранимая процедура которая также предварительно компилируется и сохраняется на сервере для последующего выполнения. В отличие от хранимой процедуры, подготовленный оператор обычно не пишется на процедурном языке и не может использовать или изменять переменные или использовать структуры потока управления, вместо этого полагаясь на декларативный язык запросов к базе данных. Благодаря своей простоте и эмуляции на стороне клиента подготовленные операторы более переносимы между поставщиками.
Поддержка программного обеспечения
[ редактировать ]Основные СУБД , включая SQLite , [5] MySQL , [6] Оракул , [7] IBM Дб2 , [8] Microsoft SQL-сервер [9] и PostgreSQL [10] поддержка подготовленных заявлений. Подготовленные операторы обычно выполняются через двоичный протокол, отличный от SQL, для эффективности и защиты от внедрения SQL, но в некоторых СУБД, таких как MySQL, подготовленные операторы также доступны с использованием синтаксиса SQL для целей отладки. [11]
Ряд языков программирования поддерживают подготовленные операторы в своих стандартных библиотеках и эмулируют их на стороне клиента, даже если базовая СУБД их не поддерживает, Java JDBC . включая [12] Perl DBI , [13] PHP PDO [1] и Python . DB-API [14] Эмуляция на стороне клиента может быть быстрее для запросов, которые выполняются только один раз, за счет уменьшения количества обращений к серверу, но обычно медленнее для запросов, выполняемых много раз. Он одинаково эффективно противостоит атакам SQL-инъекций.
Многие типы атак SQL-инъекций можно устранить, отключив литералы , что фактически требует использования подготовленных операторов; по состоянию на 2007 год [update] только H2 поддерживает эту функцию. [15]
Примеры
[ редактировать ]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));
}
}
}
}
Ява 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 :
#!/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;
С# АДО.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 БД-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])
Магический прямой 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 (начиная с версии 5.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
См. также
[ редактировать ]Ссылки
[ редактировать ]- ^ Jump up to: а б Группа документации PHP. «Подготовленные операторы и хранимые процедуры» . Руководство по PHP . Проверено 25 сентября 2011 г.
- ^ Петруния, Сергей (28 апреля 2007 г.). «Оптимизатор MySQL и подготовленные операторы» . Блог Сергея Петрунии . Проверено 25 сентября 2011 г.
- ^ Зайцев, Петр (2 августа 2006 г.). «Подготовленные операторы MySQL» . Блог о производительности MySQL . Проверено 25 сентября 2011 г.
- ^ «7.6.3.1. Как работает кэш запросов» . Справочное руководство MySQL 5.1 . Оракул . Проверено 26 сентября 2011 г.
- ^ «Подготовленные объекты операторов» . SQLite . 18 октября 2021 г.
- ^ Оракул. «20.9.4. Заявления, подготовленные API C» . Справочное руководство по MySQL 5.5 . Проверено 27 марта 2012 г.
- ^ «13 Oracle Dynamic SQL» . Руководство программиста прекомпилятора Pro*C/C++, выпуск 9.2 . Оракул . Проверено 25 сентября 2011 г.
- ^ «SQL: определение, история, функции и типы команд SQL» .
- ^ «SQL Server 2008 R2: подготовка операторов SQL» . Библиотека MSDN . Майкрософт . Проверено 25 сентября 2011 г.
- ^ "ПОДГОТОВИТЬ" . Документация PostgreSQL 9.5.1 . Группа глобального развития PostgreSQL . Проверено 27 февраля 2016 г.
- ^ Оракул. «12.6. Синтаксис SQL для подготовленных операторов» . Справочное руководство по MySQL 5.5 . Проверено 27 марта 2012 г.
- ^ «Использование подготовленных операторов» . Учебники по Java . Оракул . Проверено 25 сентября 2011 г.
- ^ Банс, Тим. «Спецификация ДБИ-1.616» . КПАН . Проверено 26 сентября 2011 г.
- ^ «Python PEP 289: Спецификация API базы данных Python v2.0» .
- ^ «SQL-инъекции: как не застрять» . Кодист. 8 мая 2007 года . Проверено 1 февраля 2010 г.