В системах управления базами данных подготовленный запрос или параметризованный запрос — это предоставляемая СУБД возможность предварительной компиляции кода SQL, отделённого от данных. Преимущества подготовленных запросов:
эффективность, вытекающая из того, что их можно использовать повторно без повторной компиляции; безопасность. Их использование уменьшает или устраняет возможность атаки путём SQL-инъекции.
Подготовленный оператор фактически является предварительно скомпилированным шаблоном, в который подставляются постоянные значения во время каждого выполнения, и обычно используются такие операторы SQL DML, такие как INSERT, SELECT или UPDATE.
Обычная последовательность использования подготовленных операторов:
Подготовка: приложение создает шаблон запроса и отправляет его в СУБД. Некоторые значения остаются неуказанными, они называются параметрами, заполнителями или переменными связывания (обозначены ниже как «?»): INSERT INTO products (name, price) VALUES (?, ?); Компиляция: СУБД компилирует (анализирует, оптимизирует и транслирует) шаблон запроса и сохраняет результат, не выполняя его. Выполнение: приложение предоставляет (или привязывает) значения для параметров шаблона оператора, а СУБД выполняет оператор (возможно, возвращая результат). Приложение может запросить у СУБД многократное выполнение оператора с разными значениями. В приведенном выше примере приложение может предоставить значения «велосипед» для первого параметра и «10900» для второго параметра, а затем значения «обувь» и «7400».
Альтернативой подготовленному запросу является вызов SQL непосредственно из исходного кода приложения таким образом, чтобы сочетались код и данные. Прямой эквивалент приведенному выше примеру:
INSERT INTO products (name, price) VALUES ("bike", "10900");
Не все оптимизации могут быть выполнены во время компиляции шаблона оператора по двум причинам: лучший план запроса может зависеть от конкретных значений параметров, и лучший план запроса может меняться с течением времени из-за изменения таблиц и индексов. Когда и если подготовленный запрос выполняется только один раз, он будет выполняться медленнее из-за дополнительного обращения к серверу. Ограничения реализации также могут привести к снижению производительности; например, некоторые версии MySQL не кэшировали результаты подготовленных запросов. Хранимые процедуры, которые также предварительно компилируются и сохраняются на сервере для последующего выполнения, обладают аналогичными преимуществами. В отличие от хранимых процедур, подготовленный запрос обычно не пишется на процедурном языке и не может использовать или изменять переменные или использовать структуры потока управления, полагаясь вместо этого на декларативный язык запросов к базе данных. Благодаря своей простоте и возможности эмуляции на стороне клиента (если целевая СУБД их не поддерживает) подготовленные запросы более переносимы между различными СУБД, чем хранимые процедуры.
Поддержка в программном обеспечении Почти все распространённые СУБД, включая SQLite, MySQL, Oracle, DB2, Microsoft SQL Server and PostgreSQL поддерживают подготовленные запросы. Подготовленные запросы обычно вызываются с использованием специального двоичного протокола, который, увеличивает скорость передачи данных и, как предполагается, дополнительно защищает от SQL-инъекции, но некоторые СУБД, включая, например, MySQL, позволяют, в отладочных целях, вызывать подготовленные запросы с использованием синтаксиса запросов SQL.
Многие языки программирования поддерживают подготовленные запросы в своих стандартных библиотеках и эмулируют их для случаев, когда целевая СУБД не поддерживает такую возможность. Среди этих языков - Java (с использованием JDBC), Perl (с использованием DBI (perl)), PHP (с использованием PDO), и Python (с использованием DB-API). Эмуляция на стороне клиента может быть эффективнее с точки зрения производительности для однократных запросов и менее эффективной для многократных. Она также помогает против SQL-инъекций, как и прямая реализация подготовленных запросов на стороне СУБД.
Примеры 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 provides "setters" (setInt(int), setString(String), setDouble(double), etc.) for all major built-in data types.
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 "$$_\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())
// ...
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())
Примечания
Категория:Базы данных Категория:SQL