Forum Stats

  • 3,839,996 Users
  • 2,262,557 Discussions
  • 7,901,120 Comments

Discussions

ORA-01400 Error in application

2»

Answers

  • 656867
    656867 Member Posts: 172
    Hi,

    Thank you for taking the time to help me out.

    This is the trigger I created. It works perfectly for incrementing and inserting my primary key. What I also need the trigger to do is take the password that is typed in and encrypt it.
    create or replace TRIGGER encrypt_pw_t1
    BEFORE INSERT on encrypt_pw
    FOR EACH ROW
    BEGIN
    IF :new.pw_id IS NULL
    THEN SELECT pw_id2_seq.nextval
    INTO :new.pw_id
    FROM DUAL;
    END IF;
    END;
    These are the two tables that are being used with the package and functions:
    CREATE TABLE ENCRYPT_PW 
    ( 
    PW_ID INTEGER CONSTRAINT PW_ID2_PK PRIMARY KEY,
    CLIENTID VARCHAR2(50 BYTE),
    CLIENT_CODE VARCHAR2(50 BYTE),
    PASSWORD VARCHAR2(256 BYTE),
    CLIENT_NAME VARCHAR2(256 BYTE)
    );
    
    
    CREATE TABLE ED_ENCRYPT 
    ( 
    PK VARCHAR2(128 BYTE), 
    EK RAW(128) 
    ); 
    This is the package and functions being created:
    CREATE OR REPLACE PACKAGE ENC_CLIENT 
    AS 
    FUNCTION ENC_PASSWORD( 
    p_password in VARCHAR2,
    p_clientid in VARCHAR2,
    p_unlock_code in VARCHAR2 DEFAULT NULL) 
    RETURN VARCHAR2; 
    
    FUNCTION DEC_PASSWORD( 
    p_password in VARCHAR2,
    p_clientid in VARCHAR2,
    p_unlock_code in VARCHAR2 DEFAULT NULL) 
    RETURN VARCHAR2; 
    END; 
    / 
    
    CREATE OR REPLACE PACKAGE BODY ENC_CLIENT 
    AS 
    g_master_key VARCHAR2(32) := 'ThisIsTheSuperSe'; 
    g_unlock_code VARCHAR2(10) := 'OpenSesame'; 
    l_mod number := dbms_crypto.ENCRYPT_AES128 
    + dbms_crypto.CHAIN_CBC 
    + dbms_crypto.PAD_PKCS5; 
    
    FUNCTION enc_password ( 
    p_password in VARCHAR2,
    p_clientid in VARCHAR2,
    p_unlock_code in VARCHAR2 DEFAULT NULL) 
    RETURN VARCHAR2 
    AS 
    l_key RAW(256); 
    l_stored_key RAW(256); 
    BEGIN 
    IF (p_unlock_code IS NULL 
    OR p_unlock_code != g_unlock_code) 
    THEN 
    RETURN NULL; 
    END IF; 
    
    -- Random key generation 
    l_key := dbms_crypto.randombytes(16); 
    
    -- Encrypts random key with master key into a stored key 
    l_stored_key := dbms_crypto.encrypt(l_key, 
    l_mod, 
    UTL_I18N.STRING_TO_RAW(g_master_key, 'AL32UTF8') 
    ); 
    
    -- store unique value and stored key in table 
    BEGIN
    DELETE ED_ENCRYPT WHERE PK = p_clientid;
    END;
    
    INSERT INTO ED_ENCRYPT 
    VALUES (p_clientid, l_stored_key); 
    
    -- returns encrypted salary that is encrypted with the random key 
    RETURN UTL_ENCODE.BASE64_ENCODE(dbms_crypto.encrypt(UTL_I18N.STRING_TO_RAW(p_password,'AL32UTF8'), 
    l_mod, 
    l_key)); 
    END; 
    
    FUNCTION dec_password ( 
    p_password in VARCHAR2,
    p_clientid in VARCHAR2,
    p_unlock_code in VARCHAR2 DEFAULT NULL) 
    RETURN VARCHAR2 
    AS 
    l_key RAW(256); 
    BEGIN 
    IF (p_unlock_code IS NULL 
    OR p_unlock_code != g_unlock_code) 
    THEN 
    RETURN NULL; 
    END IF; 
    
    SELECT dbms_crypto.decrypt(ek, 
    l_mod, 
    UTL_I18N.STRING_TO_RAW(g_master_key,'AL32UTF8')) 
    INTO l_key 
    FROM ED_ENCRYPT 
    WHERE pk = p_clientid; 
    RETURN UTL_I18N.RAW_TO_CHAR(dbms_crypto.decrypt(UTL_ENCODE.BASE64_DECODE(p_password),l_mod,l_key),'AL32UTF8'); 
    END; 
    END; 
    /
    If there is anything else that is needed, please let me know.

    Thanks again for your help.
  • ATD
    ATD Member Posts: 11,184
    edited Sep 10, 2008 8:58AM Answer ✓
    Hi,

    I hope that I'm reading your code correctly.... I think your trigger should be:
    CREATE OR REPLACE TRIGGER ENCRYPT_PW_T1
    BEFORE INSERT OR UPDATE ON ENCRYPT_PW
    FOR EACH ROW
    BEGIN
      IF :NEW.PW_ID IS NULL
        THEN SELECT PW_ID2_SEQ.NEXTVAL INTO :NEW.PW_ID FROM DUAL;
      END IF;
      IF INSERTING THEN
        :NEW.PASSWORD := ENC_CLIENT.ENC_PASSWORD(:NEW.PASSWORD, :NEW.CLIENTID, 'OpenSesame');
      ELSE
        IF :NEW.PASSWORD <> :OLD.PASSWORD THEN
          :NEW.PASSWORD := ENC_CLIENT.ENC_PASSWORD(:NEW.PASSWORD, :NEW.CLIENTID, 'OpenSesame');
        END IF;
      END IF;
    END;
    This will handle inserts and updates. The code should first check if a new PW_ID PK value is needed, and set it if it is. Then, for inserts only, it will encrypt the password entered - I'm assuming here that the password will be "plain text" and requires encryption. For updates only, check if the current password matches the old password (comparing a new decrypted version to the old decrypted version) - if it doesn't match (ie, the password has been changed), then update the password value with the encrypted new password.

    Unfortunately, the dbms_crypto package does not seem to be available on my OTN workspace (not sure why), so I have not been able to test this.

    Andy
  • 656867
    656867 Member Posts: 172
    Hi Andy,

    Thank you for the help. I actually was not that far off. Instead of referencing :NEW_PASSWORD, I was referencing password.

    I will test this out and see how it goes.

    Thanks a bunch. You were very helpful.
  • 656867
    656867 Member Posts: 172
    Hi Andy,

    I just wanted to give you an update. Your fix worked. As I suspected, the error of my way was using the actual field names from the table and not the :NEW references.

    Thank you and Thank you again. You were a great help!
  • ATD
    ATD Member Posts: 11,184
    You're welcome!
This discussion has been closed.