I need to flag some data hashed in md5 inside a function.

I have the table optima.BDM_TMP_MAIL_HARD contains some mail. I need to match some mail of another table in md5 with the mail of this table.

MY TABLE WITH MAIL IN MD5

TABLE BDM_TMP_MAIL_HARD

I use a function, taken from another package, called 'to_md5' that converts the mail to md5. So, I will use this function on the table BDM_TMP_MAIL_HARD.

The problem is when I execute the function, it never ends.

I've done multiple tests, the select count is correct and if I try to do the update without function, it works. If I execute the function without the to_md5(), it works.

Just if I put inside the function to_md5, the function never ends.

Why does the function work without the to_md5() function?

  UPDATE MY_TABLE SET MATCH= FILTRE_MARKET_OP.FILTRE_V1_MARKET_OP_MD5(lower(emailMD5));  



     FUNCTION FILTRE_V1_MARKET_OP_MD5(mail IN varchar) RETURN NUMBER IS   
    hard number;
    mail_filtre  varchar2(300);
    BEGIN
     hard := 0;
    mail_filtre := mail;

        SELECT count(*) INTO hard FROM optima.BDM_TMP_MAIL_HARD   WHERE EMAIL IS NOT NULL AND optima.to_md5(email)  = mail_filtre;
        -
            IF hard >= 1 THEN
                 hard := 1;
                GOTO FIN;
                
            END IF;

   <<FIN>>
  
        RETURN hard;
    
    END FILTRE_V1_MARKET_OP_MD5;

Otherwise, cause that's not working, i do manually the update like that:

update MY_TABLE set MATCH = 1 where lower(emailMD5) in (select uoptima.to_md5(lower(EMAIL)) from optima.BDM_TMP_MAIL_HARD where EMAIL is not null) and emailMD5 is not null; 

and this is my custom md5 function:

    create or replace FUNCTION to_MD5 (str IN VARCHAR2)
RETURN VARCHAR2
IS v_checksum VARCHAR2(32);
BEGIN
    v_checksum := LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW( sys.dbms_obfuscation_toolkit.md5(input_string => str) ) ) );
    RETURN v_checksum;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
    WHEN OTHERS THEN
        -- Consider logging the error and then re-raise
        RAISE;
END to_MD5;

CodePudding user response：

Is optima.to_md5(email) a custom function you have written to generate an MD5 hash? If you are on Oracle 12c or higher, it would be better to use the STANDARD_HASH function to generate the MD5 hash like this STANDARD_HASH(email, 'MD5').

CodePudding user response：

The dbms_obfuscation_toolkit is deprecated after 11G so if you ever upgrade your backing yourself into a corner.

MD5 has been cryptographically broken and considered insecure.

You should be using DBMS_CRYPTO:

select rawtohex(
   DBMS_CRYPTO.Hash (
       UTL_I18N.STRING_TO_RAW ('foo', 'AL32UTF8'),  2)
    ) from dual;

Output:

ACBD18DB4CC2F85CEDEF654FCCC4A4D8

Here is a SHA512 function that I use to generate a hash KEY. Note if you are storing the value it should be in a column defined as RAW.

CREATE or REPLACE FUNCTION HASH_SHA512 (
    psINPUT IN VARCHAR2
    ) RETURN VARCHAR2 AS
    rHash RAW (512);
    BEGIN
    rHash := DBMS_CRYPTO.HASH (TO_CLOB (psINPUT), 
dbms_crypto.HASH_SH512);
    RETURN (LOWER (RAWTOHEX (rHash)));
    END HASH_SHA512;
/

SELECT HASH_SHA512('This is a SHA512 output') from dual

aae656dfa520d7609f45de886f8f677ea2fdebb88400c5c3d6e8412c221c99944c42d167f6b3401c12a9636ea6121ae610861b1e3340a0ddba7e0ea096a64f75