ORA-MD5 字符串超长解决办法

ORA版本11.2

首先看一下ORA-MD5介绍：https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_obtool.htm#ARPLS67278

96 DBMS_OBFUSCATION_TOOLKIT

DBMS_OBFUSCATION_TOOLKIT enables an application to encrypt data using either the Data Encryption Standard (DES) or the Triple DES algorithms.

Note:

DBMS_OBFUSCATION_TOOLKIT is deprecated. DBMS_CRYPTO is intended to replace the DBMS_OBFUSCATION_TOOLKIT, providing greater ease of use and support for a range of algorithms to accommodate new and existing systems. See Chapter 39, "DBMS_CRYPTO" for more information.

MD5 Procedures and Functions

These subprograms generate MD5 hashes of data. The MD5 algorithm ensures data integrity by generating a 128-bit cryptographic message digest value from given data.

Syntax

DBMS_OBFUSCATION_TOOLKIT.MD5(
   input            IN   RAW,
   checksum         OUT  raw_checksum);

DBMS_OBFUSCATION_TOOLKIT.MD5(
   input_string     IN   VARCHAR2,
   checksum_string  OUT  varchar2_checksum);

DBMS_OBFUSCATION_TOOLKIT.MD5(
   input         IN  RAW)
  RETURN raw_checksum;

DBMS_OBFUSCATION_TOOLKIT.MD5(
   input_string  IN  VARCHAR2)
  RETURN varchar2_checksum;

Parameters

Table 96-8 MD5 Procedure and Function Parameters

Parameter Name	Description
input	Data to be hashed
checksum	128-bit cryptographic message digest
input_string	String to be hashed
checksum_string	128-bit cryptographic message digest

 

input_string ==> VARCHAR2--最大4000字节

input ==> RAW --最大2000字节

SELECT DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => CONTENT) FROM TEST.TEST_TABLE

一开始写了个字符串调用，字符串大于4000，报字符串超长。

于是想了一个办法，直接对CLOB进行转换，结果还是报错。

看了下对RAW字段介绍

RAW(size)

Raw binary data of length size bytes. You must specify size for a RAW value. Maximum size is: 32767 bytes if MAX_STRING_SIZE = EXTENDED 2000 bytes if MAX_STRING_SIZE = STANDARD Refer to Extended Data Types for more information on the MAX_STRING_SIZE initialization parameter.

 

虽然可以把值调高一点，可是CLOB中大于几万的字节比比皆是，所以我认为，MD5的参数最大值就是32767字节。

 

-------------------------------------------------------------------------------------------------------------------------------

于是想到了hash算法

GET_HASH_VALUE Function

This function computes a hash value for the given string.

Syntax

DBMS_UTILITY.GET_HASH_VALUE (
   name      VARCHAR2, 
   base      NUMBER, 
   hash_size NUMBER)
  RETURN NUMBER;

Parameters

Table 157-19 GET_HASH_VALUE Function Parameters

Parameter	Description
name	String to be hashed.
base	Base value for the returned hash value at which to start
hash_size	Desired size of the hash table

SELECT dbms_utility.get_hash_value(CONTENT,0,100000) FROM TEST.TEST_TABLE;

依然失败。不要放弃。

 

解决办法：使用DBMS_CRYPTO包

注意仔细看这段：

DBMS_OBFUSCATION_TOOLKIT is deprecated. DBMS_CRYPTO is intended to replace the DBMS_OBFUSCATION_TOOLKIT, providing greater ease of use and support for a range of algorithms to accommodate new and existing systems. See Chapter 39, "DBMS_CRYPTO" for more information.

实际上这个方法要被废弃了，在19c中就没有这个包了。

地址：https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_crypto.htm#ARPLS664

40.4 DBMS_CRYPTO Algorithms

The DBMS_CRYPTO package contains predefined cryptographic algorithms, modifiers, and cipher suites.

These are shown in the following tables.

Table 40-3 DBMS_CRYPTO Cryptographic Hash Functions

Name	Description
HASH_MD4	Produces a 128-bit hash, or message digest of the input message
HASH_MD5	Also produces a 128-bit hash, but is more complex than MD4
HASH_SH1	Secure Hash Algorithm (SHA-1). Produces a 160-bit hash.
HASH_SH256	SHA-2, produces a 256-bit hash.
HASH_SH384	SHA-2, produces a 384-bit hash.
HASH_SH512	SHA-2, produces a 512-bit hash.

40.9.5 HASH Function

A one-way hash function takes a variable-length input string, the data, and converts it to a fixed-length (generally smaller) output string called a hash value. The hash value serves as a unique identifier (like a fingerprint) of the input data. You can use the hash value to verify whether data has been changed or not.

Note that a one-way hash function is a hash function that works in one direction. It is easy to compute a hash value from the input data, but it is hard to generate data that hashes to a particular value. Consequently, one-way hash functions work well to ensure data integrity. Refer to “When to Use Hash or Message Authentication Code (MAC) Functions” in DBMS_CRYPTO Operational Notes for more information about using one-way hash functions.

This function applies to data one of the supported cryptographic hash algorithms listed in Table 40-3.

Syntax

DBMS_CRYPTO.Hash (
   src IN RAW,
   typ IN PLS_INTEGER)
 RETURN RAW;

DBMS_CRYPTO.Hash (
   src IN BLOB,
   typ IN PLS_INTEGER)
 RETURN RAW;

DBMS_CRYPTO.Hash (
   src IN CLOB CHARACTER SET ANY_CS,
   typ IN PLS_INTEGER)
 RETURN RAW;

 1、查看clob数据：

SELECT CONTENT FROM TEST.TEST_TABLE
WHERE CONTENTID='f942de3a-6cdd-4532-a3d9-a888142a30c1'

 2、进行MD5加密

--进行MD5加密
SELECT lower(rawtohex(dbms_crypto.hash(CONTENT,2))) FROM TEST.TEST_TABLE 
WHERE CONTENTID='f942de3a-6cdd-4532-a3d9-a888142a30c1'

--7b6e6fdd906ce5119ae8143e393e7010

3、验证数据准确性

为了验证准确性，在PG加入一条一样的数据

select content from test.test_table 
WHERE CONTENTID='f942de3a-6cdd-4532-a3d9-a888142a30c1'

在PostgreSQL进行MD5加密:

select md5(content) FROM test.test_table 
WHERE CONTENTID='f942de3a-6cdd-4532-a3d9-a888142a30c1'

--7b6e6fdd906ce5119ae8143e393e7010

 

对比成功！