Связаться с нами

  • (097) ?601-88-87
    (067) ?493-44-27
    (096) ?830-00-01

Статьи

OpenNET: стаття - Доступ до даних на основі збережених процедур в веб-додатках (database sql function mysql php)

  1. Доступ до даних на основі збережених процедур в веб-додатках (database sql function mysql php)

Доступ до даних на основі збережених процедур в веб-додатках (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 [ відповісти ]
+ / - Судячи за статтею, автору незнайоме поняття ORM, не кажучи вже про конкретику - Propel, Doctrine, DBFinder.

  • 1.4 , matriks (??), 9:28, 22/03/2009 [ відповісти ]
+ / - По-моєму автор не ставив завданням "відкриття збережених процедур", а зробив порівняльну характеристику і виділив +/- використання збережених процедур. Стаття орієнтована на початківців або "другого рівня" програмістів, хоча думаю знайдуться і досвідчені кому буде що черпнуть з даної статті.

PS "ламати легше, ніж стороить", це аналогія до "ганьбити", кому не зрозуміло.

  • 1.5 , Alman (??), 5:50, 27/04/2009 [ відповісти ]
+ / - Ну. ніж хвалити або ганьбити цю статтю, доклали б пару нормальних рішень по даному напрямку. я ось теж початківець в цьому плані. хотілося б побачити більш складні приклади хранімок і виклику їх з PHP, з використанням декількох параметрів при виклику процедури.
  • 1.6 , Гришан Антон (?), 15:56, 04/05/2009 [ відповісти ]
+ / - Доброго дня!

Щодо виклику збережених процедур з безліччю параметрів.

Справа в тому, що в статті наведено приклад класу, який дозволяє здійснювати виклик збережених процедур з будь-якою кількістю параметрів. приклад:
$ Db-> loadUser ($ email, $ password);

Виконання даного коду призведе до виклику процедури, що "loadUser" c двома параметрами $ email і $ password


Додати коментар

Спонсори:

Хостинг:



Це виглядає звичним - тому здається логічним і зручним, але чи так це насправді?
Щоб стало зрозуміло, про що йде мова, почнемо з прикладу: <?
Наведу далі код класу DBaccess (для роботи класу потрібно PHP5 c mysqli розширенням, MySQL 5): <?
QuotedParams, $ param === null?

Новости

Все товары для праздника оптом купить
Как сделать правильный выбор в работе, бизнесе и жизни, о котором никогда не придется жалеть. Мы хотим рассказать вам об удивительной и очень простой технике 7 вопросов, которые позволят оценить ситуацию

Пиротехника своими руками в домашних
Самые лучшие полезные самоделки рунета! Как сделать самому, мастер-классы, фото, чертежи, инструкции, книги, видео. Главная САМОДЕЛКИ Дизайнерские

Фольгированные шары с гелием
Для начала давайте разберемся и чего же выполнен фольгированный шар и почему он летает дольше?! Как вы помните, наши латексные шарики достаточно пористые, поэтому их приходится обрабатывать специальным

Как сделать красивую снежинку из бумаги
Красивые бумажные снежинки станут хорошим украшением дома на Новый год. Они создадут в квартире атмосферу белоснежной, зимней сказки. Да и просто занимаясь вырезанием из бумаги снежинок разнообразной

Надувные шарики с гелием с доставкой
На праздники часто бывают востребованы воздушные шарики, надутые гелием. Обычно, их покупают уже готовыми (надутыми) и привозят на праздник. Или, приглашают специалистов, которые приезжают и надувают

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

Аниматоры на детские праздники в Зеленограде
Уж сколько раз твердили миру…Что готовиться ко дню рождения нужно заранее, а не бегать в предпраздничный день угорелой кошкой. Нельзя впихнуть в 24 часа дела, рассчитанные на недели. К празднику нужно

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