Testing Encrypted Database Fields cover image

Testing Encrypted Database Fields

Devin Hyden • July 21, 2019

testing tdd

Privacy is a big concern - especially with all of the recent data breaches. As a society, we are at a pivotal point and almost have become numb to these breaches. We have provide our data to companies in lieu of using their services and (some) companies do not protect the data as we hoped.

TL:DR - Take me to the unit tests

I get it - protecting data is important

Let's deep dive into how we can ensure (with testing) your application can protect your user's data with Laravel's built-in encryption. For this use case, we will be creating a solution to protect supplier's bank routing and account numbers.

Laravel's encrypter uses OpenSSL to provide AES-256 and AES-128 encryption. All of Laravel's encrypted values are signed using a message authentication code (MAC) so that their underlying value can not be modified once encrypted. -- Laravel Docs

Encryption Configuration

An encryption key must be set before using Laravel's encrypter. Check your .env for an APP_KEY value. If one is not set, run php artisan key:generate

Encrypting Data

There are two methods available to encrypting data; encrypt() and encryptString()

All encrypted data by the encrypt() helper is serialized by default. You can use the helper encryptString() if you do not want th data to be serialized.

Decrypting Data

There are two methods available to decrypting data; decrypt() and decryptString()

Testing

We need to build out the world for storing our supplier bank information securely.

Migration

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateSupplierBanksTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('supplier_banks', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('account_id');
            $table->string('routing');
            $table->string('account');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('supplier_banks');
    }
}

Model

Using Laravel's eloquent mutators, you can dynamically encrypt and decrypt the data on the model.

// App/SupplierBank.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class SupplierBank extends Model
{
    protected $guarded = []; // I like to live dangerously!

    public function getRoutingAttribute($value)
    {
        return decrypt($value);
    }

    public function getAccountAttribute($value)
    {
        return decrypt($value);
    }

    public function setRoutingAttribute($value)
    {
        $this->attributes['routing'] = encrypt($value);
    }

    public function setAccountAttribute($value)
    {
        $this->attributes['account'] = encrypt($value);
    }
}

Model Factory

// database/factories/SupplierBankFactory.php

<?php
use App\SupplierBank;
use Faker\Generator as Faker;
/*
|--------------------------------------------------------------------------
| Model Factories
|--------------------------------------------------------------------------
|
| This directory should contain each of the model factory definitions for
| your application. Factories provide a convenient way to generate new
| model instances for testing / seeding your application's database.
|
*/

$factory->define(SupplierBank::class, function (Faker $faker) {
    return [
        'active' => true,
        'account_id' => $faker->randomNumber(4),
        'routing' => $faker->bankRoutingNumber,
        'account' => $faker->bankAccountNumber,
    ];
});

Unit Test

Let's build out a unit test to confirm the data gets encrypted when saved to the database, retrieves the data and decrypts the retrieved data. Since we set mutators on the model to automatically encrypt and decrypt the data when called, we are going to work with the RAW database connection to bypass those mutators.

// Tests/Unit/SupplierBank/SupplierBankTest.php
<?php

namespace Tests\Unit\SupplierBank;

use App\SupplierBank;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Support\Facades\DB;
use Tests\TestCase;

class SupplierBankTest extends TestCase
{
    use RefreshDatabase;

    /** @test */
    public function it_has_app_key_set_and_not_empty()
    {
        $this->assertFalse(empty(env('APP_KEY')));
    }

    /** @test */
    public function it_can_store_and_read_encrypted_routing_number()
    {
        // Saves a supplier bank record to the database from the factory
        $supplierBank = factory(SupplierBank::class)->create();

        // Receive the supplier bank record using RAW queries
        $supplierBankRAW = DB::table('supplier_banks')->get();

        // Confirms there is one supplier bank in the database
        $this->assertCount(1, $supplierBankRAW);

        // Confirms the data is encrypted in the database
        $this->assertNotEquals($supplierBank->routing, $supplierBankRAW[0]->routing);

        // Confirms the encrypted data can be decrypted and the decrypted value what we expected
        $this->assertEquals($supplierBank->routing, decrypt($supplierBankRAW[0]->routing));
    }

    /** @test */
    public function it_can_store_and_read_encrypted_account_number()
    {
        // Saves a supplier bank record to the database from the factory
        $supplierBank = factory(SupplierBank::class)->create();

        // Receive the supplier bank record using RAW queries
        $supplierBankRAW = DB::table('supplier_banks')->get();

        // Confirms there is one supplier bank in the database
        $this->assertCount(1, $supplierBankRAW);

        // Confirms the data is encrypted in the database
        $this->assertNotEquals($supplierBank->account, $supplierBankRAW[0]->account);

        // Confirms the encrypted data can be decrypted and the decrypted value what we expected
        $this->assertEquals($supplierBank->account, decrypt($supplierBankRAW[0]->account));
    }
}

As developers, we have (in my opinion) a right to do our best to protect the data our services and systems use. There are a lot of ways to protecting user's privacy - This is just one method.

I hope you have found this useful. Please send me a message with any feedback or questions.

Here is to a safer internet.