ORA-01400 Error in application

656867

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

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

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

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

You're welcome!