Oracle check data in sha512 encryption

Question

I need to check data in sha512 encryption by Oracle query.

Example query:

SELECT * FROM text WHERE SHA512(id) = '$id'

Answer 1

Oracle provides DBMS_CRYPTO package to deal with encryption and hashes.

You may incorporate SHA hash calculation and comparation with some string constant in the way like showed in this query:

select *
from text t
where 
  lower(                           -- to guarantee same character case 

    rawtohex(                      -- convert hash to string representation  
      dbms_crypto.hash(            -- hash calculation function

        utl_raw.cast_to_raw(t.id), -- need to convert a string before passing it to 
                                   -- function in parameter because otherwise 
                                   -- it casted to RAW directly with hextoraw().

        6                          -- dbms_crypto.HASH_SH512 - for Oracle 12c only 
      )
    )
  )
  = 
  lower(                           -- to guarantee same character case   
    :some_id_sha2_const_parameter  -- parameter string to compare with
  ) 

Unfortunately, your can't handle SHA-512 hashes in Oracle 11g because of lack of support. But it's possible with Oracle 12c.

Documentation:
DBMS_CRYPTO package in Oracle 11g
DBMS_CRYPTO package in Oracle 12c

There are still two things that you must take into account:

1. Be sure that character sets of source strings are same because hash calculated on binary data.
2. From performance point of view it's better to store precomputed hash values in the database in a column, index it, and compare external parameter with value stored in column.