Czasami deweloper musi przekazać w żądaniu zestaw parametrów lub nawet całą próbkę na wejściu. Czasami napotykane są bardzo dziwne rozwiązania tego problemu.
Podejdziemy "od odwrotnej" strony i zobaczymy, jak nie należy tego robić, dlaczego, i jak można to zrobić lepiej.
Bezpośrednie "wklejenie" wartości w ciało zapytania
Zwykle wygląda to mniej więcej tak:
query = "SELECT * FROM tbl WHERE id = " + value… lub tak:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)O tym sposobie powiedziano, napisano i dość dużo:
Prawie zawsze jest to — bezpośrednia droga do ataków SQL injection i dodatkowego obciążenia logiki biznesowej, która zmuszona jest do "klejenia" ciągu Twojego zapytania.
Takie podejście może być częściowo uzasadnione tylko w przypadku konieczności korzystania z partycjonowania w wersjach PostgreSQL 10 i niższych, aby uzyskać bardziej efektywny plan. W tych wersjach lista skanowanych sekcji jest określana jeszcze bez uwzględnienia przekazywanych parametrów, tylko na podstawie ciała zapytania.
$n-argumenty
Użycie parametrów — to dobrze, umożliwia to używanie , zmniejszając obciążenie zarówno logiki biznesowej (ciąg zapytania jest formułowany i przekazywany tylko raz), jak i serwera bazy danych (nie ma potrzeby ponownego analizowania i planowania dla każdego egzemplarza zapytania).
Zmienna liczba argumentów
Problemy mogą czekać, gdy zechcemy przekazać z góry nieznaną liczbę argumentów:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Jeśli pozostawimy zapytanie w takiej formie, to choć ochrania nas to przed potencjalnymi atakami, nadal prowadzi to do konieczności wklejenia/analizy zapytania dla każdej możliwości liczby argumentów. Już lepiej, niż robić to za każdym razem, ale da się to obejść.
Wystarczy przekazać tylko jeden parametr, który zawiera serializowaną reprezentację tablicy:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'Jedyna różnica — konieczność jawnego przekształcania argumentu do wymaganego typu tablicy. Ale nie sprawia to problemu, ponieważ i tak z góry wiemy, gdzie się kierujemy.
Przekazywanie próbki (macierzy)
Zwykle są to różne opcje przekazywania zbiorów danych do wstawienia do bazy "w jednym zapytaniu":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Oprócz opisywanych powyżej problemów z "przeklejaniem" zapytania, może to nas również doprowadzić do out of memory i spadku serwera. Przyczyna jest prosta — przy argumentach PG zasoby rezerwują dodatkową pamięć, a liczba rekordów w zestawie jest ograniczona tylko przez aplikacyjne wymagania logiki biznesowej. W szczególnie skrajnych przypadkach można było zobaczyć „numerowe” argumenty powyżej 9000 zł — nie rób tak.
Przepiszmy zapytanie, stosując już „dwupoziomową” serializację:
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;
Tak, w przypadku „złożonych” wartości w tablicy, należy je otaczać cudzysłowami.
Jasne, że w ten sposób można „rozwinąć” wybór z dowolną liczbą pól.
unnest, unnest, …
Od czasu do czasu pojawiają się opcje przekazywania zamiast „tablicy tablic” kilku „tablic kolumn”, o których wspomniałem :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Przy tym sposobie, pomylenie się przy generowaniu list wartości dla różnych kolumn może w prosty sposób prowadzić do całkowicie nieoczekiwanych wyników, zależnych także od wersji serwera:
-- $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 |YANG
Począwszy od wersji 9.3 PostgreSQL wprowadził pełnoprawne funkcje do pracy z typem json. Dlatego, jeśli definiowanie parametrów wejściowych odbywa się w przeglądarce, możesz tam również formować obiekt json dla zapytania SQL:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'Dla wcześniejszych wersji taki sam sposób można używać dla each(hstore), ale prawidłowe „zwirowanie” z eskalowaniem złożonych obiektów w hstore może powodować problemy.
json_populate_recordset
Jeśli z góry wiesz, że dane z „wejściowej” tablicy json będą wykorzystywane do uzupełnienia jakiejś tabeli, możesz znacznie zaoszczędzić na „dereferencjacji” pól i przekształceniu ich do odpowiednich typów, korzystając z funkcji 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 ta funkcja po prostu „rozwinie” przekazaną tablicę obiektów w wybór, nie opierając się na formacie tabeli:
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 | 2TABELA TYMCZASOWA
Ale jeśli objętość danych w przesyłanej selekcji jest zbyt duża, to umieszczenie jej w jednym zserializowanym parametrze — staje się trudne, a czasami nawet niemożliwe, ponieważ wymaga jednorazowego przydzielenia dużej ilości pamięciNa przykład, potrzebujesz długo zbierać dużą paczkę danych dotyczących zdarzeń z zewnętrznego systemu, a potem chcesz raz na jakiś czas przetworzyć ją po stronie bazy danych.
W tym przypadku najlepszym rozwiązaniem będzie użycie :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- powtórz wiele razy
...
-- tutaj robimy coś użytecznego z tą tabelą jako całością
Metoda jest dobra właśnie do rzadkiego przesyłu dużych ilości danych.
Z punktu widzenia opisu struktury swoich danych tymczasowa tabela różni się od „zwykłej” tylko jednym kryterium w tabeli systemowej pg_class, a w pg_type, pg_depend, pg_attribute, pg_attrdef, … — nie różni się niczym.
Dlatego w systemach webowych z dużą liczbą krótkotrwałych połączeń dla każdego z nich taka tabela będzie generować nowe wpisy systemowe za każdym razem, które usuwane są przy zamykaniu połączenia z bazą danych. W rezultacie, niekontrolowane użycie TEMP TABLE prowadzi do „nabrzmiewania” tabel w pg_catalog i spowolnienia wielu operacji, które je wykorzystują.
Oczywiście, można z tym walczyć za pomocą okresowego przejścia VACUUM FULL po tabelach katalogu systemowego.
Zmienne sesji
Załóżmy, że przetwarzanie danych z poprzedniego przypadku jest wystarczająco skomplikowane dla jednego zapytania SQL, ale chcemy je wykonywać dość często. To znaczy chcemy używać przetwarzania proceduralnego w , ale używanie przesyłania danych przez tymczasowe tabele będzie zbyt kosztowne.
Używanie parametrów $n do przesyłania do bloku anonimowego również nie będzie możliwe. Wyjściem z sytuacji pomogą nam zmienne sesji oraz funkcja current_setting.
Do wersji 9.2 należało wcześniej skonfigurować custom_variable_classes dla „własnych” zmiennych sesji. W aktualnych wersjach można pisać mniej więcej tak:
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 : 3W innych wspieranych językach proceduralnych można znaleźć i inne rozwiązania.
Znasz jeszcze inne sposoby? Podziel się w komentarzach!
Źródło: habr.com
