PostgreSQL-antipatronen: sets en selecties doorgeven aan SQL

Van tijd tot tijd heeft de ontwikkelaar het nodig geef een reeks parameters of zelfs een volledige selectie door aan het verzoek "bij de ingang". Soms zijn er heel vreemde oplossingen voor dit probleem.

Laten we "van het tegenovergestelde" gaan en kijken hoe het niet moet, waarom en hoe u het beter kunt doen.

Directe "invoeging" van waarden in de aanvraagtekst

Het ziet er meestal ongeveer zo uit:

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

... of zo:

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

Over deze methode wordt gezegd, geschreven en geschreven zelfs getekend genoeg:

Dat is het bijna altijd direct pad naar SQL-injectie en een extra belasting van de bedrijfslogica, die wordt gedwongen om uw querystring te "lijmen".

Deze aanpak kan alleen gedeeltelijk worden gerechtvaardigd als dat nodig is. partitie gebruiken in PostgreSQL versies 10 en lager voor een efficiënter plan. In deze versies wordt de lijst met gescande secties bepaald zonder rekening te houden met de verzonden parameters, alleen op basis van de aanvraagtekst.

$n argumenten

Gebruiken tijdelijke aanduidingen parameters is goed, het stelt u in staat om te gebruiken OPGESTELDE VERKLARINGEN, waardoor de belasting van zowel de bedrijfslogica (de querystring wordt slechts één keer gevormd en verzonden) als van de databaseserver (opnieuw parseren en plannen is niet vereist voor elk exemplaar van het verzoek) wordt verminderd.

Variabel aantal argumenten

Er staan ​​ons problemen te wachten als we vooraf een onbekend aantal argumenten willen doorgeven:

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

Als u het verzoek in dit formulier achterlaat, zal het ons weliswaar behoeden voor mogelijke injecties, maar toch leiden tot de noodzaak om het verzoek te lijmen/ontleden voor elke optie uit het aantal argumenten. Al beter dan elke keer te doen, maar je kunt ook zonder.

Het is voldoende om slechts één parameter met geserialiseerde representatie van een array:

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

Het enige verschil is de noodzaak om het argument expliciet om te zetten naar het gewenste arraytype. Maar dit levert geen problemen op, aangezien we van tevoren al weten waar we aan toe zijn.

Monsteroverdracht (matrix)

Meestal zijn dit allerlei mogelijkheden om datasets “in één verzoek” over te brengen voor invoeging in de database:

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

Naast de hierboven beschreven problemen met het "opnieuw lijmen" van de aanvraag, kan dit ook bij ons leiden uit het geheugen en servercrash. De reden is eenvoudig: PG reserveert extra geheugen voor de argumenten en het aantal records in de set wordt alleen beperkt door de verlanglijst van de bedrijfslogica-applicatie. In bijzonder klinische gevallen was het noodzakelijk om te zien "genummerde" argumenten groter dan $9000 - doe het niet op deze manier.

Laten we de query herschrijven en al toepassen "twee-niveau" serialisatie:

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;

Ja, in het geval van "complexe" waarden binnen een array, moeten ze tussen aanhalingstekens worden geplaatst.
Het is duidelijk dat je op deze manier de selectie kunt "uitbreiden" met een willekeurig aantal velden.

ontnest, ontnest, …

Van tijd tot tijd zijn er opties om in plaats van een "array van arrays" verschillende "arrays van kolommen" door te geven die ik noemde in het laatste artikel:

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

Als u met deze methode een fout maakt bij het genereren van zoeklijsten voor verschillende kolommen, is het heel gemakkelijk om volledig te krijgen onverwachte resultaten, die ook afhangen van de serverversie:

-- $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

Vanaf versie 9.3 heeft PostgreSQL volwaardige functies geïntroduceerd voor het werken met het json-type. Daarom, als uw invoerparameters in de browser zijn gedefinieerd, kunt u daar en formulier json-object voor SQL-query:

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

Voor eerdere versies kan dezelfde methode worden gebruikt elke(hwinkel), maar correct "vouwen" met escape-complexe objecten in hstore kan problemen veroorzaken.

json_populate_recordset

Als u van tevoren weet dat de gegevens van de "input" json-array een tabel gaan invullen, kunt u veel besparen op "dereferencing" -velden en casten naar de gewenste typen met behulp van de functie 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

En deze functie zal de doorgegeven reeks objecten eenvoudig "uitbreiden" tot een selectie, zonder te vertrouwen op het tabelformaat:

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

TIJDELIJKE TAFEL

Maar als de hoeveelheid data in de verzonden sample erg groot is, dan is het moeilijk, en soms zelfs onmogelijk, om het in één geserialiseerde parameter te gooien, omdat het een eenmalige grote geheugentoewijzing. U moet bijvoorbeeld voor een lange, lange tijd een grote hoeveelheid gebeurtenisgegevens van een extern systeem verzamelen en deze vervolgens eenmalig verwerken aan de databasezijde.

In dit geval zou de beste oplossing zijn om te gebruiken tijdelijke tafels:

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

De methode is goed voor onregelmatige verzending van grote volumes gegevens.
Vanuit het oogpunt van het beschrijven van de structuur van zijn gegevens, verschilt een tijdelijke tabel slechts in één kenmerk van een "gewone" tabel. in pg_class systeemtabelen in pg_type, pg_depend, pg_attribute, pg_attrdef, ... - en helemaal niets.

Daarom zal een dergelijke tabel in websystemen met een groot aantal kortstondige verbindingen voor elk van hen elke keer nieuwe systeemrecords genereren, die worden verwijderd wanneer de verbinding met de database wordt verbroken. Eventueel, ongecontroleerd gebruik van TEMP TABLE leidt tot "zwelling" van tabellen in pg_catalog en het vertragen van veel bewerkingen die ze gebruiken.
Dit kan natuurlijk bestreden worden periodieke pas VACUÜM VOL volgens de systeemcatalogustabellen.

Sessievariabelen

Stel dat de verwerking van de gegevens uit de vorige casus vrij complex is voor een enkele SQL-query, maar u wilt dit vrij vaak doen. Dat wil zeggen, we willen procedurele verwerking gebruiken in DOEN blokkeren, maar het gebruik van gegevensoverdracht via tijdelijke tabellen zal te duur zijn.

We kunnen ook geen $n-parameters gebruiken om door te geven aan een anoniem blok. De sessievariabelen en de functie helpen ons om uit de situatie te komen. huidige instelling.

Vóór versie 9.2 moest u vooraf configureren speciale naamruimte aangepaste_variabele_klassen voor "hun" sessievariabelen. In de huidige versies kunt u zoiets als dit schrijven:

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

Er zijn andere oplossingen beschikbaar in andere ondersteunde proceduretalen.

Meer manieren weten? Deel in de comments!

Bron: www.habr.com