Podceňované Prepared Statements
Ve své praxi jsem se mnohokrát setkal s programátory, kteří o Prepared Statements (dále už jen PS) vůbec nevědí nebo v lepším případě je sice znají, ale jejich využití jim přináší pouze kus kódu navíc.
Přitom PS přináší výhody jak ve výkonu, tak i v ochraně proti SQL Injection. V následujících řádcích bych chtěl ukázat výhodu PS v praktické ukázce. K pochopení PS je nutné znát alespoň zběžně koncept, jakým databázový stroj funguje a jaké činnosti provádí před samotným provedením SQL dotazu.
Tento postup bude odlišný pro každou databázi, může se lišit i v jednotlivých verzích. Moderní databázové stroje využívají sdílené paměti (shared pool), která je dostupná všem databázovým sessions. V této paměti se mimo jiné ukládají i připravené execution plány. Když do databáze odešleme SQL dotaz, provede jeho syntaktickou a sémantickou kontrolu, ověří práva pro aktuální session a požadovanou operaci. V neposlední řadě prohledá sdílenou paměť, zda už pro zaslaný SQL dotaz neexistuje execution plan. Pokud plán nenalezne, provádí se tzv. hard parse, což vyvolá akci optimizeru, který pro daný SQL dotaz najde nejvýhodnější execution plan a ten uloží do sdílené paměti. Při následném volání stejného SQL dotazu už databáze vytvořený execution plan najde a použije ho.
Hard parse je pro databázi relativně časově náročná činnost, proto bychom se měli snažit eliminovat počet těchto operací. Execution plány také samozřejmě ubírají kus ze sdílené paměti a logicky, čím více plánů, tím méně paměti pro ostatní operace. Pokud je sdílená paměť vyčerpána, dojde k jejímu částečnému uvolnění odstraněním nejméně používaných execution plánů. Takže může nastat situace, kdy za běhu dojde k opětovnému hard parse dříve použitého dotazu. Jaký vliv na tuto činnost mají PS?
Vytvoříme a pustíme jednoduchý SQL dotaz, například:
SELECT * FROM employees WHERE employee_id = 1;
Pro tento select bude vytvořen execution plan a uložen do paměti. Při opětovném zavolání už nedojde k hard parse. Pokud následně pustíme dotaz:
SELECT * FROM employees WHERE employee_id = 2;
Databáze pro tento dotaz execution plan nenajde a opět spouští hard parse. Pokud budeme mít v databázi deset tisíc zaměstnanců, na které se postupně zeptáme, budeme mít v paměti uložených rovných deset tisíc execution plánů i když je dotaz v podstatě stejný a pro každého zaměstnance bude použit stejný postup, jak data databáze získá. Z toho vyplývá, že na jeden, logicky stejný dotaz, alokujeme zbytečně 10 tisíc krát více paměti než je nutné.
Proč tomu tak je? Databáze při hledání execution plánu v paměti nehledá jeho význam, ale z důvodu rychlosti hledá pouze HASH položeného SQL dotazu. Proto jakákoli změna, byť jen v bezvýznamné mezeře způsobí že hash SQL dotazu se změní a execution plan pro něj nenajde a pustí se hard parse. V tuto chvíli nastupují na řadu PS a bind proměnných. SQL dotaz bude vypadat, v závislosti na platformě, nějak takto:
SELECT * FROM employees WHERE employee_id = :eid;
Do proměnné eid, pak vložíme hledané ID zaměstnance. I když do databáze pustíme tento dotaz pro všech deset tisíc zaměstnanců, pro parser to bude stále jeden a tentýž SQL dotaz. Výsledek oproti přímému zápisu proměnných do SQL dotazu je viditelný. Ve sdílené paměti máme pouze jeden execution plan a při 9999 voláních dotazu se neprováděl hard parse, ale pouze soft parse (pro jednoduchost: provede pouze doplnění proměnných). Abych své tvrzení potvrdil, předvedu praktickou ukázku. Ukázka je psaná v Oracle 10g a využívá testovacího schématu HR (human resources).
Nejdříve bude nutné vypráznit shared pool:
ALTER SYSTEM FLUSH SHARED_POOL;
Následně napíšeme:
SELECT /* testik */ * FROM employees WHERE employee_id =201;
SELECT /* testik */ * FROM employees WHERE employee_id =202;
SELECT /* testik */ * FROM employees WHERE employee_id =203;
SELECT /* testik */ * FROM employees WHERE employee_id = 203;
Komentář v SQL dotazu nám poslouží pro snažší nalezení execution plánů, viz dále. Nyní vytvoříme proměnnou eid typu number a postupně spustíme SQL dotazy se stejnými ID
VARIABLE eid NUMBER;
EXEC :eid := 201;
SELECT /* testik */ * FROM employees WHERE employee_id = :eid;
EXEC :eid := 202;
SELECT /* testik */ * FROM employees WHERE employee_id = :eid;
EXEC :eid := 203;
SELECT /* testik */ * FROM employees WHERE employee_id = :eid;
Nyní provedeme select z pohledu v$sqlarea, který nám ukáže zhruba následující:
SELECT sql_text, loads, parse_calls FROM v$sqlarea WHERE sql_text LIKE '%testik%';
SQL_TEXT LOADS PARSE_CALLS
-------------------------------------------------------------------------------- ---------- -----
SELECT /* testik */ * FROM employees WHERE employee_id =201 1 1
SELECT /* testik */ * FROM employees WHERE employee_id =202 1 1
SELECT /* testik */ * FROM employees WHERE employee_id =203 1 1
SELECT sql_text, loads, parse_calls FROM v$sqlarea WHERE sql_text LIKE '%testik%' 1 1
SELECT /* testik */ * FROM employees WHERE employee_id = 203 1 1
SELECT /* testik */ * FROM employees WHERE employee_id = :eid 1 3
6 rows selected
Z výsledku lze vyčíst, že pro volání SQL dotazů s přímým uvedením proměnné vznikl pro každý vlastní execution plan a byl použit vždy jednou. Dokonce byl vytvořen další execution plan pro volání dotazu ve kterém přibyla mezera. Pro volání dotazů s bind proměnnou existuje pouze jediný execution plan, který byl použit třikrát. Je tedy jednoznačné, že použití PS pomůže celkovému výkonu databáze.
Na začátku jsem psal, že PS nejenom pozitivně ovlivní výkon, ale mají svůj význam i na poli bezpečnosti, kdy zamezí možnosti SQL Inection. Jelikož v databázi v drtivé většině případů potřebujeme vyhledávat dle uživatelsky definovaných kritérií, musí programátor obslužné aplikace myslet na možnost nestandardních vstupů, kdy se potencionální útočník snaží, pomocí vhodně zaslaných vstupních údajů, prolomit aplikaci a dostat se k údajům, které si nepřejeme publikovat. Příklad takového neošetřeného SQL dotazu v PHP může vypadat následovně:
$query = "UPDATE users SET password = '" . $_POST['password'] . "' WHERE id = 1";
Pokud v proměnné password dorazí text podobný tomuto: ‚– bude ve skutečnosti zavolán dotaz:
UPDATE users SET password = ''--' WHERE id = 1
Což znamená, že všem uživatelům bude nastaveno prázdné heslo. V případě kritické aplikace je to fatální bezpečnostní incident, jehož následek si raději nechci představit. Proto je nutná striktní kontrola vstupních dat, které jsou použity při volání SQL dotazů jako jejich parametry. Tato činnost je mnohými programátory neprávem opomíjena, a pro ty, kteří kontrolu vstupních údajů provádí, to znamená spoustu práce navíc.
Při bindování proměnných tak předáváme databázi dotaz oproštěný jakéhokoli uživatelského vstupu, ten pak posíláme dodatečně před samotným provedením SQL dotazu a databáze jej použije jako vstupní kritéria existujícího execution planu. Tento postup, i když to na první pohled takto nevypadalo, může značně usnadnit práci programátora, který se nemusí zabývat složitým escapováním potenciálně nebezpečných znaků.