如何安全识别Oracle数据库中BLOB字段的内容类型？

以下是几种只读、无风险的识别方法，完全不会修改数据库数据：

1. 基于字符编码特征的文本识别 #

文本类内容（如UTF-8、GBK编码）有固定的字节规律，非文本内容（图片、文档）则包含大量不可打印或无规律的二进制字节。可以通过读取BLOB前若干字节判断：

• UTF-8文本常以EF BB BF的BOM开头；
• ASCII文本的字节范围多集中在0x20-0x7E（可打印字符），很少包含空字节（0x00）。

示例SQL：

-- 筛选含UTF-8 BOM的BLOB字段
SELECT utc.table_name, utc.column_name
FROM user_tab_columns utc
WHERE utc.data_type = 'BLOB'
AND EXISTS (
    SELECT 1
    FROM your_table t
    WHERE UTL_RAW.SUBSTR(t.your_blob_col, 1, 3) = 'EFBBBF'
);

-- 排除含空字节的非文本BLOB，剩余大概率为文本
SELECT utc.table_name, utc.column_name
FROM user_tab_columns utc
WHERE utc.data_type = 'BLOB'
AND EXISTS (
    SELECT 1
    FROM your_table t
    WHERE DBMS_LOB.GETLENGTH(t.your_blob_col) > 0
    AND UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.SUBSTR(t.your_blob_col, 1, 100)) NOT LIKE '%' || CHR(0) || '%'
);

2. 查找文本专属特征标记 #

文本内容通常包含特定的标记（如空格、JSON的{、HTML的<>），而非文本内容极少出现连续的这类标记。

示例SQL：

-- 筛选包含文本特征标记的BLOB字段
SELECT utc.table_name, utc.column_name
FROM user_tab_columns utc
WHERE utc.data_type = 'BLOB'
AND EXISTS (
    SELECT 1
    FROM your_table t
    WHERE DBMS_LOB.GETLENGTH(t.your_blob_col) > 0
    AND (
        DBMS_LOB.INSTR(t.your_blob_col, UTL_RAW.CAST_TO_RAW(' ')) > 0 -- 空格
        OR DBMS_LOB.INSTR(t.your_blob_col, UTL_RAW.CAST_TO_RAW('{')) > 0 -- JSON
        OR DBMS_LOB.INSTR(t.your_blob_col, UTL_RAW.CAST_TO_RAW('<')) > 0 -- HTML/XML
    )
);

3. 基于文件头的非文本反向筛选 #

图片、文档等非文本内容有固定的文件头标识，反向排除这些BLOB后，剩余的即为疑似文本内容：

• JPG: FF D8 FF
• PNG: 89 50 4E 47
• PDF: 25 50 44 46

示例SQL：

-- 筛选非图片/文档的BLOB（疑似文本）
SELECT utc.table_name, utc.column_name
FROM user_tab_columns utc
WHERE utc.data_type = 'BLOB'
AND NOT EXISTS (
    SELECT 1
    FROM your_table t
    WHERE DBMS_LOB.GETLENGTH(t.your_blob_col) >= 4
    AND (
        UTL_RAW.SUBSTR(t.your_blob_col, 1, 3) = 'FFD8FF' -- JPG
        OR UTL_RAW.SUBSTR(t.your_blob_col, 1, 4) = '89504E47' -- PNG
        OR UTL_RAW.SUBSTR(t.your_blob_col, 1, 4) = '25504446' -- PDF
    )
);

4. 抽样验证确认 #

对上述方法筛选出的疑似文本BLOB，抽样转换为字符串查看内容，进一步确认：

示例SQL：

-- 抽样转换BLOB为字符串（取前200字节）
SELECT DBMS_LOB.SUBSTR(t.your_blob_col, 200) AS sample_text
FROM your_table t
WHERE DBMS_LOB.GETLENGTH(t.your_blob_col) > 0
AND UTL_RAW.SUBSTR(t.your_blob_col, 1, 3) = 'EFBBBF' -- 疑似UTF-8文本
FETCH FIRST 10 ROWS ONLY;

注意事项 #

• 所有操作均为只读，不会修改数据库数据，完全安全；
• 针对超大BLOB，仅读取前N字节检查即可，避免性能损耗；
• 加密文本、压缩文本可能被误判，需结合业务场景进一步验证。

内容的提问来源于stack exchange，提问作者marciel.deg