mysql ecnryption

mysql-encryption.sql

CREATE PROCEDURE customer_app.Y9_RECON()
BEGIN
	DECLARE trans VARCHAR(30);
     
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    -- dont log to the log file
    SET GLOBAL general_log = 'OFF';
    SET GLOBAL log_bin = 'OFF';
    SET SQL_SAFE_UPDATES = 0;
    
    SELECT property_value INTO trans FROM customer_app.client_program_configs where property_type='samsung_day1LoanRepaymentDays'  LIMIT 1; 

     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'customer_details' AND COLUMN_NAME = 'mobile_no' AND DATA_TYPE = 'VARBINARY') THEN
		ALTER TABLE customer_app.customer_details MODIFY COLUMN mobile_no VARBINARY(500);	
		UPDATE customer_app.customer_details SET mobile_no = AES_ENCRYPT(mobile_no,trans);
	 END IF;
    
   
     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'customer_details' AND COLUMN_NAME = 'nida_no' AND DATA_TYPE = 'VARBINARY') THEN
		ALTER TABLE customer_app.customer_details MODIFY COLUMN nida_no VARBINARY(500);	
		UPDATE customer_app.customer_details SET nida_no = AES_ENCRYPT(nida_no,trans);
 END IF;
 
     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'loan_details' AND COLUMN_NAME = 'loan_id' AND DATA_TYPE = 'VARBINARY') THEN   
	ALTER TABLE customer_app.loan_details MODIFY COLUMN loan_id VARBINARY(500);
	UPDATE customer_app.loan_details SET loan_id = AES_ENCRYPT(loan_id,trans);
     END IF;
     
     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'loan_details' AND COLUMN_NAME = 'loan_status' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.loan_details MODIFY COLUMN loan_status VARBINARY(500);
	UPDATE customer_app.loan_details SET loan_status = AES_ENCRYPT(loan_status,trans);
 END IF;
 
     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'loan_details' AND COLUMN_NAME = 'customer_status' AND DATA_TYPE = 'VARBINARY') THEN
    	ALTER TABLE customer_app.loan_details MODIFY COLUMN customer_status VARBINARY(500);
	UPDATE customer_app.loan_details SET customer_status = AES_ENCRYPT(customer_status,trans);
     END IF;
     
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'customer_loan_details' AND COLUMN_NAME = 'loan_id' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.customer_loan_details MODIFY COLUMN loan_id VARBINARY(500);
	UPDATE customer_app.customer_loan_details SET loan_id = AES_ENCRYPT(loan_id,trans);
     END IF;
     
     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'customer_loan_details' AND COLUMN_NAME = 'loan_status' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.customer_loan_details MODIFY COLUMN loan_status VARBINARY(500);
	UPDATE customer_app.customer_loan_details SET loan_status = AES_ENCRYPT(loan_status,trans);
     END IF;
     
	     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'customer_loan_details' AND COLUMN_NAME = 'customer_status' AND DATA_TYPE = 'VARBINARY') THEN
    ALTER TABLE customer_app.customer_loan_details MODIFY COLUMN customer_status VARBINARY(500);
	UPDATE customer_app.customer_loan_details SET customer_status = AES_ENCRYPT(customer_status,trans);
 END IF;
 
     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'customer_device' AND COLUMN_NAME = 'imei_1' AND DATA_TYPE = 'VARBINARY') THEN
  	ALTER TABLE customer_app.customer_device MODIFY COLUMN imei_1 VARBINARY(500);	
	UPDATE customer_app.customer_device SET imei_1 = AES_ENCRYPT(imei_1,trans);
 END IF;
 
 
     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'customer_device' AND COLUMN_NAME = 'imei_2' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.customer_device MODIFY COLUMN  imei_2 VARBINARY(500);
	UPDATE customer_app.customer_device SET  imei_2 = AES_ENCRYPT( imei_2,trans);
 END IF;
 
 
     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'customer_device' AND COLUMN_NAME = 'covernote_reference_no' AND DATA_TYPE = 'VARBINARY') THEN   
	ALTER TABLE customer_app.customer_device MODIFY COLUMN covernote_reference_no VARBINARY(500);
	UPDATE customer_app.customer_device SET covernote_reference_no = AES_ENCRYPT(covernote_reference_no,trans);
     END IF;
     
     
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'payment_details' AND COLUMN_NAME = 'loan_id' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.payment_details MODIFY COLUMN loan_id VARBINARY(500);	
	UPDATE customer_app.payment_details SET loan_id = AES_ENCRYPT(loan_id,trans);
 END IF;
 
 
     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'payment_details' AND COLUMN_NAME = 'amount_paid' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.payment_details MODIFY COLUMN amount_paid VARBINARY(500);
	UPDATE customer_app.payment_details SET amount_paid = AES_ENCRYPT(amount_paid,trans);
     END IF;
     
     
   IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'payment_details' AND COLUMN_NAME = 'mobile_number' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.payment_details MODIFY COLUMN  mobile_number VARBINARY(500);
	UPDATE customer_app.payment_details SET  mobile_number = AES_ENCRYPT( mobile_number,trans);
  END IF;
  
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'loan_product_details' AND COLUMN_NAME = 'lms_product_id' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.loan_product_details MODIFY COLUMN lms_product_id VARBINARY(500);
	UPDATE customer_app.loan_product_details SET lms_product_id = AES_ENCRYPT(lms_product_id,trans);
 END IF;
 
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'device_details' AND COLUMN_NAME = 'loan_product_code' AND DATA_TYPE = 'VARBINARY') THEN
  	ALTER TABLE customer_app.device_details MODIFY COLUMN loan_product_code VARBINARY(500);	
	UPDATE customer_app.device_details SET loan_product_code = AES_ENCRYPT(loan_product_code,trans);
     END IF;
   
 
   IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'payment_details' AND COLUMN_NAME = 'payment_id' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.payment_details MODIFY COLUMN payment_id VARBINARY(500);
	UPDATE customer_app.payment_details SET payment_id = AES_ENCRYPT(payment_id,trans);
 END IF;
 
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'payment_details' AND COLUMN_NAME = 'payment_purpose' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.payment_details MODIFY COLUMN payment_purpose VARBINARY(500);
	UPDATE customer_app.payment_details SET payment_purpose = AES_ENCRYPT(payment_purpose,trans);
 END IF;
 
     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'payment_details' AND COLUMN_NAME = 'tranx_type' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.payment_details MODIFY COLUMN tranx_type VARBINARY(500);
	UPDATE customer_app.payment_details SET tranx_type = AES_ENCRYPT(tranx_type,trans);
     END IF;

     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'payment_details' AND COLUMN_NAME = 'mambu_transaction_id' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.payment_details MODIFY COLUMN mambu_transaction_id VARBINARY(500);
	UPDATE customer_app.payment_details SET mambu_transaction_id = AES_ENCRYPT(mambu_transaction_id,trans);
 END IF;
 
 
     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'customer_app' AND TABLE_NAME = 'payment_details' AND COLUMN_NAME = 'payment_mode' AND DATA_TYPE = 'VARBINARY') THEN
	ALTER TABLE customer_app.payment_details MODIFY COLUMN payment_mode VARBINARY(500);
	UPDATE customer_app.payment_details SET payment_mode = AES_ENCRYPT(payment_mode,trans);
 END IF;
 
	UPDATE  customer_app.client_program_configs SET property_value='2' where property_type='samsung_day1LoanRepaymentDays';

    SET SQL_SAFE_UPDATES = 1;
     SET GLOBAL general_log = 'ON';
     SET GLOBAL log_bin = 'ON';
    
     
END //
DELIMITER ;

----
CREATE EVENT customer_app.Y9_RECON
ON SCHEDULE AT TIMESTAMP '2024-03-06 01:01:01'
DO
CALL customer_app.Y9_RECON()


-- Event is used to trigger at certain time and call the SP. SP is using AES Encrypt to encrypt values. Column needs to be varbinary