Transparent Table/Column Encryption

What is it?

Hazaar DBI can encrypt data automatically as it is written to and read from the database. Once enabled, encryption and decryption are handled in the DBI layer without application changes for insert, update, or select operations. This adds protection in transit and at rest for selected data, and ensures only the application can decrypt it.

Encryption can be activated for an entire table or for specific columns. Only string values are supported. Because encrypted values have variable length, encrypted columns should use a TEXT type to ensure sufficient storage.

Enabling Encryption

Encryption requires a unique key that is used to encrypt and decrypt data. A sufficiently long, randomly generated string is recommended.

Info

On Linux, you can use the pwgen program to generate a key. Execute pwgen 64 1 to generate a 64-character key suitable for AES-256 encryption.

Once you have a key, update your database configuration to enable encryption.

PHP

<?php
return [
    'development' => [
        // ... other config options ...
        'encrypt' => [
            'key' => '{your encryption key}', // Optional and only recommended for testing
            'table' => [
                'users' => ['password'],
                'credit_data' => true,
            ],
        ],
    ],
];

JSON

{
    "development": {
        "encrypt": {
            "key": "{your encryption key}",
            "table": {
                "users": ["password"],
                "credit_data": true
            }
        }
    }
}

INI

[development]
encrypt.key = {your encryption key}
encrypt.table.users[] = password
encrypt.table.credit_data = true

In the example above, encryption is enabled on two tables. In the users table, a single column named password is encrypted. In the credit_data table, all string columns are encrypted.

Warning

The key option is included for development convenience. Avoid storing encryption keys in configuration files for production systems.

Practical Usage Example

Once encryption is configured, all database operations automatically encrypt and decrypt data for the specified tables and columns. Here's a complete example:

<?php
use Hazaar\DBI\Adapter;
use Hazaar\Util\DateTime;

$db = Adapter::create([
    'type' => 'pgsql',
    'host' => 'localhost',
    'dbname' => 'my_database',
    'user' => 'db_user',
    'password' => 'db_password',
    'encrypt' => [
        'table' => ['users' => ['ssn', 'credit_card']],
        'cipher' => 'aes-256-cbc',
        'keyfile' => '.db_key', // Use keyfile in production
    ],
]);

// Insert data - encryption happens automatically
$userId = $db->insert('users', [
    'name' => 'John Doe',
    'email' => '[email protected]',
    'ssn' => '123-45-6789',        // This will be encrypted
    'credit_card' => '4111-1111-1111-1111', // This will be encrypted
]);

// Query data - decryption happens automatically
$user = $db->findOne('users', ['id' => $userId]);
echo $user['ssn']; // Displays decrypted value: 123-45-6789

// Update encrypted data
$db->update('users', 
    ['ssn' => '987-65-4321'], // New value is encrypted automatically
    ['id' => $userId]
);

The encrypted data is stored in the database as encrypted strings, but your application code works with plain values transparently.

Keeping Your Key Safe

Specifying the key in configuration is convenient for development, but it is not recommended for production. For production systems, store the key in the config directory using a file named .db_key (by default).

Using your preferred text editor, create the key file and add the key. On Linux you can do something like this:

> echo "{your encryption key}" > {APPLICATION_PATH}/app/configs/.db_key

Where your encryption key is the value you generated, and APPLICATION_PATH is the root application path that contains the app and public directories.

Configuration Options

Below is the full list of available configuration options in the encrypt config object of the database config.

cipher (default: aes-256-ctr)

The encryption algorithm used to encrypt and decrypt data. This can be any algorithm supported by OpenSSL in PHP. You can use openssl_get_cipher_methods() to list available algorithms on your system. See the PHP documentation for openssl-get-cipher-methods for more information.

table

The table object is a key/value map where the key is the table name and the value is either a boolean to enable or disable whole-table encryption, or an array of column names to encrypt.

Encrypt a whole table

{
    "tablename": true
}

Encrypt selected columns in a table

{
    "tablename": [ "column1", "column2", "column3" ]
}

checkstring (default: !!)

The check string is used to verify that decryption succeeded. It is a short prefix (typically 1-2 characters) added before encryption and checked after decryption, then removed. The default is suitable for most use cases.

key

This is not recommended for production use. Specify your encryption key here for local development or testing.

keyfile (default: .db_key)

The keyfile option specifies an alternate key file path. This can be a relative path to the configs directory or an absolute path outside the application directory.

Tips

You can use the keyfile option to point to the .key file that is used for encrypting files to reduce the number of key files you need to manage.