Nazwa tabeli jako parametr funkcji PostgreSQL

Chcę przekazać nazwę tabeli jako parametr w funkcji Postgres. Wypróbowałem ten kod:

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
    BEGIN
    IF EXISTS (select * from quote_ident($1) where quote_ident($1).id=1) THEN
     return 1;
    END IF;
    return 0;
    END;
$$ LANGUAGE plpgsql;

select some_f('table_name');

I mam to:

ERROR:  syntax error at or near "."
LINE 4: ...elect * from quote_ident($1) where quote_ident($1).id=1)...
                                                             ^

********** Error **********

ERROR: syntax error at or near "."

A oto błąd, który otrzymałem po zmianie na to select * from quote_ident($1) tab where tab.id=1:

ERROR:  column tab.id does not exist
LINE 1: ...T EXISTS (select * from quote_ident($1) tab where tab.id...

Prawdopodobnie quote_ident($1)działa, bo bez where quote_ident($1).id=1części, którą dostaję 1, czyli coś jest wybrane. Dlaczego pierwsza może quote_ident($1)działać, a druga nie w tym samym czasie? Jak można to rozwiązać?

Można to jeszcze bardziej uprościć i ulepszyć:

CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer)
    LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)
   INTO result;
END
$func$;

Zadzwoń z nazwą kwalifikowaną w schemacie (patrz poniżej):

SELECT some_f('myschema.mytable');  -- would fail with quote_ident()

Lub:

SELECT some_f('"my very uncommon table name"');

Główne punkty

• Użyj OUTparametru, aby uprościć funkcję. Możesz bezpośrednio wybrać wynik dynamicznego SQL do niego i gotowe. Nie ma potrzeby stosowania dodatkowych zmiennych i kodu.

• EXISTSrobi dokładnie to, czego chcesz. Otrzymasz, truejeśli wiersz istnieje lub w falseinny sposób. Można to zrobić na różne sposoby, EXISTSzazwyczaj jest to najbardziej wydajne.

• Wydaje się, że chcesz odzyskać liczbę całkowitą , więc rzutuję booleanwynik z EXISTSdo integer, co daje dokładnie to, co miałeś. Zamiast tego zwróciłbym wartość logiczną .

• Używam typu identyfikatora obiektu regclassjako typu wejściowego dla _tbl. To robi wszystko quote_ident(_tbl)lub format('%I', _tbl)zrobiłoby, ale lepiej, ponieważ:

• .. równie dobrze zapobiega iniekcji SQL .

• .. zawodzi natychmiast i bardziej wdzięcznie, jeśli nazwa tabeli jest nieprawidłowa / nie istnieje / jest niewidoczna dla bieżącego użytkownika. ( regclassParametr ma zastosowanie tylko do istniejących tabel).

• .. działa z nazwami tabel kwalifikowanymi według schematu, w przypadku których zwykły quote_ident(_tbl)lub format(%I)nie powiedzie się, ponieważ nie mogą rozwiązać niejednoznaczności. Musiałbyś osobno przekazywać i zmieniać nazwy schematów i tabel.

• Nadal używam format(), ponieważ upraszcza składnię (i pokazuje, jak jest używany), ale z %szamiast %I. Zazwyczaj zapytania są bardziej złożone, więc format()pomaga bardziej. Dla prostego przykładu moglibyśmy równie dobrze połączyć:

    EXECUTE 'SELECT (EXISTS (SELECT FROM ' || _tbl || ' WHERE id = 1))::int'
  

• Nie ma potrzeby kwalifikowania idkolumny do tabeli, jeśli na FROMliście jest tylko jedna tabela . W tym przykładzie nie jest możliwa żadna dwuznaczność. (Dynamiczne) polecenia SQL wewnątrz EXECUTEmają oddzielny zakres , zmienne funkcyjne lub parametry nie są tam widoczne - w przeciwieństwie do zwykłych poleceń SQL w treści funkcji.

Oto dlaczego zawsze poprawnie unikasz wprowadzania danych przez użytkownika dla dynamicznego SQL:

db <> fiddle tutaj demonstruje wstrzyknięcie SQL
_{Stary sqlfiddle}

Jeśli to możliwe, nie rób tego.

Oto odpowiedź - to anty-wzór. Jeśli klient zna tabelę, z której chce uzyskać dane, to SELECT FROM ThatTable. Jeśli baza danych jest zaprojektowana w taki sposób, że jest to wymagane, wydaje się, że została zaprojektowana w sposób nieoptymalny. Jeśli warstwa dostępu do danych musi wiedzieć, czy wartość istnieje w tabeli, łatwo jest utworzyć kod SQL w tym kodzie, a umieszczenie tego kodu w bazie danych nie jest dobre.

Wydaje mi się, że jest to instalowanie urządzenia wewnątrz windy, w którym można wpisać numer pożądanego piętra. Po naciśnięciu przycisku Go przesuwa mechaniczną dłoń do odpowiedniego przycisku dla żądanego piętra i naciska go. To wprowadza wiele potencjalnych problemów.

Uwaga: tutaj nie ma zamiaru kpić. Mój głupi przykład z windą był * najlepszym urządzeniem, jakie mogłem sobie wyobrazić * do zwięzłego wskazywania problemów z tą techniką. Dodaje bezużyteczną warstwę pośredniej, przenosząc wybór nazwy tabeli z przestrzeni wywołującej (przy użyciu solidnego i dobrze zrozumiałego DSL, SQL) do hybrydy, używając niejasnego / dziwacznego kodu SQL po stronie serwera.

Taki podział odpowiedzialności poprzez przeniesienie logiki konstrukcji zapytań do dynamicznego SQL sprawia, że ​​kod jest trudniejszy do zrozumienia. Narusza standardową i niezawodną konwencję (jak zapytanie SQL wybiera, co wybrać) w nazwie niestandardowego kodu obarczonego potencjałem błędu.

Oto szczegółowe punkty dotyczące niektórych potencjalnych problemów związanych z tym podejściem:

• Dynamiczny SQL oferuje możliwość iniekcji SQL, która jest trudna do rozpoznania w kodzie frontonu lub w samym kodzie zaplecza (aby to zobaczyć, trzeba je razem sprawdzić).

• Procedury i funkcje składowane mogą uzyskiwać dostęp do zasobów, do których właściciel SP / funkcji ma prawa, ale wywołujący nie. O ile rozumiem, bez szczególnej ostrożności, to domyślnie, gdy używasz kodu, który tworzy dynamiczny SQL i uruchamia go, baza danych wykonuje dynamiczny SQL na prawach wywołującego. Oznacza to, że albo w ogóle nie będziesz mógł korzystać z uprzywilejowanych obiektów, albo będziesz musiał otworzyć je dla wszystkich klientów, zwiększając powierzchnię potencjalnego ataku na dane uprzywilejowane. Ustawienie SP / funkcji w czasie tworzenia, aby zawsze działała jako określony użytkownik (w SQL Server EXECUTE AS) może rozwiązać ten problem, ale komplikuje sprawę. Zwiększa to ryzyko wstrzyknięcia SQL, o którym mowa w poprzednim punkcie, czyniąc dynamiczny SQL bardzo kuszącym wektorem ataku.

• Kiedy programista musi zrozumieć, co robi kod aplikacji, aby go zmodyfikować lub naprawić błąd, bardzo trudno mu będzie uzyskać dokładne zapytanie SQL, które jest wykonywane. Można użyć profilera SQL, ale wymaga to specjalnych uprawnień i może mieć negatywny wpływ na wydajność systemów produkcyjnych. Wykonane zapytanie może zostać zarejestrowane przez SP, ale zwiększa to złożoność i przynosi wątpliwe korzyści (wymaga dostosowania nowych tabel, usunięcia starych danych itp.) I jest dość nieoczywiste. W rzeczywistości niektóre aplikacje są zaprojektowane w taki sposób, że programista nie ma poświadczeń bazy danych, więc zobaczenie przesłanego zapytania jest dla niego prawie niemożliwe.

• Gdy wystąpi błąd, na przykład podczas próby wybrania tabeli, która nie istnieje, otrzymasz z bazy danych komunikat zawierający informację o „nieprawidłowej nazwie obiektu”. Stanie się to dokładnie tak samo, niezależnie od tego, czy tworzysz kod SQL na zapleczu, czy w bazie danych, ale różnica polega na tym, że jakiś biedny programista, który próbuje rozwiązać problem z systemem, musi przejść o jeden poziom głębiej do kolejnej jaskini poniżej tej, w której istnieje problem, aby zagłębić się w cudowną procedurę, która robi wszystko, aby spróbować ustalić, na czym polega problem. Dzienniki nie pokażą „Błąd w GetWidget”, pokaże „Błąd w OneProcedureToRuleThemAllRunner”. Ta abstrakcja ogólnie pogorszy system .

Przykład w pseudo-C # przełączania nazw tabel na podstawie parametru:

string sql = $"SELECT * FROM {EscapeSqlIdentifier(tableName)};"
results = connection.Execute(sql);

Chociaż nie eliminuje to każdego możliwego problemu, jaki można sobie wyobrazić, wady, które nakreśliłem w innej technice, są nieobecne w tym przykładzie.

W kodzie plpgsql instrukcja EXECUTE musi być używana do zapytań, w których nazwy tabel lub kolumny pochodzą ze zmiennych. Również IF EXISTS (<query>)konstrukcja jest niedozwolone, jeśli queryjest dynamicznie wygenerowany.

Oto twoja funkcja z rozwiązanymi obydwoma problemami:

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
DECLARE
 v int;
BEGIN
      EXECUTE 'select 1 FROM ' || quote_ident(param) || ' WHERE '
            || quote_ident(param) || '.id = 1' INTO v;
      IF v THEN return 1; ELSE return 0; END IF;
END;
$$ LANGUAGE plpgsql;

Pierwsza nie "działa" w takim sensie, jak masz na myśli, działa tylko o tyle, o ile nie generuje błędu.

Spróbuj SELECT * FROM quote_ident('table_that_does_not_exist');, a zobaczysz, dlaczego funkcja zwraca 1: zaznaczenie zwraca tabelę z jedną kolumną (nazwaną quote_ident) z jednym wierszem (zmienna $1lub w tym konkretnym przypadku table_that_does_not_exist).

To, co chcesz zrobić, będzie wymagało dynamicznego SQL, czyli miejsca, w którym quote_*funkcje mają być używane.

Jeśli chodziło o sprawdzenie, czy tabela jest pusta, czy nie (id = 1), oto uproszczona wersja przechowywanego proc Erwina:

CREATE OR REPLACE FUNCTION isEmpty(tableName text, OUT zeroIfEmpty integer) AS
$func$
BEGIN
EXECUTE format('SELECT COALESCE ((SELECT 1 FROM %s LIMIT 1),0)', tableName)
INTO zeroIfEmpty;
END
$func$ LANGUAGE plpgsql;

Wiem, że to stary wątek, ale natknąłem się na niego ostatnio, próbując rozwiązać ten sam problem - w moim przypadku dla niektórych dość złożonych skryptów.

Przekształcenie całego skryptu w dynamiczny SQL nie jest idealnym rozwiązaniem. Jest to żmudna i podatna na błędy praca, a ponadto tracisz możliwość parametryzacji: parametry muszą być interpolowane na stałe w języku SQL, co ma zły wpływ na wydajność i bezpieczeństwo.

Oto prosta sztuczka, która pozwala zachować SQL w stanie nienaruszonym, jeśli potrzebujesz tylko dokonać wyboru z tabeli - użyj dynamicznego SQL, aby utworzyć tymczasowy widok:

CREATE OR REPLACE FUNCTION some_f(_tbl varchar) returns integer
AS $$
BEGIN
    drop view if exists myview;
    execute format('create temporary view myview as select * from %s', _tbl);
    -- now you can reference myview in the SQL
    IF EXISTS (select * from myview where myview.id=1) THEN
     return 1;
    END IF;
    return 0;
END;
$$ language plpgsql;

Jeśli chcesz, aby nazwa tabeli, nazwa kolumny i wartość były dynamicznie przekazywane do funkcji parametru

użyj tego kodu

create or replace function total_rows(tbl_name text, column_name text, value int)
returns integer as $total$
declare
total integer;
begin
    EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total;
    return total;
end;
$total$ language plpgsql;


postgres=# select total_rows('tbl_name','column_name',2); --2 is the value

Mam PostgreSQL w wersji 9.4 i zawsze używam tego kodu:

CREATE FUNCTION add_new_table(text) RETURNS void AS
$BODY$
begin
    execute
        'CREATE TABLE ' || $1 || '(
        item_1      type,
        item_2      type
        )';
end;
$BODY$
LANGUAGE plpgsql

I wtedy:

SELECT add_new_table('my_table_name');

U mnie to działa.

Uwaga! Powyższy przykład jest jednym z tych, które pokazują "Jak tego nie zrobić, jeśli chcemy zachować bezpieczeństwo podczas odpytywania bazy danych": P