EMQX Oracle 数据桥接安全加固：Action SQL 只解析不执行、拒绝 DDL/DCL/TCL 与大文本绑定支持

后端物联网消息队列通信【免费下载链接】emqxThe most scalable and reliable MQTT broker for AI, IoT, IIoT and connected vehicles项目地址https://gitcode.com/gh_mirrors/em/emqx点击查看免费下载导读本文围绕 EMQX 仓库中 Oracle 数据库桥接emqx_bridge_oracle/emqx_oracle的一项关键修复展开prepare/status 检查改为只解析、不执行用户 Action SQL并拒绝顶层 DDL/DCL/TCL 语句同时改进超过 4000 字节文本 payload 的绑定支持。读完本文你将理解 EMQX 在创建 Oracle Action 时如何安全地校验 SQL 模板既不触发数据变更又能提前发现表不存在等错误以及如何让大体积 MQTT 消息文本顺利写入 Oracle NCLOB 列。全文以 changes/ee/fix-17605.en.md 为骨架结合仓库源码与测试用例给出可验证的实现细节。一、问题背景Oracle Action 的 SQL 模板与 Prepare 阶段EMQX 规则引擎通过数据桥接将 MQTT 消息写入 Oracle 时Action 的核心配置是 SQL 模板sql字段。该模板使用${topic}、${payload}等占位符运行时由emqx_placeholder:preproc_sql/2将模板预处理为带命名绑定的预编译语句再通过 jamdb_oracle 驱动以绑定参数方式执行见 emqx_oracle.erl 的parse_prepare_sql与init_prepare。修复前存在两个痛点prepare/status 检查会真正执行用户 SQL为验证模板可解析、目标表存在旧实现可能直接执行语句带来副作用风险如果模板被误写成CREATE TABLE、DROP TABLE等语句一旦准备阶段执行将直接修改数据库结构。Oracle 文本绑定存在 4000 字节上限问题Oracle 的VARCHAR2绑定默认上限为 4000 字节超过该长度的文本 payload例如较大的 JSON 消息体在绑定阶段会失败导致大消息无法入库。本次修复issue fix-17605同时解决了这两类问题其成果集中在两个层面emqx_oracle连接器驱动层新增解析式预检 语句类型白名单机制emqx_bridge_oracle桥接测试层新增针对不执行用户 SQL拒绝 DDL大 payload的 CT 用例。二、核心修复一prepare/status 检查改为只解析、不执行2.1 用dbms_sql.parse匿名块替代直接执行在 emqx_oracle.erl 中所有 prepare 路径连接池建立、channel 添加、健康检查都收敛到同一个入口check_if_table_exists(Conn, SQL, _Tokens0) - case sql_has_parse_side_effect(SQL) of true - {error, unsupported_sql_statement}; false - check_if_sql_parseable(Conn, SQL, 1) end.其中check_if_sql_parseable/3源码位置构造了一段 PL/SQL 匿名块借助 Oracle 内置包dbms_sql的parse过程只做语法解析而不执行语句declare c integer; begin c : dbms_sql.open_cursor; dbms_sql.parse(c, :1, dbms_sql.native); dbms_sql.close_cursor(c); exception when others then if dbms_sql.is_open(c) then dbms_sql.close_cursor(c); end if; raise; end;调用时把用户 SQL 原文作为唯一绑定参数:1传入{ParseSQL, [binary_to_list(SQL)]}。这样解析成功返回{ok, [{proc_result, 0, _}]}即ok模板可安全使用解析失败proc_result返回 Oracle 错误码如 904/942/1013 等进入错误归类逻辑详见第四节。从实现看这套预检在prepare_sql_to_conn/4源码中被每个连接调用并在连接池注册为 reconnect callback因此连接重建后依然会重新校验。2.2 测试如何证明没有执行仓库用两条测试用例从正反两面验证只解析、不执行正面验证执行了就会留下痕迹t_prepare_does_not_execute_user_sql 先在 Oracle 上创建一个带BEFORE INSERT触发器sql_create_probe_trigger见 SUITE 第 310-318 行和一张审计表mqtt_probe_audit触发器在向mqtt_test表 INSERT 时以自治事务向审计表写入一条prepare标记。随后正常创建 connector 与 action 并触发 status 检查最后断言mqtt_probe_audit行数为0—— 证明 prepare 阶段从未真正执行 INSERT。反向验证拒绝 DDL 且不建表t_prepare_rejects_ddl_without_executing 将CREATE TABLE mqtt_prepare_probe (id NUMBER)作为 Action SQL 提交断言该表并未被创建count_user_table(..., MQTT_PREPARE_PROBE)为 0同时轮询 Action 状态接口最终status_reason中应包含unsupported_sql_statement。三、核心修复二拒绝顶层 DDL/DCL/TCL 语句3.1 词法级语句类型检查check_if_table_exists先调用sql_has_parse_side_effect/1源码做一次纯本地词法检查提取 SQL 的首个有效 token与一组有解析副作用的语句关键字比对命中即直接返回{error, unsupported_sql_statement}根本不会发起网络请求分类被拒绝的关键字DDL数据定义语言alter、create、drop、truncate、rename、comment、flashback、purge、analyze、associate、disassociate、audit、noauditDCL数据控制语言grant、revoke、lockTCL事务控制语言commit、rollback、savepoint、set其他explainEXPLAIN PLAN对应的错误消息宏定义在 emqx_oracle.erl 第 14-20 行-define(UNSUPPORTED_SQL_STATEMENT_MSG, unsupported_sql_statement: DDL, DCL, and transaction control statements are not supported in Oracle Action SQL templates. ).3.2 token 提取对注释与空白是健壮的first_sql_token/1及其辅助函数源码在取首个 token 前会跳过空白字符空格、\t、\n、\r、\f、\v行注释-- ...块注释/* ... */。token 统一转小写比对因此CREATE、Create、create都会被识别。仓库内置的 eunit 测试first_sql_token_test_第 788-798 行覆盖了这些场景例如-- comment\nSELECT 1 FROM dual会正确解析出select/* unterminated comment解析为空 token。3.3 白名单之外合法 Action SQL 仍然放行sql_has_parse_side_effect_test_第 742-786 行明确列出被放行的常规语句类型即 Action 模板的合法范围INSERT INTO ... VALUES(:1, :2)EMQX 默认模板即 INSERTUPDATE ... SET ... WHERE ...DELETE FROM ... WHERE ...MERGE INTO ... USING dual ON ...SELECT ... WHERE ...与WITH ... SELECT ...匿名 PL/SQL 块BEGIN NULL; END;用于存储过程调用场景对应 SUITE 中的sql_stored_procedure_template。四、错误归类与健康状态机unsupported_sql_statement / undefined_table预检的错误需要映射为桥接的健康状态相关逻辑集中在 on_get_channel_status 与do_check_prepares/3第 373-414 行预检结果Action 健康状态状态说明okconnected模板可解析、目标表存在{error, undefined_table}disconnected{unhealthy_target, Oracle table is invalid. Please check if the table exists...}表/视图不存在或标识符无效{error, unsupported_sql_statement}disconnected{unhealthy_target, unsupported_sql_statement: DDL, DCL, and transaction control statements are not supported...}顶层语句类型不被允许其他错误connecting暂时性失败等待下一次健康检查do_check_prepares会遍历连接池中的每个 worker 连接逐一用check_if_table_exists验证因此即使部分连接异常也能被捕获。check_if_sql_parseable/3对 Oracle 返回码的归类规则第 578-599 行与handle_parse_error_description/1第 601-611 行ORA-00904: invalid identifier→{error, undefined_table}列名写错如测试中的retainxORA-00942: table or view does not exist→{error, undefined_table}ORA-01013: user requested cancel of current operation→ 视为可重试重试一次其他 ORA 错误 → 原样返回描述作为其他错误处理。错误码解析使用正则ORA-([0-9])从描述文本中提取oracle_error_codes/1第 613-624 行。此外针对noproc连接进程重启竞态见源码中的Note [jamdb oracle race condition]与socket closed两类瞬时故障check_if_sql_parseable会带重试计数自动重试一次。五、大文本 payload超过 4000 字节的绑定支持Oracle 传统VARCHAR2绑定参数存在 4000 字节上限而 MQTT 消息 payload 完全可能超过该长度。本次修复同时改善了文本型 payload 的写入支持条件与效果如下条件payload 占位符${payload}位于 SQL 模板的最后一个绑定参数位置效果超过 4000 字节的文本 payload 可以正常绑定并写入。仓库在桥接层将 payload 列设计为NCLOB见测试建表语句 第 194-195 行payload NCLOB以及 第 242-251 行 的t_mqtt_msgs表NCLOB 用于承载大文本。测试用例给出可直接验证的证据链t_probe_with_large_value 使用模板INSERT INTO mqtt_test(topic, msgid, payload, retain) VALUES (${topic}, ${id}, ${payload}, ${id})${payload}为最后一个绑定参数执行 probe预期返回 204成功证明大值在预检阶段即可通过t_eec_1322_large_payload_after_small_payload 先写入小 payload{msg:heelo}再写入large_json_payload()—— 即binary:copy(a, 5000)生成的 5000 字节文本经 JSON 编码后的 payload第 513-514 行断言两行均成功落库、Action 状态保持connected且 trace 中无oracle_connector_query_return错误事件。六、配置与使用如何应用这些修复6.1 Action 的 SQL 模板配置Oracle Action 的sql参数在 emqx_bridge_oracle.erl 中定义类型为emqx_schema:template()默认值insert into t_mqtt_msgs(msgid, topic, qos, payload) values (${id}, ${topic}, ${qos}, ${payload})schema 同时给出 Action 级批量参数默认值第 131-135 行batch_size默认 100、batch_time默认100ms与 CT 用例中的?with_batch矩阵batch_size100, batch_time200ms对应。6.2 Connector 连接配置要点连接器配置项定义于 emqx_oracle_schema.erl配置项类型说明serverhost:port默认端口 1521emqx_oracle:oracle_host_options/0sidbinary可选Oracle SID与service_name至少填一个service_namebinary可选Oracle 服务名与sid至少填一个rolenormal/sysdba连接角色默认normalusername/passwordstring必填测试默认system/oraclepool_sizeinteger连接池大小默认 8见?DEFAULT_POOL_SIZEemqx_bridge_oracle的config_validator/1第 194-203 行会在sid与service_name都缺失时返回校验错误neither SID nor Service Name was set对应测试 t_no_sid_nor_service_name。6.3 编写 Action SQL 模板的实践建议综合本次修复编写 Oracle Action SQL 时应遵循只使用 DMLINSERT/UPDATE/DELETE/MERGE或匿名 PL/SQL 块禁止在模板顶层出现CREATE、DROP、TRUNCATE、GRANT、COMMIT等 DDL/DCL/TCL 语句 —— 否则 Action 将处于disconnected状态status_reason显示unsupported_sql_statement目标表必须在创建 Action 前建好否则预检报ORA-00942状态显示目标表无效unhealthy_target超过 4000 字节的文本 payload建议将${payload}放在 SQL 模板的最后一个绑定参数位置并将目标列设计为NCLOB以获得最佳兼容性模板中的占位符支持嵌套取值如${payload.msg}可配合emqx_placeholder预处理字段缺失时绑定为NULL见 t_message_with_null_value。七、相关源码与测试索引如需深入阅读可关注以下仓库文件apps/emqx_oracle/src/emqx_oracle.erl核心实现含check_if_table_exists、check_if_sql_parseable、sql_has_parse_side_effect、do_check_prepares、on_get_channel_status以及-ifdef(TEST)内的全套 eunit 用例apps/emqx_oracle/src/emqx_oracle_schema.erl连接器 HOCON schemaserver/sid/service_name/role 等apps/emqx_bridge_oracle/src/emqx_bridge_oracle.erlOracle Action 与 Connector 的桥接 API schema、默认 SQL 模板与参数校验apps/emqx_bridge_oracle/test/emqx_bridge_oracle_SUITE.erlCT 测试覆盖prepare 不执行用户 SQL拒绝 DDL大 payload 顺序写入存储过程模板表被删除/缺失空值/嵌套 token等场景。结语fix-17605 这项修复让 EMQX 的 Oracle 数据桥接在SQL 模板安全预检与大文本写入两个维度上更加稳健预检阶段通过dbms_sql.parse匿名块做到只解析、不执行配合首 token 词法检查从源头拒绝 DDL/DCL/TCL再通过 ORA 错误码归类驱动健康状态机使问题在规则真正触发前即可被发现而对超过 4000 字节文本 payload 的绑定支持则让大体积 MQTT 消息能够可靠地落库到 Oracle NCLOB 列。上述行为均有源码与测试用例可查证可直接作为排查 Oracle 桥接状态异常unsupported_sql_statement、unhealthy_target的参考依据。赞分享后端物联网消息队列通信【免费下载链接】emqxThe most scalable and reliable MQTT broker for AI, IoT, IIoT and connected vehicles项目地址https://gitcode.com/gh_mirrors/em/emqx点击查看免费下载相关推荐EMQX 5.1.0 版本深度解读连接保活、数据桥接与安全加固的全面升级EMQX 5.1.0 版本深度解读连接保活、数据桥接与安全加固的全面升级 导读 本文基于当前仓库中 changes/e5.1.0.en.md https://后端物联网消息队列通信EMQX Oracle 数据库连接器emqx_oracle深度解析连接管理、SQL 模板与数据桥接实战EMQX Oracle 数据库连接器emqx_oracle深度解析连接管理、SQL 模板与数据桥接实战 导读 本文围绕 EMQX 仓库中的 Oracle后端物联网消息队列通信PGlite 事务安全加固事务句柄在事务关闭后被拒绝执行PGlite 事务安全加固事务句柄在事务关闭后被拒绝执行 导读 本篇文章围绕 PGlite 一个重要的防御性变更展开 当事务Transaction已经结数据库嵌入式数据库WebAssembly上一篇5万Boids稳跑帧Unity ECS(DOTS)鱼群模拟完整指南下一篇Avail区块链状态同步工具比较性能评估创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考