在 Oracle 中制作一行的 sha1-hash

2021-12-27 00:00:00 hash sha1 oracle

我在 Oracle 数据库上的选择中创建行的 sha1-hash 时遇到问题.我在 MSSQL 中做了如下:

I'm having a problem with making a sha1-hash of a row in a select on an Oracle database. I've done it in MSSQL as follows:

SELECT *,HASHBYTES('SHA1',CAST(ID as varchar(10)+
  TextEntry1+TextEntry2+CAST(Timestamp as varchar(10)) as Hash
FROM dbo.ExampleTable
WHERE ID = [foo]

但是，我似乎找不到在使用 Oracle 时使用的类似函数.就我的谷歌搜索给我带来的，我猜 dbms_crypto.hash_sh1 与它有关，但我还没有能够将我的大脑围绕它......

However, I can't seem to find a similar function to use when working with Oracle. As far as my googling has brought me, I'm guessing dbms_crypto.hash_sh1 has something to do with it, but I haven't been able to wrap my brain around it yet...

任何指针将不胜感激.

推荐答案

DBMS_CRYPTO 是生成哈希的正确包.默认情况下，它不会授予 PUBLIC，您必须专门授予它 (GRANT EXECUTE ON SYS.DBMS_CRYPTO TO user1).

The package DBMS_CRYPTO is the correct package to generate hashes. It is not granted to PUBLIC by default, you will have to grant it specifically (GRANT EXECUTE ON SYS.DBMS_CRYPTO TO user1).

这个函数的结果是数据类型RAW.您可以将其存储在 RAW 列中或使用 RAWTOHEX 或 UTL_ENCODE.BASE64_ENCODE 函数将其转换为 VARCHAR2.

The result of this function is of datatype RAW. You can store it in a RAW column or convert it to VARCHAR2 using the RAWTOHEX or UTL_ENCODE.BASE64_ENCODE functions.

HASH 函数被重载以接受三种数据类型作为输入:RAW、CLOB 和 BLOB.由于隐式转换规则，如果您使用 VARCHAR2 作为输入，Oracle 将尝试将其转换为 RAW 并且很可能会失败，因为此转换仅适用于十六进制字符串.

The HASH function is overloaded to accept three datatypes as input: RAW, CLOB and BLOB. Due to the rules of implicit conversion, if you use a VARCHAR2 as input, Oracle will try to convert it to RAW and will most likely fail since this conversion only works with hexadecimal strings.

如果你使用VARCHAR2，那么你需要将输入转换为二进制数据类型或CLOB，例如:

If you use VARCHAR2 then, you need to convert the input to a binary datatype or a CLOB, for instance :

DECLARE
   x RAW(20);
BEGIN
   SELECT sys.dbms_crypto.hash(utl_raw.cast_to_raw(col1||col2||to_char(col3)), 
                               sys.dbms_crypto.hash_sh1) 
     INTO x 
     FROM t;
END;

您可以在 DBMS_CRYPTO 的文档中找到更多信息.哈希

相关文章