Home » SQL & PL/SQL » SQL & PL/SQL » Convert Number (16 bytes) to encrypted raw value (Oracle 10g)
icon4.gif  Convert Number (16 bytes) to encrypted raw value [message #316862] Mon, 28 April 2008 11:08 Go to next message
fnoyer
Messages: 2
Registered: April 2008
Junior Member
Hi.

I'd like to have an "encrypted index" for new entries in the database. For that I'd like to have an sequence with a number and before each insertion the sequence number is encrypted with DBMS_OBFUSCATION_TOOLKIT.desencrypt.

To sum up :
NUMBER -> RAW (8 bytes) -- encrypted --> RAW (8 bytes)

My main concern is the transformation of the NUMBER representating the 8 bytes value into a raw 8 bytes

I try to convert with :
utl_raw.cast_from_number
utl_raw.CAST_FROM_BINARY_INTEGER
I cannot convert my 8 bytes values (due to 4 bytes representation or oracle internal number representation)

Please help me. I feel desesperated
Embarassed
Re: Convert Number (16 bytes) to encrypted raw value [message #316866 is a reply to message #316862] Mon, 28 April 2008 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select utl_raw.cast_from_number(1) from dual;

C102

Where is the problem?
What is exactly your issue.
Explain with a number, for instance 1, what you want.

Regards
Michel
Re: Convert Number (16 bytes) to encrypted raw value [message #316875 is a reply to message #316866] Mon, 28 April 2008 12:43 Go to previous messageGo to next message
fnoyer
Messages: 2
Registered: April 2008
Junior Member
Hi Michel.

This is mainly the algorithm (sorry for the by hand padding Sad

I have a number into a sequence that is limited from 0 to 2^64-1 and positive.
I want to encrypt it for scrambling.
But when I try to convert this sequence number into a raw byte (required by the encryption function), there is limitation.
For instance :
data := utl_raw.cast_from_number(17179869184); it bug -> ORA-06502: PL/SQL: numeric or value error: raw variable length too long
and 17179869184 only equals 2^34.
If I try with cast_from_binary_integer it seems to be limited to 4 bytes values and it seems to be signed.



Quote:
DECLARE
a NUMBER(19) := 18446744073709551615;
data RAW(8);
data2 VARCHAR2(16);
resultat RAW(8);
pad INTEGER;
g_key RAW(32767) := UTL_RAW.cast_to_raw('12345678');
BEGIN
data := utl_raw.cast_from_number(a);

pad := (8 - utl_raw.LENGTH(data));


DBMS_OUTPUT.PUT_LINE(' pad' || pad);
DBMS_OUTPUT.PUT_LINE(' utl_raw.LENGTH(data)' || utl_raw.LENGTH(data));

DBMS_OUTPUT.PUT_LINE(' data ' || data);
data := concat(0,data);
DBMS_OUTPUT.PUT_LINE(' data ' || data);
data := concat(0,data);

DBMS_OUTPUT.PUT_LINE(' data ' || data);
data := concat(0,data);
DBMS_OUTPUT.PUT_LINE(' data ' || data);
data := concat(0,data);
DBMS_OUTPUT.PUT_LINE(' data ' || data);
data := concat(0,data);
DBMS_OUTPUT.PUT_LINE(' data ' || data);
data := concat(0,data);
DBMS_OUTPUT.PUT_LINE(' data ' || data);
DBMS_OBFUSCATION_TOOLKIT.desencrypt(input => data ,
key => g_key,
encrypted_data => resultat);
DBMS_OUTPUT.PUT_LINE(' resultat' || resultat);
DBMS_OUTPUT.PUT_LINE(' resultat ' || resultat);
DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => resultat,
key => g_key,
decrypted_data => data2);
DBMS_OUTPUT.PUT_LINE(' data2 ' || data2);
END;
Re: Convert Number (16 bytes) to encrypted raw value [message #316882 is a reply to message #316875] Mon, 28 April 2008 13:12 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select utl_raw.cast_from_number(17179869184) from dual;

C6024850575C55

SQL> declare 
  1    a raw(100);
  2  begin
  3    a := utl_raw.cast_from_number(17179869184);
  4  end;
  5  /

Instruction traitée.

SQL> declare 
  1    a raw(100);
  2  begin
  3    a := utl_raw.cast_from_number(18446744073709551615);
  4  end;
  5  /

Instruction traitée.

RAW( 8 ) is too small. Oracle does not store numbers as you think.
See my previous example 1 -> C102 not 1.

Regards
Michel

[Updated on: Mon, 28 April 2008 13:13]

Report message to a moderator

Previous Topic: problem with implementing a scenario in sql due to group by limitation
Next Topic: Return all months in format of YYYYMM between two given dates with a sql query
Goto Forum:
  


Current Time: Tue Dec 06 14:12:04 CST 2016

Total time taken to generate the page: 0.05580 seconds