Подготавливаемые запросы

СУБД MySQL поддерживает подготавливаемые запросы. Подготавливаемые (или параметризованные) запросы используются для повышения эффективности, когда один запрос выполняется многократно.

Принцип работы

Выполнение подготавливаемого запроса проводится в два этапа: подготовка и исполнение. На этапе подготовки на сервер посылается шаблон запроса. Сервер выполняет синтаксическую проверку этого шаблона, строит план выполнения запроса и выделяет под него ресурсы.

MySQL сервер поддерживает неименованные, или позиционные, псевдопеременные ?.

Пример #1 Первый этап: подготовка


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Не удалось подключиться к MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

/* обычный запрос */
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
    echo "Не удалось создать таблицу: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* подготавливаемый запрос, первая стадия: подготовка */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
    echo "Не удалось подготовить запрос: (" . $mysqli->errno . ") " . $mysqli->error;
}
?>

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

Пример #2 Второй этап: привязка параметров и выполнение


<?php
/* подготавливаемый запрос, вторая стадия: привязка и выполнение */
$id = 1;
if (!$stmt->bind_param("i", $id)) {
    echo "Не удалось привязать параметры: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Не удалось выполнить запрос: (" . $stmt->errno . ") " . $stmt->error;
}
?>

Повторное выполнение запроса

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

Пример #3 Выражение INSERT один раз подготавливается, а затем многократно выполняется


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Не удалось подключиться к MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

/* обычный запрос */
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
    echo "Не удалось создать таблицу: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* подготавливаемый запрос, первая стадия: подготовка */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
     echo "Не удалось подготовить запрос: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* подготавливаемый запрос, вторая стадия: привязка и выполнение */
$id = 1;
if (!$stmt->bind_param("i", $id)) {
    echo "Не удалось привязать параметры: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Не удалось выполнить запрос: (" . $stmt->errno . ") " . $stmt->error;
}

/* подготавливаемый запрос: повторные выполнения, на сервер передаются только значения переменных */
for ($id = 2; $id < 5; $id++) {
    if (!$stmt->execute()) {
        echo "Не удалось выполнить запрос: (" . $stmt->errno . ") " . $stmt->error;
    }
}

/* рекомендуется явно закрывать запросы */
$stmt->close();

/* обычный запрос */
$res = $mysqli->query("SELECT id FROM test");
var_dump($res->fetch_all());
?>

Результат выполнения данного примера:

array(4) {
  [0]=>
  array(1) {
    [0]=>
    string(1) "1"
  }
  [1]=>
  array(1) {
    [0]=>
    string(1) "2"
  }
  [2]=>
  array(1) {
    [0]=>
    string(1) "3"
  }
  [3]=>
  array(1) {
    [0]=>
    string(1) "4"
  }
}

Каждый подготавливаемый запрос использует ресурсы сервера. Если запрос больше не нужен, его необходимо сразу закрыть. Если не сделать этого явно, запрос закроется сам, но только когда PHP освободит его дескриптор, как правило это происходит при выходе запроса из области видимости или при завершении работы скрипта.

Использование подготавливаемых запросов не всегда приводит к повышению эффективности. Если параметризованный запрос запускается лишь раз, это приводит к большему количеству клиент-серверных обменов данными, нежели при выполнении простого запроса. Именно по этой причине в примере выше выражение SELECT выполнялось, как обычный запрос.

Также, имеет смысл рассмотреть SQL синтаксис вставки множества значений в выражении INSERT. В примере выше мультивставка (значения для вставки перечисляются через запятую) в предложении INSERT обошлась бы дешевле, чем подготовленный запрос.

Пример #4 Меньше обменов данными при использовании мультивставок SQL


<?php
if (!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3), (4)")) {
    echo "Не удалось выполнить множественную вставку: (" . $mysqli->errno . ") " . $mysqli->error;
}
?>

Типы данных значений в результирующей таблице

В протоколе клиент-серверного взаимодействия MySQL для обычных и подготавливаемых запросово пределены разные протоколы передачи данных клиенту. Параметризованные запросы используют так называемый двоичный протокол. Сервер MySQL посылает результирующий набор клиенту "как есть" в двоичном формате. Данные в таблице не преобразуются в текст. Соответственно, клиентские библиотеки, получив двоичные данные, пытаются привести их к подходящим типам данных PHP. Например, столбец результатов запроса типа SQL INT PHP примет и преобразует в тип integer.

Пример #5 Исходные типы данных


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Не удалось подключиться к MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
    echo "Не удалось создать таблицу: (" . $mysqli->errno . ") " . $mysqli->error;
}

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$res = $stmt->get_result();
$row = $res->fetch_assoc();

printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
?>

Результат выполнения данного примера:

id = 1 (integer)
label = a (string)

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

Получение результатов запроса с привязкой переменных

Результаты из подготовленного запроса можно получить либо привязав выходные переменные, либо запросив объект mysqli_result.

Выходные параметры нужно привязывать после выполнения запроса. Каждому столбцу результирующей таблицы должна соответствовать ровно одна переменная.

Пример #6 Привязка переменных к результату запроса


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Не удалось подключиться к MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
    echo "Не удалось создать таблицу: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!($stmt = $mysqli->prepare("SELECT id, label FROM test"))) {
    echo "Не удалось подготовить запрос: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$stmt->execute()) {
    echo "Не удалось выполнить запрос: (" . $mysqli->errno . ") " . $mysqli->error;
}

$out_id    = NULL;
$out_label = NULL;
if (!$stmt->bind_result($out_id, $out_label)) {
    echo "Не удалось привязать выходные параметры: (" . $stmt->errno . ") " . $stmt->error;
}

while ($stmt->fetch()) {
    printf("id = %s (%s), label = %s (%s)\n", $out_id, gettype($out_id), $out_label, gettype($out_label));
}
?>

Результат выполнения данного примера:

id = 1 (integer), label = a (string)

Объекты подготавливаемых запросов по умолчанию возвращают не буферизованные результирующие наборы. Эти таблицы никаким неявным образом не переносятся на клиента, они остаются на сервере, занимая его ресурсы, пока клиентский процесс самостоятельно не извлечет все данные. Если клиент не может извлечь данные результирующего набора, или после закрытия объекта запроса остаются невыбранными какие-то данные, то на mysqli ложится ответственность неявно подчистить этот мусор за клиентским процессом.

Также можно буферизовать данные результирующих таблиц подготовленного запроса с помощью функции mysqli_stmt_store_result().

Извлечение результатов запроса посредством mysqli_result интерфейса

Вместо использования привязки переменных к результатам запроса, результирующие таблицы можно извлекать средствами mysqli_result интерфейса. Функция mysqli_stmt_get_result() возвращает буферизованный результирующий набор строк.

Пример #7 Использование mysqli_result для выборки результатов запроса


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Не удалось подключиться к MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
    echo "Не удалось создать таблицу: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!($stmt = $mysqli->prepare("SELECT id, label FROM test ORDER BY id ASC"))) {
    echo "Не удалось подготовить запрос: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$stmt->execute()) {
     echo "Не удалось выполнить запрос: (" . $stmt->errno . ") " . $stmt->error;
}

if (!($res = $stmt->get_result())) {
    echo "Не удалось получить результат: (" . $stmt->errno . ") " . $stmt->error;
}

var_dump($res->fetch_all());
?>

Результат выполнения данного примера:

array(1) {
  [0]=>
  array(2) {
    [0]=>
    int(1)
    [1]=>
    string(1) "a"
  }
}

Использование mysqli_result interface имеет дополнительное преимущество в том, что буферизация результирующих таблиц на клиенте предлагает гибкую систему навигации по этим таблицам.

Пример #8 Буферизация результирующего набора для удобства чтения данных


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Не удалось подключиться к MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a'), (2, 'b'), (3, 'c')")) {
    echo "Не удалось создать таблицу: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!($stmt = $mysqli->prepare("SELECT id, label FROM test"))) {
    echo "Не удалось подготовить запрос: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$stmt->execute()) {
     echo "Не удалось выполнить запрос: (" . $stmt->errno . ") " . $stmt->error;
}

if (!($res = $stmt->get_result())) {
    echo "Не удалось получить результат: (" . $stmt->errno . ") " . $stmt->error;
}

for ($row_no = ($res->num_rows - 1); $row_no >= 0; $row_no--) {
    $res->data_seek($row_no);
    var_dump($res->fetch_assoc());
}
$res->close();
?>

Результат выполнения данного примера:

array(2) {
  ["id"]=>
  int(3)
  ["label"]=>
  string(1) "c"
}
array(2) {
  ["id"]=>
  int(2)
  ["label"]=>
  string(1) "b"
}
array(2) {
  ["id"]=>
  int(1)
  ["label"]=>
  string(1) "a"
}

Экранирование и SQL инъекции

Значения привязанных к запросу переменных сервер экранирует автоматически. Серверу необходима подсказка в виде указания типов привязываемых переменных, чтобы правильно преобразовать их значения. Дополнительную информацию см. в описании функции mysqli_stmt_bind_param(). Привязанные переменные отправляются на сервер отдельно от запроса и таким образом не могут влиять на него. Сервер использует эти значения непосредственно в момент выполнения, уже после того, как был обработан шаблон выражения. Привязанные параметры не нуждаются в экранировании, так как они никогда не подставляются непосредственно в строку запроса. Необходимо отправлять тип привязанной переменной на сервер, чтобы определить соответствующее преобразование. Смотрите функцию mysqli_stmt_bind_param() для получения большей информации.

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

Эмуляция подготовленного запроса на клиенте

В API нет возможности эмулировать подготавливаемые запросы на клиенте.

Сравнение обычного и подготавливаемого запросов

В таблице ниже приведено сравнение обычного и подготовленного на сервере запросов.

**Сравнение подготовленных и не-подготовленных выражений**
	Подготовленный запрос	Обычный запрос
Количество обращений к серверу, SELECT, однократное выполнение	2	1
Количество строк с текстом запроса, переданных клиентом на сервер	1	1
Количество обращений к серверу, SELECT, n-кратное выполнение	1 + n	n
Количество строк с текстом запроса, переданных клиентом на сервер	1 шаблон, n раз параметры, если есть	n раз весь текст вместе с параметрами, если они есть
Привязка входных параметров средствами API	Есть, автоматическое экранирование значений параметров	Нет, параметры экранируются вручную
Привязка выходных параметров средствами API	Есть	Нет
Поддерживает использование API mysqli_result	Да, используется mysqli_stmt_get_result()	Да
Буферизация результирующих наборов	Есть, можно использовать функцию mysqli_stmt_get_result() или mysqli_stmt_store_result()	Есть, это умолчание для mysqli_query()
Работа с не буферизованными результирующими наборами	Возможна, используется привязка выходных параметров с помощью API	Возможна, используется функция mysqli_real_query() совместно с mysqli_use_result()
Способ передачи данных протокола клиент-серверного взаимодействия MySQL	Двоичный протокол	Текстовый протокол
SQL типы значений результирующего набора	Сохраняются при извлечении	Преобразовываются в строки или сохраняются при извлечении
Поддерживает все SQL выражения	Последние версии MySQL поддерживают большинство, но не все	Да

См. также