在Blob表的触发器中计算Blob哈希值

Question

我的环境是Oracle 11g。我有一张名为T_IMG的表，其列包括一个图像blob、一个图像标识符和计算为SHA256的图像哈希值。我希望每次向T_IMG表插入或更新行时都能计算并插入图像哈希值。为此，我在T_IMG表上使用了一个before触发器来插入或更新，但我在触发器中访问图像blob列时遇到了困难。

CREATE TABLE TEST.T_IMG
(
    IDN       VARCHAR2(18 BYTE)      NOT NULL,
    IMG       BLOB                   NOT NULL,
    IMGHASH   VARCHAR2(128 BYTE)
);

对于字符串和二进制大对象（BLOB），SHA256哈希是通过以下代码计算的，该代码基于Sean Stuber在http://seanstuber.wordpress.com/2012/03/22/using-java-to-extend-dbms_crypto/上提供的示例。

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED sha2 AS
import java.security.MessageDigest;
import oracle.sql.*;
public class sha2
{
    public static oracle.sql.RAW get_digest_string( String p_string, int p_bits ) throws Exception
    {
        MessageDigest v_md = MessageDigest.getInstance( "SHA-" + p_bits );
        byte[] v_digest;
        v_digest = v_md.digest( p_string.getBytes( "UTF-8" ) );
        return RAW.newRAW(v_digest);
    }

    public static oracle.sql.RAW get_digest_blob( oracle.sql.BLOB p_blob, int p_bits ) throws Exception
    {
        byte[] allBytesInBlob;
        allBytesInBlob = p_blob.getBytes(1, (int) p_blob.length());
        MessageDigest v_md = MessageDigest.getInstance( "SHA-" + p_bits );
        byte[] v_digest = v_md.digest( allBytesInBlob );
        return RAW.newRAW(v_digest);
    }
}
/
CREATE OR REPLACE FUNCTION sha2_string(p_string in VARCHAR2, p_bits in number)
RETURN RAW AS LANGUAGE JAVA
NAME 'sha2.get_digest_string( java.lang.String, int ) return oracle.sql.RAW';
/
CREATE OR REPLACE FUNCTION sha2_blob(p_byte in BLOB, p_bits in number)
RETURN RAW AS LANGUAGE JAVA
NAME 'sha2.get_digest_blob( oracle.sql.BLOB, int ) return oracle.sql.RAW';
/

上述功能是有效的。测试它们，我得到了以下结果：

SELECT sha2_string('0123456789',256) FROM DUAL;
SELECT sha2_blob(utl_raw.cast_to_raw('0123456789'),256) FROM DUAL;

SHA2_STRING('0123456789',256)
-------------------------------------------------------------------------------- 84D89877F0D4041EFB6BF91A16F0248F2FD573E6AF05C19F96BEDB9F882F7882
选择了 1 行。

SHA2_BLOB(UTL_RAW.CAST_TO_RAW('0123456789'),256)
-------------------------------------------------------------------------------- 84D89877F0D4041EFB6BF91A16F0248F2FD573E6AF05C19F96BEDB9F882F7882
选择了 1 行。

我在 T_IMG 表上使用插入或更新前触发器来计算图像列的 SH256 哈希值。

CREATE OR REPLACE TRIGGER TEST.TR_IMG_UPSERT
BEFORE INSERT OR UPDATE
ON TEST.T_IMG

FOR EACH ROW
DECLARE 
    imghash varchar2(128);
    vblob blob;
BEGIN

    vblob := :new.IMG;

    IF(vblob IS NOT NULL)THEN
        /*-- this works
        imghash := sha2_string('0123456789',256); 
        :new.imghash := imghash; */

        /*-- this works too
        vblob := utl_raw.cast_to_raw('0123456789');
        imghash := sha2_blob(vblob,256);
        :new.imghash := imghash; */

        -- this DOES NOT work
        imghash := sha2_blob(vblob,256);
        :new.imghash := imghash;

    END IF;

END;
/

触发器本身是有效的；当它在模拟数据（制造的字符串或blob）上计算并插入哈希值时，但在真实图像blob数据上却抛出以下错误：

ORA-29532: Java调用由未捕获的Java异常终止：java.sql.SQLException: 无效的空lob操作
ORA-06512: 在"TEST.SHA2_BLOB"中
ORA-06512: 在"TEST.TR_IMG_UPSERT"中的第13行
ORA-04088: 执行触发器"TEST.TR_IMG_UPSERT"期间出错

看起来触发器将:new.IMG数据视为空blob。

为什么会这样，并且我如何解决每次在T_IMG表中插入或更新行时自动计算和插入图像哈希到T_IMG表的问题？

注意：哈希不能由客户端应用程序插入/更新数据行提供。

AdiM

- adim

---

这对我来说在11.2.0.3.0和12.1.0.1.0上都有效，使用插入语句insert into t_img(idn, img) values(1, utl_raw.cast_to_raw('0123456789'));。 - Jon Heller

@jonearles 我的Oracle引擎版本是11.2.0.1.0。我在触发器下面这一行代码遇到了问题：por_hash := sha2_blob(:new.IMG,256);，它涉及到读取:new.IMG列的内容。 - adim

我在Oracle支持中心找不到相关的错误，但我认为最近版本中已经修复了一个错误。尽管只是第四个数字，但11.2.0.1和11.2.0.3之间存在显着差异。首先，您应该在最新版本上尝试您的代码。如果仍然无法解决问题，则还应发布您正在使用的确切“INSERT”语句。 - Jon Heller

Answer 1

我在Oracle PL/SQL中执行了以下代码：

var idn varchar2(18 byte);
exec :idn := '0';

insert into t_img (idn, img) values (:idn, utl_raw.cast_to_raw('0123456789') );
commit;
select idn, dbms_lob.getlength(img) as length, imghash from t_img where idn=:idn;

update t_img set img=utl_raw.cast_to_raw('012345678901') where idn=:idn;
commit;
select idn, dbms_lob.getlength(img) as length, imghash from t_img where idn=:idn;

并且它起作用。

SQL*Plus：Release 11.2.0.1.0 Production于2013年8月31日星期六11:20:04

版权所有(c)1982, 2010，Oracle。保留所有权利。

已连接到：Oracle Database 11g Release 11.2.0.1.0 - 64位生产

PL/SQL过程成功完成。

已创建1行。 提交完成。

IDN LENGTH IMGHASH

---

0 10 84D89877F0D4041EFB6BF91A16F0248F2FD573E6AF05C19F96BEDB9F882F7882

已更新1行。 提交完成。

IDN LENGTH IMGHASH

---

0 12 5CE9AB10EA74270D12620DFACD74D262C6411E20761E459BB1B265DE883422AC

我发现我发布的错误仅在Toad（11.6.0.43免费版本）中手动编辑IMG blob字段并提交时发生，但是当我通过PL/SQL练习表格（插入，更新）时不会出现这种情况。

AdiM

- adim

Answer 2

我不得不使用这个技巧：

create or replace TRIGGER DESPACHO_ACTA_ARCHIVO_IU 
BEFORE INSERT OR UPDATE  ON CAT_DESPACHO_ACTA_ARCHIVO FOR EACH ROW
DECLARE TEMPFLD BLOB;
blobid number;
BEGIN
SELECT BLOBTEMP_SEQ.NEXTVAL INTO blobid FROM SYS.DUAL;
INSERT INTO blobtemp  VALUES (:new.bacta,blobid);
SELECT blobfield INTO :new.bacta FROM blobtemp where id =blobid ;
delete from blobtemp where id =blobid ;
:NEW.SHEX_SHA1 := sha2_blob(:new.bacta,1); 
END;

创建一个临时表来强制 blob 持久化并获取正确的哈希值。

- user3063731