Does Oracle have any built-in hash function?

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?

Solution

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

Comments

[Shannon Severance]

In some ways this is a better answer than the accepted answer in the duplicate question. Code examples and detail, versus just a link to a guide.

[Shannon Severance]

OTOH, and really worth -1, one generally does want to use a cryptographic hash of salt + password, not encryption, for storing passwords (Unless you need to be able to extract the password, but scary.) See security.stackexchange.com/questions/8945/…

[Andrew Martinez]

@ShannonSeverance I am afraid I can't argue the point any more intelligently than linking an article to someone who is an expert; please accept my apologies. If you have thoughts on the matter I am always opening to hearing new ideas. I work off the current assumption that unsalted encryption algorithms and unsalted hash algorithms are both reversible. Hashes are one way till someone reverses them which isn't long. With that in mind consider what is different about them. krebsonsecurity.com/2012/06/…

[Shannon Severance]

Ah, interesting. I had not heard the cryptographic hash versus password hash distinction. The article linked in comments was good. My comment was on the first version that was saying to use encryption. Now +1.

[Andrew Martinez]

You would not believe the discussion this sparked at work. Using generic words like 'encrypt' 'hash' and 'algorithm' when dealing with this kind of thing made all of us run in circles and then run to our computers to look things up.

[Mohsen Heydari]

Here is an implementation of BCrypt inside Oracle DB