Вопрос по plpgsql, postgresql – Имя таблицы как параметр функции PostgreSQL

62

Я хочу передать имя таблицы в качестве параметра в функции Postgres. Я попробовал этот код:

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');

И я получил это:

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 "."

И вот ошибка, которую я получил, когда изменился на этот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...

Наверное,quote_ident($1) работает, потому что безwhere quote_ident($1).id=1 часть я получаю1, что означает, что что-то выбрано. Почему первыйquote_ident($1) работа а вторая не одновременно? И как это можно решить?

@DavidS Спасибо за комментарий, я попробую это. John Doe
Я знаю, что этот вопрос довольно старый, но я нашел его, когда искал ответ на другой вопрос. Может ли ваша функция просто запросить информационную_схему? Я имею в виду, что это своего рода то, для чего это нужно - позволить вам запрашивать и видеть, какие объекты существуют в базе данных. Просто идея. David S

Ваш Ответ

7   ответов
15

Это ответ. Это ужасный анти-паттерн. Какой цели это служит? Если клиент знает таблицу, из которой он хочет получить данные, тоSELECT FROM ThatTable! Если вы спроектировали свою базу данных так, как это требуется, вы, вероятно, спроектировали ее неправильно. Если вашему уровню доступа к данным необходимо знать, существует ли значение в таблице, то в этом коде легко выполнить динамическую часть SQL. Вставить его в базу данных не хорошо.

У меня есть идея: давайте установим устройство внутри лифтов, где вы можете ввести желаемое количество этажей. Затем, когда вы нажимаете «Go», он перемещает механическую руку к нужной кнопке для нужного этажа и нажимает ее для вас. Революционная!

Очевидно, мой ответ был слишком коротким для объяснения, поэтому я исправляю этот дефект более подробно.

Я не собирался издеваться. Мой глупый пример лифта былthe very best device I could imagine для краткого указания на недостатки техники, предложенной в вопросе. Этот метод добавляет совершенно бесполезный уровень косвенности и без необходимости перемещает выбор имени таблицы из пространства вызывающего абонента, используя надежный и понятный DSL (SQL), в гибрид, используя неясный / причудливый SQL-код на стороне сервера.

Такое разделение ответственности за счет перемещения логики построения запросов в динамический SQL усложняет понимание кода. Он разрушает вполне разумное соглашение (как SQL-запрос выбирает, что выбрать) в имени пользовательского кода, чреватого ошибкой.

Dynamic SQL offers the possibility of SQL injection that is hard to recognize in the front end code or the back end code singly (one must inspect them together to see this).

Stored procedures and functions can access resources that the SP/function owner has rights to but the caller doesn't. As far as I understand, when you use code that produces dynamic SQL and runs it, the database executes the dynamic SQL under the rights of the caller. This means you either won't be able to use privileged objects at all, or you have to open them up to all clients, increasing the surface area of potential attack to privileged data. Setting the SP/function at creation time to always run as a particular user (in SQL Server, EXECUTE AS) may solve that problem, but makes things more complicated. This exacerbates the risk of SQL injection mentioned in the previous point, by making the dynamic SQL a very enticing attack vector.

When a developer must understand what the application code is doing in order to modify it or fix a bug, he'll find it very difficult to get the exact SQL query being executed. SQL profiler can be used, but this takes special privileges and can have negative performance effects on production systems. The executed query can be logged by the SP but this increases complexity for no reason (maintaining new tables, purging old data, etc.) and is totally non-obvious. In fact, some applications are architected such that the developer does not have database credentials, so it becomes almost impossible for him to actually see the query being submitted.

When an error occurs, such as when you try to select a table that doesn't exist, you'll get a message along the lines of "invalid object name" from the database. That will happen exactly the same whether you're composing the SQL in the back end or the database, but the difference is, some poor developer who's trying to troubleshoot the system has to spelunk one level deeper into yet another cave below the one where the problem actually exists, to dig into the wonder-procedure that Does It All and try to figure out what the problem is. Logs won't show "Error in GetWidget", it will show "Error in OneProcedureToRuleThemAllRunner". This abstraction will just make your system worse.

Вот гораздо лучший пример псевдо-C # переключения имен таблиц на основе параметра:

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

Каждый недостаток, который я упомянул с другой техникой, полностью отсутствует в этом примере.

Нет просто никакой цели, никакой выгоды, никаких возможных улучшений в представлении имени таблицы в хранимой процедуре.

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded John Doe
Error: User Rate Limit Exceeded
Error: User Rate Limit ExceededwrongError: User Rate Limit ExceededalreadyError: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
0

чтобы имя таблицы, имя столбца и значение динамически передавались в функцию в качестве параметра

используйте этот код

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
3

что вы имеете в виду, он работает только постольку, поскольку не генерирует ошибку.

ПытатьсяSELECT * FROM quote_ident('table_that_does_not_exist');, и вы поймете, почему ваша функция возвращает 1: выборка возвращает таблицу с одним столбцом (с именемquote_ident) с одной строкой (переменная$1 или в этом конкретном случаеtable_that_does_not_exist).

То, что вы хотите сделать, потребует динамического SQL, который на самом делеquote_* функции предназначены для использования.

Error: User Rate Limit Exceededtable_that_does_not_existError: User Rate Limit Exceeded John Doe
0

чтобы проверить, является ли таблица пустой или нет (id = 1), вот упрощенная версия хранимой процедуры Erwin:

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;
9

ВЫПОЛНИТЬ оператор должен использоваться для запросов, в которых имена таблиц или столбцов происходят из переменных. Так жеIF EXISTS (<query>) конструкция не допускается, когдаquery динамически генерируется.

Вот ваша функция с обоими проблемами:

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;
Error: User Rate Limit Exceededquote_ident()Error: User Rate Limit Exceeded John Doe
Error: User Rate Limit Exceededif exists(<query>)Error: User Rate Limit Exceeded
Error: User Rate Limit ExceededIF EXISTS <query>Error: User Rate Limit Exceeded John Doe
Error: User Rate Limit ExceededIF EXISTS (<query>) THEN ...Error: User Rate Limit Exceeded<query>Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
-2

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

А потом:

SELECT add_new_table('my_table_name');

Это хорошо работает для меня.

Attention! Выше приведен пример, который показывает «Как не делать, если мы хотим обеспечить безопасность во время запросов к базе данных» :П

Error: User Rate Limit Exceededsqlfiddle.com/#!15/70fd5/1Error: User Rate Limit Exceededbobby-tables.com
Error: User Rate Limit Exceeded
Error: User Rate Limit ExceedednewError: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
91

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

Вызов с именем, дополненным схемой (см. Ниже):

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

Или же:

SELECT some_f('"my very uncommon table name"')
Major points

Use an OUT parameter to simplify the function. You can directly select the result of the dynamic SQL into it and be done. No need for additional variables and code.

EXISTS does exactly what you want. You get true if the row exists or false otherwise. There are various ways to do this, EXISTS is typically most efficient.

You seem to want an integer back, so I cast the boolean result from EXISTS to integer, which yields exactly what you had. I would return boolean instead.

I use the object identifier type regclass as input type for _tbl. That does everything quote_ident(_tbl) or format('%I', _tbl) would do, but better, because:

.. it prevents SQL injection just as well.

.. it fails immediately and more gracefully if the table name is invalid / does not exist / is invisible to the current user. (A regclass parameter is only applicable for existing tables.)

.. it works with schema-qualified table names, where a plain quote_ident(_tbl) or format(%I) would fail because they cannot resolve the ambiguity. You would have to pass and escape schema and table names separately.

I still use format(), because it simplifies the syntax (and to demonstrate how it's used), but with %s instead of %I. Typically, queries are more complex so format() helps more. For the simple example we could as well just concatenate:

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

No need to table-qualify the id column while there is only a single table in the FROM list. No ambiguity possible in this example. (Dynamic) SQL commands inside EXECUTE have a separate scope, function variables or parameters are not visible there - as opposed to plain SQL commands in the function body.

Протестировано с PostgreSQL 9.1.format() требует как минимум ту версию.

Вот почему выalways Правильно экранировать пользовательский ввод для динамического SQL:

SQL Fiddle демонстрирует внедрение SQL

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit ExceededregclassError: User Rate Limit Exceeded%LError: User Rate Limit ExceededwrongError: User Rate Limit Exceeded
Error: User Rate Limit Exceeded John Doe
Error: User Rate Limit ExceededDO $$BEGIN EXECUTE 'ANALYZE mytbl'; END$$;

Похожие вопросы