Re: How to write encryption/de-cryption function using PL/SQL

In article <4v547k$88q_at_news.agt.net>, gagg_at_agt.net says...
>
>Try looking at the PL/SQL TRANSLATE function. When you apply a touch of
>imagination this routine can be easily buried in a Stored Proc. and used to
>encript/decript data.
>
>
>Be carefull of using it in a where clause ... remember that any predicate
>containing a function call is a non-indexable predicate.
>
>In article <32091748.C10_at_hkstar.com>, warrenli_at_hkstar.com says...
>>
>>Can anyone give me some advice about how to write PL/SQL functions
>>for encrypting/decrpting data. Its datatype may be VARCHAR2, NUMBER
>>or DATE.
>>
>>Thank You
>

What you are talking about is known as a substitution cipher. This is an extremely easy cipher method to break. The reason being that even though it appears random, it isn't. There is still the same number of occurrences of each letter. A simple statistical count of the occurences of each letter will tell me that you are using a substitution cipher. Then all i have to do is figure out what each letter is substituted for. Even a slow machine can do a brute force substitution of all possibilities. Note that the German Enigma machine from WWII was a more sophisticated variation of a substitution cipher and it was broken in the 40's.

Anyway, if you want to use a substitution cipher here is one based on ROT13

CREATE OR REPLACE PROCEDURE system.rot13  ( password IN VARCHAR2 DEFAULT 'ORACLE', hash_value OUT VARCHAR2 ) AS   pwd_length NUMBER;
  plain_char CHAR(1);
  cypher_char CHAR(1);
  temp VARCHAR(20);
BEGIN
  temp := '';
  pwd_length := LENGTH(password);
  FOR lcntr IN 1 .. pwd_length
  LOOP

    plain_char  := UPPER(SUBSTR( password, lcntr, 1 ));
    cypher_char := CHR(ASCII(plain_char) + 13);
    temp        := temp||cypher_char;

Note that ROT13 was never intended to keep anything secret...

-- 
Tony Noble
tnoble_at_mt.gov
Opinions expressed do not necessarily reflect those of my employer.