Does Oracle have any built-in hash function? [duplicate]

Question

Possible Duplicate:
Is there any hash function in PL/SQL?

I have a column with NCLOB database type in Oracle 11g. I need to get a hash value for its content. How can I do this using any built-in Oracle function or inside a PL/SQL SP in Oracle?

Answer 1

Yes: hashing and encrypting (related but not exactly the same) are all done via the SYS package DBMS_CRYPTO.

Simple SHA-1 Hashing

l_hash := dbms_crypto.hash( l_src, dbms_crypto.HASH_SH1 );

Simple MD5 Hashing

l_hash := dbms_crypto.hash( l_src, dbms_crypto.HASH_MD5 );

Overview of dbms_crypto.hash()

The hash() function is overloaded to accept the following types: RAW, BLOB, and CLOB. According to the implicity data conversions for raw acceptable input types are RAW, CHAR, VARCHAR2, NCHAR, NVARCHAR2, LONG, BLOB. All other data types (DATE, TIMESTAMP, etc) not covered under RAW/implicit RAW conversion, BLOB, and CLOB will have to be passed through TO_CHAR() first.

It is worth noting that dbms_crypto.hash() supports the following hashing algorithms:

• HASH_MD4
• HASH_MD5
• HASH_SH1

Passwords: Just In Case

If you are storing passwords, I suggest that you use a password storage hash (bcrypt, PBKDF2, or scrypt) instead of a cryptographic hash (md5, sha-1, etc). The difference is that password storage hashes are meant to take time to break while cryptographic hashes are meant to be done quickly. When attacking a system's password list via brute force it orders of magnitude more time intensive when attempting to break a salted value that is passed through a cryptographic algorithm. Consider that using a password hash on a single value can take ~100ms (not much for a single authentic login), but very slow for a brute force (millions/billions of attempts per password) over your entire password list.

Oracle Hates Password Hashes

For what its worth I am not aware of any packages from Oracle that provide password hashing support. You can however accomplish this by using 'loadjava' and putting a Java bcrypt implementation within the JVM that runs withing Oracle's RDBMS. You can then use a PL/SQL wrapper to call your Java class that implements bcrypt. If you are using a middle-tier you can use many other options available to you in that language (.NET, PHP, Perl, Ruby, Python, Java, etc) and skip trying to use 'loadjava'.

I meant encryption not hashes!

In case the hashing you need is not covered by dbms_crypto.hash(), you might be looking for encryption via dbms_crypto.encrypt which works very similarly except that it takes in the following types:

• ENCRYPT_DES
• ENCRYPT_3DES_2KEY
• ENCRYPT_3DES
• ENCRYPT_AES
• ENCRYPT_PBE_MD5DES
• ENCRYPT_AES128
• ENCRYPT_AES192
• ENCRYPT_AES256

Here is the full 11gR2 documentation on DBMS_CRYPTO. All other versions are available via tahiti.oracle.com. Just click on your version and then search for 'dbms_crypto'.