12 Replies Latest reply on Jan 12, 2017 7:19 PM by Guil Sa-Oracle

    How to hash a value, assuming MD5 is not a solution

    Guil Sa-Oracle

      The below obfuscate password function [1] is returning different values given the same input 'hello world'. I expect it to return the same hash value in order to save end-users password.

      I almost used MD5 from the APEX API [2], however it is no longer secure [3]. Did I write a function to encrypt data instead of hashing it?

       

      [1].

      create or replace FUNCTION obfuscate(password in VARCHAR2)
          RETURN VARCHAR2 IS
      
          output_string      VARCHAR2 (200);
          encrypted_raw      RAW (2000);             -- stores encrypted binary text
          num_key_bytes      NUMBER := 256/8;        -- key length 256 bits (32 bytes)
          key_bytes_raw      RAW (32);               -- stores 256-bit encryption key
          encryption_type    PLS_INTEGER :=          -- total encryption type
                                  DBMS_CRYPTO.ENCRYPT_AES256
                                + DBMS_CRYPTO.CHAIN_CBC
                                + DBMS_CRYPTO.PAD_PKCS5;
      BEGIN
      key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
         encrypted_raw := DBMS_CRYPTO.ENCRYPT
            (
               src => UTL_I18N.STRING_TO_RAW (password,  'WE8MSWIN1252'),
               typ => encryption_type,
               key => key_bytes_raw
            );
      
      
      RETURN encrypted_raw;
      END obfuscate;
      

       

      [2]. MD5 vulnerability: https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=md5+hash+security

      [3]. MD5 Procedures and Functions [APEX API]: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_obtool.htm#i1003449

        • 1. Re: How to hash a value, assuming MD5 is not a solution
          PMON

          3310806 wrote:

           

          My obfuscate password function [1] is returning different values given the same input 'hello world'. Expect it to return the same hash value in order to save end-user's password.

          I almost used MD5 from APEX API [2], however it is not MD5 is no longer secure [3]. Did I write a function to encrypt data instead of hashing it?

           

          [1].

          1. createorreplaceFUNCTIONobfuscate(passwordinVARCHAR2)
          2. RETURNVARCHAR2IS
          3. output_stringVARCHAR2(200);
          4. encrypted_rawRAW(2000);--storesencryptedbinarytext
          5. num_key_bytesNUMBER:=256/8;--keylength256bits(32bytes)
          6. key_bytes_rawRAW(32);--stores256-bitencryptionkey
          7. encryption_typePLS_INTEGER:=--totalencryptiontype
          8. DBMS_CRYPTO.ENCRYPT_AES256
          9. +DBMS_CRYPTO.CHAIN_CBC
          10. +DBMS_CRYPTO.PAD_PKCS5;
          11. BEGIN
          12. key_bytes_raw:=DBMS_CRYPTO.RANDOMBYTES(num_key_bytes);
          13. encrypted_raw:=DBMS_CRYPTO.ENCRYPT
          14. (
          15. src=>UTL_I18N.STRING_TO_RAW(password,'WE8MSWIN1252'),
          16. typ=>encryption_type,
          17. key=>key_bytes_raw
          18. );
          19. RETURNencrypted_raw;
          20. ENDobfuscate;

           

          [2]. MD5 vulnerability: https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=md5+hash+security

          [3]. MD5 Procedures and Functions [APEX API]: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_obtool.htm#i1003449

          A good encryption algorithm will always return a different hash value, especially when the same password is provided, or it would be much easier to crack.  Besides, the user won't be logging into your application with your hashed value, so it doesn't matter what value is stored as long as it comes out the same when it's decrypted.

           

          FYI, from the documentation "To retrieve original plaintext data, DECRYPT must be called with the same cipher, modifiers, key, and IV that was used to encrypt the data originally."  The way your code is structured you need to store your encryption key in the table also so you can use the same key for decryption of the hash.

           

          Hope this helps.

          • 2. Re: How to hash a value, assuming MD5 is not a solution
            Guil Sa-Oracle

            Made my day! Will look into it. Million thanks.

            • 3. Re: How to hash a value, assuming MD5 is not a solution
              fac586

              3310806 wrote:

              Please update your forum profile with a recognisable username instead of "3310806": Video tutorial how to change nickname available

               

              Always include the information detailed in these guidelines when asking a question so we can provide version- and environment-specific information: how to get answers from forum

              Did I write a function to encrypt data instead of hashing it?

              Yes (function names are a big clue). If you want to generate a hash (and you do in this case) use a hash function.

              Pmon wrote:

               

              A good encryption algorithm will always return a different hash value, especially when the same password is provided, or it would be much easier to crack. Besides, the user won't be logging into your application with your hashed value, so it doesn't matter what value is stored as long as it comes out the same when it's decrypted.

               

              FYI, from the documentation "To retrieve original plaintext data, DECRYPT must be called with the same cipher, modifiers, key, and IV that was used to encrypt the data originally." The way your code is structured you need to store your encryption key in the table also so you can use the same key for decryption of the hash.

               

              Hope this helps.

              Far from it. This would be disastrous. Any intruder gaining access to that table (for example via a SQL injection attack) would very rapidly be able to decrypt the user passwords for that system (and all the others that users reuse them on).

               

              For user authentication, store password hashes created using the most secure hash algorithm available in DBMS_CRYPTO on your database version, with suitable salts and key stretching.

              • 4. Re: How to hash a value, assuming MD5 is not a solution
                Guil Sa-Oracle

                [Edit: wrote this in response to Pmon before seeing fac586's response]

                 

                My only concern is that a hash function isn't the same as encryption. I want to implement hashing. Not sure you we're on the same page.

                 

                Also, I wasn't aware of hash randomsation. Is that the same thing as salt?

                 

                Regarding salt, just reading up more on this, it seems like:

                • salt can be unique per user's password.
                • can also be stored in the same table as password.
                • a pepper can be used as a second piece of randomization (second salt), but stored in the code itself and doesn't need to be unique.

                 

                Regards.

                Guil

                • 5. Re: How to hash a value, assuming MD5 is not a solution
                  PMON

                  Guil,

                   

                  My apologies for replying on a subject that I've only casually read on.  As pointed out you were talking about hashing which result should always be the same.  I got mixed up and thought probabilistic encryption applied in your case, but hashing apparently is always deterministic.

                   

                  Hope I didn't waste too much of your time. 

                  • 7. Re: How to hash a value, assuming MD5 is not a solution
                    Guil Sa-Oracle

                    So SHA2 seems secure.

                     

                    Why is this not working? [1]

                     

                    DECLARE
                     lRAW  RAW(512);
                     lClob CLOB := 'Patient is showing signs of pizza box intolerance: Try an ODA';
                    BEGIN
                      dbms_output.put_line('Source:  ' || SUBSTR(lClob,1,128));
                    
                    
                      lRAW := dbms_crypto.hash(lCLOB, dbms_crypto.hash_sh512);
                      dbms_output.put_line('SH2-512: ' || lRAW);
                    END;
                    /
                    

                     

                    returns

                    This however does work. Is HASH_SH1 enough for masking passwords or should I be doing it with HASH_SH512?

                     

                    DECLARE
                    password VARCHAR2(19) := 'helloworld';
                    password_raw RAW(128) := utl_raw.cast_to_raw(password);
                    BEGIN
                    DBMS_OUTPUT.PUT_LINE(dbms_crypto.hash(password_raw, sys.dbms_crypto.hash_sh1));
                    END;
                    /
                    

                     

                    Thank you in advance.

                     

                    [1]. Oracle 12c DBMS_CRYPTO Demos, Syntax, and Example Code

                    • 8. Re: How to hash a value, assuming MD5 is not a solution
                      Guil Sa-Oracle

                      Just for the record here's what has worked for me, using SH1. Obviously never publish your salt like I did here.

                       

                      create or replace FUNCTION obfuscate(password in VARCHAR2)

                          RETURN VARCHAR2 IS

                        

                          salt varchar2(12) := 'Q*r&!5^q%2Rf';

                          password_raw RAW(128) := utl_raw.cast_to_raw( password || salt );

                          hash_raw RAW (128);

                       

                      BEGIN

                       

                      hash_raw := dbms_crypto.hash(password_raw, sys.dbms_crypto.hash_sh1);

                       

                      RETURN hash_raw;

                      END obfuscate;

                      • 9. Re: How to hash a value, assuming MD5 is not a solution
                        fac586

                        Guil Sa-Oracle wrote:

                         

                        So SHA2 seems secure.

                         

                        Why is this not working? [1]

                        1. DECLARE
                        2. lRAWRAW(512);
                        3. lClobCLOB:='Patientisshowingsignsofpizzaboxintolerance:TryanODA';
                        4. BEGIN
                        5. dbms_output.put_line('Source:'||SUBSTR(lClob,1,128));
                        6. lRAW:=dbms_crypto.hash(lCLOB,dbms_crypto.hash_sh512);
                        7. dbms_output.put_line('SH2-512:'||lRAW);
                        8. END;
                        9. /

                        returns

                        What database version are you using? You've referenced both 10.2 and 12.1 resources in this thread.

                         

                        As stated above:

                        For user authentication, store password hashes created using the most secure hash algorithm available in DBMS_CRYPTO on your database version...

                         

                        Support for the HASH_SH512 algorithm was introduced in 12.1.

                         

                        Always include details of your environment—including full version numbers of all relevant products—when asking a question so we can provide the appropriate solutions and resources: how to get answers from forum

                        • 10. Re: How to hash a value, assuming MD5 is not a solution
                          Guil Sa-Oracle

                          I’m on APEX  5.0.4.00.12 and SQLPlus 11.2.0.3.0.  Looks like I don't have SHA512 then.

                           

                          Thank you!

                          • 11. Re: How to hash a value, assuming MD5 is not a solution
                            Christian Neumueller-Oracle

                            Guil,

                             

                            if this is for an Oracle internal system, you should strongly consider using apex.oraclecorp.com to host your application. It is trivial to use Single Sign-On there, so you do not have to roll your own (and likely insecure - getting this right is hard) authentication. At least take a look at the FAQ here and follow the link to the GIS Good Practice Guide under "Is Oracle Application Express secure?"

                             

                            Regards,

                            Christian

                            • 12. Re: How to hash a value, assuming MD5 is not a solution
                              Guil Sa-Oracle

                              Hi Christian,

                               

                              Here's what this is for: I'm helping design a new homepage/system for the general public to sign-up to register for something. So it's basically a leads database we'll then use internally. Part of the new requirement is to allow visitors to self-register and manage their own profile. Biggest pain point today is has been keeping the database up-to-date, thus the self-service, external solution. Hopefully this gives better context as to why I'm trying to be diligent about security.

                               

                              Regards.

                              Guil