стрілка вверх

підготовлені вирази

Prepared Statements /
Підготовлені Вирази

квітень 2016

Матеріал за ресурсом php.net Prepared Statements.

Знайомство із Prepared Statements, вони ж Підготовлені Вирази (або Параметизовані Вирази), можливо, в подальшому в тексті статті ПВ.



Зміст

  1. Принцип роботи
  2. Повторне виконання виразу
  3. Тип значень результуючої вибірки даних
  4. Отримуємо результуючу вибірку даниx, застосовуючи зв'язані змінні
  5. Результуюча вибірка за допомогою інтерфейсу mysqli_result
  6. Екранування даних та SQL-інєкція
  7. Порівняльна таблиця підготовлених та звичайних запитів

1. Принцип роботи

БД MySQL підтримує підготовлені вирази.

Підготовлений вираз (або параметизований вираз) використовується для підвищення ефективності системи роботи з даними.

Вираз запиту до серверу БД створюється одноразово, проте використання шаблону запиту відбувається багаторазово.


Підготовлені вирази виконуються в два етапи:
- підготовка (prepare)
- виконання (execute).

На підготовчому етапі відправляється шаблон запиту до серверу БД, сервер виконує синтаксичну перевірку запиту та визначає власні ресурси для подальшого використання.

Сервер MySQL у підготовлених виразах підтримує псевдозмінні, що позначаються знаком  ?  .


Приклад #1 Етап перший: Підготовка

<?php
$mysqli 
= new  mysqli("example.com" "user" "pass" "db");
if (
$mysqli->connect_errno ) {
    echo 
"Помилка підключення до серверу БД: (" 
    $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;
}

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

Після підготовки виразу йде його виконання.

Під час виконання клієнт зв'язує значення параметрів та відправляє їх до серверу БД.

Сервер БД вже створює запит із шаблону, що в нього вже був (етап підготовка), та виконує його.


Приклад #2 Етап 2: Звязуємо параметри та виконуємо запит

<?php
$id 1 ;
if (!
$stmt ->bind_param ("i"$id )) {
    echo 
"Помилка під час звязування параметрів: ("
     $stmt-> errno ") "  $stmt->error ;
}

if (!
$stmt-> execute ()) {
    echo 
"Помилка під час виконання виразу: ("
     $stmt->errno  ") "  $stmt->error ;
}
?>

2. Повторне виконання виразу

Шаблон запиту підготовлених виразів можна використовувати багаторазово.

Під час кожного слідуючого виконання підготовленого виразу поточні значення параметрів будуть передаватись до серверу БД і вноситись в тіло шаблону запиту.

Повторна перевірка синтаксису запиту, як і створення шаблону запиту вже не відбувається.


Приклад #3 SQL-запит INSERT підготовлюється один раз, виконується багаторазово.

<?php
$mysqli 
= new mysqli ("example.com" "user""pass" "db");
if (
$mysqli->connect_errno ) {
   echo 
"Помилка підключення до серверу БД: (" 
    $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;
}

/* Підготовлений вираз, Етап 1: Підготовка */
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-скрипту.

Застосування підготовленого виразу не завжди найбільш ефективний шлях використання запитів до БД. Під час роботи із ПВ відбувається запуск ряду клієнт-серверних застосунків на відміну від роботи звичайних SQL-запитів. Ось чому запит SELECT, що був застосований в прикладі вище, був реалізований як звичайний запит.

Декілька слів щодо виконання мульти-запиту INSERT...
Наприклад, застосування запиту INSERT із синтаксисом, коли вноситься до БД декілька значень (приклад 4), вигідніше, аніж застосування підготовленого виразу, що був в прикладі вище (приклад 3).


Приклад #4 Зменшення кількості обміну пакетів між клієнт-сервером, застосовуючи мульти SQL-запит INSERT порівняно із підготовленим виразом.

<?php
if (!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3), (4)" )) {
    echo 
"Multi-INSERT failed: ("  .
     $mysqli->errno  ") ". $mysqli-> error;
}
?>


3. Тип значень результуючої вибірки даних

Протокол обміну даних СКБД MySQL визначає різний протокол обміну даних для підготовлених виразів та для звичайних запитів між веб-сервером та сервером БД. ПВ використовують протокол, що має назву двійковий протокол. Сервер БД відправляє результуючу вибірку даних "як є" в двійковому форматі. Дані не кодуються в строкові дані перед відправленням до веб-серверу, замість того клієнтська бібліотека перетворює двійковий код значень у відповідний PHP формат.

Наприклад, результуюча вибірка SQL з типом даних INT буде перетворена в PHP дані формату integer.


Приклад #5 Початкові типи даних

<?php
$mysqli 
= new  mysqli("example.com" "user" "pass" "db" );
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)

Це дещо відрізняється від обміну даними із сервером БД звичайних SQL-запитів, - так звичайний запит за замовчуванням повертає всі дані як строковий тип даних.
Проте можна змінити налаштування зєднання і тоді немає різниці, яким чином відбувається передача даних, чи застосовується ПВ чи як звичайний SQL-запит.


4. Отримуємо результуючу вибірку даниx, застосовуючи зв'язані змінні

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

Результуюча вибірка даних підготовленого виразу повинна бути відповідним чином звязана після виконання. Певна одна змінна повинна привязуватись до певного значення колонки виразу результуючої вибірки.

Приклад #6 Привязуємо відповідні змінні результуючої вибірки даних

<?php
$mysqli 
= new  mysqli("example.com" "user" "pass""db" );
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_labelgettype( $out_label));
}
?>

Результат виконання цієї частини скрипту

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


Підготовлені вирази за замовчуванням повертають не буферизовану результуючу вибірку даних. Тобто результуюча вибірка ніяким неявним чином не отримується і не передається до клієнту. Вона (результуюча вибірка даних) залишається на сервері Бази Даних та займає його ресурси аж поки клієнт (веб-сервер) не отримає всі необхідні дані. Таким чином варто розважливо споживати ресурси ситеми.

Дані результуючої вибірки даних підготовленого виразу можна зберегти застосовуючи функцію mysqli_stmt_store_result.


5. Результуюча вибірка за допомогою інтерфейсу mysqli_result

Крім того, що вихідні дані можна зв'язувати, їх також можна отримати за допомогою функції mysqli_stmt_get_result(), яка вже буферизує дані на клієнтській стороні.


Приклад #7 Застосовуємо mysqli_result

<?php
$mysqli 
= new  mysqli("example.com" "user" "pass" "db");
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 та буферизуючи значення даних на клієнтській стороні маємо можливість будувати систему php-скрипту більш гнучкою при використанні отриманих даних.


Приклад #8 Буферизація результуючої вибірки даних для більш зручної роботи

<?php
$mysqli 
= new  mysqli("example.com" "user" "pass""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"
}


6. Екранування даних та SQL-інєкція

Зв'язані змінні відправляються до серверу окремо від SQL-запиту і тому це безпечніше з точки зору вразливостей. Сервер БД використовує значення параметрів після того, як відбувся аналіз шаблону підготовленого виразу. Додатково екранувати значення зв'язаних параметрів немає необхідності, так як вони ніколи прямо не встановлюються в запит. Щоб відбулися відповідні перетворення підготовленого виразу для серверу необхідно додатково вказати тип даних значень параметрів. Щоб виконати зв'язування значень параметрів та шаблону підготовленого виразу застосовується функція mysqli_stmt_bind_param().

Таке розділення підготовленого виразу вважається безпечним заходом стосовно SQL-інєкції, проте того ж рівня безпеки можна досягнути і використовуючи звичайні запити до серверу БД за умови якщо правильно форматувати вхідні дані. Звісно варто зважати, що таке форматування вхідних даних вимагає більше логічних перевірок даних. Таким чином підготовлені вирази є більш простішими та менш схильними до помилок у питанні безпеки серверу БД.



7. Порівняльна таблиця підготовлених та звичайних запитів

Підготовлені та не-підготовлені вирази
  Підготовлений вираз Звичайний запит
Кількість звернень до серверу БД, 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()
Передача даних клієнт-серверний протокол Двійковий протокол Текстовий протокол
Тип даних результуючої вибірки даних Зберігаються при виборі даних Перетворюються до строкових даних або зберігаються при виборі
Підтримка виразів SQL Остання версія MySQL підтримує більшість але не всі Так