Forem: Mike Mollick

Using Unique Columns with Soft Deletes in Laravel

Mike Mollick — Wed, 29 Jul 2020 17:25:42 +0000

When you mix Laravel's soft deletion feature with your database's unique column constraints you're may have seen error messages that look something like SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ....

This can be a common scenario if you use soft-deletes on your users table, where you have a unique constraint on the email field. With soft deletes this means if a user deletes their account they'll be unable to recreate the account at a later date because the email still exists in the table. Unless we intend to write user-hostile software, we probably want the user to be able to re-register with the same email.

Let's dive into how we can mitigate this issue and allow users to be deleted without preventing the email from being used in the future.

Setup

To start off let's cover some basic assumptions about our application. We have a base Laravel user table migration, with the soft deletes attribute added.

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
    $table->softDeletes();
});

We also have the base Laravel User model, with the SoftDeletes attribute added.

class User extends Authenticatable
{
    use Notifiable, SoftDeletes;

    ...

Demonstrating the Problem

With this setup we can create uses and delete users, but creating a new user with the same email results in the "Integrity constraint violation" error.

1. Create User

User::create([
    'name' => 'Sam',
    'email' => 'sam@example.com',
    'password' => bcrypt(Str::random(8)),
]);

Users Table:

id	name	email	email_verified_at	password	remember_token	created_at	updated_at	deleted_at
1	Sam	sam@example.com		$2y$10$OHcjoGZrF1zxSRLclThvbu5sNeiYdfzaxubzdJqZn64JtcAbauVai		2020-07-29 16:39:38	2020-07-29 16:39:38

2. Delete User

User::find(1)->delete();

Users Table:

id	name	email	email_verified_at	password	remember_token	created_at	updated_at	deleted_at
1	Sam	sam@example.com		$2y$10$OHcjoGZrF1zxSRLclThvbu5sNeiYdfzaxubzdJqZn64JtcAbauVai		2020-07-29 16:39:38	2020-07-29 16:40:56	2020-07-29 16:40:56

3. Recreating user produces an error

User::create([
    'name' => 'Sam',
    'email' => 'sam@example.com',
    'password' => bcrypt(Str::random(8)),
]);

Illuminate/Database/QueryException with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'sam@example.com' for key 'users.users_email_unique' (SQL: insert into `users` (`name`, `email`, `password`, `updated_at`, `created_at`) values (Sam, sam@example.com, $2y$10$DNrfq9wPEcSsOvFOX97tF.kiu2Zg.yRGqVvNPRRLi.BFtOK2fCbqC, 2020-07-29 16:41:30, 2020-07-29 16:41:30))'

Solution

The best way to get around this is to mutate the data contained in this unique column on delete. Now initially this might sound like a bad idea, since if you're using the soft deletion feature you probably want the data to remain in tact. But have no fear, we can mutate the data in a way that it remains readable.

With Laravel we can use Model Observers to accomplish this with little effort.

1. Create a UserObserver class

php artisan make:observer UserObserver --model=User

2. Update the `delete` method to mutate the email

public function deleted(User $user)
{
    $user->update([
        'email' => time() . '::' . $user->email
    ]);
}

3. Register UserObserver within our `AppServiceProvider`

public function boot()
{
    User::observe(UserObserver::class);
}

Demonstrating the Solution

1. Create User

User::create([
    'name' => 'Sam',
    'email' => 'sam@example.com',
    'password' => bcrypt(Str::random(8)),
]);

Users Table:

id	name	email	email_verified_at	password	remember_token	created_at	updated_at	deleted_at
1	Sam	sam@example.com		$2y$10$eNHbAmyL4DzTkclDeUbLBu3a9d3dmCpgEa7Ayd0utmJK/klKc3BXG		2020-07-29 16:57:15	2020-07-29 16:57:15

2. Delete User

User::find(1)->delete();

In the database we can see the magic happening, now our email field contains a timestamp that makes this value unique.

Users Table:

id	name	email	email_verified_at	password	remember_token	created_at	updated_at	deleted_at
1	Sam	1596041868::sam@example.com		$2y$10$eNHbAmyL4DzTkclDeUbLBu3a9d3dmCpgEa7Ayd0utmJK/klKc3BXG		2020-07-29 16:57:15	2020-07-29 16:57:48	2020-07-29 16:57:48

3. Recreating user creates a new record

User::create([
    'name' => 'Sam',
    'email' => 'sam@example.com',
    'password' => bcrypt(Str::random(8)),
]);

Users Table:

id	name	email	email_verified_at	password	remember_token	created_at	updated_at	deleted_at
1	Sam	1596041868::sam@example.com		$2y$10$eNHbAmyL4DzTkclDeUbLBu3a9d3dmCpgEa7Ayd0utmJK/klKc3BXG		2020-07-29 16:57:15	2020-07-29 16:57:48	2020-07-29 16:57:48
2	Sam	sam@example.com		$2y$10$NX62LTAQCbVn9uj3fjs4qeL55VnzCIflnGsG/xQ/8QlpXlbiONLIW		2020-07-29 16:59:03	2020-07-29 16:59:03

Conclusion

With the addition of a single Model Observer we're able to get around this issue and avoid a user-hostile experience. There are some draw backs to this, in particular the ability to restore soft-deleted records becomes tricky in the event that the user has created a new account. At that point however it's unlikely that you would need to restore the original record.

If you intend on restoring the record for reporting you can instead use the withTrashed attribute with a custom accessor for the email attribute to strip the prefixed data.

Encrypt your Development Environment Traffic

Mike Mollick — Fri, 15 Feb 2019 04:41:20 +0000

Keeping your development environment faithful to your production environment ensures your application works as expected. However, our development environments often come with caveats and drawbacks that lead to us negating specific settings, features or functionality. One of these often overlooked features is the ability to serve your application over HTTPS.

While keeping the traffic from your local Docker container encrypted may not be a priority, ensuring your application is loading all of its assets and data over the correct protocol is. A hard-coded HTTP:// URL in a <link> or <script> tag can break your application's styling or prevent scripts from loading at all. The best way to prevent this is to load your application over HTTPS during development as well.

To load your application using HTTPS locally we'll have to generate SSL certificates. In a production environment, you're likely using a Certificate Authority (CA) to generate trusted certificates like Let's Encrypt or one of the dozens of paid CA's.

While it is possible to reuse the same certificates in your production environment, passing around your production certificate and private keys for the certificate introduces significant security risks. It's best to not to use your production certificate and private key on your local development machine.

The best solution to enable HTTPS locally is to use a self-signed certificate. Your browser won't trust this self-signed certificate natively and will most likely tell you "Your connection is not private." Encountering this message on a website you don't control is not something you should ignore. However, in this instance, you control the origin of the cert and the server itself so you can safely bypass this screen.

While these principals work for any environment or stack; this article explains how to create a self-signed certificate with Docker and Nginx. If you're not interested in the Docker specific example, skip to the end of the article.

Docker Example

With Docker, we have two options for creating self-signed certificates. The first option is to generate the certificate on your local machine and copy them into the container during the build process. The second is to create a certificate during the build process.

Copying a certificate from your local machine.

I won't go into very much detail on the process of generating a self-signed certificate, as it's been explained at great lengths many times over. You'll find additional links that explain the process in more detail at the end of the article.

To create a new self-signed certificate you'll first need to ensure OpenSSL is available on your machine. If you're running a Unix machine (Linux or MacOS), it's likely already installed. To ensure it's available, run the following command:

$ openssl version

This command should output something like "LibreSSL 2.6.5" or "OpenSSL 1.1.1a 20 Nov 2018" depending on your platform. If you receive a message saying "command not found," you'll need to install OpenSSL before proceeding. Once you've verified that OpenSSL is available, you can run the following command in the terminal and follow the interactive prompts.

$ openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout PROJECT_DIRECTORY/nginx.key -out PROJECT_DIRECTORY/nginx.crt

Replace "PROJECT_DIRECTORY" with the directory where your Dockerfiles are.

Once completed you'll be left with a nginx.key and nginx.crt file. Your Dockerfile can be modified to copy these two files into your container. This step is shown below with the two lines that begin with ADD PROJECT_DIRECTORY/...

FROM nginx:latest

# Adds certificates from local machine to docker container
ADD PROJECT_DIRECTORY/nginx.key /etc/nginx/ssl/nginx.key
ADD PROJECT_DIRECTORY/nginx.crt /etc/nginx/ssl/nginx.crt

# Assumes servers/vhosts are defined in /etc/nginx/conf.d/
ADD PROJECT_DIRECTORY/vhost.conf /etc/nginx/conf.d/default.conf

With the certificates now in our Docker container, the last step is to update our Nginx server configuration to use the certificates. This step is demonstrated below in our vhost.conf file using the ssl_certificate and ssl_certificate_key options.

server {
    listen 443 ssl http2 default_server;
    listen [::]:443 ssl http2 default_server;

    index index.php index.html;
    root /var/www/public;

    # Self-signed certificate setup
    ssl_certificate /etc/nginx/ssl/nginx.crt;
    ssl_certificate_key /etc/nginx/ssl/nginx.key;

    # Example PHP application
    location / {
        try_files $uri /index.php?$args;
    }

    # Example PHP application upstream
    location ~ \.php$ {
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_pass app:9000;
        fastcgi_index index.php;
        include fastcgi_params;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        fastcgi_param PATH_INFO $fastcgi_path_info;
    }
}

This option works well if you're developing a project on your own. However, if you're working with several other developers, they'll also need the certificates. Each developer could generate a local certificate to use, but requires introduces additional steps to the setup process. A better method is to check the certificate into source control although there is another option which makes the process easier.

Generate the Certificate with Docker Build

The best solution I've found is to generate the certificate during the build process with the Dockerfile. This method allows every developer to have their certificates that only their machine knows about as well as removing the burden of managing certificate expirations in source control.

If a developer ever encounters an expired certificate they can rebuild the Docker container with a single command to get a fresh certificate. The previous approach would require someone of the team to generate a new cert locally, commit it to version control and then rebuild the docker container.

To do this, we'll modify the previous Dockerfile to look like the following:

FROM nginx:latest

# Ensure OpenSSL is available and generate the certificate chain
RUN apt-get update && apt-get install -y openssl\
    && mkdir /etc/nginx/ssl\
    && openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout /etc/nginx/ssl/nginx.key -out /etc/nginx/ssl/nginx.crt -subj "/C=US/ST=Ohio/L=Cleveland/O=X/CN=www.example.com"

# Assumes servers/vhosts are defined in /etc/nginx/conf.d/
ADD PROJECT_DIRECTORY/vhost.conf /etc/nginx/conf.d/default.conf

Instead of copying the static certificates with the ADD command we're now installing OpenSSL (if not already available) and generating the certificate on the container. OpenSSL normally requires us to answer several questions before issuing the certificate chain. However, the -subj flag allows us to specify the answers to these questions when we run the command. With this, we can now create "unattended" self-signed certificates. In this scenario, we would use the same Nginx config from the previous example.

TL;DR

Use self-signed certificates for local development and auto-generate them during your development environment setup. Use the following command to generate unattended certificates (no prompts).

$ openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout /etc/nginx/ssl/nginx.key -out /etc/nginx/ssl/nginx.crt -subj "/C=US/ST=Ohio/L=Cleveland/O=X/CN=www.example.com"

Forem: Mike Mollick

Using Unique Columns with Soft Deletes in Laravel

Setup

Demonstrating the Problem

1. Create User

2. Delete User

3. Recreating user produces an error

Solution

1. Create a UserObserver class

2. Update the delete method to mutate the email

3. Register UserObserver within our AppServiceProvider

Demonstrating the Solution

1. Create User

2. Delete User

3. Recreating user creates a new record

Conclusion

Encrypt your Development Environment Traffic

Docker Example

Copying a certificate from your local machine.

Generate the Certificate with Docker Build

TL;DR

2. Update the `delete` method to mutate the email

3. Register UserObserver within our `AppServiceProvider`