C, PHP, VB, .NET

Стандартно една SQL заявка преминава през два етапа – подготовка (което включва лексикален и синтактичен анализ) и изпълнение. Подготовката е нещо подобно на компилацията при езиците за програмиране – текстът въведен в заявката се подготвя до изпълним за средата код. След това този код се изпълнява и се връща резултат към софтуерния продукт. Подготвените заявки ни дават възможност да разделим тези две стъпки и да ги изпълняваме отделно една от друга. Това означава, че имаме възможност първо да „компилираме“ заявката с една команда, а да я изпълним впоследствие чрез втора.

Подготвените заявки могат да бъдат с и без параметри. Ето един пример за такава заявка без параметри:

mysql> USE world;
Database changed
mysql> PREPARE citycount FROM
    -> 'SELECT COUNT(*) FROM city';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

В горния пример ние подготвихме заявката ‘SELECT COUNT(*) FROM city’ за изпълнение. Впоследствие можем да я изпълним по следния начин:

mysql> EXECUTE citycount;
+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

„Параметризирани“ са тези подготвени заявки, които не са просто статично зададени, а на които им подаваме различни параметри, според които можем да имаме различен резултат. Например ако искаме не просто броя на всичките градове в системата, а градовете от определена държава, то можем да подготвим заявката по следния начин:

mysql> PREPARE citycount_by_country_code FROM
    -> 'SELECT COUNT(*) FROM city WHERE CountryCode = ?';
Query OK, 0 rows affected (0.30 sec)
Statement prepared

Обърнете внимание на въпросителния знак – той указва мястото, където ще бъде „вмъкнат“ параметъра впоследствие. Ето как правим това:

mysql> SET @ccode = 'BGR';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE citycount_by_country_code USING @ccode;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql> SET @ccode = 'GBR';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE citycount_by_country_code USING @ccode;
+----------+
| COUNT(*) |
+----------+
|       81 |
+----------+
1 row in set (0.00 sec)

Разбира се параметрите може да са повече от един, като единственото което трябва да съблюдаваме е техния ред:

mysql> PREPARE citycount_by_code_and_district FROM
    -> 'SELECT COUNT(*) FROM city
    '>  WHERE CountryCode = ? AND District = ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @ccode = 'BGR';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @district = "Plovdiv";
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE citycount_by_code_and_district
    -> USING @ccode, @district;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

След като спрем да ги използваме е нужно да „почистим“, т.е. да освободим паметта от подготвените/параметризираните заявки, които сме направили. Това се прави чрез командата DEALLOCATE:

mysql> mysql> DEALLOCATE PREPARE citycount;
Query OK, 0 rows affected (0.00 sec)

mysql> DEALLOCATE PREPARE citycount_by_code
Query OK, 0 rows affected (0.00 sec)

mysql> DEALLOCATE PREPARE citycount_by_code_and_district;
Query OK, 0 rows affected (0.00 sec)

Освен това е редно да споменем, че параметризирани заявки могат да се правят и върху INSERT/UPDATE/DELETE, както и върху множество други оператори, а не само върху SELECT.

Какво печелим от подготвените заявки без параметри? Идеята за тях се свежда до това да направим подготовката на заявката само веднъж, а след това да я изпълняваме множество пъти. Ако трябва да изпълняваме множество от едни и същи динамични заявки, то за всяка една от тях ще имаме процес на подготвяне и изпълнение, т.е. при N заявки ще имаме N подготовки, а иначе ще имаме само една. Все пак имайте и предвид това, че за нуждите на еднократно изпълнение на заявка подготвените заявки са по-бавни от динамичните.  Причината е, че се заделят допълнителни ресурси за поддържане на „дървото на изпълнение“ на компилираната заявка. Тоест ние бихме спечелили от подготвени заявки без параметри само тогава, когато изпълняваме заявките много пъти в една сесия.

В общия случай обаче от подготвените заявки без параметри не се печели почти нищо, защото както подготвените, така и обикновените динамични заявки ще се възползват от query_cache, а кешът  тъй или иначе е най-бързия възможен начин за връщане на резултат от заявките. Тоест от подготвените заявки без параметър печелим тогава, когато се налага да изпълняваме заявките много поредни пъти и НЕ се използва query_cache. Като частична полза може да се каже това, че при повторни изпълнения на заявката към сървъра се подава само низ „EXECUTE <име-на-заявка>“, а не целия текст на SQL заявката (което може да ви спести малко количество мрежови трафик за всяка заявка).

Що се отнася до параметризираните заявки – там вече предимството на подготвената заявка се проявава в повече практически ситуации. Предимствата основно са три:

• Ако трябва да изпълните една и съща заявка много пъти и с различни параметри, то параметризираните заявки ще имат значително по-голямо бързодействие спрямо динамичните;
• При многократно изпълнение се намалява мрежовия трафик (вече го споменахме в предишния параграф), макар и незначително;
• Предотвратява възможностите за атаки от тип „SQL Injection“, понеже логиката на заявките вече е предварително зададена и няма начин да бъде променена по време на изпълнение. Все пак внимавайте – това, че сме защитени от такива атаки не означава, че „сме си свършили работата“. Много по-добре е въобще да НЕ се изпълнява SQL заявка при въведени невалидни данни, отколкото да се изпълнява такава, което очевидно ще изхаби ресурси, а резултатът от нея (би трябвало да) е предварително известен (тоест безсмислено търсен). Валидирането на коректността на данните като правило е добре на първо място да е задача на приложението (при това извършено непосредствено след получаването им) и в никакъв случай НЕ трябва отговорността за това да се прехвърля напълно върху системата за запазването им.

Ако параметризирана заявка се изпълнява еднократно или множество пъти, но с едни и същи параметри, то предимството от първата точка в общия случай се изгубва по същия начин, както в параметризираните заявки без параметри – в тези случаи с предимство ще се стремим да се възползваме от кеша на заявките (query cache).

Сега остава да дискутираме и начините за практическа употреба на подготвени заявки в реално действащи приложения. Горният метод за изпълнение на подготвени заявки се нарича „SQL интерфейс“. С него софтуерните продукти работещи с MySQL база от данни подават заявките по същия начин, както го правят с динамичните заявки, а подготовката и изпълнението се правят изцяло от страната на СУБД.

Съществуват и други, значително по-добри начини за работа с подготвени заявки, например MySQL C API client library (за програми написани са езика за програмиране C), MySQL Connector/J (за приложения на Java) и MySQL Connector/Net (за ADO.Net). При традиционния протокол заявките се изпращат към MySQL като текстови низ, след което самата СУБД конвертира стойностите на параметрите до съответния тип данни. Споменатите интерфейси позволяват програмата да комуникира директно със СУБД като предава данните като бинарна поредица, като до голяма степен се запазват типовете на данните. По-важното в случаят е, че интерфейсите минимизират мрежовия трафик между програмата и СУБД и то този път значително – практически се предават само стойностите на параметрите, заедно с число-идентификатор на заявката, а не целия ѝ текст (дори в SQL интерфейса трябваше да се подава думата „EXECUTE“ и името на заявката като текстови низове, което е доста по-голямо количество информация спрямо едно обикновено число-идентификатор). Затова е препоръчително да се възползвате от средите за програмиране, които предлагат възможност за работа с такива интерфейси.

Например в PHP разширението mysqli.so (забележете, че в края на името има „i“, защото старото разширение mysql.so не се възползва от споменатия интерфейс) работи аналогично на C API client library (има почти пълно съответствие между двете). Ето примерен код за работа с параметризирани заявки в PHP:

// Свързваме се с базата от данни
$link = mysqli_connect('хост','име','парола','база от данни');
if(mysqli_connect_errno()){
	echo 'Проблем при връзката с базата от данни';
	exit;
}
// Заявката, която ще подготвим
$query = "SELECT COUNT(*) AS citiesN FROM city WHERE CountryCode = ?";
// Параметъра, който ще подадем
$ccode_param = "BGR";
// Инициализиране на инстанция за заявката
$statement = mysqli_stmt_init($link);
// Подготвяме заявката за изпълнение
if (mysqli_stmt_prepare($statement, $query)){
    // Подаваме параметъра към вече подготвената заявка
    // "s" означава "string", т.е. параметъра е текстови низ
    mysqli_stmt_bind_param($statement, "s", $ccode_param);
   // Изпълняваме заявката
   mysqli_stmt_execute($statement);
   // Ще записваме резултата от заявката в променлива $result
   mysqli_stmt_bind_result($statement, $result);
   // Взимаме резултата от изпълнението на заявката
   // Той ще бъде записан в $result
   mysqli_stmt_fetch($statement);
   // Отпечатваме резултата
   if(isset($result)) echo "Броят на градовете в $ccode_param e $result";
   else "Броят на градовете в $ccode_param e 0";
   // Затваряме интерфейса за параметризираната заявка
   mysqli_stmt_close($statement);
}
// Затваряме връзката с базата от данни
mysqli_close($link);

В горния пример разбира се ние изпълнихме заявката само веднъж, т.е. не се възползвахме от предимствата на подготвените заявки, а даже напротив – разходвахме (макар и минимално) допълнителни ресурси. Тук обаче е момента да отбележим нещо много важно: винаги затваряйте интерфейса за параметризираната заявка от момента, от който няма да я използвате повече  – от примера това става с извикването на функцията mysqli_stmt_close. Често срещана практика при програмистите е да забравят да затварят връзките си към базите от данни, което не е прекалено голям проблем, понеже стандартно те се затварят с края на изпълнението на PHP скрипта. При изпълнение на подготвени заявки обаче се поддържа отделна нишка с връзка към сървъра за всяка една параметризирана заявка, а ние казахме, че използваме подготвените заявки предимно тогава, когато ще ги изпълняваме многократно, т.е. очаква се скрипта да работи с големи обеми от информация и да отнема дълго време за изпълнение. Умножете броя на връзките за параметризирани заявки по броя на потребителите, които използват системата в един момент от време и ще видите, че ненужно поддържаните връзки към базата от данни могат да отнемат значително количество ресурси на СУБД, без да ги употребяват. В практиката често се среща претоварване на SQL сървър поради неправилно менажиране на подготвени заявки от страна на приложението, които работят с него.

Някои от интерфейсите, като например Connector/J и Connector/Net поддържат и подготовка на заявките от клиентската страна, т.е. ЧАСТ от „компилирането“ се извършва от приложението, а СУБД го приема като (полу)готов за изпълнение код. В PHP „подготовка на заявките от страна на клиента“ (client-side prepared statements) се поддържа от разширението PDO. На този етап това предлага доста по-бързо изпълнение на подготвени заявки изпълнени еднократно, но е значително по-бавно при многократно изпълнение на една и съща заявка спрямо „подготовката от страна на сървъра“ (server-side prepared statements, което разгледахме като практика по-горе).

Обобщение: Използвайте подготвени и параметризирани заявки тогава, когато се налага да изпълнявате една и съща заявка множество пъти. Когато е възможно винаги се възползвайте от интерфейсите за подаване на подготвени заявки, а не SQL интерфейса, но в същото време не забравяйте стриктно да затваряте неактивните връзки към базата данни. Подготвените заявки са препоръчително средство за защита от SQL Injections, но не ги разглеждайте като основно средство за сигурност – винаги допълнително си филтрирайте входните данни.