Většina aplikací používá SQL databáze tak, že pokládají serveru opakovaně téměř stejné SQL dotazy a mění v těchto dotazech jen několik málo údajů. Na tuto skutečnost reaguje řada nadstaveb pro práci se SQL, které se více či méně úspěšně snaží vkládat na definovaná místa v dotazech proměnná data, a dělat tak otrockou práci s tvorbou řetězce SQL dotazu za programátora. V případě nepoužívání těchto nadstaveb pak většina tvůrců aplikací sáhne k jednoduché variantě vytvoření finálního SQL dotazu jako řetězce pomocí nějaké funkce podobné printf(), například v jazyce C:
snprintf(q, qsiz, "SELECT * FROM tab WHERE name='%s'", name); nebo třeba v PHP:
$q = "SELECT * FROM tab WHERE name='". $name ."'" Ať je vámi používané rozhraní sebevíce chytře udělané, na konci vždy dojde k vytvoření řetězce se SQL dotazem a k jeho odeslání na server. A to se všemi problémy s tím spojenými. Jedná se hlavně o bezpečnostní rizika a pak neefektivní neustálé parsování stejných dotazů serverem.
Bezpečnostní problémy spojené s tvorbou SQL dotazů jsou pravděpodobně většině tvůrcům databázových aplikací zřejmé, a to hlavně u aplikací, kde mezi klávesnicí „zlých hochů“ a SQL dotazem, kam jsou jejich data vkládána, je jen několik málo řádek kódu. Typicky například u Web aplikací. Data od uživatelů je v těchto případech nutné kontrolovat a některé kontrolní znaky jako jednoduché uvozovky nebo středník zbavovat jejich specifického významu.
Další krokem je parsování takto vytvořeného dotazu serverem. Ač by se tato činnost mohla zdát poměrně snadná a rychlá, v případě PostgreSQL tomu tak zcela není. Důvodem jsou dynamické datové typy, funkce, operátory apod. To vše je nutné v SQL serveru připravit a ověřit pro každý dotaz, což pochopitelně stojí nějaký čas.
Od verze 7.3 je možné si dotazy před-připravit a následně serveru posílat samostatná data, která si server sám dosadí do dotazu. Od verze 7.4 je pak pro tento postup podpora přímo na úrovni klient-server protokolu. Dosazení dat do dotazu obstarává server v tzv. executoru, který zpracovává naparsovaný a již optimalizovaný dotaz.
Používání parametrů u SQL dotazů není interně v PostgreSQL nic nového. Například pro funkce a triggery psané v C již dlouho šlo používat parametry dotazů v SPI (Server Programming Interface). Parametr je definován pomocí znaku dolaru. Například:
SELECT * FROM tab WHERE name=$1 u dalších parametrů se pouze zvýší číslo za dolarem. Jeden parametr může být použit v dotazu vícekrát a v libovolném pořadí. Číslo určuje, kde (na jaké pozici) hledat data pro parametr v poli, které se předává serveru při finálním zpracování dotazu.
Naprosto stejně je tomu i v rozhraní EXECUTE/PREPARE. PREPARE i EXECUTE jsou standardní SQL příkazy. Například příkaz:
PREPARE myquery (text) AS SELECT * FROM tab WHERE name=$1; připraví na straně serveru vše potřebné pro vykonání dotazu definovaného za slůvkem „AS“ a uloží tzv. „query plan“, podle kterého může být dotaz zpracováván do paměti serveru pod názvem „myquery“. Počet takto uložených dotazů je neomezený a neměl by vzhledem k používání hash pole mít vliv na čas nutný pro nalezení toho správného dotazu podle jeho jména. Důležitou součástí příkazu PREPARE je seznam datových typů jednotlivých parametrů, které jsou v dotazu použity. Tento seznam se definuje v závorkách za pojmenováním dotazu. Tato nutnost je logická, protože umožňuje serveru připravit si případné funkce pro přetypování nebo nalezení nejlepších možných indexů pro dotaz s ohledem na použité datové typy parametrů.
Pochopitelně nejen SELECT, ale i jiné DML (Data Manipulation Language) příkazy lze v PREPARE použít. To znamená i UPDATE, DELETE a INSERT.
Životnost dotazu v paměti je dána délkou trvání vaší session se SQL serverem. Jinak řečeno po odhlášení a ukončení spojení jsou z paměti serveru odstraněny i všechny uložené dotazy. Pokud chcete dotaz odstranit dříve, můžete tak učinit pomocí příkazu:
DEALLOCATE myquery; Dotaz již máme připraven. Nic by nám nemělo bránit začít ho používat. Například dotaz:
EXECUTE myquery ('Bill'); vykoná uložený SELECT s tím, že na místo $1 vloží řetězecBill. Z pohledu aplikace je vše, co vrátí server, i práce s daty odpovědi naprosto shodné jako při použití SELECTu. Podobně je tomu i při použití jiných DML dotazů.
Různá EXECUTE pro různé uložené dotazy lze volat libovolněkrát v libovolném pořadí.
U složitých a rozsáhlých dotazů, kde převažuje práce s výrazy obsaženými v dotazu nad prací s daty, je zrychlení hodně zřejmé. Pochopitelně záleží na vašem individuálním využití. Výhodou v případě uložených dotazů je používání déletrvajících (persistentních) spojení. Pokud vaše aplikace naváže spojení se serverem, položí dotaz a odpojí se, je používání PREPARE/EXECUTE je hloupostí. I když neustálé opakované navazování spojení za tím samým účelem je do jisté míry i dost velkou hloupostí aplikace samotné.
Z bezpečnostního hlediska u parametrů předávaných samostatně mimo dotaz nemůže dojít k tomu, že by data parametru server interpretoval jinak než jako pouhá data. Takže tato data mohou obsahovat cokoliv včetně kontrolních znaků, jako jsou uvozovky a středníky.
Pochopitelně tato bezpečnostní výhoda poněkud kulhá na obě nohy v případe, že předáváme parametry dotazu serveru standardním řetězcem v příkazu EXECUTE, kde stejně jako u jiných příkazů musíme ošetřit, aby nám někdo nepropašoval do našeho dotazu nějaký vlastní dotaz.
Proto od verze 7.4 PostgreSQL podporuje předávání parametrů dotazům na úrovni protokolu mezi klientem a serverem. To znamená, že pokud použijeme k tomu určené rozhraní, data nepůjdou vůbec do parseru PostgreSQL a není možné, aby došlo k jejich nežádoucí interpretaci. Pro tvůrce aplikací to znamená, že se již nemusejí starat o obsah dat od uživatelů a bez obav je mohou použít v SQL dotazech. Rozhraním je v tomto případě standardní klientská knihovna libpq. Netuším, jak jsou na tom v současné době nástroje a interpretované jazyky s používáním těchto nových vlastností libpq, ale lze předpokládat, že dříve nebo později k jejich podpoře dojde. Alespoň u nástrojů používaných pro web aplikace by to bylo velmi vhodné. Následující řádky proto čistě informačně budou o jazyce C a knihovně libpq.
Změny v protokolu PostgreSQL se netýkají jen předávání parametrů pro pomocí PREPARE před-připravené dotazy, ale obecně všech dotazů. Lze přímo i pro jednotlivé dotazy oddělit dotaz a jeho data. Standardně je pro položení dotazu serveru používána funkce PQexec():
PQexec(conn, "SELECT * FROM tab WHERE name='Bill'"); Pokud chceme předat parametry samostatně a tím i bezpečněji:
data[0]='Bill';
PQexecParams(conn, "SELECT * FROM tab WHERE name=$1", 1, NULL, data, NULL, NULL, 0); kde „data“ je pole s parametry. Protože nejsem velkým příznivcem opisovaní manuálů do článků a knih, tak to, co znamenají ostatní parametry PQexecParams(), přenechám samostudiu případných zájemců.
V případě, že máte dotaz již před-připravený pomocí PREPARE, tak naprosto obdobně:
data[0]='Bill';
PQexecPrepared(conn, "myquery", 1, data, NULL, NULL, 0); Pro úplnost, přípravu pomocí PREPARE lze provést klasickou cestou:
PQexec(conn, "PREPARE myquery (text) AS SELECT * FROM tab WHERE name=$1"); Celý kód by pak v trošku naivním a jednoduchém provedení mohl vypadat takto:
PGresult *
ask(char *name)
{
char *params[1];
static int prepared = 0;
if (!prepared)
{
PQexec(conn, "PREPARE myquery (text) AS SELECT * FROM tab WHERE name=$1");
prepared = 1;
}
params[0] = name;
return PQexecPrepared(conn, "myquery", 1, (const char *const *) params, NULL, NULL, 0);
} V aplikacích s větším množstvím dotazů bude asi vhodné udržovat si nějaký jednoduchý popis dotazů s informací o tom, byl-li dotaz již připraven pomocí PREPARE a je-li možné rovnou použít PQexecPrepared(). To je ale vše otázkou fantazie programátorů, kteří dotyčné rozhraní chtějí používat. Věřím, že jejich fantazie bude větší než používání nějaké static proměnné jako ve výše uvedeném příkladě.
