基于用户输入创建动态数据结构的最佳方案及SQL注入防护咨询

一、现有动态建表代码的安全性与规范问题 #

安全性（SQL注入防护） #

你的代码在注入防护上做得比较到位：

• tableName函数用parseInt(dialog_id)处理输入，确保最终表名仅包含前缀+数字，再用反引号包裹，完全避免了表名注入风险。
• generateColumnSQL函数对字段名做了严格过滤：截取第一个单词后移除所有非字母/数字/下划线的字符，还检查字段名不能以数字开头；字段类型仅从预定义的COLUMN_TYPES中取值，完全杜绝了用户输入直接拼接SQL的可能。只要COLUMN_TYPES里都是合法的SQL数据类型（比如INT, VARCHAR(255)这类），这部分不存在注入风险。

规范问题 #

动态建表的最大问题是可维护性差：

• 随着用户输入增多，数据库表数量会爆炸式增长，备份、迁移、索引优化都会变得异常麻烦。
• 每张表结构独立，无法统一做数据统计、批量查询，后期扩展功能的成本极高。

二、存储用户自定义结构数据的最佳方案 #

你提到的EAV（实体-属性-值）模式（单表存每行字段）确实存在数据类型单一、查询性能差、数据完整性难保障的问题，不推荐作为首选方案。以下是更优的替代方案：

1. 优先选择：JSON/JSONB类型存储（适配MySQL 5.7+/PostgreSQL） #

这是当前最主流的方案，平衡了灵活性、性能和开发成本：

• 只需要建一张统一的表，用JSON/JSONB字段存储用户自定义的所有字段和对应值，天然支持不同数据类型。
• 数据库支持JSON字段的查询、筛选、索引（PostgreSQL的JSONB支持GIN索引，MySQL可对特定JSON路径建索引）。

示例表结构（MySQL）：

CREATE TABLE user_custom_data (
  id INT PRIMARY KEY AUTO_INCREMENT,
  dialog_id INT NOT NULL COMMENT '关联的会话ID',
  custom_fields JSON NOT NULL COMMENT '用户自定义字段的键值对',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY idx_dialog_id (dialog_id) -- 确保每个会话对应一条数据
);

插入数据示例：

INSERT INTO user_custom_data (dialog_id, custom_fields)
VALUES (123, '{"username": "john_doe", "age": 30, "is_active": true, "join_date": "2024-01-01"}');

查询数据示例：

-- 提取指定字段
SELECT 
  custom_fields->>'$.username' AS username,
  custom_fields->>'$.age' AS age,
  custom_fields->>'$.is_active' AS is_active
FROM user_custom_data
WHERE dialog_id = 123;

-- 按JSON字段筛选
SELECT * FROM user_custom_data
WHERE custom_fields->>'$.age' > 25;

2. 强类型约束需求：模板表+多类型数据字段 #

如果业务需要严格的类型校验和数据完整性，可以采用“模板定义+多字段存储”的模式：

• 用一张表存储用户定义的字段结构（类型、是否必填等），另一张表用不同类型的字段存储对应值。

示例表结构：

-- 存储用户自定义的字段模板
CREATE TABLE custom_field_templates (
  id INT PRIMARY KEY AUTO_INCREMENT,
  dialog_id INT NOT NULL,
  field_name VARCHAR(100) NOT NULL COMMENT '字段名',
  field_type ENUM('INT', 'VARCHAR', 'BOOLEAN', 'DATE') NOT NULL COMMENT '字段类型',
  is_required BOOLEAN DEFAULT FALSE,
  UNIQUE KEY idx_dialog_field (dialog_id, field_name) -- 避免同会话重复字段
);

-- 存储用户自定义的数据
CREATE TABLE custom_field_data (
  id INT PRIMARY KEY AUTO_INCREMENT,
  dialog_id INT NOT NULL,
  field_name VARCHAR(100) NOT NULL,
  value_int INT COMMENT '存储整数类型值',
  value_str VARCHAR(255) COMMENT '存储字符串类型值',
  value_bool BOOLEAN COMMENT '存储布尔类型值',
  value_date DATE COMMENT '存储日期类型值',
  -- 关联模板表，确保字段合法
  FOREIGN KEY (dialog_id, field_name) REFERENCES custom_field_templates(dialog_id, field_name),
  UNIQUE KEY idx_dialog_field (dialog_id, field_name)
);

这种方案的优点是能严格控制数据类型和完整性，缺点是查询时需要根据字段类型判断取哪个值，代码逻辑会稍复杂。

3. 不推荐的方案 #

• 单纯EAV模式：如你所说，所有字段只能用同一类型存储，查询时需要大量聚合操作，性能极差，数据完整性无法保证。
• 动态建表：如你现有代码的做法，表数量膨胀后维护成本极高，仅适合极端小众场景。

总结 #

如果没有强类型约束的硬性要求，JSON/JSONB存储是最优选择，开发成本低、灵活性高、性能也能满足大部分场景；如果必须保证数据类型严格性，再考虑“模板表+多类型字段”的方案。

内容的提问来源于stack exchange，提问作者Ultimate Gobblement