在 Oracle 中制作一行的 sha1-hash
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1749753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Making a sha1-hash of a row in Oracle
提问by PrometheusDrake
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:
我在 Oracle 数据库上的选择中创建行的 sha1-hash 时遇到问题。我在 MSSQL 中完成了如下操作：
SELECT *,HASHBYTES('SHA1',CAST(ID as varchar(10)+
TextEntry1+TextEntry2+CAST(Timestamp as varchar(10)) as Hash
FROM dbo.ExampleTable
WHERE ID = [foo]
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...
但是，在使用 Oracle 时，我似乎找不到类似的函数。就我的谷歌搜索给我带来的而言，我猜 dbms_crypto.hash_sh1 与它有关，但我还没有能够将我的大脑环绕在它周围......
Any pointers would be greatly appreciated.
任何指针将不胜感激。
回答by Vincent Malgrat
The package DBMS_CRYPTOis 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).
包DBMS_CRYPTO是生成哈希的正确包。默认情况下，它不会授予 PUBLIC，您必须专门授予它 ( GRANT EXECUTE ON SYS.DBMS_CRYPTO TO user1)。
The result of this function is of datatype RAW. You can store it in a RAWcolumn or convert it to VARCHAR2using the RAWTOHEXor UTL_ENCODE.BASE64_ENCODEfunctions.
此函数的结果是数据类型RAW。您可以将其存储在RAW列中或将其转换为VARCHAR2使用RAWTOHEX或UTL_ENCODE.BASE64_ENCODE函数。
The HASHfunction is overloaded to accept three datatypes as input: RAW, CLOBand BLOB. Due to the rules of implicit conversion, if you use a VARCHAR2as input, Oracle will try to convert it to RAWand will most likely fail since this conversion only works with hexadecimal strings.
该HASH函数被重载以接受三种数据类型作为输入：RAW、CLOB和BLOB。由于隐式转换的规则，如果您使用 aVARCHAR2作为输入，Oracle 将尝试将其转换为RAW并且很可能会失败，因为此转换仅适用于十六进制字符串。
If you use VARCHAR2then, you need to convert the input to a binary datatype or a CLOB, for instance :
如果使用VARCHAR2then，则需要将输入转换为二进制数据类型或 a CLOB，例如：
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;
you will find additional information in the documentation of DBMS_CRYPTO.hash
您将在文档中找到更多信息 DBMS_CRYPTO.hash
回答by Pierre-Gilles Levallois
The DBMS_crypto package does not support varchar2. It works with raw type so if you need a varchar2 you have to convert it. Here is a sample function showing how to do this :
DBMS_crypto 包不支持 varchar2。它适用于原始类型，因此如果您需要 varchar2，则必须对其进行转换。这是一个示例函数，显示了如何执行此操作：
declare
p_string varchar2(2000) := 'Hello world !';
lv_hash_value_md5 raw (100);
lv_hash_value_sh1 raw (100);
lv_varchar_key_md5 varchar2 (32);
lv_varchar_key_sh1 varchar2 (40);
begin
lv_hash_value_md5 :=
dbms_crypto.hash (src => utl_raw.cast_to_raw (p_string),
typ => dbms_crypto.hash_md5);
-- convert into varchar2
select lower (to_char (rawtohex (lv_hash_value_md5)))
into lv_varchar_key_md5
from dual;
lv_hash_value_sh1 :=
dbms_crypto.hash (src => utl_raw.cast_to_raw (p_string),
typ => dbms_crypto.hash_sh1);
-- convert into varchar2
select lower (to_char (rawtohex (lv_hash_value_sh1)))
into lv_varchar_key_sh1
from dual;
--
dbms_output.put_line('String to encrypt : '||p_string);
dbms_output.put_line('MD5 encryption : '||lv_varchar_key_md5);
dbms_output.put_line('SHA1 encryption : '||lv_varchar_key_sh1);
end;
回答by Israel Rocha
You can define this function in your favorite package, I defined in utils_pkg.
你可以在你最喜欢的包中定义这个函数，我在 utils_pkg 中定义。
FUNCTION SHA1(STRING_TO_ENCRIPT VARCHAR2) RETURN VARCHAR2 AS
BEGIN
RETURN LOWER(TO_CHAR(RAWTOHEX(SYS.DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(STRING_TO_ENCRIPT), SYS.DBMS_CRYPTO.HASH_SH1))));
END SHA1;
Now to call it
现在调用它
SELECT UTILS_PKG.SHA1('My Text') AS SHA1 FROM DUAL;
The response is
回应是
SHA1
--------------------------------------------
5411d08baddc1ad09fa3329f9920814c33ea10c0
You can select a column from some table:
您可以从某个表中选择一列：
SELECT UTILS_PKG.SHA1(myTextColumn) FROM myTable;
Enjoy!
享受！
回答by Sheyanoff
Just to put it here, if someone will search for.
只是把它放在这里，如果有人会搜索。
In Oracle 12 you can use standard_hash(<your_value>, <algorythm>)function.
With no parameter <algorythm>defined, it will generate SHA-1 hash (output datatype raw(20))
在 Oracle 12 中，您可以使用standard_hash(<your_value>, <algorythm>)函数。没有<algorythm>定义参数，它将生成 SHA-1 哈希（输出数据类型raw(20)）
