Forum Stats

  • 3,758,076 Users
  • 2,251,327 Discussions
  • 7,870,016 Comments

Discussions

Crypt and decrypt strings and clobs

User_G3NSO
User_G3NSO Member Posts: 9 Green Ribbon

I'm trying to implement encryption and decryption for strings as a lead into a larger project where I will have to do the same for CLOBS.


I wanted to get a small working sample first before I take the next step but I'm running into an issue with strings with the last step of my test and was hoping someone could help me out as the last query to decrypt the password is failing and I can't seem to find the issue and would appreciate some help and expertise.


Secondly, once this process is working could it be easily retrofitted to encrypt and decrypt clobs? I was hoping to use as much as the same code as possible with minimal amount of changes to work with CLOBS.


My test CASE is below and I know the password can be seen and I'll address that later on. Btw, I ran this on livesql in case anyone needs to test with the same setup



CREATE TABLE customer (

            id           NUMBER,

           username     VARCHAR2(30),

          cardnumber   NUMBER,

         password     VARCHAR2(200),

        CONSTRAINT customer_pk PRIMARY KEY(id)

         );

create sequence customer_seq;
CREATE OR REPLACE PACKAGE encrypt_decrypt

AS

   FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;

   FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;

END;

/

CREATE OR REPLACE PACKAGE BODY encrypt_decrypt

AS

     encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES

                                     + DBMS_CRYPTO.CHAIN_CBC

                                     + DBMS_CRYPTO.PAD_PKCS5;

     encryption_key     RAW (32) := UTL_RAW.cast_to_raw('HIDEANDSEEKINFO321');


     FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC

     IS

        encrypted_raw      RAW (2000);

     BEGIN

        encrypted_raw := DBMS_CRYPTO.ENCRYPT

        (

           src => UTL_RAW.CAST_TO_RAW (p_plainText),

           typ => encryption_type,

           key => encryption_key

        );

       RETURN encrypted_raw;

     END encrypt;

     FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC

     IS

        decrypted_raw      RAW (2000);

     BEGIN

        decrypted_raw := DBMS_CRYPTO.DECRYPT

        (

            src => p_encryptedText,

            typ => encryption_type,

            key => encryption_key

        );

        RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw));

     END decrypt;

END;
/

insert into customer(id,username,cardnumber,password) values (customer_seq.nextval,'Fredrik',32435683, encrypt_decrypt.encrypt('kI32432J'));

-- Problem is here

Select id,username,cardnumber,password,encrypt_decrypt(password) decrypted password from customer;


Tagged:

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 467 Bronze Trophy
    -- Problem is here
    
    Select id,username,cardnumber,password,encrypt_decrypt(password) decrypted password from customer;
    

    To correct the syntax error, refer to SQL Language Reference.

  • mathguy
    mathguy Member Posts: 10,071 Blue Diamond

    Before you take another step: Are you seriously planning to store encrypted passwords (as opposed to hashed passwords, which is a very different thing)? Are you aware of the possible problems?

    Why would you, or anyone, ever need to decrypt a password?

  • User_G3NSO
    User_G3NSO Member Posts: 9 Green Ribbon

    @mathguy NO I have no intention of storing encrypted passwords. My intention was to try to get this working as a POC to possibly encrypt clobs.

    For the CLOB project I am successfully generating sha256 hash keys for CLOBS as a start. Before I go all IN I need to see the pitfalls of going down this route

  • User_G3NSO
    User_G3NSO Member Posts: 9 Green Ribbon

    @User_H3J7U your suggestion is worthless as I already referred to the documentation that's why I posted a question here. If you have nothing of substance to add perhaps you shouldn't comment whatsoever

  • ascheffer
    ascheffer Member Posts: 1,910 Gold Trophy
    edited Sep 10, 2021 1:14PM

    You have given a lot of information about the project you are working on, but forgot to tell us what your issue/problem is. If it is ORA-00923 user User_H3J7U might be correct to point you to the SQL documentation (but even I can't find the proper syntax description of a column alias in that documentation :) ).

  • Stefan Jager
    Stefan Jager Member Posts: 1,751 Silver Trophy
    Select id,username,cardnumber,password,encrypt_decrypt(password) decrypted password from customer;
    

    You are calling the package with a parameter instead of the function, and then the alias you're giving it has a space, so that won't work either. Shouldn't it be

    encrypt_decrypt.decrypt(password) as decrypted_password
    

    ?