Appendix A: Implement column-level encryption in MariaDB

This appendix details the implementation of column-level encryption within MariaDB databases using its built-in encryption functions like AES_ENCRYPT and AES_DECRYPT. When paired with Transparent Data Protection (TDP), this approach enables granular security controls where specific database directories can be selectively encrypted. By combining these complementary encryption methodologies, you can establish a multi-layered defense strategy that protects sensitive data both at the application level and within the physical storage architecture.

Connect to MariaDB

Connect to MariaDB by using one of the following techniques:

Use HeidiSQL.
Use the command line.

Use HeidiSQL (GUI)

Perform the following steps to connect to MariaDB by using HeidiSQL (GUI):

Launch the HeidiSQL application.

Select [ New ] in the Session Manager window to create a new session.

Enter the following Connection settings:

Network type: Select MariaDB or MySQL (TCP/IP).
Hostname / IP: Enter your server IP address or hostname (use 127.0.0.1 or localhost for local connections).
User: Enter your MariaDB username (such as root or a specific user).
Password: Enter your password.
Port: 3306 (default for MariaDB/MySQL).

Select [ Open ].

Use the command line

Perform the following steps to connect to MariaDB by using the command line:

Open your terminal or command prompt.

Execute the following command to connect by using the mariadb (or mysql) client:

PowerShell

mariadb -h your_host_or_ip -u your_username -p

Replace your_host_or_ip and your_username.
When prompted, enter your password.

For local connections, you might use the following command instead:

PowerShell

mariadb -u root -p

Create a new database

Create a new database by using one of the following techniques:

Use HeidiSQL.
Use the command line.

Use HeidiSQL

Right-click on your server connection name in the left panel.

Select Create new > Database.

Enter a name (such as TestDB).

Select [ OK ].

Use the command line

Execute the following SQL command:

SQL

  CREATE DATABASE TestDB;

Switch to the new database:

SQL

  USE TestDB;

Create a sample table and apply AES_ENCRYPT at the column level

The following SQL query creates a table and inserts data, encrypting the ssn column by using AES_ENCRYPT.

AES_ENCRYPT returns a binary string (VARBINARY or BLOB). We define the ssn column accordingly.

-- Make sure you are using the correct database
  USE TestDB;

  -- Create the table
  CREATE TABLE sensitive_data (
      id INT AUTO_INCREMENT PRIMARY KEY, -- MariaDB equivalent of serial
      name VARCHAR(255),              -- Using VARCHAR instead of TEXT
      ssn VARBINARY(255)              -- Store encrypted data (adjust size if needed)
  );

  -- Insert data, encrypting the SSN field
  -- IMPORTANT: Replace 'encryption_key' with a strong, securely managed key in real applications!
  INSERT INTO sensitive_data (name, ssn)
  VALUES
  ('John Doe', AES_ENCRYPT('123-45-6789', 'encryption_key'));

Execute this SQL code in your HeidiSQL Query tab or your command-line client.

View unencrypted data

To decrypt the data when querying, use the AES_DECRYPT function. You likely need to CAST the result back to a readable character type (like CHAR) by using the following command:

-- IMPORTANT: Use the SAME 'encryption_key' used during encryption.
  SELECT
      name,
      CAST(AES_DECRYPT(ssn, 'encryption_key') AS CHAR) AS decrypted_ssn
  FROM sensitive_data;

View column-level encrypted data

To see the raw encrypted data stored in the table (which appears as binary or unreadable characters in most clients), use the following command to select the column directly:

SELECT id, name, ssn
  FROM sensitive_data;

View column-level encrypted data in hexadecimal format

To view the encrypted binary data as a more readable hexadecimal string, use the following HEX() function:

SELECT id, name, HEX(ssn) AS encrypted_ssn_hex
  FROM sensitive_data;

Find the physical DB files location

MariaDB typically stores each database in a subdirectory within its main data directory. This section explores locating the data directory and database folder.

Find the MariaDB data directory

Run the following SQL query to find the directory:

Sql

SHOW VARIABLES LIKE 'datadir';

Note the path returned in the Value column (such as C:\Program Files\MariaDB X.Y\data\).

Locate the database folder

Go to the datadir path on your server file system.
That directory contains a folder named after your database (such as TestDB). This folder contains the physical files for your tables, such as .frm, .ibd files if using InnoDB.

Summary of the Symmetric Key used for an encryption operation

Function: AES_ENCRYPT / AES_DECRYPT in MariaDB.
Algorithm: Uses the Advanced Encryption Standard (AES). By default, it uses AES-128. You can configure the block encryption mode by using the block_encryption_mode system variable (the default often depends on the MariaDB version, but it might be aes-128-ecb).
Key: You directly provide the key_str (passphrase) to the functions. MariaDB uses this string to perform the encryption or decryption.
Key Management: Crucially, you are responsible for managing this key_str. MariaDB does not store it in relation to these functions. In real applications, avoid hardcoding keys directly in SQL or application code. Store them securely (such as in a secrets manager, environment variables, or a secure configuration file) and pass them to the application logic that constructs the SQL query.

Encryption example

We recommend you handle key loading in your application code:

Your application reads the encryption key from the text file, such as C:\MariaDBKeys\DEK.txt.
The application then passes this key as a parameter or variable when constructing the SQL query that uses AES_ENCRYPT or AES_DECRYPT.

The following conceptual example is the SQL part only, assuming the application provides the key:

-- Example table structure
  CREATE TABLE sensitive_data_filekey (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name TEXT,
      address VARBINARY(512) -- Adjust size as needed
  );

  INSERT INTO sensitive_data_filekey (name, address_plain) -- Store plain temporarily if needed
  VALUES
  ('Alice', '123 Main St'),
  ('Bob', '456 Oak Ave');

  -- *** In your Application Code ***
  -- 1. Read the key from 'C:\MariaDBKeys\DEK.txt' into a variable, let's call it `fileKey`.
  -- 2. Construct and execute the UPDATE statement, passing `fileKey`.

  -- SQL executed by the application (using a placeholder like ? for the key)
  -- Assume address_plain holds the original value, and address will hold encrypted
  UPDATE sensitive_data_filekey
  SET address = AES_ENCRYPT(address_plain, ?) -- The application binds the 'fileKey' variable here
  WHERE address IS NULL; -- Example condition

  -- To view encrypted data (application provides key for decryption)
  SELECT id, name, CAST(AES_DECRYPT(address, ?) AS CHAR) AS decrypted_address -- App binds 'fileKey'
  FROM sensitive_data_filekey;

  -- To view raw encrypted data
  SELECT * FROM sensitive_data_filekey;

​Connect to MariaDB

​Use HeidiSQL (GUI)

​Use the command line

​Create a new database

​Use HeidiSQL

​Use the command line

​Create a sample table and apply AES_ENCRYPT at the column level

​View unencrypted data

​View column-level encrypted data

​View column-level encrypted data in hexadecimal format

​Find the physical DB files location

​Find the MariaDB data directory

​Locate the database folder

​Summary of the Symmetric Key used for an encryption operation

​Encryption example

Connect to MariaDB

Use HeidiSQL (GUI)

Use the command line

Create a new database

Use HeidiSQL

Use the command line

Create a sample table and apply AES_ENCRYPT at the column level

View unencrypted data

View column-level encrypted data

View column-level encrypted data in hexadecimal format

Find the physical DB files location

Find the MariaDB data directory

Locate the database folder

Summary of the Symmetric Key used for an encryption operation

Encryption example