接着上篇
http://www.anbob.com/?p=1313
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
...
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
C:\>echo %nls_lang%
AMERICAN_AMERICA.WE8ISO8859P1
C:\>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
C:\>echo %nls_lang%
AMERICAN_AMERICA.ZHS16GBK
SQL> create table testcrypt(id int,passwd varchar2(100),encrypted varchar2(1000));
Table created.
SQL> insert into testcrypt values (1,'123456',null);
1 row created.
SQL> insert into testcrypt values (2,'123456ab!',null);
1 row created.
SQL> commit;
SQL> select * from testcrypt
ID PASSWD ENCRYPTED
---------- ------------------------------ ---------------------------------
1 123456
2 123456ab!
SQL> update testcrypt set encrypted=encrypt(passwd,'mark1234');
2 rows updated.
SQL> select * from testcrypt;
ID PASSWD ENCRYPTED
---------- ------------------------------ ---------------------------------
1 123456 遞:朋\
2 123456ab! t?+鷏S怲a貈
SQL> select id,passwd,decrypt(encrypted,'mark1234')decrypted from testcrypt;
ID PASSWD DECRYPTED
---------- ------------------------------ ----------------------------------------
1 123456 123456
2 123456ab! 123456ab!
再改进一下，为了查询加密后的数据格式更好看
SQL> create or replace function encrypt(p_in varchar2,p_key varchar2) return varchar2
2 is
3 v_in varchar2(255);
4 v_rtn varchar2(1000);
5 begin
6 v_in := rpad(p_in,(trunc(length(p_in)/8)+1)*8,chr(0));
7 return UTL_RAW.CAST_TO_RAW(dbms_obfuscation_toolkit.desencrypt(input_string=>v_in,key_string=>p_key));
8 end;
9 /
Function created.
SQL> create or replace function decrypt(p_in varchar2,p_key varchar2) return varchar2
2 is
3 v_in varchar2(2000);
4 begin
5 dbms_obfuscation_toolkit.DESDECRYPT(input_string => UTL_RAW.CAST_TO_varchar2(p_in),key_string => p_key,
6 decrypted_string=> v_in);
7 v_in := rtrim(v_in,chr(0));
8 return v_in;
9 end;
10 /
Function created.
SQL> update testcrypt set encrypted=encrypt(passwd,'mark1234');
2 rows updated.
SQL> select * from testcrypt;
ID PASSWD ENCRYPTED
---------- ------------------------------ --------------------------------------------------
1 123456 DF663A7FC5F35C0C
2 123456ab! 749A192BFA6C53905461D880A59D0F1C
SQL> select id,passwd,decrypt(encrypted,'mark1234')decrypted from testcrypt;
ID PASSWD DECRYPTED
---------- ------------------------------ --------------------------------------------------
1 123456 123456
2 123456ab! 123456ab!
DES3加密码解密也一样
dbms_obfuscation_toolkit.DES3Encrypt
dbms_obfuscation_toolkit.DES3Decrypt
单向加密算法
dbms_obfuscation_toolkit.md5
SQL> insert into testcrypt values(3,'anbob.com',null);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from testcrypt;
ID PASSWD ENCRYPTED
---------- ------------------------------ --------------------------------------------------
1 123456 DF663A7FC5F35C0C
2 123456ab! 749A192BFA6C53905461D880A59D0F1C
3 anbob.com
SQL> create or replace function encrypt_md5(p_in varchar2) return varchar2
2 is
3 begin
4 return RawToHex(UTL_RAW.CAST_TO_RAW(dbms_obfuscation_toolkit.MD5(input_string=>p_in)));
5 end;
6 /
Function created.
SQL> update testcrypt set encrypted=encrypt_md5(passwd) where id=3;
1 row updated.
SQL> select * from testcrypt;
ID PASSWD ENCRYPTED
---------- ------------------------------ --------------------------------------------------
1 123456 DF663A7FC5F35C0C
2 123456ab! 749A192BFA6C53905461D880A59D0F1C
3 anbob.com 354A5B219144A6B3833864596D87DE6A
SQL> select 1 from testcrypt where id=3 and encrypted=encrypt_md5('anbob.com');
1
----------
1
SQL> select 1 from testcrypt where id=3 and encrypted=encrypt_md5('anbob.cn');
no rows selected
NOTE:DES3Encrypt,DESEncrypt加密的数据必须是8byte的倍数,KEY最少8byte，8数据的来历可以去研究DES的加密算法，
CLENT和服务器字符集要一致避免隐式转换造成的解密错误。md5没有加密前的数据长度限制，返回的数据是32bytes,
没有绝对解不了的密，只是个时间问题。