转自:

写在前面：

MD5和Base64的函数实现并不是本文的重点，重点是在实现过程中遇到的字符集转换的麻烦

 

最近由于工作需要，要将BIEE与公司现有的ERP系统做集成，使用外部表的方式。

现有系统基于.Net平台，数据库为SQL Server 2008 R2，用户密码采用先算MD5在转Base64的方式存于数据库中。

代码逻辑如下

byte[] byteString = null;
MD5 md5 = MD5.Create();
byteString = md5.ComputeHash(Encoding.Unicode.GetBytes(Str));
md5.Clear();
string NewStr = Convert.ToBase64String(byteString);

byte[] byteString = null; MD5 md5 = MD5.Create(); byteString = md5.ComputeHash(Encoding.Unicode.GetBytes(Str)); md5.Clear(); string NewStr = Convert.ToBase64String(byteString);

Encoding.Unicode.GetBytes(Str)这句为最关键的部分，其作用是把Str转换成了Unicode字节数组，正是这里给整个工作带来了困难！因为本人不懂C#，所以不知道C#里的Unicode是使用的UTF16LE(小头)，而非常见的UTF16BE(大头)。SQL Server 里的Nvachr应该也是采用的是UTF16LE，而Oracle里的NVarchar2则是采用的UTF16BE，正是因为这点导致MD5出来的值串不一致！

 

对于qaz123三者的输出值分别为：

 

Oracle:

SQL> select utl_raw.cast_to_varchar2(utl_encode.base64_encode(GET_MD5('qaz123'))) from dual;

UTL_RAW.CAST_TO_VARCHAR2(UTL_E
--------------------------------------------------------------------------------
NPhcqA7DU9MFK4otOXOgxQ==

SQL> select utl_raw.cast_to_varchar2(utl_encode.base64_encode(GET_MD5('qaz123'))) from dual; UTL_RAW.CAST_TO_VARCHAR2(UTL_E -------------------------------------------------------------------------------- NPhcqA7DU9MFK4otOXOgxQ==

SQL Server:

select dbo.f_ToBase64(hashbytes('MD5',convert(NVARCHAR,'qaz123')))

LwUQYUuskyOhnZXD8WUh8Q==

select dbo.f_ToBase64(hashbytes('MD5',convert(NVARCHAR,'qaz123'))) LwUQYUuskyOhnZXD8WUh8Q==

 

C#:

LwUQYUuskyOhnZXD8WUh8Q==

Oracle的字符集为AL32UTF8、SQL Server的字符集为GBK

由于qaz123在UTF8和UTF16下的编码都一样所以就算我在Oracle下把qaz123转换成UTF16，其最终结果还是不是预期的，如下：

 

select utl_raw.cast_to_varchar2(utl_encode.base64_encode(GET_MD5(N'qaz123'))) from dual;

UTL_RAW.CAST_TO_VARCHAR2(UTL_E
--------------------------------------------------------------------------------
NPhcqA7DU9MFK4otOXOgxQ==

select utl_raw.cast_to_varchar2(utl_encode.base64_encode(GET_MD5(N'qaz123'))) from dual; UTL_RAW.CAST_TO_VARCHAR2(UTL_E -------------------------------------------------------------------------------- NPhcqA7DU9MFK4otOXOgxQ==

直到我把其转为UTF16LE，结果才和C#中的一样

注：Oracle中的UTF16默认为UTF16BE

select utl_raw.cast_to_varchar2(utl_encode.base64_encode(GET_MD5(convert('qaz123','AL16UTF16LE')))) from dual;

UTL_RAW.CAST_TO_VARCHAR2(UTL_E
--------------------------------------------------------------------------------
LwUQYUuskyOhnZXD8WUh8Q==

select utl_raw.cast_to_varchar2(utl_encode.base64_encode(GET_MD5(convert('qaz123','AL16UTF16LE')))) from dual; UTL_RAW.CAST_TO_VARCHAR2(UTL_E -------------------------------------------------------------------------------- LwUQYUuskyOhnZXD8WUh8Q==

 

至此，搞定！

 

为了验证上面我的说法，可以看一下Oracle中，qaz123在不同编码下的代码点

SQL> select 'UTF8',dump('qaz123') from dual
  2  union all
  3  select 'UTF16BE',dump(N'qaz123') from dual
  4  union all
  5  select 'UTF16LE', dump(convert('qaz123','AL16UTF16LE')) from dual
  6  ;

'UTF8'  DUMP('QAZ123')
------- ----------------------------------------------
UTF8    Typ=96 Len=6: 113,97,122,49,50,51
UTF16BE Typ=96 Len=12: 0,113,0,97,0,122,0,49,0,50,0,51
UTF16LE Typ=1 Len=12: 113,0,97,0,122,0,49,0,50,0,51,0

SQL> select 'UTF8',dump('qaz123') from dual 2 union all 3 select 'UTF16BE',dump(N'qaz123') from dual 4 union all 5 select 'UTF16LE', dump(convert('qaz123','AL16UTF16LE')) from dual 6 ; 'UTF8' DUMP('QAZ123') ------- ---------------------------------------------- UTF8 Typ=96 Len=6: 113,97,122,49,50,51 UTF16BE Typ=96 Len=12: 0,113,0,97,0,122,0,49,0,50,0,51 UTF16LE Typ=1 Len=12: 113,0,97,0,122,0,49,0,50,0,51,0

 

 

 

下面给出Oracle和SQL Server下的相关函数实现代码

Oracle:

创建MD5函数

CREATE OR REPLACE FUNCTION GET_MD5
( p_str in varchar2)
RETURN varchar2 IS
BEGIN
RETURN Utl_Raw.Cast_To_Raw(DBMS_OBFUSCATION_TOOLKIT.MD5(input_string => lower(P_Str)));
END;

CREATE OR REPLACE FUNCTION GET_MD5 ( p_str in varchar2) RETURN varchar2 IS BEGIN RETURN Utl_Raw.Cast_To_Raw(DBMS_OBFUSCATION_TOOLKIT.MD5(input_string => lower(P_Str))); END;

调用转换成Base64

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;

 

SQL Server:

从SQL Server 2005开始，其自带了hashbytes函数用于生成字符串的Hash值，所以MD5这一步就可以直接采用现有的函数了。

至于转换成Base64，系统并没有提供相关函数，不过我们有变通的方法，原理是SQL Server使用FOR XML 生成xml 实例时，binary 数据使用base64 编码

函数如下：

create function f_ToBase64
(@bin varbinary(max))
returns varchar(max)
as begin
return cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(max)')
end

create function f_ToBase64 (@bin varbinary(max)) returns varchar(max) as begin return cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(max)') end

最终调用：

select dbo.f_ToBase64(hashbytes('MD5',convert(NVARCHAR,'qaz123')))

select dbo.f_ToBase64(hashbytes('MD5',convert(NVARCHAR,'qaz123')))

特别需要注意红色部分，这是为了和C#代码逻辑保持一致。