7

I want to encrypt password column in the database and I am trying to use encrypt function present inside DBMS_CRYPTO package (have given execute access from sys account to the current user) but I am getting the following error. Please give me some examples of how to use this function:

select DBMS_CRYPTO.encrypt('12345', dbms_crypto.DES_CBC_PKCS5, 'A1') from dual;

Error:

ORA-06553: PLS-221: 'DES_CBC_PKCS5' is not a procedure or is undefined 06553. 00000 - "PLS-%s: %s"

Improve this question

3 Answers 3

Reset to default
5

The constant dbms_crypto.DES_CBC_PKCS5 is referenceble on PL/SQL only - not from SQL.

You must replace it with literal value in the SELECT statement.

To get the value use PL/SQL block

 begin
   dbms_output.put_line(dbms_crypto.DES_CBC_PKCS5);
 end;
 /

.

 4353

You must also use a longer key

 select DBMS_CRYPTO.encrypt(UTL_RAW.CAST_TO_RAW ('ABCDEFGH12345'), 4353 /* = dbms_crypto.DES_CBC_PKCS5 */, 'A1A2A3A4A5A6CAFE') from dual;

 9320CBCBD25E8721BD04990A0EAEAF00
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

The example that you have given works fine. But when I change the text to encrypt, I got the following error: (ORA-01465: invalid hex number 01465. 00000 - "invalid hex number") select DBMS_CRYPTO.encrypt('ABCDEFGH12345', 4353, 'A1A2A3A4A5A6CAFE') from dual;
The src parameter must be casted to RAW. I updateted the example.
Please tell how to decrypt as well. This is what I have tried so far. I encrypted as u said: select DBMS_CRYPTO.encrypt(UTL_RAW.CAST_TO_RAW ('ABCDEFGH12345'), 4353, UTL_RAW.CAST_TO_RAW ('A1A2A3A4A5A6CAFE')) from dual; (Output: 80AA4DEA59B77C433A2142AE9CDD235A). Now I am trying to decrypt to get the actual value: select DBMS_CRYPTO.decrypt('80AA4DEA59B77C433A2142AE9CDD235A', 4353, UTL_RAW.CAST_TO_RAW ('A1A2A3A4A5A6CAFE')) from dual; But I am getting result as 41424344454647483132333435
working now. Used UTL_RAW.CAST_TO_VARCHAR2 and got the actual value
The decrypt must be casted back to varchar: select utl_raw.cast_to_varchar2(DBMS_CRYPTO.decrypt('9320CBCBD25E8721BD04990A0EAEAF00', 4353 /* = dbms_crypto.DES_CBC_PKCS5 */, 'A1A2A3A4A5A6CAFE')) from dual;
3

Encrypt

select DBMS_CRYPTO.encrypt(UTL_RAW.CAST_TO_RAW('ABCDEFGH12345'), 4353 /* = dbms_crypto.DES_CBC_PKCS5 */, UTL_RAW.CAST_TO_RAW ('A1A2A3A4A5A6CAFE')) from dual;

Decrypt

select UTL_RAW.CAST_TO_varchar2(DBMS_CRYPTO.decrypt('80AA4DEA59B77C433A2142AE9CDD235A', 4353, UTL_RAW.CAST_TO_RAW ('A1A2A3A4A5A6CAFE'))) from dual;
Improve this answer

Comments

3

The answer above is fine, I add just some information about 4353. I saw this request used as is and I noticed this value is not really understood.

4353 is an addition of three informations about encryption used (des, aes and so on), block cipher mode of operation (ecb or cbc) and padding mode.

So, 4353 stands for 1 (des) + cbc mode (256) + pkcs5 padding (4096)

If you prefer aes 256, you have to use 4356

4358 stands for aes 128

and so on.

The Oracle page which describes the different parameters is here.

Hope this additional information helps everyone understand better DBMS_CRYPTO.

Improve this answer

1 Comment

That's correct. If it helps, think of the crypto constant as a bitfield describing all three factors required for symmetric encryption: Algorithm | ChainingMode | Padding. Addition works because of the components making up the field.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.