ProHoster > Blog > administration > PostgreSQL-antimønstre: Sender sæt og markeringer til SQL

PostgreSQL-antimønstre: Sender sæt og markeringer til SQL

Fra tid til anden har udvikleren brug for videregive et sæt parametre eller endda et helt udvalg til anmodningen "ved indgangen". Nogle gange er der meget mærkelige løsninger på dette problem.
PostgreSQL-antimønstre: Sender sæt og markeringer til SQL
Lad os gå "fra det modsatte" og se, hvordan du ikke gør det, hvorfor, og hvordan du kan gøre det bedre.

Direkte "indsættelse" af værdier i forespørgselslegemet

Det ser normalt sådan ud:

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

... eller sådan her:

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

Om denne metode siges, skrives og endda tegnet nok:

PostgreSQL-antimønstre: Sender sæt og markeringer til SQL

Det er det næsten altid direkte sti til SQL-injektion og en ekstra belastning af forretningslogikken, som er tvunget til at "lime" din forespørgselsstreng.

Denne tilgang kan kun delvist begrundes, hvis det er nødvendigt. bruge partitionering i PostgreSQL version 10 og derunder for en mere effektiv plan. I disse versioner bestemmes listen over scannede sektioner uden at tage hensyn til de transmitterede parametre, kun på grundlag af anmodningsorganet.

$n argumenter

Brug pladsholdere parametre er god, det giver dig mulighed for at bruge UDARBEJDEDE UDTALELSER, hvilket reducerer belastningen både på forretningslogikken (forespørgselsstrengen dannes og transmitteres kun én gang) og på databaseserveren (genparsing og planlægning er ikke påkrævet for hver forekomst af anmodningen).

Variabelt antal argumenter

Problemer vil afvente os, når vi ønsker at videregive et ukendt antal argumenter på forhånd:

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

Hvis du efterlader anmodningen i denne formular, så selvom det vil redde os fra potentielle injektioner, vil det stadig føre til behovet for at lime / analysere anmodningen for hver mulighed fra antallet af argumenter. Allerede bedre end at gøre det hver gang, men du kan undvære det.

Det er nok kun at videregive én parameter, der indeholder serialiseret repræsentation af et array:

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

Den eneste forskel er behovet for eksplicit at konvertere argumentet til den ønskede matrixtype. Men det giver ikke problemer, da vi allerede på forhånd ved, hvor vi henvender os.

Prøveoverførsel (matrix)

Normalt er disse alle mulige muligheder for at overføre datasæt til indsættelse i databasen "i én anmodning":

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

Ud over de ovenfor beskrevne problemer med "genlimningen" af forespørgslen, kan dette også føre os til ikke mere hukommelse og servernedbrud. Årsagen er enkel - PG reserverer yderligere hukommelse til argumenterne, og antallet af poster i sættet er kun begrænset af forretningslogikapplikationens ønskeliste. I især kliniske tilfælde var det nødvendigt at se "nummererede" argumenter større end $9000 - gør det ikke på denne måde.

Lad os omskrive forespørgslen og anvende allerede "to-niveau" serialisering:

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, i tilfælde af "komplekse" værdier inde i en matrix, skal de indrammes med anførselstegn.
Det er klart, at man på denne måde kan "udvide" udvalget med et vilkårligt antal felter.

uroligheder, uroligheder, …

Fra tid til anden er der muligheder for at passere i stedet for en "array af arrays" flere "arrays af kolonner", som jeg nævnte i sidste artikel:

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

Med denne metode, hvis du laver en fejl, når du genererer værdilister for forskellige kolonner, er det meget nemt at få helt uventede resultater, som også afhænger af serverversionen:

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

Fra version 9.3 har PostgreSQL fuldgyldige funktioner til at arbejde med json-typen. Derfor, hvis dine inputparametre er defineret i browseren, kan du lige der og danne json-objekt til SQL-forespørgsel:

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

For tidligere versioner kan samme metode bruges til hver(hstore), men korrekt "foldning" med undslippende komplekse objekter i hstore kan forårsage problemer.

json_populate_recordset

Hvis du på forhånd ved, at dataene fra "input" json-arrayet vil gå til at udfylde en eller anden tabel, kan du spare meget i "dereferencing"-felter og caste til de ønskede typer ved hjælp af json_populate_recordset-funktionen:

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

json_to_recordset

Og denne funktion vil simpelthen "udvide" den beståede række af objekter til en markering uden at stole på tabelformatet:

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

MIDLERTIDIG TABEL

Men hvis mængden af ​​data i den transmitterede prøve er meget stor, så er det svært og nogle gange umuligt at smide det ind i en serialiseret parameter, da det kræver en engangs stor hukommelsesallokering. For eksempel skal du indsamle et stort parti hændelsesdata fra et eksternt system i lang, lang tid, og så vil du behandle det en gang på databasesiden.

I dette tilfælde ville den bedste løsning være at bruge midlertidige borde:

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

Metoden er god til sjælden transmission af store mængder data.
Fra synspunktet om at beskrive strukturen af ​​dens data, adskiller en midlertidig tabel sig fra en "almindelig" tabel i kun én funktion. i pg_class systemtabel, og i pg_type, pg_depend, pg_attribute, pg_attrdef, ... - og slet ingenting.

I websystemer med et stort antal kortlivede forbindelser for hver af dem vil en sådan tabel derfor hver gang generere nye systemposter, som slettes, når forbindelsen til databasen lukkes. Til sidst, ukontrolleret brug af TEMP TABLE fører til "hævelse" af tabeller i pg_catalog og bremse mange operationer, der bruger dem.
Det kan man selvfølgelig bekæmpe periodisk bestået VAKUUM FULD i henhold til systemkatalogtabellerne.

Sessionsvariabler

Antag, at behandlingen af ​​dataene fra den foregående sag er ret kompleks for en enkelt SQL-forespørgsel, men du vil gøre det ret ofte. Det vil sige, at vi vil bruge proceduremæssig behandling i DO blokere, men det vil være for dyrt at bruge dataoverførsel gennem midlertidige tabeller.

Vi kan heller ikke bruge $n-parametre til at overføre til en anonym blok. Sessionsvariablerne og funktionen hjælper os med at komme ud af situationen. aktuelle_indstilling.

Før version 9.2 skulle du forudkonfigurere særligt navneområde tilpassede_variable_klasser for "deres" sessionsvariabler. På nuværende versioner kan du skrive noget som dette:

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

Der findes andre løsninger på andre understøttede proceduresprog.

Kender du flere måder? Del i kommentarerne!

Kilde: www.habr.com

Tilføj en kommentar