Oracle SQL Select Hex value to SHA256

Question

There's a specific column (hex_values) on a table, where all the values (strings) are base16 (HEX) encoded.

Is it possible to SELECT all the members of hex_values and bring the result already SHA256 hashed?

For example:

 -------------------------
|       HEX_VALUES        |
 -------------------------
| 70617373776f7264313233a | //password123 (HEX)
 -------------------------  
| 31323370617373776f7264a | //123password (HEX)
 ------------------------- 
| 6c6f72656d697073756d    | //loremipsum (HEX)
 ------------------------- 
| 6d7970617373776f7264    | //mypassword (HEX)
 -------------------------

And the SQL Query would do all the job, like this (row 1 example):

70617373776f7264313233a (string HEX) → password123 (regular string) → sha256(password123) (giant string)

So the result from this SQL Query would be:

 -------------------------
|       HEX_VALUES        |
 -------------------------
| sha256(password123)     | 
 -------------------------  
| sha256(123password)     | 
 ------------------------- 
| sha256(loremipsum)      | 
 ------------------------- 
| sha256(mypassword)      | 
 -------------------------

I know I can :

1) Select hex_values (Oracle SQL)

2) After SQL Query: Transform to Regular String → SHA256(Regular String)

But I >really< need to do this without using any other source code besides Oracle SQL.

sticky bit · Accepted Answer · 2020-03-17T21:42:22.927

0

In Oracle 12 and above there's the standard_hash() function you could use. Use hextoraw() on the strings, then pass that value to standard_hash().

SELECT standard_hash(hextoraw(hex_values), 'SHA256')
       FROM elbat;

And by the way, SHA256 is a hashing algorithm not an encryption algorithm. So there is no such thing as "SHA256 encrypted" values.

edited Mar 17 '20 at 21:42

answered Mar 17 '20 at 21:39

sticky bit

36,626
12
31
42

Thank you so much! ♥ – Vitor Mascia Mar 17 '20 at 21:41
After all, this is what worked for me: `SELECT standard_hash( UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(NAME)), 'SHA256' ) FROM ELBAT` – Vitor Mascia Mar 18 '20 at 13:51

score 0 · Answer 2 · answered Mar 18 '20 at 13:55

0

After all, this is what worked for me:

 SELECT 
    standard_hash(
      UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(HEX_VALUES)),
      'SHA256'
    ) 
    FROM ELBAT

answered Mar 18 '20 at 13:55

Vitor Mascia

109
2
9

Oracle SQL Select Hex value to SHA256

2 Answers2