11 Replies Latest reply: Jan 24, 2008 6:17 AM by 269171 RSS

    Getting the MD5 of an entire ROW ?

    269171
      Hi All,

      I'm looking for how to get the MD5 of an entire row (or a known set of columns).

      I've found the DBMS_OBFUSCATION_TOOLKIT.MD5 function, which takes either "input IN RAW" or "input_string IN VARCHAR2" as a parameter.

      So, I've three questions:
      1) Is is possible to get the entire value Row in a manner to pass into this function? (e.g. As a String)
      2) Is this DBMS_OBFUSCATION_TOOLKIT package only in Enterprise edition and not in Standard edition ? (How do I check this)
      3) or Is there a better way to get the MD5 of a Row ??

      My row of data will contain a column of type MDSYS.SDO_GEOMETRY.

      Cheers,
      Ronan
        • 1. Re: Getting the MD5 of an entire ROW ?
          BluShadow
          1. Yes, you could concatenate all the columns together as a single string.
          2. Don't know. I've only ever used Enterprise edition. If you have standard edition, why not take a look. ;)
          3. Not that I know of.
          • 2. Re: Getting the MD5 of an entire ROW ?
            269171
            SQL> desc interstates;
            Name Null? Type
            ----------------------------------------------------- -------- -------------------------
            GEOM MDSYS.SDO_GEOMETRY
            HIGHWAY VARCHAR2(35)
            ROUTEN VARCHAR2(4)

            SQL> select DBMS_OBFUSCATION_TOOLKIT.MD5( ''||GEOM||HIGHWAY||ROUTEN ) FROM interstates;
            select DBMS_OBFUSCATION_TOOLKIT.MD5( ''||GEOM||HIGHWAY||ROUTEN ) FROM interstates
            *
            ERROR at line 1:
            ORA-00932: inconsistent datatypes: expected NUMBER got MDSYS.SDO_GEOMETRY


            SQL> select DBMS_OBFUSCATION_TOOLKIT.MD5( ''||HIGHWAY||ROUTEN ) FROM interstates;
            select DBMS_OBFUSCATION_TOOLKIT.MD5( ''||HIGHWAY||ROUTEN ) FROM interstates
            *
            ERROR at line 1:
            ORA-06553: PLS-307: too many declarations of 'MD5' match this call


            HHMMmmm ... Not much luck with that approach.

            Ronan
            • 3. Re: Getting the MD5 of an entire ROW ?
              269171
              Actually, I can get around the SDO_GEOMETRY problem like so:

              SQL> select DBMS_OBFUSCATION_TOOLKIT.MD5( TO_CHAR(SDO_UTIL.TO_WKTGEOMETRY(GEOM)||HIGHWAY||ROUTEN) ) AS MD5 FROM interstates;
              select DBMS_OBFUSCATION_TOOLKIT.MD5( TO_CHAR(SDO_UTIL.TO_WKTGEOMETRY(GEOM)||HIGHWAY||ROUTEN) ) AS MD5 FROM interstates
              *
              ERROR at line 1:
              ORA-06553: PLS-307: too many declarations of 'MD5' match this call

              But that still has the "too many declarations" error.

              Any way around this ?
              • 4. Re: Getting the MD5 of an entire ROW ?
                APC
                But that still has the "too many declarations" error.
                It's a feature. It's thrown when we have overloaded functions with similar argument lists which differ by there return type. We have to differentiate the functions by the parameter names. In your case something like this should work...
                select DBMS_OBFUSCATION_TOOLKIT.MD5(input_string=>TO_CHAR(SDO_UTIL.TO_WKTGEOMETRY(GEOM)||HIGHWAY||ROUTEN) ) AS MD5 
                FROM interstates
                ...
                Cheers, APC

                Blog : http://radiofreetooting.blogspot.com/
                • 5. Re: Getting the MD5 of an entire ROW ?
                  269171
                  Still no luck with that one...

                  select DBMS_OBFUSCATION_TOOLKIT.MD5( input_string => TO_CHAR( ROUTEN ) ) ) AS MD5 FROM interstates;

                  ERROR at line 1:
                  ORA-00907: missing right parenthesis

                  I've seen examples, using that input_string, but only in a procedure, I can't get it to work through a standard SQL statement.

                  For example:

                  SQL> SET SERVEROUTPUT ON;
                  SQL> BEGIN
                  2 DBMS_OUTPUT.PUT_LINE (
                  3 'md5 hash => ' ||
                  4 DBMS_OBFUSCATION_TOOLKIT.MD5 (
                  5 input => UTL_RAW.CAST_TO_RAW ('plain text'))
                  6 );
                  7 END;
                  8 /
                  md5 hash => 31BC5C2B8FD4F20CD747347B7504A385

                  PL/SQL procedure successfully completed.

                  SQL> SELECT DBMS_OBFUSCATION_TOOLKIT.MD5 ( input => UTL_RAW.CAST_TO_RAW ('plain text')) FROM DUAL;
                  SELECT DBMS_OBFUSCATION_TOOLKIT.MD5 ( input => UTL_RAW.CAST_TO_RAW ('plain text')) FROM DUAL
                  *
                  ERROR at line 1:
                  ORA-00907: missing right parenthesis
                  • 6. Re: Getting the MD5 of an entire ROW ?
                    MichaelS
                    You'll need at least 11g for this to work:
                    SQL>  select dbms_obfuscation_toolkit.md5 (input_string => ename) from emp where empno = 7788
                    
                    DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING=>ENAME)                               
                    --------------------------------------------------------------------------------
                    Ä5Ù6ë²®ÏÔ¾éy«Ý    
                    In lower versions it seems you're out of luck with this method and I suppose you need to do it in plsql ....
                    • 7. Re: Getting the MD5 of an entire ROW ?
                      269171
                      Ah OK .... I thought that I might be doing something wrong.

                      Thanks !
                      • 8. Re: Getting the MD5 of an entire ROW ?
                        Sentinel
                        In lower versions all you need to do is define a wrapper function that then calls the appropriate instance of the md5 function.

                        e.g.:
                        create or replace
                        function string2md5(input_string varchar2) 
                        return dbms_obfuscation_toolkit.varchar2_checksum is
                        begin
                          return dbms_obfuscation_toolkit.MD5(input_string=>input_string);
                        end;
                        • 9. Re: Getting the MD5 of an entire ROW ?
                          269171
                          Thanks Sentinel, I'll look into adding a function like that.

                          Although that particular function returns a slightly garbled response, so I'd like to call the other method which takes a RAW parameter.

                          create or replace
                          function string2md5(input_string varchar2)
                          return dbms_obfuscation_toolkit.varchar2_checksum is
                          begin
                          return dbms_obfuscation_toolkit.MD5(input=>UTL_RAW.CAST_TO_RAW(input_string));
                          end;
                          /

                          SQL> select string2md5( 'ABC' ) from dual;
                          select string2md5( 'ABC' ) from dual
                          *
                          ERROR at line 1:
                          ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                          ORA-06512: at "MVDEMO_TEST.STRING2MD5", line 4


                          Any ideas ?
                          • 10. Re: Getting the MD5 of an entire ROW ?
                            MichaelS
                            Check the signature:
                            create or replace function string2md5 (input_string varchar2)
                               return dbms_obfuscation_toolkit.raw_checksum
                            ...
                            • 11. Re: Getting the MD5 of an entire ROW ?
                              269171
                              D'Oh !

                              Brilliant, Thanks very much.