Column Value is Hashed or not

Question

How to identify that a column in oracle is encrypted using DBMS_CRYPTO.HASH or DBMS_OBFUSCATION_TOOLKIT.MD5 or not

Table with HASH Value

Is there any function that i can run across all columns of the oracle database (ALL_TAB_COLUMNS) which will return true if column is encrypted or not?

Answer 1

I think it is very difficult to identify the columns that are encrypted. However, it should be easy to find candidate columns because they usually contain only hex data and are of a fixed length.

There are two common ways to store hex data in Oracle, as a RAW or as a string (CHAR,VARCHAR2 etc.). RAW is the proper datatype for raw data, as you don't have characterset issues, and it is unambiguos about character case (f6099c0 is the same as F6099C0), and it's half the size. However, in my experience, strings are sadly used more often, mostly because the developer have never heard of a RAW datatype. In your hunt for hashes, you'll have to look for both, though.

Some example data:

CREATE TABLE t (
   md5r  RAW(16), 
   md5v  CHAR(32),
   sha1r RAW(20),
   sha1v CHAR(40)
);

INSERT INTO t
SELECT md5raw, rawtohex(md5raw) as md5str,
       sh1raw, rawtohex(sh1raw) as sh1str
  FROM (SELECT DBMS_CRYPTO.Hash(tr, 2) AS md5raw,
               DBMS_CRYPTO.Hash(tr, 3) AS sh1raw
          FROM (SELECT UTL_I18N.STRING_TO_RAW (tv, 'AL32UTF8') AS tr
                  FROM (SELECT object_name as tv FROM dba_objects where rownum <= 10)));
EXEC dbms_stats.gather_table_stats(null, 't');

You can now filter out the candidate columns by the fact that the highest value in the column is as long as the lowes value. As low/high values are stored as hex strings, you need to double the length (quite confusingly) to 32/64 for MD5, 40/80 for SHA1 etc:

SELECT owner, table_name, column_name, data_type, data_length
  FROM all_tab_columns 
 WHERE data_type = 'RAW' 
   AND LENGTH(low_value)=40 AND LENGTH(high_value)=40;

T SHA1R RAW 20

SELECT owner, table_name, column_name, data_type, data_length
  FROM all_tab_columns 
 WHERE data_type IN ('CHAR','VARCHAR','VARCHAR2')
   AND LENGTH(low_value)=80 AND LENGTH(high_value)=80;

T SHA1V CHAR 40

I'd check next whether all values in the table are of a fixed length for those candidates. If the table is large, I'd start with a small sample. For instance for column sha1r:

SELECT min(length(sha1r)) as min_len, 
       max(length(sha1r)) as max_len, 
       count(*) as n 
  FROM t SAMPLE (0.1);

Next, I'd check for the string versions if they are all hex characters (this is obviously not needed for the raw version):

SELECT sha1v, CASE WHEN (REGEXP_LIKE(sha1v,'^[0-9A-Fa-f]*$')) THEN 1 ELSE 0 END as hex
  FROM t;

Now you could use tools like https://code.google.com/archive/p/hash-identifier/, mentioned in this question to check the hash type.

Your example F6099C0932D0E2B13286219F99C265975B33FD84 results in

Possible Hashs:
[+]  SHA-1
[+]  MySQL5 - SHA-1(SHA-1($pass))

BTW, please use text instead of images, I had to manually type in your example to try it out, instead of copy and paste, which is a pain.