PostgreSQL-Antipatterns: Übergabe von Sets und Selects an SQL

Von Zeit zu Zeit benötigt der Entwickler Übergeben Sie eine Reihe von Parametern oder sogar eine ganze Auswahl an die Anfrage "am Eingang". Manchmal gibt es sehr seltsame Lösungen für dieses Problem.

Gehen wir „vom Gegenteil“ aus und sehen wir, wie man es nicht macht, warum und wie man es besser machen kann.

Direktes „Einfügen“ von Werten in den Anfragetext

Normalerweise sieht es so aus:

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

... oder so:

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

Über diese Methode wird gesagt, geschrieben und sogar gezeichnet genug:

Fast immer ist es so Direkter Weg zur SQL-Injection und eine zusätzliche Belastung der Geschäftslogik, die gezwungen ist, Ihre Abfragezeichenfolge zu „kleben“.

Dieser Ansatz kann nur teilweise gerechtfertigt sein, wenn dies erforderlich ist. Partitionierung verwenden in PostgreSQL-Versionen 10 und niedriger für einen effizienteren Plan. In diesen Versionen wird die Liste der gescannten Abschnitte ohne Berücksichtigung der übermittelten Parameter, nur auf Basis des Anfragetextes ermittelt.

$n-Argumente

Verwenden Platzhalter Die Parameter sind gut und ermöglichen die Verwendung VORBEREITETE ERKLÄRUNGEN, wodurch die Belastung sowohl der Geschäftslogik (die Abfragezeichenfolge wird nur einmal gebildet und übertragen) als auch des Datenbankservers (erneutes Parsen und Planen ist nicht für jede Abfrageinstanz erforderlich) reduziert wird.

Variable Anzahl von Argumenten

Probleme erwarten uns, wenn wir eine unbekannte Anzahl von Argumenten im Voraus übergeben wollen:

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

Wenn Sie die Anfrage in diesem Formular hinterlassen, erspart uns dies zwar mögliche Injektionen, führt aber dennoch dazu, dass die Anfrage zusammengefügt/analysiert werden muss für jede Option aus der Anzahl der Argumente. Schon besser, als es jedes Mal zu tun, aber man kann auch darauf verzichten.

Es reicht aus, nur einen Parameter zu übergeben, der Folgendes enthält serialisierte Darstellung eines Arrays:

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

Der einzige Unterschied besteht darin, dass das Argument explizit in den gewünschten Array-Typ konvertiert werden muss. Dies stellt jedoch kein Problem dar, da wir bereits im Voraus wissen, wohin wir uns wenden.

Probentransfer (Matrix)

In der Regel handelt es sich dabei um alle möglichen Möglichkeiten, Datensätze zum Einfügen in die Datenbank „in einer Anfrage“ zu übertragen:

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

Zusätzlich zu den oben beschriebenen Problemen beim „Umkleben“ der Anfrage kann dies auch bei uns dazu führen aus dem Gedächtnis und Serverabsturz. Der Grund ist einfach: PG reserviert zusätzlichen Speicher für die Argumente und die Anzahl der Datensätze im Satz wird nur durch die Wunschliste der Geschäftslogikanwendung begrenzt. In besonders klinischen Fällen war eine Untersuchung erforderlich „nummerierte“ Argumente größer als 9000 $ - nicht so.

Schreiben wir die Abfrage neu und wenden sie bereits an „zweistufige“ Serialisierung:

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, bei „komplexen“ Werten innerhalb eines Arrays müssen diese in Anführungszeichen gesetzt werden.
Klar ist, dass man auf diese Weise die Auswahl um beliebig viele Felder „erweitern“ kann.

unnest, unnest, …

Von Zeit zu Zeit gibt es Möglichkeiten, anstelle eines „Arrays von Arrays“ mehrere von mir erwähnte „Arrays von Spalten“ zu übergeben im letzten Artikel:

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

Wenn Sie mit dieser Methode beim Generieren von Wertelisten für verschiedene Spalten einen Fehler machen, ist es sehr einfach, sie vollständig zu erhalten unerwartete Ergebnisse, die auch von der Serverversion abhängen:

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

Ab Version 9.3 verfügt PostgreSQL über vollwertige Funktionen für die Arbeit mit dem JSON-Typ. Wenn Ihre Eingabeparameter also im Browser definiert sind, können Sie direkt dort ein Formular erstellen JSON-Objekt für SQL-Abfrage:

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

Für frühere Versionen kann dieselbe Methode verwendet werden every(hstore), aber das korrekte „Falten“ mit maskierten komplexen Objekten im hstore kann zu Problemen führen.

json_populate_recordset

Wenn Sie im Voraus wissen, dass die Daten aus dem JSON-Array „Eingabe“ in eine Tabelle eingefügt werden, können Sie mit der Funktion json_populate_recordset viel sparen, indem Sie Felder „dereferenzieren“ und in die gewünschten Typen umwandeln:

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

json_to_recordset

Und diese Funktion „erweitert“ einfach das übergebene Array von Objekten in eine Auswahl, ohne auf das Tabellenformat angewiesen zu sein:

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

Temporärer Tisch

Wenn die Datenmenge in der übertragenen Stichprobe jedoch sehr groß ist, ist es schwierig und manchmal unmöglich, sie in einen serialisierten Parameter umzuwandeln, da hierfür eine einmalige Eingabe erforderlich ist große Speicherzuweisung. Beispielsweise müssen Sie über einen sehr langen Zeitraum hinweg eine große Menge an Ereignisdaten von einem externen System sammeln und diese dann einmalig auf der Datenbankseite verarbeiten.

In diesem Fall wäre die beste Lösung die Verwendung temporäre Tabellen:

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

Die Methode ist gut für die seltene Übertragung großer Volumina Daten.
Im Hinblick auf die Beschreibung der Struktur ihrer Daten unterscheidet sich eine temporäre Tabelle von einer „normalen“ Tabelle nur in einem Merkmal. in der pg_class-SystemtabelleUnd in pg_type, pg_depend, pg_attribute, pg_attrdef, ... – und überhaupt nichts.

Daher generiert eine solche Tabelle in Websystemen mit einer großen Anzahl kurzlebiger Verbindungen für jede von ihnen jedes Mal neue Systemdatensätze, die gelöscht werden, wenn die Verbindung zur Datenbank geschlossen wird. Zusammenfassend, Die unkontrollierte Verwendung von TEMP TABLE führt zum „Anschwellen“ der Tabellen in pg_catalog und verlangsamt viele Vorgänge, die sie verwenden.
Dem kann man natürlich entgegenwirken periodischer Durchgang VAKUUM VOLL gemäß den Systemkatalogtabellen.

Sitzungsvariablen

Angenommen, die Verarbeitung der Daten aus dem vorherigen Fall ist für eine einzelne SQL-Abfrage recht komplex, Sie möchten dies jedoch häufig tun. Das heißt, wir wollen die prozedurale Verarbeitung nutzen DO-Block, aber die Verwendung der Datenübertragung über temporäre Tabellen wäre zu teuer.

Wir können $n-Parameter auch nicht zur Übergabe an einen anonymen Block verwenden. Die Sitzungsvariablen und die Funktion helfen uns, aus der Situation herauszukommen. Aktuelle Einstellung.

Vor Version 9.2 mussten Sie eine Vorkonfiguration durchführen spezieller Namensraum benutzerdefinierte_Variablenklassen für „ihre“ Sitzungsvariablen. In aktuellen Versionen können Sie etwa Folgendes schreiben:

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

Es sind weitere Lösungen in anderen unterstützten prozeduralen Sprachen verfügbar.

Kennen Sie weitere Möglichkeiten? Teilen Sie es in den Kommentaren!

Source: habr.com