> ## Documentation Index
> Fetch the complete documentation index at: https://docs.futurex.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Appendix A: Implement column-level encryption in MariaDB
> Detailed guide to implement column-level encryption using MariaDB’s built-in AES functions.
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:
1. Use HeidiSQL.
2. 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 expandable lines wrap title="PowerShell" theme={null}
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 expandable lines wrap title="PowerShell" theme={null}
mariadb -u root -p
```
## Create a new database
Create a new database by using one of the following techniques:
1. Use HeidiSQL.
2. 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:
```none title="SQL" expandable lines wrap theme={null}
CREATE DATABASE TestDB;
```
Switch to the new database:
```none title="SQL" expandable lines wrap theme={null}
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.
```Plaintext wrap theme={null}
-- 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:
```Plaintext wrap theme={null}
-- 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:
```Plaintext wrap theme={null}
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:
```Plaintext wrap theme={null}
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 expandable lines wrap title="Sql" theme={null}
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:
1. Your application reads the encryption key from the text file, such as `C:\MariaDBKeys\DEK.txt`.
2. 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:
```Plaintext wrap theme={null}
-- 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;
```