# pseudonimiseren = pseudonymize is a way to make readable text unreadable.
# It's simply encrypting.

# First do a test query on the table.

SET @pseudo = '';

SELECT id, surname, `password` FROM fakenames LIMIT 3,1;

SELECT HEX( AES_ENCRYPT( surname,`password` ) ) INTO @pseudo FROM fakenames LIMIT 3,1;

SELECT @pseudo;

SELECT id, surname,`password`, AES_DECRYPT( UNHEX( @pseudo ),`password` ) FROM fakenames LIMIT 3,1;

# Now add a column pseudoniem
ALTER TABLE `fakenames` DROP IF EXISTS `pseudoniem`;

ALTER TABLE `fakenames` ADD `pseudoniem` VARCHAR(255) COLLATE 'utf8mb4_general_ci' NOT NULL;

# Update the column
UPDATE fakenames SET pseudoniem = HEX( AES_ENCRYPT( surname,`password` ) );

SELECT id, surname, `password`, pseudoniem FROM fakenames LIMIT 3,1;

# Do a SELECT with a LIKE.
# The LIKE should contain an uppercase caracter if you search for a surname that has a capital character.
# With all lowercase it will not appear in the result.
# Or do not use a uppercase character as the first character.
# This only applies in direct queries.
# A query in a stored procedure does not have that problem.

SELECT id, surname,`password`, AES_DECRYPT( UNHEX( pseudoniem),`password` ) AS pseudo FROM fakenames HAVING pseudo LIKE '%ee%';

DELIMITER ;;

DROP PROCEDURE IF EXISTS `spZoekPseudoniem`;;
CREATE PROCEDURE `spZoekPseudoniem`(IN `pseudon` char(25))
SELECT id, surname, givenname, username, `password`, pseudoniem, aes_decrypt( unhex( pseudoniem ),`password` ) AS pseudo FROM fakenames HAVING pseudo LIKE concat('%',pseudon,'%');;

DELIMITER ;

CALL spZoekPseudoniem('ee');