ProHoster > Blogi > antaminen > PostgreSQL-antipatterns: Joukkojen ja valintojen välittäminen SQL:lle

PostgreSQL-antipatterns: Joukkojen ja valintojen välittäminen SQL:lle

Ajoittain kehittäjä tarvitsee välittää pyyntöön joukon parametreja tai jopa koko valinnan "sisäänkäynnillä". Joskus tähän ongelmaan on hyvin outoja ratkaisuja.
PostgreSQL-antipatterns: Joukkojen ja valintojen välittäminen SQL:lle
Mennään "päinvastoin" ja katsotaan, miten sitä ei tehdä, miksi ja miten voit tehdä sen paremmin.

Suora arvojen "lisääminen" pyynnön runkoon

Se näyttää yleensä tältä:

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

... tai näin:

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

Tästä menetelmästä sanotaan, kirjoitetaan ja edes piirretty tarpeeksi:

PostgreSQL-antipatterns: Joukkojen ja valintojen välittäminen SQL:lle

Melkein aina on suora polku SQL-injektioon ja ylimääräinen kuormitus liiketoimintalogiikalle, joka on pakotettu "liimamaan" kyselymerkkijonosi.

Tämä lähestymistapa voi olla osittain perusteltu vain tarvittaessa. käytä osiointia PostgreSQL-versiossa 10 ja sitä vanhemmissa tehokkaamman suunnitelman saavuttamiseksi. Näissä versioissa skannattujen osien luettelo määritetään ottamatta huomioon lähetettyjä parametreja, vain pyynnön rungon perusteella.

$n argumenttia

Käyttää paikkamerkit parametrit ovat hyvät, sen avulla voit käyttää VALMISTETUT LAUSUNNOT, mikä vähentää sekä liiketoimintalogiikan (kyselymerkkijono muodostetaan ja lähetetään vain kerran) että tietokantapalvelimen kuormitusta (uudelleen jäsentämistä ja suunnittelua ei vaadita jokaiselle pyynnön esiintymälle).

Vaihteleva määrä argumentteja

Ongelmat odottavat meitä, kun haluamme välittää tuntemattoman määrän argumentteja etukäteen:

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

Jos jätät pyynnön tähän lomakkeeseen, vaikka tämä säästää meidät mahdollisilta pistoksilta, se johtaa silti tarpeeseen liimata / jäsentää pyyntö jokaiselle vaihtoehdolle argumenttien määrästä. Jo parempi kuin tehdä se joka kerta, mutta voit pärjätä ilmankin.

Riittää, kun välität vain yhden parametrin, joka sisältää taulukon sarjamuotoinen esitys:

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

Ainoa ero on tarve muuntaa argumentti haluttuun taulukkotyyppiin. Mutta tämä ei aiheuta ongelmia, koska tiedämme jo etukäteen, mihin olemme kohdistaneet osoituksen.

Näytteen siirto (matriisi)

Yleensä nämä ovat kaikenlaisia ​​​​vaihtoehtoja tietojoukkojen siirtämiseksi tietokantaan "yhdessä pyynnöstä":

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

Edellä kuvattujen pyynnön "uudelleenliimaukseen" liittyvien ongelmien lisäksi tämä voi myös johtaa meidät muisti loppu ja palvelin kaatui. Syy on yksinkertainen - PG varaa lisämuistia argumenteille, ja joukon tietueiden määrää rajoittaa vain liiketoimintalogiikkasovellus Wishlist. Erityisesti kliinisissä tapauksissa oli tarpeen nähdä "numeroidut" argumentit yli 9000 dollaria - älä tee sitä näin.

Kirjoitetaan kysely uudelleen, hakemus on jo tehty "kaksitasoinen" serialisointi:

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;

Kyllä, jos taulukon sisällä on "monimutkaisia" arvoja, ne on kehystettävä lainausmerkeillä.
On selvää, että tällä tavalla voit "laajentaa" valintaa mielivaltaisella määrällä kenttiä.

unnest, unnest,…

Ajoittain on vaihtoehtoja ohittaa "taulukoiden taulukon" sijaan useita mainitsemiani "saraketaulukoita" viimeisessä artikkelissa:

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

Tällä menetelmällä, jos teet virheen luodessasi arvoluetteloita eri sarakkeille, on erittäin helppo saada kokonaan odottamattomia tuloksia, jotka riippuvat myös palvelimen versiosta:

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

Versiosta 9.3 alkaen PostgreSQL:ssä on täysimittaiset toiminnot json-tyypin kanssa työskentelemiseen. Siksi, jos syöttöparametrisi on määritetty selaimessa, voit luoda lomakkeen suoraan siellä json-objekti SQL-kyselylle:

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

Aiemmissa versioissa samaa menetelmää voidaan käyttää jokainen (hstore), mutta oikea "taitto" ja pakenevat monimutkaiset objektit hstoressa voi aiheuttaa ongelmia.

json_populate_recordset

Jos tiedät etukäteen, että “input” json-taulukon tiedot menevät johonkin taulukkoon, voit säästää paljon “dereferencing” -kentissä ja suoratoistossa haluttuihin tyyppeihin json_populate_recordset-funktiolla:

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

json_to_recordset

Ja tämä toiminto yksinkertaisesti "laajentaa" välitetyn objektijoukon valinnaksi turvautumatta taulukkomuotoon:

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

VÄLIAIKAINEN PÖYTÄ

Mutta jos datamäärä lähetetyssä näytteessä on erittäin suuri, sen heittäminen yhteen sarjoitettuun parametriin on vaikeaa ja joskus mahdotonta, koska se vaatii kertaluonteisen suuri muistivaraus. Sinun on esimerkiksi kerättävä suuri erä tapahtumatietoja ulkoisesta järjestelmästä pitkän, pitkän ajan ja sitten haluat käsitellä sen kerran tietokantapuolella.

Tässä tapauksessa paras ratkaisu olisi käyttää väliaikaiset pöydät:

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

Menetelmä on hyvä suurten määrien harvoin lähettämiseen tiedot.
Tietojensa rakenteen kuvauksen kannalta väliaikainen taulukko eroaa "tavallisesta" taulukosta vain yhdellä ominaisuudella. pg_class järjestelmätaulukossaja sisään pg_type, pg_depend, pg_attribute, pg_attrdef, ... - eikä yhtään mitään.

Siksi verkkojärjestelmissä, joissa on suuri määrä lyhytaikaisia ​​yhteyksiä jokaiselle niistä, tällainen taulukko luo joka kerta uusia järjestelmätietueita, jotka poistetaan, kun yhteys tietokantaan suljetaan. Lopulta, TEMP TABLE:n hallitsematon käyttö johtaa taulukoiden "turpoamiseen" pg_catalogissa ja hidastaa monia niitä käyttäviä toimintoja.
Tietysti tätä vastaan ​​voidaan taistella määräajoin vacuum FULL järjestelmäluettelotaulukoiden mukaan.

Istunnon muuttujat

Oletetaan, että edellisen tapauksen tietojen käsittely on melko monimutkaista yksittäiselle SQL-kyselylle, mutta haluat tehdä sen melko usein. Toisin sanoen haluamme käyttää prosessikäsittelyä DO lohko, mutta tiedonsiirron käyttäminen väliaikaisten taulukoiden kautta tulee liian kallista.

Emme myöskään voi käyttää $n-parametria siirtymiseen anonyymiin lohkoon. Istuntomuuttujat ja funktio auttavat meitä pääsemään pois tilanteesta. nykyinen asetus.

Ennen versiota 9.2 sinun oli tehtävä esiasetukset erityinen nimiavaruus custom_variable_classes "heidän" istuntomuuttujilleen. Nykyisissä versioissa voit kirjoittaa jotain tällaista:

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

Muitakin ratkaisuja on saatavilla muilla tuetuilla prosessikielillä.

Tiedätkö lisää tapoja? Jaa kommenteissa!

Lähde: will.com

Lisää kommentti