)Oracle、SQL Server下MD5+Base64密碼加密實現

轉自:http://blog.csdn.net/lele5000/article/details/6704247

寫在前面：

MD5和Base64的函數實現並不是本文的重點，重點是在實現過程中遇到的字元集轉換的麻煩

 

最近由於工作需要，要將BIEE與公司現有的ERP系統做整合，使用外部表格的方式。

現有系統基於.Net平台，資料庫為SQL Server 2008 R2，使用者密碼採用先算MD5在轉Base64的方式存於資料庫中。

代碼邏輯如下

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)這句為最關鍵的部分，其作用是把Str轉換成了Unicode位元組數組，正是這裡給整個工作帶來了困難！因為本人不懂C#，所以不知道C#裡的Unicode是使用的UTF16LE(小頭)，而非常見的UTF16BE(大頭)。SQL Server 裡的Nvachr應該也是採用的是UTF16LE，而Oracle裡的NVarchar2則是採用的UTF16BE，正是因為這點導致MD5出來的值串不一致！

 

對於qaz123三者的輸出值分別為：

 

Oracle:

view plain copy to clipboard print ?

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

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

SQL Server:

view plain copy to clipboard print ?

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

select dbo.f_ToBase64(hashbytes('MD5',convert(NVARCHAR,'qaz123'))) </p><p>LwUQYUuskyOhnZXD8WUh8Q==<br />

 

C#:

LwUQYUuskyOhnZXD8WUh8Q==

Oracle的字元集為AL32UTF8、SQL Server的字元集為GBK

由於qaz123在UTF8和UTF16下的編碼都一樣所以就算我在Oracle下把qaz123轉換成UTF16，其最終結果還是不是預期的，如下：

 

view plain copy to clipboard print ?

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

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

直到我把其轉為UTF16LE，結果才和C#中的一樣

註：Oracle中的UTF16預設為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;  
2.    
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 />--------------------------------------------------------------------------------<br />LwUQYUuskyOhnZXD8WUh8Q==

 

至此，搞定！

 

為了驗證上面我的說法，可以看一下Oracle中，qaz123在不同編碼下的代碼點

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  ;  
7.    
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,97,0,122,0,49,0,50,0,51  
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,97,0,122,0,49,0,50,0,51<br />UTF16LE Typ=1 Len=12: 113,0,97,0,122,0,49,0,50,0,51,0

 

 

 

下面給出Oracle和SQL Server下的相關函數實現代碼

Oracle:

建立MD5函數

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;

調用轉換成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:

從SQL Server 2005開始，其內建了hashbytes函數用於產生字串的Hash值，所以MD5這一步就可以直接採用現有的函數了。

至於轉換成Base64，系統並沒有提供相關函數，不過我們有變通的方法，原理是SQL Server使用FOR XML 產生xml 執行個體時，binary 資料使用base64 編碼

函數如下：

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 />

最終調用：

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')))

特別需要注意紅色部分，這是為了和C#代碼邏輯保持一致。