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

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

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

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

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

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

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

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Неподготовленный запрос */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");

/* Подготовленный запрос, шаг 1: подготовка */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");

/* Подготовленный запрос, шаг 2: связывание и выполнение */
$id = 1;
$label = 'PHP';
$stmt->bind_param("is", $id, $label); // "is" означает, что $id связывается, как целое число, а $label — как строка

$stmt->execute();

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

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

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

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Неподготовленный запрос */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");

/* Подготовленный запрос, шаг 1: подготовка */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");

/* Подготовленный запрос, шаг 2: связывание и выполнение */
$stmt->bind_param("is", $id, $label); // "is" означает, что $id связывается, как целое число, а $label — как строка

$data = [
    1 => 'PHP',
    2 => 'Java',
    3 => 'C++'
];
foreach ($data as $id => $label) {
    $stmt->execute();
}

$result = $mysqli->query('SELECT id, label FROM test');
var_dump($result->fetch_all(MYSQLI_ASSOC));

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

array(3) {
  [0]=>
  array(2) {
    ["id"]=>
    string(1) "1"
    ["label"]=>
    string(3) "PHP"
  }
  [1]=>
  array(2) {
    ["id"]=>
    string(1) "2"
    ["label"]=>
    string(4) "Java"
  }
  [2]=>
  array(2) {
    ["id"]=>
    string(1) "3"
    ["label"]=>
    string(3) "C++"
  }
}

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

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

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

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

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");

$values = [1, 2, 3, 4];

$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?), (?), (?), (?)");
$stmt->bind_param('iiii', ...$values);
$stmt->execute();

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

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

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

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Неподготовленный запрос */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$result = $stmt->get_result();
$row = $result->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 = PHP (string)

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

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

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

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

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

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Неподготовленный запрос */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

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

$stmt->bind_result($out_id, $out_label);

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 = PHP (string)

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

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

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

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

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

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Неподготовленный запрос */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

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

$result = $stmt->get_result();

var_dump($result->fetch_all(MYSQLI_ASSOC));

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

array(1) {
  [0]=>
  array(2) {
    ["id"]=>
    int(1)
    ["label"]=>
    string(3) "PHP"
  }
}

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

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

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Неподготовленный запрос */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP'), (2, 'Java'), (3, 'C++')");

$stmt = $mysqli->prepare("SELECT id, label FROM test");
$stmt->execute();

$result = $stmt->get_result();

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

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

array(2) {
  ["id"]=>
  int(3)
  ["label"]=>
  string(3) "C++"
}
array(2) {
  ["id"]=>
  int(2)
  ["label"]=>
  string(4) "Java"
}
array(2) {
  ["id"]=>
  int(1)
  ["label"]=>
  string(3) "PHP"
}

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

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

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

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

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

Смотрите также