sql - 安全地编写动态 SQL，使用在任何 View 中估计行的示例函数

Question

上周我给自己写了一个客户端数据透视表生成器。屏幕上包括表和 View 的列表。在此过程中，我发现估计选定表或 View 中的行数很有帮助。事实证明，估计表数很容易，但我不知道如何估计 View 中的行数。今天，我在读书

COUNT(*) MADE FAST

Laurenz Albe 的博文以这段巧妙的文章结尾:

CREATE FUNCTION row_estimator(query text) RETURNS bigint
   LANGUAGE plpgsql AS
$$DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;

   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;

那个。是。好的。我意识到这可以用作 View 估算器，所以我写了一个函数(阅读“hacked together”):

DROP FUNCTION IF EXISTS api.view_count_estimate (text, text);

CREATE OR REPLACE FUNCTION api.view_count_estimate (
    schema_name text, 
    view_name text)
 RETURNS BIGINT 

AS $$

DECLARE
   plan jsonb;
   query text;
BEGIN

   EXECUTE 
   'select definition 
      from pg_views 
     where schemaname = $1 and
           viewname   = $2'

    USING schema_name,view_name
    INTO query;

   EXECUTE 
   'EXPLAIN (FORMAT JSON) ' || query 
   INTO plan;

   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;

END;
$$ LANGUAGE plpgsql;

ALTER FUNCTION api.view_count_estimate(text, text) OWNER TO user_change_structure; 

这让我想到了在 Postgres 中我有点紧张的领域之一:安全地创建动态 SQL。我不太清楚魔术 regclass 铸件，或者我是否应该使用上面的 quote_ident() 之类的东西。使用 USING 列表构建的 SQL 是否安全？我不明白怎么可能。

我正在使用 Postgres 11.4.x。

Answer 1

正如 Laurenz 所说，您当前的代码是绝对安全的，但考虑到如今围绕 SQL 注入(inject)的大量偏执，它值得详细说明。

考虑一下你的函数的原始版本:

CREATE FUNCTION api.view_count_estimate(schema_name text, view_name text) RETURNS BIGINT 
AS $$
DECLARE result BIGINT;
BEGIN
  EXECUTE 'SELECT COUNT(*) FROM ' || schema_name || '.' || view_name INTO result;
  RETURN result;
END
$$ LANGUAGE plpgsql;

这显然对 SQL 注入(inject)是开放的，但可以通过多种方式轻松保护。最直接的方法是使用 quote_ident():

EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(schema_name) || '.' || quote_ident(view_name)
INTO result;

这保证了连接的字符串在语法上是有效的 identifiers , 如果它们包含任何符号、空格或大写字符，则将它们加双引号，这样用户输入就没有被解释为不需要的 SQL 表达式或关键字的风险(尽管它有可能使您的函数输入大小写-敏感)。

quote_ident() 的一个更简洁的替代方法是使用等效的 %I format specifier :

EXECUTE format('SELECT COUNT(*) FROM %I.%I', schema_name, view_name) INTO result;

还有一个用于嵌入字符串文字的%L说明符，相当于quote_literal()函数。

更好的方法是通过 regclass 传递 View 引用参数:

CREATE FUNCTION api.view_count_estimate(view_id regclass) RETURNS BIGINT 
AS $$
DECLARE result BIGINT;
BEGIN
  EXECUTE 'SELECT COUNT(*) FROM ' || view_id::text INTO result;
  RETURN result;
END
$$ LANGUAGE plpgsql;

regclass 类型背后的“魔法”实际上非常简单；该值本身只是 pg_class table 的整数主键, 它在大多数方面表现得像一个整数，但是 casts to 和 from 字符串值将查询 pg_class 以查找表的名称，遵循与 quote_ident() 相同的引用规则并尊重当前的 schema search path .

换句话说，你可以调用这个函数

SELECT view_count_estimate('my_view')

或

SELECT view_count_estimate('"public"."my_view"')

或

SELECT view_count_estimate('public.My_View')

...并且 regclass 转换将像在查询中一样解析标识符(而函数内的 text 将引用/限定标识符作为需要)。

但是所有这些只有在您需要将标识符 替换到您的查询中时才有必要。如果您只需要将 values 替换到查询中(就像您的函数中的情况一样)，那么参数化查询(例如 EXECUTE ... USING)是完全安全的。查询参数化不是简单的字符串替换；它在代码和数据之间保持清晰的分离(实际上，在甚至考虑参数值之前，解析整个 SQL 语句并解析所有标识符)，因此不存在 SQL 注入(inject)的可能性。

事实上，由于本例中的所有变量都是简单的参数，所以根本不需要动态查询；您的函数可以直接运行查询，无需 EXECUTE:

SELECT definition 
FROM pg_views 
WHERE schemaname = schema_name
  AND viewname = view_name
INTO query;

在幕后，PL/pgSQL 将构建完全相同的参数化查询(即 ...WHERE schemaname = $1 AND viewname = $2)并将参数绑定(bind)到您的函数输入。这种方法的额外好处只有 preparing第一次在 session 中调用该函数时查询，并在后续调用中重新绑定(bind)参数值。

实际上，在这种情况下您根本不需要查询。 pg_get_viewdef() function将返回给定 regclass 的 View 定义，因此整个事情可以简化为:

CREATE FUNCTION api.view_count_estimate(view_id regclass) RETURNS bigint 
AS $$
DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || pg_get_viewdef(view_id) INTO plan;
   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;
$$ LANGUAGE plpgsql;