Skip to Main Content
  • Questions

  • DBMS_CRYPTO.HASH HASH_SH256 does not match openssl

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, George.

Asked: October 17, 2017 - 3:21 pm UTC

Last updated: July 06, 2018 - 9:27 am UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

OM -

I need to create a (large) hash both inside the database and outside of it that yields the same result. Everything I am reading says SHA256 will satisfy the requirement, but I am not able to write SQL that achieves the same answer as the Linux library.

i.e.

SQL> select DBMS_CRYPTO.HASH( UTL_I18N.STRING_TO_RAW( 'The Little Brown Fox', 'AL32UTF8' ), 4 /* DBMS_CRYPTO.HASH_SH256 */ ) from dual ;

DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW('THELITTLEBROWNFOX','AL32UTF8'),4/*DBMS_
--------------------------------------------------------------------------------
F3DAFB6DB9169798CED60C1DF1BA3D354D579594DF3963A2172C130A012DDA7A


but 

$ echo 'The Little Brown Fox' | openssl dgst -sha
(stdin)= d7acd2042179ff8ac6c67ce667a27ca024e6a7f8


what am I missing?

Thanks, George


and Chris said...

Well, the first problem is you're not hashing to SH256 with openssl!

The second is that echo includes a newline character. Suppress this with -n. Or add it to the end of your SQL string. And you should see the same results:

bash-4.1$  echo -n 'The Little Brown Fox' | openssl dgst -sha256
(stdin)= f3dafb6db9169798ced60c1df1ba3d354d579594df3963a2172c130a012dda7a
bash-4.1$  echo  'The Little Brown Fox' | openssl dgst -sha256
(stdin)= cd0585b84f5c1f83eb5c2537a7d6b8c24da46d68d1bc89fea60b6f914d2ad8ca
bash-4.1$ sqlplus chris/xxxxx

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 17 08:57:00 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 17 2017 08:50:04 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  
SQL> select
  2    DBMS_CRYPTO.HASH( UTL_I18N.STRING_TO_RAW( 'The Little Brown Fox', 'AL32UTF8' ), 4 /* DBMS_CRYPTO.HASH_SH256 */ ) no_newline,
  3    DBMS_CRYPTO.HASH( UTL_I18N.STRING_TO_RAW( 'The Little Brown Fox' || chr(10), 'AL32UTF8' ), 4 /* DBMS_CRYPTO.HASH_SH256 */ ) with_newline
from dual
/  4    5

NO_NEWLINE
--------------------------------------------------------------------------------
WITH_NEWLINE
--------------------------------------------------------------------------------
F3DAFB6DB9169798CED60C1DF1BA3D354D579594DF3963A2172C130A012DDA7A
CD0585B84F5C1F83EB5C2537A7D6B8C24DA46D68D1BC89FEA60B6F914D2AD8CA

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thanks for the quick response!

George Liblick, October 17, 2017 - 6:13 pm UTC

I am now able to produce matching hashes!

bcrypt alternative in Oracle

Omar Sawalhah, July 03, 2018 - 10:36 pm UTC

Hi,
I just migrated data from MySQL into Oracle, I have a column encrypted using bcrypt library in Javascript. I need to validate users against their old passwords. How is this accomplished in Oracle.


Chris Saxon
July 04, 2018 - 2:47 pm UTC

Surely you can call the same Javascript library in your app? Why has migrating the database affected this?

bcrypt alternative in Oracle

Omar Sawalhah, July 05, 2018 - 12:45 pm UTC

Sorry, I didn't make my self clear. My new application is APEX and I need to write a function in the database to validate the migrated users with their old password, and I need to use the same bcrypt algorithm to change their passwords, or create new users.
I don't want to send them email to change all their passwords, so I can save it locally as sha256 or so.

Thanks.

Chris Saxon
July 05, 2018 - 3:15 pm UTC

And why can't you use the original library in APEX? You can run Javascript there. If you're stuck on how to do this, you're better off asking on the APEX forum:

https://community.oracle.com/community/database/developer-tools/application_express

bcrypt alternative in Oracle

Omar Sawalhah, July 05, 2018 - 10:19 pm UTC

Hi,
I know how to use JS libraries in APEX, my question was, simply. If I have a string like "password", is there any way to hash it to be "$2y$12$Fyj2S0an.MDKddcO3G9DduBXJJwTTylYAKmGqMOvgbsaK7Z4Hzg8K"
That what https://en.wikipedia.org/wiki/Bcrypt can generate.
in Oracle Database?
Thanks
Chris Saxon
July 06, 2018 - 9:27 am UTC

I'm not aware of an in-built bcrypt option in Oracle Database.

You may be able to do this using Java stored procedures.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library