Подготовленные операторы PHP MySQL

Вперёд

Подготовленные операторы очень полезны против одного из распространённых способов взлома сайтов, работающих с базами данных — SQL-инъекций.

Что такое подготовленный оператор

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

MySQLi поддерживает использование анонимного позиционного заполнителя (?), как показано ниже:

INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?);

В то время как PDO поддерживает как анонимный позиционный заполнитель (?), так и именованные заполнители. Именованный заполнитель начинается с двоеточия (:) за которым следует идентификатор, например:

INSERT INTO persons (first_name, last_name, email)
VALUES (:first_name, :last_name, :email);

Подготовленные операторы в основном работают следующим образом:

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

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

Преимущества использования подготовленных операторов

Подготовленные операторы сокращают время синтаксического анализа, поскольку подготовка запроса выполняется только один раз (хотя оператор выполняется несколько раз).

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

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

Давайте на примере рассмотрим, как на самом деле работают подготовленные операторы:

Пример

Процедурный Объектно-ориентированный PDO

Скопировать

<?php
/* Попытка подключения к серверу MySQL. Предполагая, что вы используете MySQL
 сервер с настройкой по умолчанию (пользователь root без пароля) */
$link = mysqli_connect("localhost", "root", "", "demo");
 
// Проверка подключения
if($link === false){
    die("Ошибка подключения. " . mysqli_connect_error());
}
 
// Подготовьте инструкцию вставки
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = mysqli_prepare($link, $sql)){
    // Привяжите переменные к подготовленному оператору как параметры
    mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
    
    /* Set the parameters values and execute
    the statement again to insert another row */
    $first_name = "Hermione";
    $last_name = "Granger";
    $email = "hermionegranger@mail.com";
    mysqli_stmt_execute($stmt);
    
    /* Установите значения параметров и выполните оператор снова, чтобы вставить другую строку */
    $first_name = "Ron";
    $last_name = "Weasley";
    $email = "ronweasley@mail.com";
    mysqli_stmt_execute($stmt);
    
    echo "Записи успешно вставлены.";
} else{
    echo "ERROR: Не удалось подготовить запрос: $sql. " . mysqli_error($link);
}
 
// Закрыть заявление
mysqli_stmt_close($stmt);
 
// Закрыть соединение
mysqli_close($link);
?>

<?php
$mysqli = new mysqli("localhost", "root", "", "demo");
 
// Проверка подключения
if($mysqli === false){
    die("ERROR: Could not connect. " . $mysqli->connect_error);
}
 
// Подготовьте инструкцию вставки
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = $mysqli->prepare($sql)){
    // Привяжите переменные к подготовленному оператору как параметры
    $stmt->bind_param("sss", $first_name, $last_name, $email);
    
    /* Set the parameters values and execute
    the statement again to insert another row */
    $first_name = "Hermione";
    $last_name = "Granger";
    $email = "hermionegranger@mail.com";
    $stmt->execute();
    
    /* Установите значения параметров и выполните оператор снова, чтобы вставить другую строку */
    $first_name = "Ron";
    $last_name = "Weasley";
    $email = "ronweasley@mail.com";
    $stmt->execute();
    
    echo "Записи успешно вставлены.";
} else{
    echo "ОШИБКА: не удалось подготовить запрос: $sql. " . $mysqli->error;
}
 
// Закрыть заявление
$stmt->close();
 
// Закрыть соединение
$mysqli->close();
?>

<?php
/* Попытка подключения к серверу MySQL. Предполагая, что вы используете MySQL
 сервер с настройкой по умолчанию (пользователь root без пароля) */
try{
    $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
    // Установите режим ошибки PDO в исключение
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ОШИБКА: не удалось подключиться. " . $e->getMessage());
}
 
// Попытка выполнения запроса вставки
try{
    // Подготовьте инструкцию вставки
    $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
    $stmt = $pdo->prepare($sql);
    
    // Привязать параметры к оператору
    $stmt->bindParam(':first_name', $first_name, PDO::PARAM_STR);
    $stmt->bindParam(':last_name', $last_name, PDO::PARAM_STR);
    $stmt->bindParam(':email', $email, PDO::PARAM_STR);
    
    /* Установите значения параметров и выполните оператор снова, чтобы вставить другую строку */
    $first_name = "Hermione";
    $last_name = "Granger";
    $email = "hermionegranger@mail.com";
    $stmt->execute();
    
    /* Установите значения параметров и выполните инструкция для вставки строки */
    $first_name = "Ron";
    $last_name = "Weasley";
    $email = "ronweasley@mail.com";
    $stmt->execute();
    
    echo "Записи успешно вставлены.";
} catch(PDOException $e){
    die("ОШИБКА: не удалось подготовить / выполнить запрос: $sql. " . $e->getMessage());
}
 
// Закрыть заявление
unset($stmt);
 
// Закрыть соединение
unset($pdo);
?>

В приведенном выше примере, мы подготовили оператор INSERT только один раз, но выполнили его несколько раз, передав другой набор параметров.

Объяснение кода (процедурный стиль)

Внутри оператора SQL INSERT приведенного выше примера вопросительные знаки используются в качестве заполнителей для значений полей first_name, last_name и email.

"INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)"

Функция mysqli_stmt_bind_param() ( строка № 16 ) связывает переменные заполнители ( ? ) в шаблоне SQL заявления. Заполнители ( ? ) будут заменены фактическими значениями, хранящимися в переменных во время выполнения.

mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);

Аргумент sss перечисляет типы данных, которыми являются параметры. Символ s сообщает mysql, что параметр является строкой. Т.е. в нашем случае каждая переменная связывания является строкой.

Аргумент может быть одним из четырех типов:

b - binary (например, изображение, файл PDF и т. д.)
d - double (число с плавающей запятой)
i - integer (целое число)
s - string (текст)

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

Сообщая mysql, какой тип данных ожидать, мы минимизируем риск SQL-инъекций.

Примечание: Если мы хотим вставить какие-либо данные из внешних источников (например, вводимые пользователем), очень важно, чтобы данные были очищены и проверены.

Использование входных данных, полученных через веб-форму

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

Посмотрите обновленный код PHP для вставки данных:

Example

Процедурный Объектно-ориентированный PDO

Скопировать

<?php
/* Попытка подключения к серверу MySQL. Предполагая, что вы используете MySQL
 сервер с настройкой по умолчанию (пользователь root без пароля) */
$link = mysqli_connect("localhost", "root", "", "demo");
 
// Проверка подключения
if($link === false){
    die("ОШИБКА: не удалось подключиться. " . mysqli_connect_error());
}
 
// Попытка выполнения запроса вставки
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = mysqli_prepare($link, $sql)){
    // Привяжите переменные к подготовленному оператору как параметры
    mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
    
    // Установить параметры
    $first_name = $_REQUEST['first_name'];
    $last_name = $_REQUEST['last_name'];
    $email = $_REQUEST['email'];
    
    // Попытка выполнить подготовленный оператор
    if(mysqli_stmt_execute($stmt)){
        echo "Записи успешно вставлены.";
    } else{
        echo "ОШИБКА: не удалось выполнить запрос: $sql. " . mysqli_error($link);
    }
} else{
    echo "ОШИБКА: не удалось подготовить запрос: $sql. " . mysqli_error($link);
}
 
// Закрыть заявление
mysqli_stmt_close($stmt);
 
// Закрыть соединение
mysqli_close($link);
?>

<?php
$mysqli = new mysqli("localhost", "root", "", "demo");
 
if($mysqli === false){
    die("ERROR: Could not connect. " . $mysqli->connect_error);
}
 
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = $mysqli->prepare($sql)){
    // Привяжите переменные к подготовленному оператору как параметры
    $stmt->bind_param("sss", $first_name, $last_name, $email);
    
    // Установить параметры
    $first_name = $_REQUEST['first_name'];
    $last_name = $_REQUEST['last_name'];
    $email = $_REQUEST['email'];
    
    // Попытка выполнить подготовленный оператор
    if($stmt->execute()){
        echo "Записи успешно вставлены.";
    } else{
        echo "ОШИБКА: не удалось выполнить запрос: $sql. " . $mysqli->error;
    }
} else{
    echo "ОШИБКА: не удалось подготовить запрос: $sql. " . $mysqli->error;
}
 
// Закрыть заявление
$stmt->close();
 
// Закрыть соединение
$mysqli->close();
?>

<?php
/* Попытка подключения к серверу MySQL. Предполагая, что вы используете MySQL
 сервер с настройкой по умолчанию (пользователь root без пароля) */
try{
    $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
    // Установите режим ошибки PDO в исключение
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ОШИБКА: не удалось подключиться. " . $e->getMessage());
}
 
// Попытка выполнения запроса вставки
try{
    // Подготовьте инструкцию вставки
    $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
    $stmt = $pdo->prepare($sql);
    
    // Привязать параметры к оператору
    $stmt->bindParam(':first_name', $_REQUEST['first_name'], PDO::PARAM_STR);
    $stmt->bindParam(':last_name', $_REQUEST['last_name'], PDO::PARAM_STR);
    $stmt->bindParam(':email', $_REQUEST['email'], PDO::PARAM_STR);
    
    // Выполнить подготовленный оператор
    $stmt->execute();
    echo "Записи успешно вставлены.";
} catch(PDOException $e){
    die("ОШИБКА: не удалось подготовить / выполнить запрос: $sql. " . $e->getMessage());
}
 
// Закрыть заявление
unset($stmt);
 
// Закрыть соединение
unset($pdo);
?>

Если вы внимательно посмотрите на пример выше, то обнаружите, что мы не использовали функцию mysqli_real_escape_string() для экранирования вводимых пользователем данных, как это было в предыдущем примере вставки данных в базу данных из HTML-формы. Поскольку в подготовленных операторах вводимые пользователем данные никогда не подставляются напрямую в строку запроса, поэтому их не нужно правильно экранировать.

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

Вперёд

Задачи и упражнения

Тесты

ООП PHP Тесты

PHP Учебник

PHP Формы

PHP Продвинутый

PHP OOP

База данных MySQL

PHP XML

PHP - AJAX

PHP Примеры

Подготовленные операторы PHP MySQL

Что такое подготовленный оператор

Преимущества использования подготовленных операторов

Пример

Объяснение кода (процедурный стиль)

Использование входных данных, полученных через веб-форму

Example

Реши задачу!

Мы здесь

Разделы сайта

Популярные темы

Квиз-тесты

Запомни нас