Appendix A: Implementing 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 allows for granular security controls where specific database directories can be selectively encrypted by combining these complementary encryption methodologies, organizations 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 (using heidisql or command line) option 1 using heidisql (gui) launch the heidisql application create new session click the \[ new ] button in the session manager window enter connection info network type select "mariadb or mysql (tcp/ip)" hostname / ip enter your server ip or hostname (use "127 0 0 1" or "localhost" for local connections) user enter your mariadb username (e g , "root" or a specific user) password enter your password port 3306 (default for mariadb/mysql) click \[ open ] option 2 using command line open your terminal or command prompt connect using the mariadb (or mysql ) client mariadb h your host or ip u your username p replace your host or ip and your username you will be prompted to enter your password for local connections, you might just use mariadb u root p create a new database using heidisql right click on your server connection name in the left panel select create new > database enter a name (e g , "testdb") click \[ ok ] using command line execute the following sql command create database testdb; switch to the new database use testdb; create a sample table and apply aes encrypt at the column level the below sql query creates a table and inserts data, encrypting the ssn column 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'll likely need to cast the result back to a readable character type (like char ) \ 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 will appear as binary/unreadable characters in most clients), simply 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 hex() function select id, name, hex(ssn) as encrypted ssn hex from sensitive data; find physical db files location mariadb typically stores each database in a subdirectory within its main data directory find the mariadb data directory run the following sql query show variables like 'datadir'; note the path returned in the value column (e g , c \program files\mariadb x y\data\ ) locate the database folder navigate to the datadir path on your server's file system inside that directory, you will find a folder named after your database (e g , testdb ) this folder contains the physical files for your tables (like frm , ibd files if using innodb) summary of the symmetric key used for this operation function aes encrypt / aes decrypt in mariadb algorithm uses the advanced encryption standard (aes) by default, it uses aes 128 the block encryption mode can be configured via the block encryption mode system variable (the default often depends on the mariadb version but might be aes 128 ecb ) key you directly provide the key str (passphrase) to the functions mariadb uses this string to perform the encryption/decryption key management crucially, you are responsible for managing this key str it is not stored by mariadb itself in relation to these functions in real applications, avoid hardcoding keys directly in sql or application code store them securely (e g , 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 the recommended approach is to handle key loading in your application code your application reads the encryption key from the text file (e g , 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 conceptual example (sql part only, assuming 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;