Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_CRYPTO (ORACLE 10.2.0)
DBMS_CRYPTO [message #413850] Fri, 17 July 2009 16:35 Go to next message
dattakumar
Messages: 9
Registered: July 2008
Location: NEW JERSEY, USA
Junior Member
Can any body please let me know how to pass the (p_in, p_key parameters) to execute the following function.

Note : I have been able to create the function in my schema, it works.

create or replace function get_enc_val
2 (
3 p_in in varchar2,
4 p_key in raw
5 )
6 return raw is
7 l_enc_val raw (2000);
8 l_mod number := dbms_crypto.ENCRYPT_AES128
9 + dbms_crypto.CHAIN_CBC
10 + dbms_crypto.PAD_PKCS5;
11 begin
12 l_enc_val := dbms_crypto.encrypt
13 (
14 UTL_I18N.STRING_TO_RAW
15 (p_in, 'AL32UTF8'),
16 l_mod,
17 p_key
18 );
19 return l_enc_val;
20* end;


Thanks
Kumar
Re: DBMS_CRYPTO [message #413854 is a reply to message #413850] Fri, 17 July 2009 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
CREATE OR REPLACE FUNCTION Get_enc_val (p_in   IN VARCHAR2, p_key  IN RAW)
RETURN RAW
IS
  l_enc_val  RAW(2000);
  l_mod      NUMBER := dbms_crypto.encrypt_aes128 + dbms_crypto.chain_cbc + dbms_crypto.pad_pkcs5;
BEGIN
  l_enc_val := dbms_crypto.Encrypt(utl_i18n.String_to_raw(p_in,'AL32UTF8'),l_mod,p_key);
  RETURN l_enc_val;
END; 


>Note : I have been able to create the function in my schema, it works.
>Can any body please let me know how to pass the (p_in, p_key parameters) to execute the following function.

How can you claim the function works, but not know how to invoke it?

What are test input values & expected/desired results?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Fri, 17 July 2009 17:26]

Report message to a moderator

Re: DBMS_CRYPTO [message #413858 is a reply to message #413854] Fri, 17 July 2009 18:45 Go to previous messageGo to next message
dattakumar
Messages: 9
Registered: July 2008
Location: NEW JERSEY, USA
Junior Member
Hello Members,

First of all I would like to apologize and I want to make it clear that I didn't greet the members at first, but please don't mistake that I am trying to be rude.

Again I am posting this code with a request if some body could help me with this.

Problem : I have been able to create the Function without any errors, I got the message Function created. Ofcourse, I got this code from a source. I am aware of the DBMS_CRYPTO, but my question here is that, in order to execute the function, I am unable to figure out how to pass the parameters. It may be a very primitive question, but for me it's a big task. Please some one help me.
CREATE OR REPLACE FUNCTION Get_enc_val
(p_in IN VARCHAR2,
p_key IN RAW)
RETURN RAW
IS
l_enc_val RAW(2000);
l_mod NUMBER := dbms_crypto.encrypt_aes128 + dbms_crypto.chain_cbc + dbms_crypto.pad_pkcs5;
BEGIN
l_enc_val := dbms_crypto.Encrypt(utl_i18n.String_to_raw(p_in,'AL32UTF8'),l_mod,
p_key);

RETURN l_enc_val;
END;

Thanks in advance,
Regards
Kumar
Re: DBMS_CRYPTO [message #413859 is a reply to message #413850] Fri, 17 July 2009 18:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_crypto.htm#ARPLS664

visit http://asktom.oracle.com & do KEYWORD search on DBMS_CRYPTO

[Updated on: Fri, 17 July 2009 18:55]

Report message to a moderator

Re: DBMS_CRYPTO [message #413860 is a reply to message #413859] Fri, 17 July 2009 18:55 Go to previous messageGo to next message
dattakumar
Messages: 9
Registered: July 2008
Location: NEW JERSEY, USA
Junior Member
Thank you Sir. I did look into this before posting my problem. Since I am new to this I had a little problem in understanding regarding passting the parameters... If you can please explain me, if you have time or when you get a chance, I really appreciate it Sir.

Regards
Kumar
Re: DBMS_CRYPTO [message #413861 is a reply to message #413850] Fri, 17 July 2009 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Do not try this at home or without consulting an Oracle GURU

SQL> set serveroutput on
SQL> 
SQL> set linesize 121
SQL> 
SQL> DECLARE
  2   l_credit_card_no VARCHAR2(19) := '1234-5678-9012-3456';
  3   l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no);
  4   l_key	RAW(128) := utl_raw.cast_to_raw('abcdefgh');
  5  
  6   l_encrypted_raw RAW(2048);
  7   l_decrypted_raw RAW(2048);
  8  BEGIN
  9    dbms_output.put_line('Original : ' || l_credit_card_no);
 10  
 11    l_encrypted_raw := dbms_crypto.encrypt(l_ccn_raw,
 12    dbms_crypto.des_cbc_pkcs5, l_key);
 13  
 14    dbms_output.put_line('Encrypted : ' ||
 15    RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw)));
 16  
 17    l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw,
 18    typ => dbms_crypto.des_cbc_pkcs5, key => l_key);
 19  
 20    dbms_output.put_line('Decrypted : ' ||
 21    utl_raw.cast_to_varchar2(l_decrypted_raw));
 22  END;
 23  /
Original : 1234-5678-9012-3456
Encrypted : 383038433541443437323232453031454333383933414233373435434643453936413643354144344245454345424136
Decrypted : 1234-5678-9012-3456

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> 
SQL> DECLARE
  2   enc_val	RAW(2000);
  3   l_key	RAW(2000);
  4   l_key_len NUMBER := 128/8; -- convert bits to bytes
  5   l_mod	NUMBER := dbms_crypto.ENCRYPT_AES128
  6   + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_ZERO;
  7  
  8  BEGIN
  9    l_key := dbms_crypto.randombytes(l_key_len);
 10  
 11    enc_val := dbms_crypto.encrypt(
 12    utl_i18n.string_to_raw('1234-5678-9012-3456', 'AL32UTF8'),
 13    l_mod, l_key);
 14  
 15    dbms_output.put_line(enc_val);
 16  END;
 17  /
5F32D693AA34522696FD4FC9A69E231DFE52EF668FD97073019FA47E341988B7

PL/SQL procedure successfully completed.
Re: DBMS_CRYPTO [message #413862 is a reply to message #413861] Fri, 17 July 2009 19:28 Go to previous messageGo to next message
dattakumar
Messages: 9
Registered: July 2008
Location: NEW JERSEY, USA
Junior Member
Though I didn't get the answer I was expecting, I really appreciate for your time Sir.

Regards
Kumar
Re: DBMS_CRYPTO [message #413865 is a reply to message #413850] Fri, 17 July 2009 21:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Though I didn't get the answer I was expecting
Sorry to disappoint.

SQL> @dbms_crypto1.sql                              
SQL> CREATE OR REPLACE FUNCTION Get_enc_val (p_in   IN VARCHAR2, p_key	IN RAW)
  2  RETURN RAW
  3  IS
  4    l_enc_val  RAW(2000);
  5    l_mod	  NUMBER := sys.dbms_crypto.encrypt_aes128 + dbms_crypto.chain_cbc + dbms_crypto.pad_pkcs5;
  6  BEGIN
  7    l_enc_val := sys.dbms_crypto.Encrypt(utl_i18n.String_to_raw(p_in,'AL32UTF8'),l_mod,p_key);
  8    RETURN l_enc_val;
  9  END;
 10  /

Function created.

SQL> set serveroutput on
SQL> declare
  2  	l_credit_card_no VARCHAR2(19) := '1234-5678-9012-3456';
  3  	-- l_key     RAW(128);
  4  	l_key	     RAW(32) :=  UTL_RAW.CAST_TO_RAW('1234567890abcdef');
  5  -- 					   1	     2	       3	 4	   5	     6
  6  	l_encrypted_raw RAW(4096);
  7  	v_encrypted	 VARCHAR2(32000);
  8  begin
  9  	l_encrypted_raw := Get_enc_val(l_credit_card_no , l_key);
 10  	dbms_output.put_line('Encrypted : ' || RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw)));
 11  end;
 12  /
Encrypted : 43344646313943423432443437303438443637394144423530363044393533423539413545374232343232454335373039323942304234373836413435353236

PL/SQL procedure successfully completed.


So what do you plan on doing with this new found knowledge?

[Updated on: Fri, 17 July 2009 22:29]

Report message to a moderator

Re: DBMS_CRYPTO [message #413915 is a reply to message #413862] Sat, 18 July 2009 17:18 Go to previous messageGo to next message
dattakumar
Messages: 9
Registered: July 2008
Location: NEW JERSEY, USA
Junior Member
Thank you so much Sir. Your patience and your knowledge is highly appreciated.

Regards
Kumar
Re: DBMS_CRYPTO [message #413961 is a reply to message #413862] Sun, 19 July 2009 17:05 Go to previous messageGo to next message
dattakumar
Messages: 9
Registered: July 2008
Location: NEW JERSEY, USA
Junior Member
Hi Mr.BlackSwan
Thanks for all the help. I had asked about how to pass the parameter when it's Type RAW. I was able to get to know how to pass the parameter after creating the function, with the help of your posting. The result is :

SQL> select get_enc_val('1234-5678-9012-3456',UTL_RAW.CAST_TO_RAW('1234567890abcdef')) result from dual;

RESULT
----------------------------------------------------------------------------------------------------------

C4FF19CB42D47048D679ADB5060D953B59A5E7B2422EC570929B0B4786A45526

SQL>

Thanks again,
Kumar
Re: DBMS_CRYPTO [message #413962 is a reply to message #413850] Sun, 19 July 2009 17:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Please note that your encrypted string is different than mine.

>C4FF19CB42D47048D679ADB5060D953B59A5E7B2422EC570929B0B4786A45526
>43344646313943423432443437303438443637394144423530363044393533423539413545374232343232454335373039323942304234373836413435353236


WHY are they different?

Which is correct?
Re: DBMS_CRYPTO [message #413963 is a reply to message #413962] Sun, 19 July 2009 18:04 Go to previous messageGo to next message
dattakumar
Messages: 9
Registered: July 2008
Location: NEW JERSEY, USA
Junior Member
In my opinion both are correct. The reason for the difference is (which is self-explanatory) :

l_encrypted_raw := Get_enc_val(l_credit_card_no , l_key);

dbms_output.put_line('Encrypted : ' || RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw)));

dbms_output.put_line('Encrypted without Casting : ' || l_encrypted_raw);

If I am wrong, please enlighten me on this.

Thanks
Kumar
Re: DBMS_CRYPTO [message #413964 is a reply to message #413850] Sun, 19 July 2009 18:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>In my opinion both are correct.
Yes, bits are bits & it does not matter how they are presented/displayed.
Re: DBMS_CRYPTO [message #413967 is a reply to message #413964] Sun, 19 July 2009 18:32 Go to previous message
dattakumar
Messages: 9
Registered: July 2008
Location: NEW JERSEY, USA
Junior Member
Thanks again!
Previous Topic: Oracle Convert number to asterisk
Next Topic: update
Goto Forum:
  


Current Time: Fri Dec 09 19:37:55 CST 2016

Total time taken to generate the page: 0.05961 seconds