如何编写可安全插入记录到可变表名的PostgreSQL SQL查询？

嘿，这个问题我太熟了！PostgreSQL里确实没法直接用参数化查询来处理表名、列名这类SQL标识符——毕竟$n占位符是专门给值用的，但咱们有安全可靠的办法来实现动态插入，还能彻底防住SQL注入。

要搞定动态表名/列名，关键是用PostgreSQL提供的标识符转义工具，同时保持插入值的参数化。这里有两个核心工具：

• quote_ident()：专门用来转义SQL标识符（表名、列名），自动处理特殊字符、关键字，避免注入。
• format()函数的%I占位符：和quote_ident()效果一样，用起来更简洁，适合拼接SQL字符串。

另外，先通过系统表验证表和列的存在，能进一步过滤恶意输入，避免无效的标识符导致错误。

下面是一个实用的动态插入函数，支持传入表名和待插入的键值对（用jsonb传递更灵活）：

CREATE OR REPLACE FUNCTION insert_dynamic(p_table_name text, p_payload jsonb)
RETURNS void AS $$
DECLARE
    v_valid_columns text;
    v_value_placeholders text;
    v_insert_sql text;
BEGIN
    -- 第一步：获取目标表的所有有效列名，自动转义
    SELECT string_agg(quote_ident(column_name), ', ')
    INTO v_valid_columns
    FROM information_schema.columns
    WHERE table_name = p_table_name
      AND table_schema = 'public'; -- 按需修改你的schema，也可以把schema设为参数

    -- 如果表不存在，直接抛出异常
    IF v_valid_columns IS NULL THEN
        RAISE EXCEPTION 'Table "%" does not exist in public schema', p_table_name;
    END IF;

    -- 第二步：生成对应列的参数占位符，用jsonb提取值并保持参数化
    SELECT string_agg('$1->>' || quote_ident(column_name), ', ')
    INTO v_value_placeholders
    FROM information_schema.columns
    WHERE table_name = p_table_name
      AND table_schema = 'public';

    -- 第三步：拼接安全的动态SQL
    v_insert_sql := format(
        'INSERT INTO %I (%s) VALUES (%s)',
        p_table_name,
        v_valid_columns,
        v_value_placeholders
    );

    -- 执行动态SQL，传入参数（这里$1对应p_payload）
    EXECUTE v_insert_sql USING p_payload;
END;
$$ LANGUAGE plpgsql;

比如你有一个users表，包含name、email列，调用方式如下：

SELECT insert_dynamic('users', '{"name": "Bob", "email": "bob@example.com"}'::jsonb);

• 标识符安全：不管是quote_ident()还是format()的%I，都能自动处理像user（关键字）、my-table（带特殊字符）这类标识符，彻底避免注入风险。
• 值的参数化：插入的值通过USING子句传入，完全不用拼接字符串，和普通参数化查询一样安全。
• 合法性验证：通过information_schema.columns获取列名，相当于自动过滤了非法的列名输入，恶意输入不存在的列名会被直接排除。

• 如果需要支持跨schema，可以把schema名也设为参数，同样用quote_ident()处理。
• 如果不想用jsonb传值，也可以改用可变参数，但jsonb的方式更适合动态列的场景。
• 如果函数需要以定义者权限执行，可以加上SECURITY DEFINER，但一定要谨慎，避免权限泄露。

内容的提问来源于stack exchange，提问作者Brian H.