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 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 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 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;