Skip to Main Content
  • Questions

  • Storing Encrypted Value in Varchar2 column

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Saminathan.

Asked: January 21, 2004 - 8:35 pm UTC

Last updated: April 22, 2004 - 11:36 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Dear Tom,

After reading many threads in this forum, I understood that it is not a good thing store encrypted value in varchar2 datatype.
For some reason our existing application is storing DESEncrypted value in varchar2 column. We defined 2x size for varchar2 column so length is not an issue.

Questions

1)Is there any performance degradation if we store encrypted value in VARCHAR2 instead of RAW?

2) How to convert encrypted value which is stored in varchar2 into RAW datatype?


and Tom said...

1) not, it is just that if you select the varchar2 out and return it to a client -- it might get "changed" since character set conversion would kick in. That would destroy the string. You would have to perform all encrypt/decrypt in the database. using exp/imp to move the data could likewise "destroy" the data. It is just "unsafe"

2) you would have to change the encrypt/decrypt procedures you have and add a new column to the table that was raw and use UTL_RAW.CAST_TO_RAW() to convert the string to be encrypted to RAW and UTL_RAW.CAST_TO_VARCHAR2() to convert the decrypted raw string back into a varchar2.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Question About RAW & MD5 Hash

Sami, January 22, 2004 - 2:34 pm UTC 

1.When we say RAW, is it a Hexadecimal number all the time?

2) My java application expects to return HASH value(which we are going to store it in db) as STRING. So is it okay to store HASH value in VARCHAR2 datatype column?

I use below method for MD5 hash.
SQL> CREATE OR REPLACE FUNCTION      MD5_RAW (INPUT_VAL VARCHAR2)
  2  RETURN VARCHAR2
  3  IS
  4  SOMETHING_RAW  RAW(100) := UTL_RAW.CAST_TO_RAW(INPUT_VAL);
  5  BEGIN
  6  RETURN DBMS_OBFUSCATION_TOOLKIT.MD5( INPUT => SOMETHING_RAW );
  7  END;
  8  /

Tom Kyte
January 22, 2004 - 7:59 pm UTC

1) no, it is binary data.

it gets converted to hex for the pleasure of us human beings -- we can read hex. binary just makes our terminals go nuts.


2) that'll return a HEX representation -- it is a varchar2 -- not a raw, so it is 100% OK to save in varchar2, yes.

Absolutely outclass

hash, March 18, 2004 - 1:31 pm UTC

Tom, this expalanation is second to none. thanks a lot

A reader, April 22, 2004 - 11:36 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library