在没有字符串连接的情况下动态组装PostgreSQL jsonb查询的方法 [英] Approaches to dynamically assemble PostgreSQL jsonb queries without string concatenation
问题描述
我想从Java/Spring Boot应用程序查询PostgreSQL数据库中的 jsonb 列.
I would like to query a jsonb column in a PostgreSQL databse from a Java / Spring Boot application.
尽管所有文档都具有相同的结构，但是jsonb文档的结构是事先未知的.数据库表中描述了jsonb列中文档的结构.
The structure of the jsonb documents is not known beforehand, although all the documents will have the same structure. The structure of the documents in the jsonb column is described in database tables.
应用程序从数据库获取jsonb文档结构描述.下一步是对jsonb数据运行分析查询.
The application obtains the jsonb document structure description from the database. The next step is running analytical queries on the jsonb data.
分析查询必须动态组装，因为在开发时尚不知道jsonb数据的结构. 如何在Java代码中创建查询而无需手动连接字符串?
The analytical queries have to be assembled dynamically as the structure of the jsonb data is not known at the development time. How could the queries be created in the Java code without concatenating strings manually?
恐怕手动组装查询会很复杂，不可读，并且可能会产生SQLi漏洞.
I am afraid that assembling the queries manually would be complex, unreadable and potentially create SQLi vulnerabilities.
推荐答案
您不能.如果需要从存储在其他表中的元数据动态构建SQL查询，则必须使用字符串连接构建SQL.
You can't. If you need to dynamically build a SQL query from meta-data stored in other tables, then you have to build the SQL using string concatenation.
我为您意识到并关注SQL注入漏洞而表示赞赏，但这仅是在使用用户提供的值时需要考虑的问题.
I applaud you for being aware and concerned about SQL Injection vulnerabilities, however that is only a concern when using user-supplied values.
如果保证该元数据是安全的，则可以安全地使用该元数据动态构建SQL语句.如果元数据是由用户输入的，即由用户提供，则有三种选择:
If the meta-data is guaranteed to be safe, then you can safely use that meta-data to dynamically build a SQL statement. If the meta-data is entered by a user, i.e. it is user-supplied, you have three choice:
-
在输入时验证元数据，例如确保字段名称是有效名称.
Validate meta-data on entry, e.g. ensure that field names are valid names.
在构建SQL时对元数据进行编码.这意味着引用字段名称.如果元数据包含值(例如，用于过滤)，则应将值作为?参数标记插入到SQL中，并将实际值提供给PreparedStatement对象.
Encode meta-data when building SQL. This means quoting field names. If meta-data includes values (e.g. for filtering), values should be inserted into SQL as ? parameter markers and the actual values given to the PreparedStatement object.
以上两者.双重保护是一件好事.
Both of the above. Double-guarding is a good thing.
这篇关于在没有字符串连接的情况下动态组装PostgreSQL jsonb查询的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
