如何防止用户修改查询中的外键？SQL资源归属验证内置方案

针对多层关联表结构下重复编写JOIN验证用户资源权限的繁琐问题，SQL没有直接的"一键验证"内置功能，但可以通过以下几种更优雅的方案解决，适用于SQLite及多数通用SQL环境：

1. 封装关联逻辑为视图（View） #

把每个资源与所属用户ID的关联逻辑预定义成视图，后续验证或查询时直接使用视图，避免重复写JOIN语句。

示例SQL： #

-- 建立帖子与用户关联的视图
CREATE VIEW PostWithUser AS
SELECT p.*, u.id AS owner_user_id
FROM posts p
JOIN users u ON p.user_id = u.id; -- 假设posts表存在user_id外键关联users.id

-- 建立段落与用户关联的视图（多层关联）
CREATE VIEW ParagraphWithUser AS
SELECT pa.*, u.id AS owner_user_id
FROM paragraphs pa
JOIN posts p ON pa.post_id = p.id
JOIN users u ON p.user_id = u.id;

验证使用方式： #

验证帖子归属时，直接查询视图：

SELECT owner_user_id FROM PostWithUser WHERE id = ?;

将返回的owner_user_id与当前用户ID对比即可。验证段落的逻辑同理，查询ParagraphWithUser视图即可。

2. 自定义标量函数封装验证逻辑 #

利用SQL的自定义函数功能，把验证逻辑封装成可复用的函数，调用时只需传入资源ID和用户ID，直接返回验证结果。

SQLite示例（需SQLite 3.31.0+支持CREATE FUNCTION，或通过应用层注册函数）： #

-- 验证帖子归属的函数
CREATE FUNCTION validate_post_owner(post_id INTEGER, user_id INTEGER)
RETURNS INTEGER
AS $$
SELECT EXISTS(
    SELECT 1
    FROM posts p
    WHERE p.id = post_id AND p.user_id = user_id
);
$$;

-- 验证段落归属的函数（多层关联）
CREATE FUNCTION validate_paragraph_owner(paragraph_id INTEGER, user_id INTEGER)
RETURNS INTEGER
AS $$
SELECT EXISTS(
    SELECT 1
    FROM paragraphs pa
    JOIN posts p ON pa.post_id = p.id
    WHERE pa.id = paragraph_id AND p.user_id = user_id
);
$$;

使用方式： #

调用函数直接获取验证结果（返回1表示合法，0表示不合法）：

SELECT validate_paragraph_owner(?, ?);

3. 直接在业务查询中嵌入权限过滤 #

不需要单独编写验证逻辑，而是把用户权限条件直接整合到读取、更新、删除的业务SQL中，一次完成权限校验和业务操作，减少查询次数。

示例： #

• 查询指定ID的帖子并验证权限：

SELECT * FROM posts WHERE id = ? AND user_id = ?;

如果返回结果为空，说明该用户无权操作此帖子。

• 查询指定ID的段落并验证权限：

SELECT pa.* FROM paragraphs pa
JOIN posts p ON pa.post_id = p.id
WHERE pa.id = ? AND p.user_id = ?;

• 更新帖子时的权限校验：

UPDATE posts SET text = ? WHERE id = ? AND user_id = ?;

只有当帖子属于当前用户时，更新操作才会生效。

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