TO_BASE64 Protects your MariaDB Data

When your PHP application needs to authenticate users you can add the user names and passwords to a MariaDB table, brilliantly simple. However, having done so you probably will notice that the password is in clear text and was not one of your greatest of ideas. At the very least you can start protecting those passwords with encoding using TO_BASE64. A built in function to MariaDB. All without needing to touch the table schema.

Your application can send the data to MariaDB which will encode the data with the function TO_BASE64. The data is then protected to some degree and is better than being stored in clear text. The reverse function of TO_BASE64 is FROM_BASE64 and is used to read data.

You will see that when we build the PHP application we will encrypt the password with password_hash, alleviating the possibility of passwords being stored in clear text and being transmitted in clear text. The verify passwords in PHP we can use the reverse function password_verify. Having said this, it still makes sense to obfuscate the data further in MariaDB by using the TO_BASE64 encoding function.

CREATE TABLE admin (
id smallint unsigned NOT NULL AUTO_INCREMENT,
username varchar(20) NOT NULL,
password varchar(20) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO admin (username, password)
VALUES ('Fred',TO_BASE64('Password1'));

SELECT username, FROM_BASE64(password) AS Password
FROM admin;