ANBOB™

接着上篇
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,
没有绝对解不了的密，只是个时间问题。

打赏

微信扫一扫，打赏作者吧～