oracle dbms_crypto,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!

打赏

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