Encrypt / Decrypt the columns of your CakePHP 4 DB

Written by James McDonald

May 13, 2021

https://stackoverflow.com/a/32261210/9230077

The above is a good solution, but it needed updating to work with CakePHP 4

I have updated the above as below to encrypt a database field with a base64 encoded encrypted string

The base64 encoding is so it can save in a VARCHAR field without throwing an error.

The encrypted fields are tagged with a prefix of ENC: so we can have non-encrypted strings co-exist by checking for an ENC: prefix and then encrypt them if there is a change to that field.

Error when storing encrypted strings in MySQL VARCHAR field

I found with a VARCHAR database field and without base64_encoding the encrypted string I would receive an error when saving to the DB. So I added base64_encode/decode to the toDatabase and toPHP methods

General error: 1366 Incorrect string value: '\x91\xE6\x8B\xFF\xC3\x19.

Create a new custom Database Type

Add src\Database\Type\CryptedType.php

This code stores values in the database with a prefix of ENC:__encrypted_string_here__ so you can migrate from non-encrypted to encrypted seamlessly

<?php

namespace App\Database\Type;

use Cake\Database\Type\BaseType;
use Cake\Utility\Security;
use Cake\Database\DriverInterface;

class CryptedType extends BaseType
{
    private $prefix = "ENC:";

    public function toDatabase($value, DriverInterface $driver): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }
        $encrypted = $this->prefix . base64_encode(Security::encrypt($value, Security::getSalt()));
        //dd($encrypted);

        return $encrypted;
    }

    public function toPHP($value, DriverInterface $driver): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }
        if (strpos($value, $this->prefix) === 0) {
            $value = substr($value, strlen($this->prefix));

            return Security::decrypt(base64_decode($value), Security::getSalt());
        } else {
            return $value;
        }
    }

    /**
     * Marshals request data into PHP strings.
     *
     * @param mixed $value The value to convert.
     * @return string|null Converted value.
     */
    public function marshal($value): ?string
    {
        if ($value === null || is_array($value)) {
            return null;
        }

        return (string)$value;
    }
}

Notifiy the CakePHP application of the new Custom Type

In config/bootstrap.php declare the type

// with the other use statements near the top
use App\Database\Type\CryptedType;

// at the bottom
TypeFactory::map('crypted', CryptedType::class);

Update the column type on the correct Table class to have the new type

In a table with a column you want to encrypt implement the _initializeSchema method and map the database field to the crypted type

class SettingsTable extends Table
{
    use LogTrait;

    /**
     * _initializeSchema
     *
     * @param  mixed $schema
     * @return TableSchemaInterface
     */
    protected function _initializeSchema(TableSchemaInterface $schema): TableSchemaInterface
    {
        $schema->setColumnType('comment', 'crypted');
        // repeat the above line for each field you want to encrypt
        return $schema;
    }

Modify the database column to have a larger character limit

It is a good idea to lenghten the crypted database columns as the values become much longer when encrypted. Change the database column with a migration

bin/cake bake migration LengthenValueFieldOnSettingsTable

Which creates a migration and you can extend the DB field

<?php
declare(strict_types=1);

// this file config/Migrations/YYYYMMDDTTTTTTT_LengthenValueFieldOnSettingsTable.php

use Migrations\AbstractMigration;

class LengthenValueFieldOnSettingsTable extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
     * @return void
     */
    public function change()
    {
        $this->table("settings")->changeColumn('comment', 'string', [
            'length' => 1000
        ])->save();
    }
}

Finally to apply the schema change to the database

bin/cake migrations migrate

Bulk Update the database to encrypt the field/s you want encrypted

Create a Command to update all the fields in the table from the command line

bin/cake bake command EncryptSettings

This code marks the fields you have configured to be encrypted as dirty so they will update when saved

<?php

declare(strict_types=1);

namespace App\Command;

use Cake\Command\Command;
use Cake\Console\Arguments;
use Cake\Console\ConsoleIo;
use Cake\Console\ConsoleOptionParser;

/**
 * EncryptSettings command.
 */
class EncryptSettingsCommand extends Command
{

    protected $modelClass = 'Settings';

    /**
     * Hook method for defining this command's option parser.
     *
     * @see https://book.cakephp.org/4/en/console-commands/commands.html#defining-arguments-and-options
     * @param \Cake\Console\ConsoleOptionParser $parser The parser to be defined
     * @return \Cake\Console\ConsoleOptionParser The built parser.
     */
    public function buildOptionParser(ConsoleOptionParser $parser): ConsoleOptionParser
    {
        $parser = parent::buildOptionParser($parser);

        return $parser;
    }


    public static function defaultName(): string
    {
        return 'encrypt_settings';
    }

    /**
     * Implement this method with your command's logic.
     *
     * @param \Cake\Console\Arguments $args The command arguments.
     * @param \Cake\Console\ConsoleIo $io The console io
     * @return null|void|int The exit code or null for success
     */
    public function execute(Arguments $args, ConsoleIo $io)
    {
        $fieldsToUpdate = ['value'];

        $response = $io->ask("Do you want to encrypt the fields Y/n", "n");

        if (strtoupper($response) !== 'Y') {
            $io->out('Exiting...');

            return;
        }

        $settings = $this->Settings->find('all');

        foreach ($settings as $setting) {

            foreach ($fieldsToUpdate as $field) {
                $setting->setDirty($field, true);
            }

            $this->Settings->save($setting);
        }
    }
}

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

Squarespace Image Export

To gain continued access to your Squarespace website images after cancelling your subscription you have several...

MySQL 8.x GRANT ALL STATEMENT

-- CREATE CREATE USER 'tgnrestoreuser'@'localhost' IDENTIFIED BY 'AppleSauceLoveBird2024'; GRANT ALL PRIVILEGES ON...

Exetel Opt-Out of CGNAT

If your port forwards and inbound and/or outbound site-to-site VPN's have failed when switching to Exetel due to their...