oracle10G 加密解密之dbms_crypto (二)

接着上一篇，继续
Error Code Reason
28827 The specified cipher suite is not defined
28829 No value has been specified for the cipher suite to be used
28233 Source data was previously encrypted
28234 DES: Specified key size too short. DES keys must be at least 8 bytes (64 bits).
AES: Specified key size is not supported. AES keys must be 128, 192, or 256 bits
28239 The encryption key has not been specified or contains a NULL value
我们继续用前面测试9I加密时的方法

SQL> desc anbob.testcrypt
 Name                                            Null?    Type
 ----------------------------------------------------------------------------------- 
 ID                                                       NUMBER(38)
 PASSWD                                                  VARCHAR2(100)
 ENCRYPTED                                             VARCHAR2(1000)
SQL> select * from anbob.testcrypt;
        ID PASSWD                         ENCRYPTED
---------- ------------------------------ --------------------------------------------------
         1 123456                         遞:朋\\
         2 123456ab!                      t+鷏Sa廿
SQL> update anbob.testcrypt set encrypted=pkg_secure.encrypt(utl_raw.cast_to_raw(passwd));
update anbob.testcrypt set encrypted=pkg_secure.encrypt(utl_raw.cast_to_raw(passwd))
                                     *
ERROR at line 1:
ORA-28239: no key provided
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 3
ORA-06512: at "SYS.DBMS_CRYPTO", line 10
ORA-06512: at "SYS.PKG_SECURE", line 6
--好28239错误，说明在KEY上，注意key 与 传入的数据必须为RAW，我们转换一下，转换用utl_i18n包或utl_raw都可以
SQL> l
  1  CREATE OR REPLACE PACKAGE pkg_secure
  2   IS
  3    typ PLS_INTEGER :=DBMS_CRYPTO.ENCRYPT_AES256
  4                       +DBMS_CRYPTO.CHAIN_CBC
  5                      +DBMS_CRYPTO.PAD_PKCS5;
  6    key CONSTANT RAW(32) :=utl_raw.cast_to_raw('mark1234');
  7    FUNCTION encrypt(input RAW) RETURN RAW;
  8    FUNCTION decrypt(encry_str RAW) RETURN RAW;
  9*  END;
SQL> select pkg_secure.encrypt(utl_i18n.string_to_raw('hello serapy ! ')) from dual;
select pkg_secure.encrypt(utl_i18n.string_to_raw('hello serapy ! ')) from dual
       *
ERROR at line 1:
ORA-28234: key length too short
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 3
ORA-06512: at "SYS.DBMS_CRYPTO", line 10
ORA-06512: at "SYS.PKG_SECURE", line 6
--再查询遇到了28234错误，看上面的提示，AES keys must be 128, 192, or 256 bits
复制

SQL> select dbms_crypto.randombytes(128/8) from dual;
DBMS_CRYPTO.RANDOMBYTES(128/8)
-----------------------------------------------
4BBAC145F4B6EA4D12A524BE181AB1A5
SQL> l
  1  CREATE OR REPLACE PACKAGE pkg_secure
  2   IS
  3    typ PLS_INTEGER :=DBMS_CRYPTO.ENCRYPT_AES256
  4                       +DBMS_CRYPTO.CHAIN_CBC
  5                      +DBMS_CRYPTO.PAD_PKCS5;
  6    key CONSTANT RAW(128) :=utl_raw.cast_to_raw('4BBAC145F4B6EA4D12A524BE181AB1A5');
  7    FUNCTION encrypt(input RAW) RETURN RAW;
  8    FUNCTION decrypt(encry_str RAW) RETURN RAW;
  9*  END;
SQL> update anbob.testcrypt set encrypted=pkg_secure.encrypt(utl_raw.cast_to_raw(passwd));
2 rows updated.
SQL> select * from anbob.testcrypt;
        ID PASSWD                         ENCRYPTED
---------- ------------------------------ --------------------------------------------------
         1 123456                         8C0A2C572DAC84D6E02024AE14DA869D
         2 123456ab!                      EB648BD238CE4D34795118BFA4569884
SQL> select id,passwd,pkg_secure.decrypt(encrypted) decrypted from anbob.testcrypt;
        ID PASSWD                         DECRYPTED
---------- ------------------------------ ------------------------------
         1 123456                         313233343536
         2 123456ab!                      313233343536616221
复制

tip:
可以正常加密码与解密了，但解密RETURN 还是raw databype,再转一下

SQL> select id,passwd,utl_raw.cast_to_varchar2(pkg_secure.decrypt(encrypted)) decrypted from anbob.testcrypt;
        ID PASSWD                         DECRYPTED
---------- ------------------------------ ------------------------------
         1 123456                         123456
         2 123456ab!                      123456ab!
--解密成功
SQL>l
  1  CREATE OR REPLACE PACKAGE pkg_secure
  2       IS
  3        typ PLS_INTEGER :=dbms_crypto.DES_CBC_PKCS5;
  4        key CONSTANT RAW(128) :=utl_raw.cast_to_raw('MARK1234');
  5        FUNCTION encrypt(input RAW) RETURN RAW;
  6        FUNCTION decrypt(encry_str RAW) RETURN RAW;
  7*     END;
--这样也是可以的，只是typ 成了一个组合套装，
SQL> select pkg_secure.encrypt(utl_raw.cast_to_raw(passwd)) from anbob.testcrypt;
PKG_SECURE.ENCRYPT(UTL_RAW.CAST_TO_RAW(PASSWD))
---------------------------------------------------------------
EADCCC1D9C19BFEA
D83D13029E7D8CA408F44196F15AAD06
SQL> select passwd,utl_raw.cast_to_varchar2(pkg_secure.decrypt(pkg_secure.encrypt(utl_raw.cast_to_raw(passwd)))) decrypted from anbob.testcrypt;
PASSWD                         DECRYPTED
------------------------------ ------------------------------
123456                         123456
123456ab!                      123456ab!
复制