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 *

You May Also Like…