2. PHPRunner General questions
  3. [SOLVED]   MySql Encription for DB
This topic is locked
[SOLVED]

  MySql Encription for DB

 		4/14/2011 4:27:06 PM
PHPRunner General questions
N
 nti author

Request for admin to explain the below sql for setting trigger in MySql DB.

I have tried to understand/decipher the sql below by looking at Template Cars, the tables created at db has no tables which helps me to understand the sql.

table at db = carscars however the db is named: cars
delimiter |
CREATE TRIGGER insert_encrypt BEFORE INSERT ON cars <<<<<<< what does cars equal? The database name ?
FOR EACH ROW BEGIN
SET NEW.Model = AES_ENCRYPT(NEW.Model,"my passphrase"); <<<<<<< what is NEW.Model ? a new table to be created? an existing table in db? a fieldname in some table?
END;
|
delimiter |
CREATE TRIGGER update_encrypt BEFORE UPDATE ON cars <<<<<<< what does cars equal? The database name ?
FOR EACH ROW BEGIN
SET NEW.Model = AES_ENCRYPT(NEW.Model,"my passphrase"); <<<<<<< what is NEW.Model ? a new table to be created? an existing table in db? a fieldname in some table?
END;
|
Many Thanks & blessing for explaination. nti
Sergey Kornilov admin 4/14/2011

This sample code doesn't have anything to do with Cars template.
I would suggest to check MySQL manual on CREATE TRIGGER and the following walk-through.
N
 		nti author 4/18/2011

After reading thru the pages/links admin listed above... and also found additional reading material via google. Posting what I discovered for newbies/novices of this post.
In my ignorance of MySql Triggers I did not know that the triggers above were utilized with our webform for adding a new field and encrypt the same field and the second trigger used with our webform for editing our field_data and encryption.
Edit: After reading admin orig post for the 35th time, he clearly states: I must have been blind the first 34 times I read the post.

{"In this example we'll show you how to encrypt and decrypt the contents of Model field in Cars table in few easy steps."}
In my first post above:

  1. "insert_encrypt" is a descriptive name for our first trigger.
  2. cars is My_Table_Name.
  3. Model is My_Field_Name.
    I could not get the two triggers configured at mySQL DB with one SQL... setting one trigger at a time presented no issues.



delimiter |
CREATE TRIGGER My_Trigger_Name BEFORE INSERT ON My_Table_Name
FOR EACH ROW BEGIN
SET NEW.My_Field_Name = AES_ENCRYPT(NEW.My_Field_Name,"my_passphrase");
END;




delimiter |
CREATE TRIGGER My_Trigger_Name BEFORE UPDATE ON My_Table_Name
FOR EACH ROW BEGIN
SET NEW.My_Field_Name = AES_ENCRYPT(NEW.My_Field_Name,"my passphrase");
END;