Encryption for fields in the database
Developer Tutorial
Implementing Field-Level Encryption via Snippets
Overview
This tutorial explains how to implement a secure "End-to-End" encryption simulation for specific text fields within your custom tables. By leveraging the system's snippet architecture, you can intercept data before it is saved to the database to encrypt it, and intercept it again before it is displayed in the form to decrypt it.
The Result: Data is stored as unreadable ciphertext in the database (protecting it from direct DB access or dumps) but appears as plain text to authorized users in the edit form.
Step 1: Encryption (Before Saving)
We use the validate_{tablename}_snippet.php file. Although named "validate", this snippet runs in the same scope as the save process, allowing us to manipulate the data arrays $fields_to_process and $values_to_process before the SQL INSERT/UPDATE command is generated.
File: setup_{app}/validate_{tablename}_snippet.php
<?php
// Configuration (Store your key securely, e.g., in config.php!)
$encryption_key = 'YourSuperSecretKey123!';
$cipher_method = "aes-256-cbc";
$field_to_encrypt = 'secret_data'; // The technical name of your field
// 1. Find the field in the data array prepared for the database
$index = array_search($field_to_encrypt, $fields_to_process);
if ($index !== false && !empty($values_to_process[$index])) {
$plaintext = $values_to_process[$index];
// 2. Generate a secure Initialization Vector (IV)
$ivlen = openssl_cipher_iv_length($cipher_method);
$iv = openssl_random_pseudo_bytes($ivlen);
// 3. Encrypt the data
$ciphertext = openssl_encrypt($plaintext, $cipher_method, $encryption_key, 0, $iv);
// 4. Combine IV and Ciphertext (needed for decryption) and encode
// Format: IV::EncryptedString
$stored_value = base64_encode($iv . '::' . $ciphertext);
// 5. Overwrite the value destined for the database
// We do NOT change $submitted_data, so the user still sees their input if validation fails elsewhere.
$values_to_process[$index] = $stored_value;
}
?>
Step 2: Decryption (On Load)
We use the form_{tablename}_snippet.php file. This runs before the HTML form is rendered. We modify the $record_data array, which holds the data loaded from the database, replacing the encrypted string with the decrypted plain text.
File: setup_{app}/form_{tablename}_snippet.php
<?php
// Same configuration as above
$encryption_key = 'YourSuperSecretKey123!';
$cipher_method = "aes-256-cbc";
$field_to_decrypt = 'secret_data';
// Only run in edit mode and if data exists
if ($is_edit_mode && !empty($record_data[$field_to_decrypt])) {
// 1. Decode Base64
$raw_data = base64_decode($record_data[$field_to_decrypt]);
// 2. Separate IV and Ciphertext
if (strpos($raw_data, '::') !== false) {
list($iv, $ciphertext) = explode('::', $raw_data, 2);
// 3. Decrypt
$decrypted_text = openssl_decrypt($ciphertext, $cipher_method, $encryption_key, 0, $iv);
// 4. Inject back into the record data array
// The form renderer will now display this decrypted value.
if ($decrypted_text !== false) {
$record_data[$field_to_decrypt] = $decrypted_text;
}
}
}
?>
Important Considerations
- Search & Filter: Since the data is encrypted in the database, standard SQL searches (
LIKE %term%) and filters on this field will not work. - List View: In the table overview, the data will appear as a garbled string (the ciphertext). To display it there, you would need a similar logic in a
list_{tablename}_snippet.php. - Key Management: Never hardcode the encryption key in the snippets if possible. Store it in a secure configuration file or environment variable.
Use Cases & Examples
This technique is ideal for storing highly sensitive information that should not be readable by database administrators or in backups:
PINs, TAN lists, Access IDs
Passwords for external services, API Keys
IMAP/SMTP Passwords
Database connection strings, Root passwords
Social Security Numbers, Insurance IDs
WLAN Keys, VPN Secrets