Доступ до даних на основі збережених процедур в веб-додатках (database sql function mysql php)
Ключові слова: database , sql , function , mysql , php , ( знайти схожі документи )
From: Антон Гришан Date: Mon, 20 Mar 2009 Повідомлення 17:02:14 +0000 (UTC) Subject: Доступ до даних на основі збережених процедур в веб-додатках Матеріал надано редакцією журналу Системний адміністратор. Опубліковано в журналі "Системний адміністратор" N 1 2009 Більшість додатків змушене працювати з базами даних, спілкуючись з СУБД на мові SQL-запитів. Іншими словами, одні програми на мовах високого рівня складають інші програми на SQL. Це виглядає звичним - тому здається логічним і зручним, але чи так це насправді? Розглянемо два способи взаємодії програми та бази даних. Перший і на даний момент більш популярний спосіб - генерація SQL-запиту в тілі скрипта (тут і далі наводяться приклади для PHP5 + MySQL 5 з встановленим розширенням mysqli): / * Вибрати всі міста в країні з заданим кодом, динамічна генерація запиту * / $ countryCode = 'RU'; $ Result = mysqli_query ($ db, "SELECT Cities.name FROM Countries, Cities WHERE Cities.countryId = Countries.id \ AND Countries.code = '". Mysqli_real_escape_string ($ db, $ countryCode). "'"); Другий спосіб - доступ до даних через збережені процедури. Процедура - об'єкт бази даних, що представляє собою набір скомпільованих SQL-інструкцій. Скрипт викликає процедуру зі списком параметрів і обробляє отриманий результат (и). Наведений вище запит (SELECT Cities.name FROM Countries, Cities WHERE Cities.countryId = Countries.id AND Countries.code = 'RU') можна зберегти у вигляді процедури - getCities, з одним вхідним параметром - код країни (countryCode char (2) ). Наведу приклад створення збереженої процедури: DELIMITER $$ CREATE PROCEDURE `getCities` (countryCode char (2)) BEGIN SELECT Cities.name FROM Countries, Cities WHERE Cities.countryId = Countries.id AND Countries.code = countryCode; END $$ DELIMITER; У момент виклику збереженої процедури СУБД підставляє значення параметра в тіло запиту і повертає результат вибірки даних. Для виклику процедури, що "getCities" на стороні сервера БД необхідно виконати наступну команду: CALL getCities ( 'RU'); Викликати процедуру getCities з PHP-додатки можна наступним чином: / * Вибрати всі міста в країні з заданим кодом, виклик збереженої процедури * / $ countryCode = 'RU'; mysqli_multi_query ($ db, "CALL getCities ( '". mysqli_real_escape_string ($ db, $ countryCode). "');"); Робота з БД через збережені процедури зараз зустрічається рідше, ніж генерація SQL-запиту в тілі програми. На мій погляд, це відбувається з наступних причин: - найбільш популярна зв'язка для написання веб-додатків - PHP + MySQL, однак тільки в MySQL 5 з'явилася можливість використовувати збережені процедури; - в більшості посібників з програмування наведені приклади роботи з БД, засновані на генерації запиту в тілі скрипта, і початківцям програмістам складно відійти від книжкових прикладів; - написання програми з використанням процедур має на увазі вміння програмістів працювати з збереженими процедурами або наявність часу для вивчення даної технології (що не завжди допустимо в рамках конкретного проекту). Переваги використання збережених процедур - Підвищення швидкості роботи БД. Процедури зберігаються в скомпільованому вигляді, а значить, СУБД не витрачає час на компіляцію запитував, чи потрібно його виконанні. Додатка не потрібно витрачати час на генерацію запиту. Команда для виклику збереженої процедури значно коротше, ніж запит, що міститься в тілі процедури, тому потрібно менше часу і трафіку на передачу команд на сервер БД. - Велика ступінь свободи. Збережені процедури підтримують: вхідні і вихідні параметри, локальні змінні, оператори умовного розгалуження, цикли, виклики вбудованих команд та інших процедур, виконання DDL-операторів. Багато в чому збережені процедури схожі на процедури мов програмування високого рівня. - Спрощення коду програми. У додатку немає SQL-запитів, а значить, програмісту не потрібно писати код для їх генерації. Для виклику процедури, що необхідно знати тільки ім'я і список параметрів (аналогічно викликом звичайних функцій / методів в тілі додатки). Такий підхід скорочує розмір коду і покращує його читабельність, що позитивно впливає на якість кінцевого продукту. - Безпека. Використання збережених процедур дозволяє значно знизити загрозу виникнення уразливості типу SQL-injection. Крім того, можна встановлювати права доступу до об'єктів бази даних для кожної процедури, що, що також сприяє підвищенню рівня безпеки програми. - Захист програми від змін структури БД. У процесі розвитку проекту може виникнути необхідність в зміні структури БД, наприклад, додати / видалити / перейменувати таблицю або стовпець. Якщо додаток генерує SQL-запити, то необхідно внести зміни в усі фрагменти коду, що відповідають за генерацію запитів. Організація доступу через збережені процедури не вимагає внесення змін в код додатка до тих пір, поки ім'я процедури і список параметрів (а також очікуваний результат) залишаються колишніми. - Зниження кількості помилок і спрощення налагодження. Найчастіше помилки в роботі програми з БД виникають з наступних причин: * додаток використовує некоректні значення для генерації SQL-запиту; * SQL-вираз некоректно описує очікуваний результат (тобто помилка в запиті); * Фрагмент коду програми, який відповідає за генерацію SQL-запиту, містить помилку і не здатний правильно побудувати потрібний запит. Якщо доступ до БД побудований на основі збережених процедур, то: - легко дізнатися, які значення потрапляють в збережену процедуру, досить роздрукувати список аргументів на момент виклику збереженої процедури; - вам не потрібно гадати по коду додатка, який саме запит повинен вийти в тому чи іншому місці програми, досить подивитися на тіло процедури, що, що значно спрощує процес налагодження; - цієї помилки виникнути не може, так як додаток взагалі не генерує SQL-запитів, всі запити знаходяться в БД у вигляді збережених процедур. Труднощі роботи з збереженими процедурами Існує маса переваг, які говорять за використання збережених процедур. Однак не варто думати, що збережені процедури, безумовно, оптимальне рішення для будь-якого проекту. Щоб зробити осмислений вибір, давайте розглянемо негативні сторони даного методу. - Проблема сумісності. Якщо необхідно забезпечити легку переносимість додатки на максимальну кількість СУБД, то, ймовірно, варто віддати перевагу динамічної генерації запитів, так як збережені процедури підтримуються не всіма СУБД. - Складність впровадження процедур в існуючий проект. Впровадження процедур в додаток, що використовує динамічну генерацію запитів, призведе до повної реорганізації коду роботи з БД. Необхідність такої реорганізації не завжди просто пояснити замовнику. - Передача складних типів даних. Іноді в якості аргументу процедурі потрібно передати не просто рядок або число, а масив даних (або більш складний об'єкт). У цьому випадку дані необхідно перетворити в рядок і в такому вигляді передавати збереженій процедурі, всередині якої відбувається зворотне перетворення. Побудова запиту в тілі додатки в цьому випадку простіше. Особливості роботи з збереженими процедурами - Збережені процедури служать тільки для доступу до даних (витяг / оновлення / видалення) та ні для чого більше. Використання процедур з іншою метою (перевірка даних або генерація HTML) є помилкою. - Процедура може повернути більше одного результату. У коді виклику збереженої процедури необхідно робити ітерацію по всьому повертається результатами і обробляти кожен з них окремо. - Досить складно передати в збережену процедуру масив значень. Найбільш популярним рішенням є передача масиву в збережену процедуру у вигляді рядка, що містить елементи масиву, розділені спеціальним символом (вертикальна риса - "|"), далі параметр аналізується в тілі процедури, що. - Якщо в тілі процедури, що необхідно динамічно генерувати SQL-запит (намагайтеся всіма можливими способами уникати написання подібних процедур), не забувайте екранувати лапки і спецсимволи у всіх переданих в процедуру параметрах, які беруть участь в побудові запиту, інакше процедура буде містити потенційну вразливість типу SQL- injection. Приклад класу для роботи з БД через збережені процедури Як ми вже помітили, збережені процедури дуже схожі на функції, наш клас буде реалізовувати прозору роботу з процедурами таким чином, щоб з точки зору програми не було різниці між звичайними функціями і збереженими процедурами. Щоб стало зрозуміло, про що йде мова, почнемо з прикладу: <? Php include 'DBaccess.php'; // Створюємо об'єкт доступу до БД $ db = new DBaccess ( 'main_db', 'localhost', 3306, 'login', 'password', 'utf8'); // Викликаємо збережену процедуру GetCities і передаємо як параметр RU $ result = $ db-> getCities ( 'RU'); // Обробляємо отриманий результат (масив даних) var_dump ($ result); ?> У наведеному вище прикладі для виклику збереженої процедури getCities з параметром RU (код країни) використовується об'єкт класу DBaccess. З точки зору програми такий виклик процедури виглядає як виклик звичайного методу, що дозволяє відмовитися від логіки написання програми, яка в свою чергу становить іншу програму на SQL. Наведу далі код класу DBaccess (для роботи класу потрібно PHP5 c mysqli розширенням, MySQL 5): <? Php class DBConnection {private $ db = null; public function __construct ($ dbName, $ host, $ port, $ login, $ password, $ charset) {$ this-> db = new mysqli ($ host, $ login, $ password, $ dbName, $ port); $ This-> db-> set_charset ($ charset); } Public function __call ($ storeProcedureName, $ params) {$ quotedParams = array (); foreach ($ params as $ param) {array_push ($ quotedParams, $ param === null? 'NULL': '\' '. $ this-> db-> escape_string ($ param).' \ ''); } $ Sql = 'CALL'. $ StoreProcedureName. '('. Implode ( ',', $ quotedParams). ');'; $ This-> db-> multi_query ($ sql); $ Results = array (); do {if ($ result = $ this-> db-> store_result ()) {$ rows = array (); while ($ row = $ result-> fetch_assoc ()) {array_push ($ rows, $ row); } $ Result-> close (); array_push ($ results, $ rows); }} While ($ this-> db-> more_results () && $ this-> db-> next_result ()); return ($ results); }}?> Усередині класу DBaccess не оголошений метод getCities, проте ми можемо викликати збережену процедуру як $ db-> getCities ( 'RU') ;. Це досягається за рахунок використання магічного методу __call () (дана можливість з'явилася в PHP5), який працює в такий спосіб: при виклику методу, що не оголошеного в класі, ім'я викликається методу і список аргументів передається в __call ($ metodName, $ params) для обробки (якщо метод оголошений). Таким чином, ми можемо викликати будь-яку процедуру, що зберігається на сервері, наприклад "$ db-> loadUser ($ email, $ password);". У наведеному вище коді класу DBaccess відсутні необхідні перевірки на помилки, можливість роботи з декількома БД, перевірка стабільності з'єднання з сервером, автоматичне перепідключення і безліч інших корисних функцій. Це зроблено навмисно, щоб проілюструвати основну ідею з використанням мінімальної кількості коду. 1. Повнофункціональна версія класу "DBaccess" - http://www.vipidn.com/dbaccess.zip. 2. Опис магічного методу __call () - http://ru2.php.net/manual/ru/language.oop5.overloading.php. 3. Опис розширення mysqli - http://ru2.php.net/manual/ru/ref.mysqli.php. 4. Інформація до роздумів - "Good and Evil in the Garden of Stored Procedures" (Jeremy D. Miller) - http://codebetter.com/blogs/jeremy.miller/archive/2005/07/05/130093.aspx
Обговорення [ RSS ]
- 1.1 , Онотоле (?), 13:42, 20/03/2009 [ відповісти ]
- 1.4 , matriks (??), 9:28, 22/03/2009 [ відповісти ]
PS "ламати легше, ніж стороить", це аналогія до "ганьбити", кому не зрозуміло.
- 1.5 , Alman (??), 5:50, 27/04/2009 [ відповісти ]
- 1.6 , Гришан Антон (?), 15:56, 04/05/2009 [ відповісти ]
Доброго дня! Щодо виклику збережених процедур з безліччю параметрів.
Справа в тому, що в статті наведено приклад класу, який дозволяє здійснювати виклик збережених процедур з будь-якою кількістю параметрів. приклад:
$ Db-> loadUser ($ email, $ password);
Виконання даного коду призведе до виклику процедури, що "loadUser" c двома параметрами $ email і $ password
Додати коментар
Спонсори:
Хостинг:
Це виглядає звичним - тому здається логічним і зручним, але чи так це насправді?
Щоб стало зрозуміло, про що йде мова, почнемо з прикладу: <?
Наведу далі код класу DBaccess (для роботи класу потрібно PHP5 c mysqli розширенням, MySQL 5): <?
QuotedParams, $ param === null?