CODEWITHSUNDEEP
sqloracle-databaseencryptionplsql
Максим Ермолаев
Максим Ермолаев

Reputation: 23

Encrypting strings in SQL (likely ORACLE PL SQL). Caesar Cipher

I need something like the Caesar Cipher to be used in my string columns for the every value in each column. It should be made something like n+1: ABcd012Ab -> BCde123Bc

The string characters may be null, may contain sepparators (, - etc.), they may be upper and lower case (it doesnt matter).

Finaly, it should be created as a procedure, and this procedure should be then used inside an UPDATE query.

It shold maybe look something like this:

Create procedure text_change(n varchar(1000))
declare @i char
declare @l varchar(100 char)
begin
For each @l in n
For each @i in @l
loop
@i = ????
end loop;
return @l;
end;

UPDATE name_of_table
SET name_of_column = text_change(column)

Would be very happy for any help!

Upvotes: 2

Views: 7840

Answers (2)

David Faber
David Faber

Reputation: 12485

If you only want to "encrypt" alphanumerics, you might try the following. You didn't specify what you wanted done with 9, Z, or z so I just took the next ASCII character (:, [, and { respectively):

SELECT mycolumn
     , TRANSLATE( mycolumn
                , '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
                , '123456789:BCDEFGHIJKLMNOPQRSTUVWXYZ[bcdefghijklmnopqrstuvwxyz{' )
  FROM mytable;

Hope this helps.

EDIT: I'm not sure why I've continued to think about this, but here is a general solution with user-defined function using Oracle's TRANSLATE() function. It doesn't include numbers but I'm sure those would be an easy addition:

CREATE OR REPLACE FUNCTION caesar_cipher
  ( p_source IN VARCHAR2, p_offset IN PLS_INTEGER )
RETURN VARCHAR2
IS
  c_abc CONSTANT VARCHAR2(128) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  v_offset PLS_INTEGER;
  v_target VARCHAR2(32767);
  v_transl VARCHAR2(128);
BEGIN
  v_offset := MOD( p_offset, LENGTH(c_abc) );
  IF (v_offset < 0) THEN
    v_offset := v_offset + LENGTH(c_abc);
 END IF;
 v_transl := SUBSTR(c_abc, v_offset+1) || SUBSTR(c_abc, 1, v_offset);
 v_target := TRANSLATE( p_source, c_abc || LOWER(c_abc), v_transl || LOWER(v_transl) );

  RETURN v_target;
END;
/

To "decrypt" use a negative value for the offset instead of a positive one; that is, CAESAR_CIPHER('CDE', -2) is the opposite of CAESAR_CIPHER('ABC', 2). It strikes me as more efficient than examining every character of the source string but I've not run a test to be sure.

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

Why restrict yourself to Caesar Cipher? You could make use of DBMS_CRYPTO package which allows you to use Data Encryption Standard (DES)

Docs

Firstly, get execute permission to this package from DBA.

SQL> GRANT EXECUTE ON DBMS_CRYPTO  TO HR;

Grant succeeded.

Then create a function like this.

CREATE OR REPLACE FUNCTION my_encrypt(
    p_source VARCHAR2,
    p_key    VARCHAR2 )
  RETURN VARCHAR2
AS
BEGIN
  RETURN UTL_RAW.CAST_TO_VARCHAR2 ( DBMS_CRYPTO.encrypt( UTL_RAW.CAST_TO_RAW (p_source), 
       dbms_crypto.DES_CBC_PKCS5, UTL_RAW.CAST_TO_RAW (p_key) ) );
END;
/

This uses DES_CBC_PKCS5 Block Cipher Suite.

So, when you run a query like this you get encrypted data.

SQL> SELECT my_encrypt('TREASURE UNDER OAK TREE',
  2                'The DBMS_CRYPTO package replaces DBMS_OBFUSCATION_TOOLKIT') AS
  3         encrypted
  4  FROM   dual;


ENCRYPTED
----------------------------
┐↨┐┐♣!┐ o)|┐┐┐┐┐┐┐┐

Decrypt function

CREATE OR REPLACE FUNCTION my_decrypt ( p_source VARCHAR2, p_key VARCHAR2  )
RETURN VARCHAR2 AS

BEGIN

   RETURN UTL_RAW.CAST_TO_VARCHAR2 ( DBMS_CRYPTO.decrypt( UTL_RAW.CAST_TO_RAW (p_source), dbms_crypto.DES_CBC_PKCS5, UTL_RAW.CAST_TO_RAW (p_key) ) );

END;
/


SQL> SELECT my_decrypt( my_encrypt('TREASURE UNDER OAK TREE',
  2                'The DBMS_CRYPTO package replaces DBMS_OBFUSCATION_TOOLKIT')  ,
  3                'The DBMS_CRYPTO package replaces DBMS_OBFUSCATION_TOOLKIT') AS
  4         decrypted
  5  FROM   dual;

DECRYPTED
---------------------------------
TREASURE UNDER OAK TREE

You could also use it to encrypt and decrypt the columns in the table.

update yourtable set SOMETEXT =
my_encrypt(SOMETEXT,'The DBMS_CRYPTO package replaces DBMS_OBFUSCATION_TOOLKIT');

update yourtable set SOMETEXT = 
my_decrypt(SOMETEXT,'The DBMS_CRYPTO package replaces DBMS_OBFUSCATION_TOOLKIT');

Upvotes: 2

Related Questions