PostgreSQL地址格式存储表的设计优化与安全性提升问询

我正在设计一个将地址各组件（街道号、街道名、城市等）存储在独立字段中的数据库，希望创建tb_address_format表来存储各国地址元素的排列顺序，以此生成有效的邮政地址。

最初的表结构设想 #

+──────────+───────────────────────────────────────────────────────────────+
| country  | address_format                                                |
+──────────+───────────────────────────────────────────────────────────────+
| us       | FORMAT('%s\n%s %s\t%s', street, city, province, postal_code)  |
| de       | FORMAT('%s\n%s %s', street, postal_code, city)                |
|          |                                                               |
|          |                                                               |
+──────────+───────────────────────────────────────────────────────────────+

拆分后的表结构 #

思考后我把信息拆分为两个字段存储：

• 格式字符串（format_string）
• 字段列表（list_of_fields）

表结构如下：

+──────────+──────────────────+──────────────────────────────────────+
| country  | format_string    | list_of_fields                       |
+──────────+──────────────────+──────────────────────────────────────+
| us       | '%s\n%s %s\t%s'  | street, city, province, postal_code  |
| de       | '%s\n%s %s'      | street, postal_code, city            |
+──────────+──────────────────+──────────────────────────────────────+

请问还能如何优化该设计并提升其安全性？

补充编辑内容 #

编辑1

我尝试在SQL的计算字段中直接求值，这需要使用扩展常量（如将\n转换为换行符）。

编辑2

street、city、province、postal_code均为表列，我最初考虑将字段列表存储为VARCHAR后再求值。

根据提示，是否可以/适合存储一个引用pg_catalog.pg_attribute表的数组？

编辑3：测试代码

CREATE TABLE tb_address(
city varchar,
street varchar,
postal_code varchar, 
county varchar,
state varchar,
country varchar);

CREATE TABLE tb_address_format(
country varchar,
address_format varchar,
list_of_fields text ARRAY);

INSERT INTO tb_address(
street, city, county, state, postal_code, country
)
VALUES
('150 5th Ave', 'New York', NULL, 'NY', '10011', 'USA'),
('Holzgasse 14', 'Köln', NULL, NULL, '50676', 'Germany');

INSERT INTO tb_address_format(
  country, address_format, list_of_fields
)
VALUES
('USA', '%s\n%s %s %s', '{"street", "city", "state", "postal_code"}'),
('Germany', '%s\n%s %s\n\n%s', '{"street", "postal_code", "city", "country"}');

预期结果

calc_formatted列为SQL计算字段，效果如下：

+--------------+----------+-------------+-------+---------+-------------------------------------+
| street       | city     | postal_code | state | country | calc_formatted                      |
+--------------+----------+-------------+-------+---------+-------------------------------------+
| 150 5th Ave  | New York | 10011       | NY    | USA     | 150 5th Ave\nNew York NY 10011\nUSA |
+--------------+----------+-------------+-------+---------+-------------------------------------+
| Holzgasse 14 | Köln     | 50676       |       | Germany | Holzgasse 14\n50676 Köln\nGermany   |
+--------------+----------+-------------+-------+---------+-------------------------------------+

一、设计优化方向 #

1. 用字段ID替代字段名存储，关联系统元数据表 #

可以参考pg_catalog.pg_attribute的思路，存储字段对应的attnum（字段ID）数组而非字段名字符串数组。优势如下：

• 避免字段名变更导致配置失效：后续修改tb_address字段名时，无需批量更新格式配置，系统元数据会自动同步。
• 减少人为错误：直接引用系统维护的字段ID，避免手动输入字段名的拼写失误。
• 提升查询效率：数字数组的比对、关联操作比字符串数组更高效。

修改后的tb_address_format表结构：

CREATE TABLE tb_address_format(
    country varchar PRIMARY KEY,
    format_string varchar NOT NULL,
    field_ids int[] NOT NULL
);

插入数据时，先通过pg_attribute获取字段ID：

-- 插入示例数据
INSERT INTO tb_address_format(country, format_string, field_ids)
VALUES
('USA', '%s\n%s %s %s', ARRAY[(SELECT attnum FROM pg_attribute WHERE attrelid='tb_address'::regclass AND attname='street'),
                               (SELECT attnum FROM pg_attribute WHERE attrelid='tb_address'::regclass AND attname='city'),
                               (SELECT attnum FROM pg_attribute WHERE attrelid='tb_address'::regclass AND attname='state'),
                               (SELECT attnum FROM pg_attribute WHERE attrelid='tb_address'::regclass AND attname='postal_code')]),
('Germany', '%s\n%s %s\n\n%s', ARRAY[(SELECT attnum FROM pg_attribute WHERE attrelid='tb_address'::regclass AND attname='street'),
                                     (SELECT attnum FROM pg_attribute WHERE attrelid='tb_address'::regclass AND attname='postal_code'),
                                     (SELECT attnum FROM pg_attribute WHERE attrelid='tb_address'::regclass AND attname='city'),
                                     (SELECT attnum FROM pg_attribute WHERE attrelid='tb_address'::regclass AND attname='country')]);

2. 拆分格式为原子化的地址元素模板 #

如果各国地址格式差异较大，可将每个地址元素的显示规则（是否显示、前缀/后缀、分隔符）单独存储，替代整体的format_string。比如创建tb_address_element_format表：

CREATE TABLE tb_address_element_format(
    country varchar NOT NULL,
    element_order int NOT NULL,
    field_name varchar NOT NULL,
    prefix varchar DEFAULT '',
    suffix varchar DEFAULT '',
    separator varchar DEFAULT '\n',
    PRIMARY KEY (country, element_order)
);

插入美国地址配置示例：

INSERT INTO tb_address_element_format(country, element_order, field_name, suffix)
VALUES
('USA', 1, 'street', ''),
('USA', 2, 'city', ' '),
('USA', 3, 'state', ' '),
('USA', 4, 'postal_code', '\nUSA');

这种设计灵活性更高，便于调整单个元素的显示规则，无需修改整个格式字符串。

3. 增加格式验证约束 #

在tb_address_format表中添加约束，确保format_string的占位符数量与字段数组的元素数量一致：

ALTER TABLE tb_address_format
ADD CONSTRAINT check_placeholder_count
CHECK (regexp_count(format_string, '%s') = array_length(field_ids, 1));

避免因占位符和字段数量不匹配导致格式化失败。

二、安全性提升措施 #

1. 规避动态SQL注入风险 #

如果直接通过存储的字段名拼接SQL生成地址，易引发注入问题。建议使用参数化查询或PostgreSQL的format函数结合动态参数：

-- 示例：通过字段ID数组动态获取字段值并格式化
CREATE OR REPLACE FUNCTION get_formatted_address(address_id int)
RETURNS varchar AS $$
DECLARE
    addr_record tb_address%ROWTYPE;
    format_rec tb_address_format%ROWTYPE;
    field_values text[];
    formatted_addr varchar;
BEGIN
    -- 获取地址记录
    SELECT * INTO addr_record FROM tb_address WHERE id = address_id;
    -- 获取对应国家的格式配置
    SELECT * INTO format_rec FROM tb_address_format WHERE country = addr_record.country;
    
    -- 根据字段ID数组提取对应字段的值
    field_values := ARRAY(
        SELECT (addr_record).*::text[]
        UNNEST(format_rec.field_ids) AS attnum
        JOIN pg_attribute ON attnum = pg_attribute.attnum
        WHERE pg_attribute.attrelid = 'tb_address'::regclass
    );
    
    -- 格式化地址
    formatted_addr := format(format_rec.format_string, field_values[1], field_values[2], field_values[3], field_values[4]);
    RETURN formatted_addr;
END;
$$ LANGUAGE plpgsql STABLE;

2. 限制格式配置的权限 #

• 仅允许管理员角色修改tb_address_format表，普通用户仅拥有查询权限，防止恶意修改配置。
• 对format_string进行内容校验，禁止包含特殊字符或风险内容，仅允许%s、换行符、空格、常见标点符号。

3. 处理空值的安全策略 #

格式化时自动过滤空值字段，避免出现多余分隔符或空白行：

-- 过滤空值
field_values := array_remove(field_values, '');
-- 重新生成匹配的格式字符串（移除对应数量的%s）
format_rec.format_string := regexp_replace(format_rec.format_string, '%s', '', array_length(format_rec.field_ids, 1) - array_length(field_values, 1));

内容的提问来源于stack exchange，提问作者Lionel Hamayon