Using Database Comments to Track Columns With Sensitive Data

Recently, I was exploring a Rails application when I noticed that the developers had marked database columns as having sensitive data via a table's column definition. Various databases support comments for columns, but here's MySQL's explanation of a column comment:

A comment for a column can be specified with the COMMENT option, up to 1024 characters long. The comment is displayed by the SHOW CREATE TABLE and SHOW FULL COLUMNS statements. It is also shown in the COLUMN_COMMENT column of the Information Schema COLUMNS table.

In MySQL, we can add a comment to a column when creating or modifying a table using the following syntax:

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Example comment for name.',
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  -- ...
)

In Laravel, we can easily add comments to table columns using the comment() method in a database migration on an existing or new column:

$table->string('email')
    ->unique()
    ->comment('The user\'s email address they use the log in');

You can inspect database column comments via the table's structure; here's an example of the migrations table in a fresh Laravel application:

Not all databases support table or column comments, but databases like MySQL and PostgreSQL allow you to add comments. If your database supports comments, they can be helpful descriptors of a column and used in various ways to help developers understand a schema.

#Using Comments to Mark Columns with Sensitive Data

Comment usage could range from user-friendly comments about a column or other contextual information. But what if we used something like sensitive_data=true to mark fields with sensitive user or system information?

Even if your Laravel application is mature, you could create a migration to mark columns containing sensitive information to audit your data and help in removing sensitive information during exports, database cloning, or user information requests:

php artisan make:migration mark_columns_with_sensitive_data

Carefully defining the columns as they were, we can add a comment in the migration for any table and columns we want to mark as sensitive:

public function up(): void
{
    Schema::table('users', function (Blueprint $table) {
        $table->string('email')->comment('sensitive_data=true')->change();
        $table->string('name')->comment('sensitive_data=true')->change();
        $table->string('password')->comment('sensitive_data=true')->change();
    });
 
    Schema::table('profiles', function (Blueprint $table) {
        // ...
    });
}

After running the migration, you will see that the database now has comments on the marked fields:

#Finding all Tables and Columns Marked as Sensitive

We now have useful comments throughout our application that developers can use for context, but our new comments allow us to go beyond documentation! We can find all columns marked as sensitive across our application and use this information for privacy, database cloning, and more.

While each database is different, MySQL can query the information schema columns table to find all fields marked as sensitive. My sensitive_data=true is a convention, you can do whatever you want, but here's how you could find all columns across the database:

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_COMMENT LIKE '%sensitive_data=true%';

Here's the output from our above example:

#Taking it Further

What if we wanted an easy way to get all the sensitive column names for a given model? There are probably various ways to achieve this, but what if we built an application trait our models could use to find sensitive columns:

namespace App\Models\Concerns;
 
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
 
/**
 * @mixin \Illuminate\Database\Eloquent\Model
 */
trait InteractWithSensitiveColumns
{
    /**
     * @return Collection<string>
     */
    public function getSensitiveColumns(): Collection
    {
        return $this->getConnection()
            ->table('INFORMATION_SCHEMA.COLUMNS')
            ->where('TABLE_NAME', $this->getTable())
            ->where('COLUMN_COMMENT', 'like', '%sensitive_data=true%')
            ->pluck('COLUMN_NAME');
    }
}

Let's take this trait for a spin in a User model instance, and see how it works:

Note that using the above trait locks you into using MySQL for models using this method. I am unsure if there is a database-agnostic way to get this information, but be aware that the above trait is MySQL-specific.

#Learn More

I hope you've seen a few ways that database comments could be useful for maintaining your application database! Whether you are creating a new application or working on an existing app, you can start using comments right away! You can learn more about managing your database through Laravel's migrations and Eloquent models in the Database documentation.