Making a sha1-hash of a row in Oracle

Question

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]

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...

Any pointers would be greatly appreciated.

Solution 1

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).

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.

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.

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;

you will find additional information in the documentation of DBMS_CRYPTO.hash

Solution 2

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 :

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;

Solution 3

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))

Solution 4

You can define this function in your favorite package, I defined in 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!

View more solutions

Comments

[PrometheusDrake]

Thanks for the help. I had to use the value dbms_crypto.hash_sh1 stands for (the integer 3) instead of the constant to make it work when doing a regular SQL. SELECT sys.dbms_crypto.hash(utl_raw.cast_to_raw(col1),3) FROM t;

[user272735]

I don't think this is exactly correct for 11g R2. dbms_crypto.hash seems to have 3 overloaded versions for raw, blob and clob. varchar2 value has to be explicitly converted to raw either with utl_i18n.string_to_raw or utl_raw.cast_to_raw. Or use clobs for character data.

[ca9163d9]

What if there are some columns which don't have string data type?

[Andrew Philips]

The comment above from @PrometheusDrake talks about dbms_crypto.hash_sh1. To be clear, this is a constant defined in the package and passed to dbms_crypto.hash as the typ parameter. It indicates that the SHA-1 hash algorithm should be used.

[Andrew Philips]

The example above uses cast_to_raw, however callers should use UTL_I18N.STRING_TO_RAW instead. cast_to_raw doesn't take into account a binary independent language conversion. If the default language of the database is changed between encrypt and decrypt, the cast_to_raw and subsequent cast_to_varchar2 may fail. For example, the varchar2 could be converted with: UTL_I18N.STRING_TO_RAW (string, 'AL32UTF8')

[Andrew Philips]

@ca9163d9 If your column is another type, look for the appropriate UTL_RAW package function. For example, if you had a column of type NUMBER, use CAST_FROM_NUMBER to convert it to RAW, then encrypt. To reverse, decrypt, then CAST_TO_NUMBER.