Herë pas here, zhvilluesi ka nevojë të transferojë në kërkesë një grup parametrash ose madje një tërësi të tërë «në hyrje». Disa herë hasen zgjidhje shumë të çuditshme për këtë problem.
Le të shohim «nga e kundërta» dhe të shohim se si nuk duhet vepruar, pse, dhe si mund ta bëjmë më mirë.
Injektimi i drejtpërdrejtë i vlerave në trupin e kërkesës
Duket zakonisht kështu:
query = "SELECT * FROM tbl WHERE id = " + value… ose kështu:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)Për këtë mënyrë është shkruar, diskutuar dhe mjaftueshëm:
G 거의 gjithmonë kjo është — një rrugë e drejtpërdrejtë drejt SQL-injeksioneve dhe një ngarkesë e tepruar në logjikën e biznesit, e cila është e detyruar të "ngjisë" vargun e kërkesës tuaj.
Pjesërisht i justifikuar një qasje e tillë mund të jetë vetëm në rastin e nevojës për të përdorur sektorizimin në versionet PostgreSQL 10 dhe më poshtë për të marrë një plan më të efektshëm. Në këto versione, lista e sektorëve të skanuar përcaktohet ende pa marrë në konsideratë parametrat e transmetuar, vetëm në bazë të trupit të kërkesës.
$n-argumentet
Përdorimi parametrat — kjo është mirë, ajo lejon të përdorim , duke zvogëluar ngarkesën si në logjikën e biznesit (rreshti i kërkesës krijohet dhe transmetohet vetëm një herë), ashtu edhe në serverin e DB (nuk është e nevojshme rishikimi dhe planifikimi për çdo rast të kërkesës).
Numri i ndryshueshëm i argumenteve
Problemet do të na presin, kur të duam të transferojmë një numër të panjohur të argumenteve:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Nëse e lëmë kërkesën në këtë formë, atëherë kjo edhe pse do të na mbrojë nga injeksionet e potencialshme, përsëri do të çojë në nevojën për ngjitje/përpunim të kërkesës për çdo variant të numrit të argumenteve. Tashmë është më mirë, sesa ta bësh këtë çdo herë, por mund të kalojmë pa këtë.
Mjafton të kalojmë vetëm një parameter, i cili përmban një përfaqësim të serializuar të vargut:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'Diferi i vetëm — nevoja për të konvertuar do të dhënën në llojin e kërkuar të vargut. Por kjo nuk shkakton probleme, pasi ne tashmë e dimë se ku po shkojmë.
Transferimi i një përzgjedhjeje (matrice)
Zakonisht këtu janë opsione të ndryshme për të transfertuar grupe të dhënash për t'i futur në bazë "me një kërkesë":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Përveç problemeve të përmendura më sipër me "ngjitjen" e kërkesës, kjo gjithashtu mund të na çojë edhe në out of memory dhe rënies së serverit. Arsyeja është e thjeshtë — nën argumenet PG rezervon memoriesh shtesë, dhe numri i të dhënave në grupin e dhënies kufizohet vetëm nga dëshirat e logjikës biznesore. Në raste të veçanta, është parë argumentet "numra" më shumë se $9000 — mos e bëj kështu.
Le të rishkruajmë kërkesën, duke aplikuar tashmë "serilizimin e dyfishtë":
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;
Po, në rastin e "vlerave të komplikuara" brenda array, ato duhet të jenë të rrethuara me citate.
Shkurt, që në këtë mënyrë mund të "zhvendosni" zgjedhjen me një numër të rastësishëm fushash.
unnest, unnest, …
Herë pas here ndodhin variante që kalojnë "array me array" disa "array me kolonat", për të cilat kam përmendur :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Me këtë metodë, nëse bëni një gabim kur gjeneroni listat e vlerave për kolona të ndryshme, është shumë e lehtë të merrni rezultate përshtatshme, të cilat varen gjithashtu nga versioni i serverit:-- $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 |
Që nga versioni 9.3, PostgreSQL ka krijuar funksione të plota për të punuar me tipin json. Pra, nëse definimi i parametrave hyrës ndodh në shfletues, mund ta formoni drejtpërdrejt atjeJSON
objekti json për kërkesën SQL SELECT key k , value v FROM json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}':
Për versionet e mëparshme, mund të përdoret një mënyrë e ngjashme përeach(hstore) , por "përmbledhja" e saktë me ekzaminimin e objekteve të komplikuara në hstore mund të shkaktojë probleme.json_populate_recordset
Nëse e dini paraprakisht, se të dhënat nga array json "hyrëse" do të shkojnë për të mbushur një tabelë, mund të kurseni shumë në "zhvleftësimin" e fushave dhe transformimin në llojet e nevojshme, duke marrë parasysh funksionin 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_recordsetDhe kjo funksion thjesht "zhvendos" array-in e objekteve të dhëna në zgjedhje, pa u mbështetur në formatin e tabelës:
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
TABELË TEMPORAREPor nëse volumi i të dhënave në zgjedhjen e dërguar është shumë i madh, atëherë ta dërgosh atë në një parametër të vetëm të serilizuar — është e vështirë, dhe ndonjëherë është e pamundur, pasi kërkon një herë
ndanjen e një sasi të madhe memoriesh выделения большого объема памяти. Për shembull, ndoshta duhet të grumbulloni një paketë të madhe të dhënash për ngjarje nga një sistem jashtë, dhe pastaj dëshironi ta përpunoni një herë në anën e DB-së.
Në këtë rast, zgjidhja më e mirë do të ishte përdorimi i :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- përsëritni sa shumë herë që është e nevojshme
...
-- këtu bëjmë diçka të dobishme me të gjithë këtë tabelë
Ky qasje është e mirë për transferta të rralla të volumit të madh të dhënave.
Nga pikëpamja e përshkrimit të strukturës së të dhënave tuaja, tabela e përkohshme dallon nga "tabela e zakonshme" vetëm për një veçori në tabelën sistemike pg_class, ndërsa në pg_type, pg_depend, pg_attribute, pg_attrdef, … — kështu që nuk ka fare dallim.
Prandaj, në sistemet web me një numër të madh lidhjesh me jetë të shkurtër, për secilën prej tyre, një tabelë e tillë do të krijojë regjistrime të reja sistemike çdo herë, të cilat fshihen me mbylljen e lidhjes me DB-në. Në fund, përdorimi i pakontrolluar i TEMP TABLE çon në "shëndrimin" e tabelave në pg_catalog dhe ngadalësimin e shumë operacioneve që i përdorin ato.
Sigurisht, me këtë mund të luftohet përmes kalimit të rregullt VACUUM FULL për tabelat e katalogut sistemik.
Variablat e sesionit
Le të supozojmë se përpunimi i të dhënave nga rasti i mëparshëm është mjaft kompleks për një SQL kërkesë, por dëshirojmë ta bëjmë shpesh. Kështu, dëshirojmë të përdorim përpunimin procedural në , por do të ishte shumë e kushtueshme të përdorin transmetimin e të dhënave përmes tabelave të përkohshme.
Të përdorim parametrat $n për të transmetuar në bllokun anonim gjithashtu nuk do të mundemi. Një zgjidhje për këtë do të na ndihmojnë variablat e sesionit dhe funksioni current_setting.
Para versionit 9.2, ishte e nevojshme të konfiguroni paraprakisht custom_variable_classes për variablat e sesionit "të tyre". Në versionet aktuale mund të shkruani përafërsisht kështu:
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 : 3Në gjuhët e tjera procedurale të mbështetura mund të gjeni edhe zgjidhje të tjera.
Keni ndonjë mënyrë tjetër? Ndani në komentet!
Burimi: habr.com
