My live project required several existing fields to be encrypted using the fieldencryption extension.
If the tables would have no data it would be simple, but what if your tables already contain many records?
When you turn on field encryption only new and edited records get encrypted automatically, which means you have to figure out how to deal with existing records after enabling encryption.
There are a few options:
- re-save each record one at a time
- turn on 'multiple edit' for every table with fields that are set as encrypted
- find another solution
In my case I had a further challenge, in that all my tables have a timestamp field with ON UPDATE CURRENT_TIMESTAMP() attribute enabled.
Some of the tables are sorted by last updated date, which meant that encrypting records had to ensure that the previous timestamp values were retained, so as not to ruin the sorting.
I battled at first with a custom page form, where I could select the table and field I wanted to update, but this was both quite troublesome and risked encrypting data that shouldn't be encrypted.
In the end I created a solution that, with a small piece of code added to the Row_Updating event, I could trigger a function after edit, which would loop through all the rows in the table and encrypting unencrypted fields which were set as encrypted, whilst retaining the previous timestamps and skipping fields which are already encrypted. The function will also show you a summary of the changes made as a toast message.
The function will only run under admin rights ( level -1 ) to avoid unecessary server queries.
My project also has a settings table which i use to dynamically switch this feature on only when needed.
In Server Events > Global > All Pages > Global_Code add the following function:
// Retro Encryption - this retrospectively encrypts data in fields where encryption was turned on, but previous unencrypted data exists
// Call this function in add or edit for any table where field encryption is enabled
function retroEncrypt($tableName, $fields) {
$EncryptToggle = '1'; //change value to 1 / 0 to enable / disable the script
if(CurrentUserLevel() =='-1' && $EncryptToggle='1') {
$encPrefix = "def"; // Prefix which your field encryption generates (inspect a few encrypted values in your table to see what this is in your project)
$messages = array(); // Initialize an array to store messages
$rowsUpdated = 0; // Initialize row update counter
$encryptedFields = array(); // Initialize array to store names of encrypted fields
$timestampFields = array(); // Initialize array to store names of timestamp fields
$autoIncField = null; // Initialize auto-increment field
$schemaQuery = "SHOW COLUMNS FROM " . $tableName; // Query the database schema to retrieve information about fields
$schemaResult = ExecuteRows($schemaQuery); // Execute the query to get the schema result
// Detect auto-increment field and add it to messages
foreach ($schemaResult as $column) {
if (strtolower($column['Extra']) === 'auto_increment') {
$autoIncField = $column['Field'];
$messages[] = "AutoIncrement field: " . $autoIncField;
break; // Stop after finding the auto-increment field
}
}
// Create a function to find encrypted fields based on the IsEncrypt === true; value
function findEncryptedFields($fields) {
$encryptedFields = array(); // Initialize an array to store encrypted fields
foreach ($fields as $fieldName => $field) {
if (isset($field->IsEncrypt) && $field->IsEncrypt === true) {
$encryptedFields[] = $fieldName; // Add encrypted field to the array
}
}
return $encryptedFields;
}
// Call the function to find encrypted fields and store the message
$encryptedFields = findEncryptedFields($fields);
if (!empty($encryptedFields)) {
$messages[] = "Encrypted fields: " . implode(", ", $encryptedFields);
} else {
$messages[] = "No encrypted fields found.";
}
// Detect timestamp fields and add them to messages
foreach ($schemaResult as $column) {
if (strpos(strtolower($column['Type']), 'timestamp') !== false) {
$timestampFields[] = $column['Field']; // Add timestamp field to the array
}
}
if (!empty($timestampFields)) {
$messages[] = "TIMESTAMP fields: " . implode(', ', $timestampFields);
} else {
$messages[] = "No TIMESTAMP fields found.";
}
// Construct the SELECT query to fetch records with unencrypted values in any of the encrypted fields
$selectQuery = "SELECT " . $autoIncField . ", " . implode(", ", $encryptedFields) . " FROM " . $tableName;
$whereClause = array();
foreach ($encryptedFields as $field) {
$whereClause[] = $field . " NOT LIKE '" . $encPrefix . "%' AND " . $field . " IS NOT NULL";
}
$selectQuery .= " WHERE " . implode(" OR ", $whereClause);
// Execute the SELECT query to get records with unencrypted values
$recordIdsResult = ExecuteRows($selectQuery);
// Loop through each record to perform the update
foreach ($recordIdsResult as $record) {
// Construct the UPDATE query for the current record
$updateQuery = "UPDATE " . $tableName . " SET ";
$updateQueryM=$updateQuery;
$setClauses = array();
$setClausesM = array(); //build the array for showing the update query in messages
// Add SET clauses for each encrypted field
foreach ($encryptedFields as $field) {
if (!empty($record[$field]) && strpos($record[$field], $encPrefix) !== 0) {
$encryptedValue = PhpEncrypt($record[$field]); // Encrypt the unencrypted value
$setClauses[] = $field . " = '" . $encryptedValue . "'";
// Truncate encrypted value for messages
$setClausesM[] = $field . " = '" . substr($encryptedValue, 0, 10) . '...' . "'"; //truncate the encrypted value for messages so that the update query isn't ridiculously long.
} else {
$setClauses[] = $field . " = " . $field; // Retain existing encrypted or NULL values
$setClausesM[] = $field . " = " . $field; // Retain existing encrypted or NULL values
}
}
// Add SET clauses for timestamp fields to retain their previous values
foreach ($timestampFields as $timestampField) {
$setClauses[] = $timestampField . " = " . $timestampField;
$setClausesM[] = $timestampField . " = " . $timestampField;
}
// Add the SET clauses to the update query
$updateQuery .= implode(", ", $setClauses);
$updateQueryM .= implode(", ", $setClausesM);
$updateQuery .= " WHERE " . $autoIncField . " = " . $record[$autoIncField];
$updateQueryM .= " WHERE " . $autoIncField . " = " . $record[$autoIncField];
// Uncomment the line below to execute the update query
ExecuteUpdate($updateQuery);
// Increment rows updated counter
$rowsUpdated++;
$messages[] = "Update Query for row '{$record[$autoIncField]}': " . $updateQueryM; //comment this line out if you don't need to display the update query
}
// Add the count of rows updated to messages
$messages[] = "Rows Updated: " . $rowsUpdated;
// Return the compiled messages
return implode("<br>", $messages);
}To call the function add this to the Row_Updated event for your table:
$messages = retroEncrypt($this->TableName, $this->Fields);
// Set the messages using $this->SetMessage()
$this->SetMessage($messages);Hope someone else finds this useful