ProHoster > blogg > administration > PostgreSQL-antimönster: Skickar uppsättningar och markeringar till SQL

PostgreSQL-antimönster: Skickar uppsättningar och markeringar till SQL

Då och då behöver utvecklaren skicka en uppsättning parametrar eller till och med ett helt urval till begäran "vid ingången". Ibland finns det väldigt konstiga lösningar på detta problem.
PostgreSQL-antimönster: Skickar uppsättningar och markeringar till SQL
Låt oss gå "från motsatsen" och se hur man inte gör det, varför och hur du kan göra det bättre.

Direkt "infogning" av värden i förfrågningskroppen

Det brukar se ut ungefär så här:

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

... eller så här:

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

Om denna metod sägs, skrivs och till och med ritad tillräckligt:

PostgreSQL-antimönster: Skickar uppsättningar och markeringar till SQL

Det är det nästan alltid direkt väg till SQL-injektion och en extra belastning på affärslogiken, som tvingas "limma" din frågesträng.

Detta tillvägagångssätt kan endast delvis motiveras om det behövs. använda partitionering i PostgreSQL version 10 och nedan för en mer effektiv plan. I dessa versioner bestäms listan över skannade sektioner utan att ta hänsyn till de överförda parametrarna, endast på basis av förfrågningsorganet.

$n argument

Använd platshållare parametrar är bra, det låter dig använda FÖRBEREDDA UTTALANDEN, vilket minskar belastningen både på affärslogiken (frågesträngen bildas och överförs endast en gång) och på databasservern (omanalys och planering krävs inte för varje instans av begäran).

Variabelt antal argument

Problem kommer att vänta oss när vi vill skicka ett okänt antal argument i förväg:

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

Om du lämnar begäran i det här formuläret, kommer det fortfarande att leda till behovet av att limma / analysera begäran, även om det kommer att rädda oss från potentiella injektioner för varje alternativ från antalet argument. Redan bättre än att göra det varje gång, men du kan klara dig utan det.

Det räcker att bara skicka en parameter som innehåller serialiserad representation av en array:

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

Den enda skillnaden är behovet av att explicit konvertera argumentet till den önskade arraytypen. Men detta skapar inga problem, eftersom vi redan i förväg vet var vi vänder oss.

Provöverföring (matris)

Vanligtvis är dessa alla möjliga alternativ för att överföra datamängder för infogning i databasen "i en begäran":

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

Utöver de ovan beskrivna problemen med "omlimningen" av förfrågan kan detta också leda oss till slut på minne och serverkrasch. Anledningen är enkel - PG reserverar ytterligare minne för argumenten, och antalet poster i uppsättningen begränsas endast av affärslogikapplikationens önskelista. I särskilt kliniska fall var det nödvändigt att se "numrerade" argument större än $9000 - gör inte på det här sättet.

Låt oss skriva om frågan och ansöka redan "tvånivås" 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 fallet med "komplexa" värden inuti en array måste de ramas in med citattecken.
Det är klart att man på så sätt kan "expandera" urvalet med ett godtyckligt antal fält.

olust, olust, …

Från tid till annan finns det alternativ för att skicka i stället för en "matris av matriser" flera "matriser av kolumner" som jag nämnde i den sista artikeln:

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

Med den här metoden, om du gör ett misstag när du genererar värdelistor för olika kolumner, är det mycket lätt att få helt oväntade resultat, som också beror på 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

Från och med version 9.3 har PostgreSQL fullfjädrade funktioner för att arbeta med json-typen. Därför, om dina inmatningsparametrar är definierade i webbläsaren, kan du direkt där och bilda json-objekt för SQL-fråga:

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

För tidigare versioner kan samma metod användas för varje (hstore), men korrekt "vikning" med flyktande komplexa objekt i hstore kan orsaka problem.

json_populate_recordset

Om du i förväg vet att data från "input" json-arrayen kommer att fylla i någon tabell, kan du spara mycket i "dereferencing"-fält och casta till önskade typer med hjälp av 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

Och den här funktionen kommer helt enkelt att "expandera" den passerade arrayen av objekt till ett urval, utan att förlita sig på tabellformatet:

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

TILLFÄLLIGT BORD

Men om mängden data i det överförda provet är mycket stort, är det svårt, och ibland omöjligt, att kasta in det i en serialiserad parameter, eftersom det kräver en engångs stor minnesallokering. Till exempel behöver du samla in en stor batch händelsedata från ett externt system under lång, lång tid, och sedan vill du bearbeta det en gång på databassidan.

I det här fallet skulle den bästa lösningen vara att använda tillfälliga bord:

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

Metoden är bra för sällsynt överföring av stora volymer data.
När det gäller att beskriva strukturen för dess data, skiljer sig en temporär tabell från en "vanlig" tabell i endast en funktion. i systemtabellen pg_class, och i pg_type, pg_depend, pg_attribute, pg_attrdef, ... – och ingenting alls.

Därför, i webbsystem med ett stort antal kortlivade anslutningar för var och en av dem, kommer en sådan tabell att generera nya systemposter varje gång, som raderas när anslutningen till databasen stängs. Så småningom, okontrollerad användning av TEMP TABLE leder till "svällning" av tabeller i pg_catalog och saktar ner många operationer som använder dem.
Detta går naturligtvis att bekämpa periodiskt godkänt VAKUUM FULL enligt systemkatalogtabellerna.

Sessionsvariabler

Anta att behandlingen av data från föregående fall är ganska komplex för en enda SQL-fråga, men du vill göra det ganska ofta. Det vill säga vi vill använda procedurbehandling i DO blockera, men att använda dataöverföring genom tillfälliga tabeller blir för dyrt.

Vi kan inte heller använda $n-parametrar för att skicka till ett anonymt block. Sessionsvariablerna och funktionen hjälper oss att ta oss ur situationen. nuvarande inställning.

Före version 9.2 var du tvungen att förkonfigurera speciellt namnutrymme anpassade_variabelklasser för "deras" sessionsvariabler. På nuvarande versioner kan du skriva något så här:

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

Det finns andra lösningar tillgängliga på andra procedurspråk som stöds.

Vet du fler sätt? Dela i kommentarerna!

Källa: will.com

Lägg en kommentar