) Implements MD5 + base64 password encryption in Oracle and SQL Server

From: http://blog.csdn.net/lele5000/article/details/6704247

Preface:

The implementation of MD5 and base64 functions is not the focus of this article. The focus is on the trouble of Character Set conversion during implementation.

 

Recently, due to work requirements, we need to integrate biee with the company's existing ERP system and use external tables.

The existing system is based on the. NET platform, and the database is SQL Server 2008 R2. The user password is saved in the database by converting MD5 to base64.

The Code logic is as follows:

View plain Copy to clipboard Print ?

1. Byte [] bytestring = NULL;
2. MD5 MD5 = md5.create ();
3. Bytestring = md5.computehash (encoding. Unicode. getbytes (STR ));
4. Md5.clear ();
5. String newstr = convert. tobase64string (bytestring );

Byte [] bytestring = NULL; <br/> MD5 MD5 = md5.create (); <br/> bytestring = md5.computehash (encoding. unicode. getbytes (STR); <br/> md5.clear (); <br/> string newstr = convert. tobase64string (bytestring); <br/>

Encoding. Unicode. getbytes (STR) is the most important part. Its function is to convert STR into a unicode byte array, which brings difficulties to the whole process! Because I do not understand C #, I do not know that Unicode in C # Is the utf16le (Small Header), and utf16be (BIG HEAD ). Nvachr in SQL Server should also use utf16le, while nvarchar2 in Oracle uses utf16be, which leads to inconsistent MD5 value strings!

 

The output values of qaz123 are:

 

ORACLE:

View plain Copy to clipboard Print ?

1. SQL> select utl_raw.cast_to_varchar2 (utl_encode.base64_encode (get_md5 ('qaz123 ') from dual;
3. Utl_raw.cast_to_varchar2 (utl_e
4. --------------------------------------------------------------------------------
5. Nphcqa7du9mfk4otoxogxq =

SQL> select transform (utl_encode.base64_encode (get_md5 ('qaz123 ') from dual; </P> <p> utl_raw.cast_to_varchar2 (utl_e <br/> reverse <br/> forward =

SQL Server:

View plain Copy to clipboard Print ?

1. Select DBO. f_tobase64 (hashbytes ('md5', convert (nvarchar, 'qaz123 ')))
3. Lwuqyuuskyohnzxd8wuh8q =

Select DBO. f_tobase64 (hashbytes ('md5', convert (nvarchar, 'qaz123') </P> <p> lwuqyuuskyohnzxd8wuh8q = <br/>

 

C #:

Lwuqyuuskyohnzxd8wuh8q =

The Oracle character set is al32utf8 and the SQL Server character set is GBK

Since qaz123 has the same encoding in utf8 and UTF16, even if I convert qaz123 to UTF16 in Oracle, the final result is not as expected, as shown below:

 

View plain Copy to clipboard Print ?

1. Select utl_raw.cast_to_varchar2 (utl_encode.base64_encode (get_md5 (N 'qaz123 ') from dual;
3. Utl_raw.cast_to_varchar2 (utl_e
4. --------------------------------------------------------------------------------
5. Nphcqa7du9mfk4otoxogxq =

Select transform (utl_encode.base64_encode (get_md5 (N 'qaz123 ') from dual; </P> <p> utl_raw.cast_to_varchar2 (utl_e <br/> encode =

The result is the same as that in C # Until I convert it to utf16le.

Note: The default UTF16 in Oracle is utf16be.

View plain Copy to clipboard Print ?

1. Select utl_raw.cast_to_varchar2 (utl_encode.base64_encode (get_md5 (convert ('qaz123 ', 'al16utf16le') from dual;
3. Utl_raw.cast_to_varchar2 (utl_e
4. --------------------------------------------------------------------------------
5. Lwuqyuuskyohnzxd8wuh8q =

Select utl_raw.cast_to_varchar2 (utl_encode.base64_encode (get_md5 (convert ('qaz123 ', 'al16utf16le') from dual; </P> <p> utl_raw.cast_to_varchar2 (utl_e <br/> unknown <br/> lwuqyuuskyohnzxd8wuh8q =

 

This is done!

 

To verify what I said above, let's take a look at the code points of qaz123 in different encodings in Oracle.

View plain Copy to clipboard Print ?

1. SQL> select 'utf8', dump ('qaz123') from dual
2. 2 Union all
3. 3 select 'utf16be', dump (n' qaz123 ') from dual
4. 4 union all
5. 5 select 'utf16le', dump (convert ('qaz123 ', 'al16utf16le') from dual
6. 6;
8. 'Utf8' dump ('qaz123 ')
9. -----------------------------------------------------
10. Utf8 typ = 96 Len = 6: 113,97, 122,49, 50, 51
11. Utf16be typ = 96 Len = 12: 0,113, 0,122
12. Utf16le typ = 1 Len = 12: 113,0, 97,0, 122,0, 49,0, 50,0, 51,0

SQL> select 'utf8', dump ('qaz123') from dual <br/> 2 Union all <br/> 3 select 'utf16be', dump (n'qaz123 ') from dual <br/> 4 union all <br/> 5 select 'utf16le', dump (convert ('qaz123 ', 'al16utf16le') from dual <br/> 6; </P> <p> 'utf8' dump ('qaz123 ') <br/> ------- -------------------------------------------- <br/> utf8 typ = 96 Len = 6: 113,97, 122,49, 50, 51 <br/> utf16be typ = 96 Len = 12: 0,113, 0,122, <br/> utf16le typ = 1 Len = 12: 113,0, 97,0,, 50, 0

 

 

 

The following describes the implementation code of functions in Oracle and SQL Server.

ORACLE:

Create an MD5 Function

View plain Copy to clipboard Print ?

1. Create or replace function get_md5
2. (P_str in varchar2)
3. Return varchar2 is
4. Begin
5. Return utl_raw.cast_to_raw (dbms_obfuscation_toolkit.md5 (input_string => lower (p_str )));
6. End;

Create or replace function get_md5 <br/> (p_str in varchar2) <br/> return varchar2 is <br/> begin <br/> return utl_raw.cast_to_raw (dbms_obfuscation_toolkit.md5 (input_string => lower (p_str); <br/> end;

Call to convert to base64

View plain Copy to clipboard Print ?

1. Select utl_raw.cast_to_varchar2 (utl_encode.base64_encode (get_md5 (convert ('qaz123 ', 'al16utf16le') from dual;

Select utl_raw.cast_to_varchar2 (utl_encode.base64_encode (get_md5 (convert ('qaz123 ', 'al16utf16le') from dual; <br/>

 

SQL Server:

Since SQL Server 2005, it comes with the hashbytes function to generate the hash value of the string, so the MD5 step can directly use the existing function.

As for base64 conversion, the system does not provide relevant functions, but we have a work ing method. The principle is that when SQL server uses for XML to generate an XML instance, binary data is usedBase64Encoding

The function is as follows:

View plain Copy to clipboard Print ?

1. Create Function f_tobase64
2. (@ Bin varbinary (max ))
3. Returns varchar (max)
4. As begin
5. Return cast (n'' as XML ). value ('xs: base64binary (XS: hexbinary (SQL: variable ("@ bin") ', 'varchar (max )')
6. End

Create Function f_tobase64 <br/> (@ bin varbinary (max) <br/> Returns varchar (max) <br/> As begin <br/> return cast (n'' as XML ). value ('xs: base64binary (XS: hexbinary (SQL: variable ("@ bin") ', 'varchar (max)') <br/> end <br/>

Final call:

View plain Copy to clipboard Print ?

1. Select DBO. f_tobase64 (hashbytes ('md5', convert (nvarchar, 'qaz123 ')))

Select DBO. f_tobase64 (hashbytes ('md5', convert (nvarchar, 'qaz123 ')))

Pay special attention to the red part to be consistent with the C # code logic.