Dropwizard+JDBI+Postgres中JSON路径双引号内参数绑定问题

问题根源 #

Postgres的SQL/JSON路径表达式本身是一个字符串字面量，Jdbi的参数绑定只能作用于SQL语句层面的占位符，无法直接替换JSON路径字符串内部双引号包裹的内容，这就导致硬编码正则能正常查询，但绑定参数时无法生效。

解决方案 #

方法1：SQL字符串拼接绑定参数（推荐，无SQL注入风险）

将JSON路径拆分为固定部分和变量部分，通过Postgres的字符串拼接操作||把绑定参数插入到路径中，让Jdbi正常处理参数绑定：

try (Handle handle = jdbi.open()) {
    return handle.createQuery(
                    "select data from company where" +
                    " data @?? ('$.employees[*].assets.vehicle[*] ? (@.vehicleNumber like_regex \"' || :regex || '\")')"
            )
            .bind("regex", somRegex)
            .map(mapper)
            .collect(toList());
}

这里通过" || :regex || "的方式，把绑定的正则参数拼接到JSON路径字符串中，最终Postgres会生成完整合法的JSON路径表达式。

方法2：预构造JSONPath字符串并绑定（需注意SQL注入风险）

如果你的正则参数来自可信来源，可以先构造完整的JSONPath字符串，再作为jsonpath类型绑定到SQL中：

try (Handle handle = jdbi.open()) {
    // 仅当somRegex可信时使用，避免SQL注入
    String jsonPath = String.format("$.employees[*].assets.vehicle[*] ? (@.vehicleNumber like_regex \"%s\")", somRegex);
    return handle.createQuery(
                    "select data from company where data @?? :jsonPath"
            )
            .bindByType("jsonPath", jsonPath, Types.OTHER) // 指定类型为postgres的jsonpath
            .map(mapper)
            .collect(toList());
}

注意：如果正则参数是用户输入等不可信内容，这种方式会存在SQL注入风险，优先使用方法1。

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