Different Encryption Methods in MySQL

Below are examples of three different encryption methods to encrypt data inserted into a MySQL database table.

A test table is created where field1 will store data in plain text and field2 will store encrypted data.

CREATE TABLE encrypt_test
(field1 varchar(255) null,
field2 varchar(255));

SHA1 Example

INSERT INTO encrypt_test(field1,field2) VALUES ('dd',SHA1('yoda'));
SELECT * FROM encrypt_test WHERE field2=SHA1('yoda');

SHA2

INSERT INTO encrypt_test(field1,field2) VALUES ('a',sha2('password',224));
SELECT * FROM encrypt_test WHERE field2 = sha3('password',224));

SELECT * FROM encrypt_test WHERE field2 = sha2('password',224));

AES_ENCRYPT

CREATE TABLE demodb
(value blob);

INSERT INTO demodb (demo) VALUES (AES_ENCRYPT('test to encrypt','PasswordKey'));
SELECT CAST(AES_DECRYPT(value,'PasswordKey') as CHAR) FROM demodb;

SELECT CAST(AES_DECRYPT(value,'PasswordKey') as CHAR) = 'test to encrypt' FROM demodb;