作为 PostgreSQL 函数参数的表名

Question

作为 PostgreSQL 函数参数的表名

functionpostgresqlplpgsqldynamic-sqlidentifier

112

我��望您能在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) 可以生效而第二个却不行？如何解决这个问题？

- John Doe

我知道这个问题有点老了，但我在寻找另一个问题的答案时发现了它。你的函数不能只查询informational_schema吗？我的意思是，从某种程度上来说，这就是它的作用——让你查询并查看数据库中存在哪些对象。这只是一个想法。 - David S

我在寻找类似于 select * from 'foo'::table 的东西。 - Time Killer

8个回答

13

如果可能的话，请不要这样做。这是反模式的做法。如果客户端知道它想从哪个表中获取数据，则使用“SELECT FROM ThatTable”。如果数据库被设计成需要这样做，那么它似乎设计得不够优秀。如果数据访问层需要知道某个值是否存在于表中，则很容易在代码中组合SQL，并将此代码推入数据库并不好。

对我来说，这就像在电梯内安装一个设备，可以在其中输入所需楼层的编号。按下“前进”按钮后，机械手臂会移动到所需楼层的正确按钮上并按下它。这会引入许多潜在问题。

请注意：这里没有嘲笑的意图。我的愚蠢电梯例子是为了简明地指出这种技术的问题。它添加了一个无用的间接层，将表名选择从调用者空间（使用强大而且易于理解的DSL SQL）移动到使用晦涩/奇怪的服务器端SQL代码的混合体中。

通过将查询构造逻辑移动到动态SQL中的责任分割使代码更难理解。它违反了一个标准和可靠的约定（SQL查询如何选择要选择的内容），以自定义代码代替，这很容易出现错误。

以下是一些潜在问题的详细说明：

- 动态SQL提供了SQL注入的可能性，在前端代码或后端代码中很难识别（必须一起检查才能看到这一点）。 - 存储过程和函数可以访问SP/函数所有者有权但调用者没有权利的资源。就我所了解的而言，如果默认情况下使用生成动态SQL并运行它的代码，则数据库会在调用者的权限下执行动态SQL。这意味着您将无法使用特权对象，或者您必须向所有客户端开放它们，从而增加了对特权数据的潜在攻击面。将SP/函数设置为始终以特定用户身份运行（在SQL Server中，使用“EXECUTE AS”）可能解决该问题，但会使事情变得更加复杂。这通过使动态SQL成为一个非常诱人的攻击向量，加剧了上述风险。 - 当开发人员必须理解应用程序代码以便修改它或修复错误时，他将发现很难获取执行的确切SQL查询。可以使用SQL Profiler，但这需要特殊权限，并且可能会对生产系统产生负面影响。已执行的查询可以由SP记录，但这增加了可疑收益的复杂性（需要适应新表格，清除旧数据等），并且相当不明显。实际上，某些应用程序的架构使得开发人员没有数据库凭据，因此他几乎无法看到提交的查询。 - 当发生错误时，例如尝试选择不存在的表时，您将从数据库获得“无效对象名称”的消息。无论您是在后端还是在数据库中组合SQL，都会发生这种情况，但区别在于，一些正在尝试解决系统问题的可怜开发人员必须深入挖掘下一个层次的洞穴，以进入神奇的流程中，了解问题所在。日志不会显示“GetWidget中的错误”，它会显示“OneProcedureToRuleThemAllRunner中的错误”。这种抽象通常会使系统变得更糟。

以下是基于参数切换表名的伪C#示

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

虽然这并不能解决所有可能出现的问题，但我所列出的其他技术中的缺陷在这个示例中是不存在的。

- ErikE

4

我并不完全同意那个观点。比如说，你按下这个“前往”按钮，然后某个机制会检查楼层是否存在。在触发器中可以使用函数，进而检查一些条件。这个决定可能不是最美好的，但如果系统已经够大了，而且你需要对逻辑进行一些修正，那么这个选择就不会那么严重，我想。 - John Doe

2

但请注意，尝试按下不存在的按钮将生成异常，无论您如何处理。您实际上不能按下不存在的按钮，因此，在按下按钮之上添加一层以检查不存在的数字是没有好处的，因为在创建该层之前并不存在该数字输入！在编程中，抽象是我认为最强大的工具。然而，添加一个仅仅是复制现有抽象的图层是错误的。数据库本身就是一个把名称映射到数据集的抽象层。 - ErikE

3

非常准确。SQL 的整个目的就是表达你想要提取的数据集合。这个函数所做的唯一事情就是封装一个“预设”的 SQL 语句。考虑到标识符也是硬编码的，整个东西看起来有点不妙。 - Nick Hristov

2

@three 在一个技能的精通阶段之前（参见技能习得的德雷福斯模型），他应该绝对遵守规则，比如“不要将表名传递到过程中用于动态SQL”。即使是暗示这并不总是坏事也是错误的建议。知道这一点，初学者会被诱惑使用它！那是不好的。只有某个主题的大师才能打破规则，因为他们是唯一有经验的人，在任何特定情况下都知道这种违反规则是否真的有意义。 - ErikE

2

@three-cups 我已经更新了更多详细信息，说明为什么这是一个不好的想法。 - ErikE

显示剩余9条评论

10

在plpgsql代码中，如果表名或列名来自变量，则必须使用EXECUTE语句进行查询。此外，当生成动态查询时，不允许使用IF EXISTS (<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;

- Daniel Vérité

谢谢，就在几分钟前我也在做同样的事情，当我看到你的答案时。唯一的区别是我不得不删除 quote_ident()，因为它添加了额外的引号，这让我有点惊讶，因为它在大多数示例中都被使用。 - John Doe

如果/当表名包含[a-z]之外的字符，或者与保留标识符冲突（例如："group"作为表名），那么这些额外的引号将是必需的。 - Daniel Vérité

顺便问一下，你能提供一个链接证明 IF EXISTS <query> 这个结构不存在吗？我很确定我看到过这样的工作代码示例。 - John Doe

1

@JohnDoe：在plpgsql中，IF EXISTS (<query>) THEN ...是一个完全有效的结构。只是对于 <query> 的动态SQL不适用。我经常使用它。此外，这个函数可以得到很大的改进。我发布了一个答案。 - Erwin Brandstetter

1

抱歉，你对于 if exists(<query>) 是正确的，在一般情况下是有效的。我刚刚检查并相应修改了答案。 - Daniel Vérité

显示剩余2条评论

9

我知道这是一个老帖子，但最近在尝试解决相同的问题时遇到了它——在我的情况下，针对一些相当复杂的脚本。

将整个脚本转换为动态SQL并不理想。这是繁琐且容易出错的工作，并且您会失去参数化的能力：必须将参数插入到SQL中的常量中，这会对性能和安全性产生不良后果。

以下是一个简单的技巧，如果你只需要从你的表中选择数据，就可以让你保持SQL的完整性——使用动态SQL创建一个临时视图：

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;

- Nathan Meyers

现在这是一个旧线程：)。以防万一，“temporary”需要模式也是临时的。您可以省略该关键字并根据需要进行清理。除了这里的正统讨论外，对于某些管理员任务来说，它至少是一种有用的技术。 - full.stack.ex

4

第一个实际上并没有“起作用”，它只是在不生成错误的情况下起作用。

尝试使用 SELECT * FROM quote_ident('table_that_does_not_exist');，你会看到为什么你的函数返回 1：select 返回一个有一列（名为 quote_ident）和一行（变量 $1 或在这种特殊情况下为 table_that_does_not_exist）的表格。

你想要做的需要动态 SQL，这实际上是 quote_* 函数应该使用的地方。

- Matt

非常感谢，Matt，table_that_does_not_exist 给出了相同的结果，你是正确的。 - John Doe

2

如果问题是测试表是否为空（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;

- Julien Feniou

2

如果您想动态地将表名、列名和值作为参数传递给函数，请使用以下代码。

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

- Sandip Debnath

-2

我使用的是9.4版本的PostgreSQL，我总是使用这段代码：

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

对我来说它很好用。

注意！ 上面的例子是那些展示“如果我们想在查询数据库时保持安全，应该怎么做”的例子之一 :P

- dm3

1

创建一个新表与操作现有表的名称不同。无论哪种方式，您都应该转义执行为代码的文本参数，否则您将容易受到SQL注入攻击。 - Erwin Brandstetter

哦，是的，我错了。这个话题误导了我，而且我也没有把它读到最后。在我的情况下通常是这样的。 :P为什么使用文本参数的代码容易受到注入攻击？ - dm3

哎呀，这真的很危险。谢谢你的回答！ - dm3

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

对于只有少数已知的表名，通常更简单的方法是避免使用动态SQL，并在单独的函数或CASE结构中明确列出少数代码变体。

话虽如此，你给出的代码可以简化和改进：

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

使用模式限定名称进行调用（见下文）：

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

或者：

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

主要要点

使用OUT参数来简化函数。你可以直接将动态SELECT的结果赋值并完成。不需要额外的变量和代码。 EXISTS正好符合你的需求。如果行存在，则返回true，否则返回false。有多种方法可以实现这一点，EXISTS通常是最高效的。

要返回与原始值相似的integer，将boolean结果转换为integer并使用OUT result integer。但最好还是像示例中演示的那样返回boolean。

我在这里使用对象标识符类型regclass作为_tbl的输入类型。这比使用text输入和quote_ident(_tbl)或format('%I', _tbl)更方便，因为：

它同样有效地防止SQL注入。
如果表名无效/不存在/对当前用户不可见，它会立即失败并更加优雅地处理。
它适用于带有模式限定的表名，而使用简单的quote_ident(_tbl)或format(%I)无法解决歧义。您需要单独传递和转义模式和表名。

一个regclass参数只适用于现有的表，显然。

我仍然使用format()，因为它简化了语法（并展示了它的用法），但是用%s代替%I。对于更复杂的查询，format()更有帮助。对于简单的例子，我们可以直接拼接。

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

不需要在FROM列表中只有一个表时对id列进行表限定，没有可能的歧义。（动态）EXECUTE内部的SQL命令具有单独的作用域，函数变量或参数不可见 - 与函数体中的普通SQL命令相反。

这就是为什么您始终要正确转义动态SQL的用户输入的原因： fiddle - 演示SQL注入
_{旧版sqlfiddle}