ProHoster > Blog > podávání > PostgreSQL Antipatterns: Předávání sad a výběrů do SQL

PostgreSQL Antipatterns: Předávání sad a výběrů do SQL

Čas od času potřebuje vývojář předat požadavku sadu parametrů nebo dokonce celý výběr "u vchodu". Někdy existují velmi zvláštní řešení tohoto problému.
PostgreSQL Antipatterns: Předávání sad a výběrů do SQL
Pojďme „z opaku“ a podívejme se, jak to nedělat, proč a jak to můžete dělat lépe.

Přímé „vložení“ hodnot do těla požadavku

Obvykle to vypadá nějak takto:

query = "SELECT * FROM tbl WHERE id = " + value

... nebo takhle:

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

O této metodě se říká, píše a dokonce nakreslený dost:

PostgreSQL Antipatterns: Předávání sad a výběrů do SQL

Téměř vždy je přímá cesta k SQL injection a další zátěž na obchodní logiku, která je nucena „slepit“ váš řetězec dotazu.

Tento přístup lze částečně ospravedlnit pouze v případě potřeby. použít rozdělení v PostgreSQL verze 10 a nižší pro efektivnější plán. V těchto verzích je seznam naskenovaných úseků určen bez zohlednění přenášených parametrů, pouze na základě těla požadavku.

$n argumentů

Použití zástupné symboly parametry jsou dobré, umožňuje používat PŘIPRAVENÁ PROHLÁŠENÍ, což snižuje zátěž jak na obchodní logiku (řetězec dotazu se vytvoří a přenese pouze jednou), tak na databázový server (pro každou instanci požadavku není vyžadována opakovaná analýza a plánování).

Variabilní počet argumentů

Problémy na nás budou čekat, když budeme chtít předem předat neznámý počet argumentů:

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

Pokud necháte žádost v tomto formuláři, i když nás to ušetří potenciálních injekcí, stále to povede k nutnosti lepit / analyzovat žádost pro každou možnost z počtu argumentů. Už je to lepší, než to dělat pokaždé, ale obejdete se bez toho.

Stačí předat pouze jeden parametr obsahující serializovaná reprezentace pole:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

Jediným rozdílem je nutnost explicitně převést argument na požadovaný typ pole. To ale nezpůsobuje problémy, protože už předem víme, kam se obracíme.

Přenos vzorku (matice)

Obvykle se jedná o nejrůznější možnosti přenosu datových sad pro vložení do databáze „v jednom požadavku“:

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

Kromě výše popsaných problémů s „přelepováním“ požadavku nás může vést i k tomu nedostatek paměti a pád serveru. Důvod je prostý – PG si pro argumenty vyhrazuje další paměť a počet záznamů v sadě je omezen pouze aplikací Wishlist obchodní logiky. Ve zvláště klinických případech bylo nutné vidět "číslované" argumenty větší než 9000 $ - nedělejte to tímto způsobem.

Přepišme dotaz, již platí "dvouúrovňová" serializace:

INSERT INTO tbl
SELECT
  unnest[1]::text k
, unnest[2]::integer v
FROM (
  SELECT
    unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;

Ano, v případě „složitých“ hodnot uvnitř pole je třeba je zarámovat do uvozovek.
Je jasné, že tímto způsobem můžete výběr „rozšířit“ o libovolný počet polí.

nestydat, nestydat,…

Čas od času existují možnosti, jak předat místo „pole polí“ několik „polí sloupců“, které jsem zmínil v posledním článku:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

S touto metodou, pokud uděláte chybu při generování seznamů hodnot pro různé sloupce, je velmi snadné získat úplně neočekávané výsledky, které také závisí na verzi serveru:

-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |

JSON

Počínaje verzí 9.3 zavedl PostgreSQL plnohodnotné funkce pro práci s typem json. Proto, pokud jsou vaše vstupní parametry definovány v prohlížeči, můžete přímo tam a formulář json objekt pro SQL dotaz:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

Pro předchozí verze lze použít stejnou metodu každý(hstore), ale správné "skládání" s escapováním složitých objektů v hstore může způsobit problémy.

json_populate_recordset

Pokud předem víte, že data ze „vstupního“ pole json půjdou vyplnit nějakou tabulku, můžete hodně ušetřit v polích „dereferencování“ a přetypování na požadované typy pomocí funkce json_populate_recordset:

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

A tato funkce jednoduše „rozšíří“ předané pole objektů do výběru, aniž by se spoléhala na formát tabulky:

SELECT
  *
FROM
  json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2

DOČASNÝ STŮL

Pokud je však množství dat v přenášeném vzorku velmi velké, pak je vložení do jednoho serializovaného parametru obtížné a někdy nemožné, protože to vyžaduje jednorázový velká alokace paměti. Například potřebujete dlouhou, dlouhou dobu shromažďovat velkou dávku dat událostí z externího systému a poté je chcete jednorázově zpracovat na straně databáze.

V tomto případě by bylo nejlepším řešením použít dočasné stoly:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

Metoda je dobrá pro občasný přenos velkých objemů data.
Z hlediska popisu struktury svých dat se dočasná tabulka od „běžné“ tabulky liší pouze jednou vlastností. v systémové tabulce pg_classA pg_type, pg_depend, pg_attribute, pg_attrdef, ... — a vůbec nic.

Proto ve webových systémech s velkým počtem krátkodobých spojení pro každý z nich bude taková tabulka generovat pokaždé nové systémové záznamy, které se při uzavření spojení s databází smažou. Nakonec, nekontrolované používání TEMP TABLE vede k "nabobtnání" tabulek v pg_catalog a zpomalení mnoha operací, které je používají.
S tím se dá samozřejmě bojovat periodický průchod VACUUM FULL podle tabulek systémového katalogu.

Proměnné relace

Předpokládejme, že zpracování dat z předchozího případu je pro jeden SQL dotaz poměrně složité, ale chcete to dělat poměrně často. To znamená, že chceme použít procesní zpracování v DO bloku, ale použití přenosu dat prostřednictvím dočasných tabulek bude příliš drahé.

Nemůžeme také použít $n-parameters k předání anonymnímu bloku. Proměnné relace a funkce nám pomohou dostat se ze situace. aktuální_nastavení.

Před verzí 9.2 jste museli předkonfigurovat speciální jmenný prostor custom_variable_classes pro „jejich“ proměnné relace. V aktuálních verzích můžete napsat něco takového:

SET my.val = '{1,2,3}';
DO $$
DECLARE
  id integer;
BEGIN
  FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP
    RAISE NOTICE 'id : %', id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
-- NOTICE:  id : 1
-- NOTICE:  id : 2
-- NOTICE:  id : 3

V jiných podporovaných procedurálních jazycích jsou k dispozici další řešení.

Znáte více způsobů? Podělte se v komentářích!

Zdroj: www.habr.com

Přidat komentář