bigywater.tech
Java · 368

SQLXML:在 JDBC 中处理 XML 数据

SQLXML 看起来只是把字符串包一层,但驱动的差别、校验的缺失与解析时的外部实体问题都值得提前知道。这篇用真实数据库逐项验证,并检查从数据库读出来的 XML 在客户端解析时是否安全。

/etc/hosts 被读到
getSource(StreamSource) 交给 JDK 默认 TransformerFactory 后外部实体被解析,输出含本机文件内容(DOM / SAX 来源被驱动保护)
缺
MySQL 上用 SAXResult 写入 sax,读回只有 “sax”
2200N vs 全部接受
PG 的 xml 列拒绝格式错误的 XML,MySQL 的 longtext 列什么都接受

写入与一次性约束

实测 · 本机执行输出 
  ── PostgreSQL(xml 列)
  createSQLXML:org.postgresql.jdbc.PgSQLXML
  setString 后再 setString:PSQLException(55000): This SQLXML object has already been initialized, so you cannot manipulate it further.
  setString → PreparedStatement.setSQLXML → insert:1 行
  getSQLXML().getString() 读回:“<!-- c --><root a="1" b="2"><![CDATA[<raw & text>]]><item>中文 &amp; é</item></root>”
  getString(columnIndex) 读 xml 列:“<!-- c --><root a="1" b="2"><![CDATA[<raw & text>]]><item>中文 &amp; é</item></root>”;getObject 类型 PgSQLXML;metadata 类型 xml/2009
  同一个 SQLXML 读两次 getString:两次都成功;相同? true
  getString 之后再 getBinaryStream / getCharacterStream / getSource:getBinaryStream → 82;getCharacterStream → 60;getSource(DOMSource) → #document
  先 getBinaryStream 读完,再 getString:82 字符
  free() 之后 getString:PSQLException(55000): This SQLXML object has already been freed.
  getSource(null) 的默认类型:DOMSource
  getSource(DOMSource.class):DOMSource
  getSource(SAXSource.class):SAXSource
  getSource(StAXSource.class):StAXSource
  getSource(StreamSource.class):StreamSource
  setResult(DOMResult) 写入:读回 “<?xml version="1.0" standalone="no"?><built k="v">hello</built>”
  setResult(StreamResult) 写入:读回 “<w a="1"><q/></w>”
  setResult(SAXResult) 写入:读回 “<s>sax</s>”
  setResult 之后再 setResult:PSQLException(55000): This SQLXML object has already been initialized, so you cannot manipulate it further.
  没有写任何内容就 setSQLXML:PSQLException(55000): This SQLXML object has not been initialized, so you cannot retrieve data from it.
  setString(null) / setSQLXML(null):1 行,读回 getSQLXML = null(wasNull true)
  ── MySQL(longtext 列)
  createSQLXML:com.mysql.cj.jdbc.MysqlSQLXML
  setString 后再 setString:成功,getString = <b/>
  setString → PreparedStatement.setSQLXML → insert:1 行
  getSQLXML().getString() 读回:“<?xml version="1.0" encoding="UTF-8"?><!-- c --><root a="1" b="2"><![CDATA[<raw & text>]]><item>中文 &amp; é</item></root>”
  getString(columnIndex) 读 xml 列:“<?xml version="1.0" encoding="UTF-8"?><!-- c --><root a="1" b="2"><![CDATA[<raw & text>]]><item>中文 &amp; é</item></root>”;getObject 类型 String;metadata 类型 LONGTEXT/-1
  同一个 SQLXML 读两次 getString:两次都成功;相同? true
  getString 之后再 getBinaryStream / getCharacterStream / getSource:getBinaryStream → 120;getCharacterStream → 60;getSource(DOMSource) → #document
  先 getBinaryStream 读完,再 getString:120 字符
  free() 之后 getString:SQLException(null): SQLXMLInstance has been free()d
  getSource(null) 的默认类型:SAXSource
  getSource(DOMSource.class):DOMSource
  getSource(SAXSource.class):SAXSource
  getSource(StAXSource.class):NullPointerException: Cannot invoke "javax.xml.stream.XMLInputFactory.createXMLStreamReader(java.io.Reader)" because "this.inputFactory" is null
  getSource(StreamSource.class):StreamSource
  setResult(DOMResult) 写入:读回 “<?xml version="1.0" encoding="UTF-8" standalone="no"?><built k="v">hello</built>”
  setResult(StreamResult) 写入:读回 “<w a="1"><q/></w>”
  setResult(SAXResult) 写入:读回 “<?xml version='1.0' encoding='UTF-8'?><s>sax”
  setResult 之后再 setResult:SQLException(S1009): Can't perform requested operation after getResult() has been called to write XML data
  没有写任何内容就 setSQLXML:NullPointerException: Cannot invoke "com.mysql.cj.jdbc.result.ResultSetInternalMethods.getCharacterStream(int)" because "this.owningResultSet" is null
  setString(null) / setSQLXML(null):1 行,读回 非 null

校验:什么会被接受

实测 · 本机执行输出 
  ── PostgreSQL(xml 列)
      格式错误(未闭合标签)            → PSQLException(2200N): ERROR: invalid XML content
      多个根元素                  → 接受;读回 “<a/><b/>”
      空字符串                   → 接受;读回 “”
      纯文本                    → 接受;读回 “hello”
      非法字符                  → PSQLException(2200N): ERROR: invalid XML content
      重复属性                   → PSQLException(2200N): ERROR: invalid XML content
      未声明的实体                 → PSQLException(2200N): ERROR: invalid XML content
      编码声明与实际不符              → 接受;读回 “<a>中</a>”
      带 DOCTYPE 与内部实体        → 接受;读回 “<!DOCTYPE d [<!ENTITY e "EXPANDED">]><d>&e;</d>”
      XML 片段(多个顶层节点)         → 接受;读回 “text <b/> more”
      命名空间前缀未声明              → 接受;读回 “<p:a/>”
  用 ps.setString 直接给 xml 列传字符串(不用 SQLXML):PSQLException(42804): ERROR: column "doc" is of type xml but expression is of type character varying
  用 ps.setObject(1, "<a/>", Types.SQLXML):成功
  用 cast(? as xml) 传字符串:成功
  PG 服务端 xml 类型的 XMLSERIALIZE 与 xpath:{x,y} | <a  b="1" ><c/></a>
  ── MySQL(longtext 列)
      格式错误(未闭合标签)            → 接受;读回 “<a><b></a>”
      多个根元素                  → 接受;读回 “<a/><b/>”
      空字符串                   → 接受;读回 “”
      纯文本                    → 接受;读回 “hello”
      非法字符                  → 接受;读回 “<a></a>”
      重复属性                   → 接受;读回 “<a x="1" x="2"/>”
      未声明的实体                 → 接受;读回 “<a>&nope;</a>”
      编码声明与实际不符              → 接受;读回 “<?xml version="1.0" encoding="ISO-8859-1"?><a>中</a>”
      带 DOCTYPE 与内部实体        → 接受;读回 “<!DOCTYPE d [<!ENTITY e "EXPANDED">]><d>&e;</d>”
      XML 片段(多个顶层节点)         → 接受;读回 “text <b/> more”
      命名空间前缀未声明              → 接受;读回 “<p:a/>”

外部实体与实体膨胀

实测 · 本机执行输出 
  ── PostgreSQL(xml 列)
  恶意文档:带 SYSTEM 外部实体指向 file:///etc/hosts
  存入列:成功
  读回 getString:“<!DOCTYPE d [<!ENTITY x SYSTEM "file:///etc/hosts">]><d>&x;</d>”
  getSource(DOMSource) 并解析:PSQLException(22000): Unable to decode xml data.
  getSource(SAXSource) 并解析:TransformerException: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 10; DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
  getSource(StAXSource) 并解析:TransformerException: javax.xml.stream.XMLStreamException: ParseError at [row,col]:[1,61]
  getSource(StreamSource) 并解析:解析成功;输出 “<?xml version="1.0" encoding="UTF-8"?><d>## # Host Database # # localhost is used to configure the loopback interface # when the system is booting. Do not change this entry. ## 127.0.0.1 localhost 255…(共 245 字符)”;含 localhost?true
  getSource(DOMSource) 拿到的 Node 再取文本:PSQLException(22000): Unable to decode xml data.
  用 JDK 默认的 DocumentBuilder 自己解析同一个字符串:解析成功;textContent 含 localhost?true
  同样的字符串,对照 disallow-doctype-decl=true 的解析器:SAXParseException: DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
  billion laughs(内部实体指数膨胀 10 层 × 10):PSQLException(2200N): ERROR: invalid XML content;客户端 getSource(DOMSource):PSQLException(22000): Unable to decode xml data.
[Fatal Error] :1:31: DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
  ── MySQL(longtext 列)
  恶意文档:带 SYSTEM 外部实体指向 file:///etc/hosts
  存入列:成功
  读回 getString:“<?xml version="1.0"?><!DOCTYPE d [<!ENTITY x SYSTEM "file:///etc/hosts">]><d>&x;</d>”
  getSource(DOMSource) 并解析:SQLException(S1009): DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
  getSource(SAXSource) 并解析:TransformerException: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 31; DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
  getSource(StAXSource) 并解析:NullPointerException: Cannot invoke "javax.xml.stream.XMLInputFactory.createXMLStreamReader(java.io.Reader)" because "this.inputFactory" is null
  getSource(StreamSource) 并解析:解析成功;输出 “<?xml version="1.0" encoding="UTF-8"?><d>## # Host Database # # localhost is used to configure the loopback interface # when the system is booting. Do not change this entry. ## 127.0.0.1 localhost 255…(共 245 字符)”;含 localhost?true
  getSource(DOMSource) 拿到的 Node 再取文本:SQLException(S1009): DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
  用 JDK 默认的 DocumentBuilder 自己解析同一个字符串:解析成功;textContent 含 localhost?true
  同样的字符串,对照 disallow-doctype-decl=true 的解析器:SAXParseException: DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
  billion laughs(内部实体指数膨胀 10 层 × 10):存入成功;客户端 getSource(DOMSource):SQLException(S1009): DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
[Fatal Error] :1:31: DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
[Fatal Error] :1:31: DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
[Fatal Error] :1:31: DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
[Fatal Error] :1:31: DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
[Fatal Error] :1:31: DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.

大文档

实测 · 本机执行输出 
  ── PostgreSQL(xml 列)
  构造的文档 7009 KB(20 万个元素)
  写入 setSQLXML 99 ms;写入 setObject(…, SQLXML) 91 ms;读 getString 11 ms(7177793 字符);读 getCharacterStream 全部读完 12 ms(7177793 字符);getSource(DOMSource) 构造 DOM 45 ms
  ── MySQL(longtext 列)
  构造的文档 7009 KB(20 万个元素)
  写入 setSQLXML 91 ms;写入 setObject(…, SQLXML) 65 ms;读 getString 5 ms(7177793 字符);读 getCharacterStream 全部读完 4 ms(7177793 字符);getSource(DOMSource) 构造 DOM 45 ms

分步演示

SQLXML
本步的对象

    本文用到的工具与边界

    • 本机 macOS 27 arm64,JDK 21.0.12;PostgreSQL 16.15 与 MySQL 26.7.0(自己初始化的隔离实例,端口 9501 / 9601);驱动 PostgreSQL JDBC 42.7.4、MySQL Connector/J 9.1.0;外部实体实验只读取本机公开的 /etc/hosts
    • 没有验证:Oracle XMLType / SQL Server xml 类型的 SQLXML、PG 的 xpath / xmltable 服务端查询性能、StAX(XMLInputFactory)默认设置对外部实体的处理、JDK 对 jdk.xml.* 系统属性(如 jdk.xml.entityExpansionLimit)的限制效果、PG 去掉 XML 声明与 encoding 字符集转换的完整规则
    京ICP备2026067502号-1京公网安备11010502063487号