Even though we encrypt the connection between the client and the dashboard and MySQL servers, some researchers have the requirement to encrypt the data itself on their server. The data on the database itself CAN BE encrypted on your server as data is inserted using a MySQL trigger: on INSERT, encrypt the data using an encryption key ONLY YOU KNOW.
The only SELECT operation we must do on the data is when we check what is the latest timestamped entry on the server side and only send what is newer from the device during the database synching mechanism. Notice that we only fetch the timestamp column, nothing else! So, if using the trigger, DO NOT ENCRYPT these 4 columns: device_id, double_end_timestamp, double_esm_user_answer_timestamp, timestamp
To encrypt your database data, you need to implement and run this trigger on your MySQL database server (it also works on AWARE’s databases). Replace the YOUR ENCRYPT KEY with your key’s choice. You’ll need to extend this snipped for each table and column you wish to encrypt:
/* ENCRYPTING DATA */ delimiter $$ CREATE TRIGGER encryptor_function_name /* Do this for each table you want to encrypt */ BEFORE INSERT ON table_name FOR EACH ROW BEGIN /* Do this for each table column you want to encrypt */ SET NEW.column_name=AES_ENCRYPT(NEW.column_name, 'YOUR ENCRYPT KEY') END$$ delimiter;