Використання змінних зв'язування в SQL для підвищення продуктивності додатків і забезпечення їх безпеки

1. Демонстрація рішення InfoSphere Guardium Database Activity Monitoring
2. Огляд змінних зв'язування
3. Яким чином змінні зв'язування підвищують продуктивність програми
4. Два способи зв'язування
5. Порівняння SQL-виразів
6. літерали
7. Лістинг 1. Два SQL-запиту з використанням літералів
8. Лістинг 2. Загальний пул з літералами
9. Лістинг 3. Вихідна інформація SQL-запитів з літералами
10. змінні підстановки
11. Лістинг 4. Два SQL-запиту з використанням змінних підстановки
12. Лістинг 5. Загальний пул зі змінними підстановки
13. Лістинг 6. Вихідна інформація SQL-запитів зі змінними підстановки
14. змінні зв'язування
15. Лістинг 7. Два SQL-запиту з використанням змінних зв'язування
16. Лістинг 8. Загальний пул зі змінними зв'язування
17. Лістинг 9. Вихідна інформація SQL-запитів зі змінними зв'язування
18. Розбір SQL-виразів і продуктивність
19. Лістинг 10. Вираз без змінних зв'язування
20. Лістинг 11. Споживання процесорних ресурсів без змінних зв'язування
21. Лістинг 12. Виконання в загальному пулі
22. Використання змінних зв'язування
23. Лістинг 13. SQL-вираз зі змінними зв'язування
24. Лістинг 14. Споживання процесорних ресурсів зі змінними зв'язування
25. Лістинг 15. Загальний пул зі змінними зв'язування
26. Зведена інформація по продуктивності
27. Оптимізація продуктивності SQL-виразів
28. Лістинг 16. Простий SQL-запит без змінних зв'язування
29. Підготовлені вираження зі змінними зв'язування
30. Лістинг 17. Просте SQL-вираз зі змінними зв'язування
31. Лістинг 18. Багаторазове використання виразу
32. Типи атак з використанням SQL-ін'єкцій
33. Некоректна обробка escape-символів
34. Коментарі та вираження в SQL
35. Безпека сервера SQL-бази даних
36. Некоректна обробка типів
37. Запобігання атак на основі SQL-ін'єкцій
38. Лістинг 19. Підготовлені вираження зі змінними зв'язування
39. Змінні зв'язування в базі даних
40. Лістинг 20. Змінні зв'язування в SQL * Plus
41. Лістинг 21. Змінні зв'язування в PL / SQL
42. Лістинг 22. Змінні зв'язування в Microsoft SQL Server
43. Лістинг 23. Мінлива зв'язування в підготовленому вираженні MySQL Server
44. Лістинг 24. Кілька змінних зв'язування в підготовленому вираженні MySQL Server
45. Висновок
46. Ресурси для скачування

Наша взаимовыгодная связь https://banwar.org/

Порівняння переваг по продуктивність і безпеки при використанні змінних зв'язування, змінних підстановки і літералів в SQL-виразах

Демонстрація рішення InfoSphere Guardium Database Activity Monitoring

З цієї демонстрації ви дізнаєтеся, як рішення IBM InfoSphere® Guardium® Database Activity Monitoring допоможе захистити ваші конфіденційні дані в розподілених системах управління базами даних на основі цілісного підходу до забезпечення безпеки даних і відповідності нормативним вимогам. Увійдіть для перегляду демонстрації IBM InfoSphere Guardium database activity monitoring .

При використанні SQL для передачі даних між своїм веб-додатком і базою даних ви можете або включити в SQL-вираз літеральние дані, або використовувати т. Н. змінні зв'язування (bind variable). Змінні зв'язування - це "групові символи" для реальних значень в SQL-виразах. Використання в SQL-виразах змінних зв'язування замість змінних підстановки (substitution variable) або литералов зводить до мінімуму тривалість обробки і дозволяє підвищити продуктивність програми на 20 - 30%. Крім цього, змінні зв'язування допомагають запобігати атакам з використанням SQL-ін'єкцій.

У цій статті порівнюється виграш в продуктивності при використанні змінних зв'язування (інші назви: змінні зв'язку, bind-параметри, параметри зв'язування, динамічні параметри) замість змінних підстановки або літералів в SQL-виразах. Я коротко представляю змінні зв'язування, а потім демонструю їх використання в SQL-виразах і демонструю результуюче підвищення продуктивності. Я також показую, як використовувати змінні зв'язування для ефективного стримування атак з використанням SQL-ін'єкцій в прикладі Java-додатка.

Огляд змінних зв'язування

Мінлива зв'язування - це змінна, позначена Групові символи, таким як (?),: Name або @name. Підстановлювальний символ залежить від використовуваного сервера бази даних з підтримкою SQL (далі: сервер SQL-бази даних). Реальне значення Групові символи вводиться в процесі функціонування, безпосередньо перед виконанням SQL-вирази.

Яким чином змінні зв'язування підвищують продуктивність програми

У більшості реляційних баз даних обробка SQL-вирази здійснюється в три етапи.

1. Розбір SQL-вирази. Верифікація синтаксису SQL-вирази і прав доступу; створення найкращого (оптимізованого) плану виконання для цього SQL-вирази. У цій точці значення підстановлювальних змінних ще не відомі.
2. Зв'язування змінних. API-інтерфейс надає реальні значення для підстановки.
3. Виконання. Здійснюється на основі обраного плану виконання з реальними значеннями підстановлювальних змінних.

Кожен раз, коли будь-яка SQL-вираз відсилається в базу даних, здійснюється точне зіставлення його тексту з метою виявлення наявності такого ж вираження в спеціальній області пам'яті, т.зв. Загалом пулі (shared pool). Якщо відповідне вираження не знайдено, база даних виконує повний розбір (hard parse) цього виразу. Якщо відповідний вираз знайдено, то база даних ініціює частковий розбір (soft parse).

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

Використання змінних зв'язування забезпечує можливість часткового розбору, в результаті чого менше процесорного часу витрачається на вибір оптимізованого плану виконання. Інформація про порядок обробки даного SQL-вирази зберігається разом з самим цим SQL-виразом в області загального пулу.

Два способи зв'язування

Залежно від використовуваного API-інтерфейсу зв'язування здійснюється одним із двох способів: Зв'язування за значенням приймає реальне значення перед кожним виконанням. Зв'язування з посиланням використовує значення, знайдене за певною адресою пам'яті.

Порівняння SQL-виразів

Використання змінних зв'язування забезпечує ряд очевидних переваг з точки зору продуктивності. Перша перевага полягає в тому, що розміщення в області загального пулу великої кількості подібних, але при цьому унікальних SQL-виразів призводить до непродуктивних витрат пам'яті; написання одного виразу з взаємозамінними змінними - це більш ефективний підхід. Крім того, розбір всіх цих SQL-виразів забирає багато часу. Скорочення кількості повних розборів зводить до мінімуму споживання процесорних ресурсів.

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

літерали

Літерали - це значення, безпосередньо передаються в SQL-запит. Наприклад, два запити в лістингу 1 можна виконати з використанням літералів.

Лістинг 1. Два SQL-запиту з використанням літералів

SQL> SELECT * FROM dual WHERE dummy = 'dummy_literal1'; no rows selected SQL> SELECT * FROM dual WHERE dummy = 'dummy_literal2'; no rows selected

У лістингу 2 показані запити в області загального пулу.

Лістинг 2. Загальний пул з літералами

SQL> SELECT sql_text, 2 executions 3 FROM v $ sql 4 WHERE INSTR (sql_text, 'SELECT * FROM dual WHERE dummy')> 0 5 AND INSTR (sql_text, 'sql_text') = 0 6 ORDER BY sql_text;

У лістингу 3 показана результуюча вихідна інформація.

Лістинг 3. Вихідна інформація SQL-запитів з літералами

SQL_TEXT EXECUTIONS ------------------------------------------------ ------------ ---------- SELECT * FROM dual WHERE dummy = 'dummy_literal1' 1 SELECT * FROM dual WHERE dummy = 'dummy_literal2 ↑ 1 2 rows selected.

В цьому випадку розбір запитів здійснювався окремо, тому в загальному пулі знаходяться два окремих запиту.

змінні підстановки

Якщо у виразі використовується змінна підстановки, то вхідний значення запитується, а вираз переписується з включенням цього значення. Переписане вираз передається в базу даних. В результаті сервер бази даних нічого не знає про змінну підстановки. У лістингу 4 показаний приклад змінної підстановки.

Лістинг 4. Два SQL-запиту з використанням змінних підстановки

SQL> SELECT * FROM dual WHERE dummy = '& dummy'; Enter value for dummy: dummy_substitution1 old 1: SELECT * FROM dual WHERE dummy = '& dummy' new 1: SELECT * FROM dual WHERE dummy = 'dummy_substitution1' no rows selected SQL> SELECT * FROM dual WHERE dummy = '& dummy'; Enter value for dummy: dummy_substitution2 old 1: SELECT * FROM dual WHERE dummy = '& dummy' new 1: SELECT * FROM dual WHERE dummy = 'dummy_substitution2' no rows selected

У лістингу 5 показані запити в області загального пулу.

Лістинг 5. Загальний пул зі змінними підстановки

SQL> SELECT sql_text, 2 executions 3 FROM v $ sql 4 WHERE INSTR (sql_text, 'SELECT * FROM dual WHERE dummy')> 0 5 AND INSTR (sql_text, 'sql_text') = 0 6 ORDER BY sql_text;

У лістингу 6 показана результуюча вихідна інформація.

Лістинг 6. Вихідна інформація SQL-запитів зі змінними підстановки

SQL_TEXT EXECUTIONS ------------------------------------------------ ------------ ---------- SELECT * FROM dual WHERE dummy = 'dummy_substitution1' 1 SELECT * FROM dual WHERE dummy = 'dummy_substitution2 ↑ 1 2 rows selected.

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

змінні зв'язування

Розглянемо, як використання змінних зв'язування впливає на загальний пул. У лістингу 7 використовується такий же формат, як і в попередніх випадках.

Лістинг 7. Два SQL-запиту з використанням змінних зв'язування

SQL> VARIABLE dummy VARCHAR2 (30); SQL> EXEC: dummy: = 'dummy_bind1'; PL / SQL procedure successfully completed. SQL> SELECT * FROM dual WHERE dummy =: dummy; no rows selected SQL> EXEC: dummy: = 'dummy_bind2'; PL / SQL procedure successfully completed. SQL> SELECT * FROM dual WHERE dummy =: dummy; no rows selected

У лістингу 8 показаний моментальний знімок області загального пулу:

Лістинг 8. Загальний пул зі змінними зв'язування

SQL> SELECT sql_text, 2 executions 3 FROM v $ sql 4 WHERE INSTR (sql_text, 'SELECT * FROM dual WHERE dummy')> 0 5 AND INSTR (sql_text, 'sql_text') = 0 6 ORDER BY sql_text;

У лістингу 9 показана вихідна інформація.

Лістинг 9. Вихідна інформація SQL-запитів зі змінними зв'язування

SQL_TEXT EXECUTIONS ------------------------------------------------ ------------ ---------- SELECT * FROM dual WHERE dummy =: dummy 2 1 row selected.

В цьому випадку обидва рази виконувалося одне і те ж SQL-вираз, тому в загальному пулі присутній лише один SQL-запит.

Розбір SQL-виразів і продуктивність

Як зазначалося вище, споживання процесорних ресурсів змінюється в залежності від типу необхідного SQL-розбору. Літерали і змінні підстановки вимагають повного розбору, який споживає більше процесорних ресурсів, ніж частковий розбір, необхідний для змінних зв'язування.

У показаному в наступних лістингах програмному коді я витягував результати вимірювання споживання процесорних ресурсів з V $ MYSTAT за допомогою запиту статистики parse time cpu. Ця статистика відображає загальне процесорний час (в десятках мілісекунд), витрачений на розбір (повний або частковий). Також показані вираження, присутні в загальному пулі.

У лістингу 10 показано вираз без змінних зв'язування.

Лістинг 10. Вираз без змінних зв'язування

SQL> DECLARE 2 l_dummy dual.dummy% TYPE; 3 BEGIN 4 FOR i IN 1 .. 10 LOOP 5 BEGIN 6 EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy =' '' || TO_CHAR (i) || '' '' 7 INTO l_dummy; 8 EXCEPTION 9 WHEN NO_DATA_FOUND THEN 10 NULL; 11 END; 12 END LOOP; 13 END; 14 /

PL / SQL-процедура завершується успішно. Код в лістингу 11 виконує процедуру і перевіряє споживання процесорних ресурсів.

Лістинг 11. Споживання процесорних ресурсів без змінних зв'язування

SQL> SELECT sn.name, ms.value 2 FROM v $ mystat ms, v $ statname sn 3 WHERE ms.statistic # = sn.statistic # 4 AND sn.name = 'parse time cpu'; NAME VALUE ------------------------------------------------ ---------------- ---------- parse time cpu 63 1 row selected.

Результати показують, що на розбір в ході цього сеансу було витрачено 630 мілісекунд процесорного часу.

У лістингу 12 показано результуюче виконання в загальному пулі:

Лістинг 12. Виконання в загальному пулі

SQL> SELECT sql_text, 2 executions 3 FROM v $ sql 4 WHERE INSTR (sql_text, 'SELECT dummy FROM dual WHERE dummy')> 0 5 AND INSTR (sql_text, 'sql_text') = 0 6 AND INSTR (sql_text, 'DECLARE' ) = 0 7 ORDER BY sql_text; SQL_TEXT EXECUTIONS ------------------------------------------------ ------------ ---------- SELECT dummy FROM dual WHERE dummy = '1' 1 SELECT dummy FROM dual WHERE dummy = '10' 1 SELECT dummy FROM dual WHERE dummy = '2' 1 SELECT dummy FROM dual WHERE dummy = '3' 1 SELECT dummy FROM dual WHERE dummy = '4' 1 SELECT dummy FROM dual WHERE dummy = '5' 1 SELECT dummy FROM dual WHERE dummy = '6' 1 SELECT dummy FROM dual WHERE dummy = '7' 1 SELECT dummy FROM dual WHERE dummy = '8' 1 SELECT dummy FROM dual WHERE dummy = '9' 1 10 rows selected.

Зверніть увагу, що загальний пул містить 10 схожих виразів, які використовують літерали.

Використання змінних зв'язування

Тепер я виконаю ці ж запити, але з використанням змінних зв'язування. У лістингу 13 показано той же самий вираз, що і в лістингу 10 , Але з використанням змінних зв'язування.

Лістинг 13. SQL-вираз зі змінними зв'язування

SQL> DECLARE 2 l_dummy dual.dummy% TYPE; 3 BEGIN 4 FOR i IN 1 .. 10 LOOP 5 BEGIN 6 EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy = TO_CHAR (: dummy)' 7 INTO l_dummy USING i; 8 EXCEPTION 9 WHEN NO_DATA_FOUND THEN 10 NULL; 11 END; 12 END LOOP; 13 END; 14 /

PL / SQL-процедура завершується успішно. Після виконання процедури в лістингу 13 і перевірки споживання процесорних ресурсів я отримав результати, показані в лістингу 14.

Лістинг 14. Споживання процесорних ресурсів зі змінними зв'язування

SQL> SELECT sn.name, ms.value 2 FROM v $ mystat ms, v $ statname sn 3 WHERE ms.statistic # = sn.statistic # 4 AND sn.name = 'parse time cpu'; NAME VALUE ------------------------------------------------ ---------------- ---------- parse time cpu 40 1 row selected.

Результати показують, що на розбір в ході цієї сесії було витрачено 400 мілісекунд процесорного часу. Це менше ніж дві третини від обсягу ресурсів, спожитих в попередньому прикладі. Тепер подивимося на загальний пул.

Лістинг 15. Загальний пул зі змінними зв'язування

SQL> SELECT sql_text, 2 executions 3 FROM v $ sql 4 WHERE INSTR (sql_text, 'SELECT dummy FROM dual WHERE dummy')> 0 5 AND INSTR (sql_text, 'sql_text') = 0 6 AND INSTR (sql_text, 'DECLARE' ) = 0 7 ORDER BY sql_text; SQL_TEXT EXECUTIONS ------------------------------------------------ ------------ ---------- SELECT dummy FROM dual WHERE dummy = TO_CHAR (: dummy) 10 1 row selected.

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

Ці приклади наочно демонструють, що заміна литералов на змінні зв'язування скорочує як споживання ресурсів пам'яті, так і споживання процесорних ресурсів. У цих випадках продуктивність була підвищена приблизно на 30%.

Зведена інформація по продуктивності

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

Тепер я продемонструю використання змінних зв'язування в контексті. Я буду вимірювати виграш в продуктивності при використанні змінних зв'язування для SQL-вирази в прикладі Java-програми.

Оптимізація продуктивності SQL-виразів

У лістингу 16 представлена ​​типова Java-програма, в якій SQL-вирази написані з використанням літералів. Для кожного циклу створюється нове SQL-вираз. Кожен раз, коли цикл зустрічається з новим значенням, створюється і виконується новий SQL-запит.

Лістинг 16. Простий SQL-запит без змінних зв'язування

sql = "SELECT t.name FROM hr.employees t WHERE employee_id ="; System.out.println ( "Start:" + new Date ()); for (int i = 0; i & lt; 10000; i ++) {statement = connection.createStatement (); resultset = statement.executeQuery (sql + Integer.toString (i)); if (resultset.next ()) {name = resultset.getString ( "name"); doSomething (name); } Resultset.close (); statement.close (); } System.out.println ( "End:" + new Date ());

Для виконання цього коду потрібно приблизно 11 секунд. Тепер перепишемо цей код з використанням підготовлених виразів (prepared statement) і змінних зв'язування.

Підготовлені вираження зі змінними зв'язування

У лістингу 17 сервера відправляється запит з певної змінної зв'язування. В процесі виконання ми пов'язуємо Java-змінну i з SQL-виразом. Це дозволяє нам використовувати один і той же план виконання для 10000 запитів, що підвищує продуктивність за рахунок зведення до мінімуму кількості розборів SQL.

Лістинг 17. Просте SQL-вираз зі змінними зв'язування

sql = "SELECT t.name FROM hr.employees t WHERE employee_id =?"; System.out.println ( "Start:" + new Date ()); for (int i = 0; i & lt; 10000; i ++) {statement = connection.prepareStatement (sql); statement.setInt (1, i); resultset = statement.executeQuery (); if (resultset.next ()) {name = resultset.getString ( "name"); doSomething (name); } Resultset.close (); statement.close (); } System.out.println ( "End:" + new Date ());

Для виконання цього коду потрібно приблизно 7 секунд. Однак зверніть увагу, що цей код створює новий вираз для кожного циклу. Ми можемо додатково поліпшити цей результат, створивши одне-єдине вираження і використовуючи його повторно в кожному циклі (див. Лістинг 18).

Лістинг 18. Багаторазове використання виразу

sql = "SELECT t.name FROM hr.employees t WHERE employee_id =?"; statement = connection.prepareStatement (sql); System.out.println ( "Start:" + new Date ()); for (int i = 0; i <10000; i ++) {statement.setInt (1, i); resultset = statement.executeQuery (); if (resultset.next ()) {name = resultset.getString ( "name"); doSomething (name); } Resultset.close (); } System.out.println ( "End:" + new Date ()); statement.close ();

Цьому Java-коду потрібно приблизно 4 секунди для виконання тих же SQL-операцій, що і в первісному коді, на виконання якого пішло 11 секунд.

Типи атак з використанням SQL-ін'єкцій

Атаки з використанням SQL-ін'єкцій в 2013 році зайняли перше місце в рейтингу загроз безпеки організації Open Web Application Security Project (див. Розділ ресурси ). При атаці з використанням SQL-ін'єкції в базу даних веб-додатки через поле для введення інформації вставляються зловмисні SQL-вирази з метою змусити додаток виконати ці вирази. Для успішної атаки з використанням SQL-ін'єкцій необхідно, щоб програмний код додатка був уразливим для введення даних користувачем. Атаки з використанням SQL-ін'єкцій використовують уразливість додатки до введених користувачем даних в результаті яких некоректної обробки escape-символів в строкових літералах з вбудованими SQL-виразами, або в результаті відсутності суворого контролю типів даних, що вводяться.

У наступному розділі розглядаються два типи вразливостей, що сприяють атакам з використанням SQL-ін'єкцій.

Некоректна обробка escape-символів

При атаці першого типу хакер поміщає текст, що містить escape-символи і вбудовані SQL- вираження, в поле форми веб-додатки або в атрибут запиту. Якщо веб-додаток і не виконує жодних escape-символи, цей текст - разом зі зловмисними SQL-виразами - передається в базу даних для виконання.

Цю вразливість ілюструє наступний рядок коду:

statement: = "SELECT * FROM emp WHERE emp_name = '" + empName + "';"

Якщо змінна empName отримує значення з поля форми веб-додатки, то атакуючий може ввести в поле empName наступний код:

'Or' 1 '=' 1

Якщо код веб-додатки не виключить символ ', він буде включений в SQL-вираз "як є", що породить наступне нове SQL-вираз:

SELECT * FROM emp WHERE emp_name = '' or '1' = '1';

При виконанні цього коду він поверне всі дані з таблиці emp, оскільки вираз '1' = '1' в конструкції WHERE завжди має значення "істина". Таким чином атакуючий успішно отримає дані про всіх співробітників в базі даних.

Коментарі та вираження в SQL

Інша поширена різновид атаки полягає в зловмисної ін'єкції в SQL-вираз символу коментаря, який блокує виконання решти запиту. В якості такої ін'єкції можуть бути використані наступні три типи SQL-коментарів:

'Or' 1 '=' 1 '-' 'or' 1 '=' 1 '({' 'or' 1 '=' 1 '/ *'

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

Крім того, атакуючий може додати зловмисні SQL-вирази в кінець існуючого вираження. Наприклад, таке значення empName призведе до видалення таблиці emp. Якщо API-інтерфейс допускає введення декількох виразів, також будуть видалені всі дані з таблиці userinfo.

a '; DROP TABLE emp; SELECT * FROM userinfo WHERE 't' = 't

При введенні такого рядка підсумкове SQL-вираз матиме вигляд:

SELECT * FROM emp WHERE emp_name = 'a'; DROP TABLE emp; SELECT * FROM userinfo WHERE 't' = 't';

Безпека сервера SQL-бази даних

Більшість реалізацій серверів SQL-баз даних дозволяє виконання декількох виразів в одному виклику, проте деякі API-інтерфейси (такі як PHP-функція mysql_query ();) не дозволяють цього. Такий захід не дає атакуючим здійснювати ін'єкцію нових запитів, але не заважає їм змінювати існуючі запити.

Некоректна обробка типів

Другий тип атаки з використанням SQL-ін'єкцій має місце, коли вводяться користувачем дані не перевіряються на відповідність допустимим типам. Наприклад, припустимо, що програміст не передбачив валідацію вводяться користувачем даних для числового поля, показаного в наступному прикладі:

statement: = "SELECT * FROM userinfo WHERE id =" + id_var + ";"

Прийняте значення id_var є числовим, однак перед відправкою цього значення в SQL-запит не проводиться ніякої валідації. Якщо змінна id_var пов'язана з формою заяви, то атакуючий зможе задати її наступним чином:

1; DROP TABLE users, yielding the following SQL: SELECT * FROM userinfo WHERE id = 1; DROP TABLE users;

У разі успішної передачі цього виразу воно змусить сервер SQL-бази даних видалити таблицю users з бази даних.

Запобігання атак на основі SQL-ін'єкцій

Коли змінна зв'язування приймає в якості аргументу в підготовлене вираз, JDBC-драйвер автоматично екранує (escape) цю змінну. В отриманих при цьому строкових даних ця змінна трактується як призначені для користувача дані і не може бути інтерпретована сервером SQL-бази даних як SQL-вираз. Тому будь-які надані користувачами дані повинні оброблятися так само перед їх додаванням в SQL-вираз. У лістингу 19 показано додавання змінної зв'язування user ID до SQL-виразу.

Лістинг 19. Підготовлені вираження зі змінними зв'язування

String selectStatement = "SELECT * FROM User WHERE userId =?"; PreparedStatement prepStmt = con.prepareStatement (selectStatement); prepStmt.setString (1, userId); ResultSet rs = prepStmt.executeQuery ();

Метод setString об'єкта prepStmt екранує рядок userID і додає її в SQL-вираз. Будь-які зловмисні SQL-вирази, передані ззовні за допомогою змінної userID, в обробленої таким способом версії вхідної інформації будуть представлені в неісполняемих вигляді.

Змінні зв'язування в базі даних

На закінчення я коротко розгляну синтаксис змінних зв'язування в поширених середовищах програмування баз даних.

У лістингу 20 показано використання змінних зв'язування в SQL * Plus.

Лістинг 20. Змінні зв'язування в SQL * Plus

SQL> variable deptno number SQL> exec: deptno: = 10 SQL> select * from emp where deptno =: deptno;

PL / SQL підтримує більшість підходів, пов'язаних зі змінними зв'язування. Розглянемо фрагмент PL / SQL-коду в лістингу 21:

Лістинг 21. Змінні зв'язування в PL / SQL

create or replace procedure dsal (p_empno in number) as begin update emp set sal = sal * 2 where empno = p_empno; commit; end; /

Зверніть увагу на те, що кожна посилання на PL / SQL-змінну являє собою змінну зв'язування.

У разі Microsoft SQL Server використовується sp_executesql, а змінна зв'язування виглядає наступним чином: @ var1. Відповідний приклад показаний в лістингу 22.

Лістинг 22. Змінні зв'язування в Microsoft SQL Server

DECLARE @IntVariable int; DECLARE @SQLString nvarchar (500); DECLARE @ParmDefinition nvarchar (500); / * Build the SQL string one time. * / SET @SQLString = N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID FROM AdventureWorks2008R2.HumanResources.Employee WHERE BusinessEntityID = @BusinessEntityID '; SET @ParmDefinition = N '@ BusinessEntityID tinyint'; / * Execute the string with the first parameter value. * / SET @IntVariable = 197; EXECUTE sp_executesql @SQLString, @ParmDefinition, @BusinessEntityID = @IntVariable; / * Execute the same string with the second parameter value. * / SET @IntVariable = 109; EXECUTE sp_executesql @SQLString, @ParmDefinition, @BusinessEntityID = @IntVariable;

У лістингу 23 показано підготовлене вираз в MySQL Server з однієї змінної зв'язування.

Лістинг 23. Мінлива зв'язування в підготовленому вираженні MySQL Server

mysql> prepare stmt from -> 'select count (*) from information_schema.schemata where schema_name =?'; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> set @schema: = 'test'; Query OK, 0 rows affected (0.00 sec) mysql> execute stmt using @schema; + ---------- + | count (*) | + ---------- + | 1 | + ---------- + 1 row in set (0.00 sec)

У лістингу 24 показано підготовлене вираз з декількома змінними зв'язування.

Лістинг 24. Кілька змінних зв'язування в підготовленому вираженні MySQL Server

mysql> prepare stmt from -> 'select count (*) -> from information_schema.schemata -> where schema_name =? or schema_name =? '; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> execute stmt -> using @ schema1, @ schema2 + ---------- + | count (*) | + ---------- + | 2 | + ---------- + 1 row in set (0.00 sec)

Висновок

У цій статті я представив змінні зв'язування і на прикладах коду порівняв їх зі змінними підстановки і з літералами з точки зору їх впливу на продуктивність. Було показано, що використання змінних зв'язування в SQL-виразах дозволяє підвищити показники продуктивності виконання SQL-коду на величину до 30%. Крім того, змінні зв'язування - це добре відомий спосіб захисту від атак з використанням SQL-ін'єкцій (див. Розділ ресурси ). Я продемонстрував кілька типів атак з використанням SQL-ін'єкцій і показав, як використовувати змінні зв'язування в декількох поширених середовищах програмування баз даних.

Ресурси для скачування

Схожі тими

• Оригінал статті: Using SQL bind variables for application performance and security .
• Get the big picture of cybersecurity intelligence , IBM developerWorks, жовтень 2013 р Узагальнені у вигляді інфографіки результати дослідження 2013 IBM Cyber ​​Security Intelligence Index, що охопила 3700 замовників в 130 країнах (в тому числі деталізовані рейтинги атак і розбивка інцидентів за категоріями).
• згідно з рейтингом 2013 Top 10 List організації Open Web Application Security Project (OWASP), атаки за допомогою ін'єкцій були основною загрозою для веб-додатків в 2013 р
• Крім того, організація OWASP опублікувала керівництво щодо запобігання атак з використанням SQL-ін'єкцій в додатках Java.
• Bind variables - The key to application performance (Akadia.com): На додаток до огляду змінних зв'язування в різних середовищах програмування баз даних в цій статті розглядаються варіанти використання, в яких змінні зв'язування застосовні в меншій мірі.
• Java Prepared Statements (Oracle Cursor Sharing & Bind Variables) блог Айкіта Ейкін (Aykut Akin), березень 2013 Щоб отримати додаткові приклади з написання SQL-запитів в Java-додатках.
• Bind variables Performance Test in Java , Mkyong, Mkyong.com, січень 2009 г. Ще один підхід до тестування продуктивності змінних зв'язування в Java-додатках.
• Parameterized Queries , Markus Winand, Use the Index, Luke !, березень 2012 р параметризованих запити і bind-параметри не розглядаються в більшості підручників з SQL, тому скористайтеся цим оглядом.
• Блог Security On developerWorks : Нові практичні рекомендації, статті і демонстраційні відеоматеріали з питань безпеки.
• Підписка на щотижневу розсилку новин Security On developerWorks з питань безпеки.
• В Твіттері @dwsecurity публікуються відомості про нові матеріали сайту developerWorks з питань безпеки.

Підпішіть мене на ПОВІДОМЛЕННЯ до коментарів