如何安全优雅地动态构造SQL/JSON路径查询字符串

最优实现方案 #

你可以利用PostgreSQL jsonpath支持的变量注入能力实现完全免手动转义的动态构造，只有键名需要用%I做标识符格式化，匹配值直接通过USING子句传入，完全规避字符串拼接带来的转义问题和注入风险：

核心写法示例

• 正则匹配场景

end_user.profile @? format('$.%I ? (@ like_regex $val flag "i")', condition.field)::jsonpath
  USING condition.value AS val;

• 普通等值匹配场景

end_user.profile @? format('$.%I ? (@ == $val)', condition.field)::jsonpath
  USING condition.value AS val;

完整函数封装

create function private.end_user_profile_filter(end_user private.end_user, condition private.profile_filter)
returns boolean as $$
select case
  when condition.condition = 'REGEX_MATCH'
    then end_user.profile @? format('$.%I ? (@ like_regex $val flag "i")', condition.field)::jsonpath
      using condition.value as val
  else
    end_user.profile @? format('$.%I ? (@ == $val)', condition.field)::jsonpath
      using condition.value as val
end;
$$ language sql stable;

方案优势

• 所有动态值由数据库原生处理转义，不存在注入风险，也不需要手动写任何转义逻辑
• 键名用%I做标识符格式化，自动处理含特殊字符的JSON键名场景
• 代码简洁易读，维护成本远低于手动转义方案

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