mysql - Why is AES_DECRYPT returning null?

Question

I've found similar questions, but no clear answer for this question. I have this table:

CREATE DATABASE testDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

CREATE TABLE testTable
(
firstName binary(32) not null,
lastName binary(32) not null
/* Other non-binary fields omitted */
)
engine=INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

This statement executes just fine:

INSERT INTO testTable (firstName) VALUES (AES_ENCRYPT('Testname', 'test'));

But, this returns NULL:

SELECT AES_DECRYPT(firstName, 'test') FROM testTable;

Why does this return NULL?

Fwiw, this returns "testValue" as expected:

SELECT AES_DECRYPT(AES_ENCRYPT('testValue','thekey'), 'thekey');

Answer 1

The answer is that the columns are binary when they should be varbinary. This article explains it:

Because if AES_DECRYPT() detects invalid data or incorrect padding, it will return NULL.

With binary column types being fixed length, the length of the input value must be known to ensure correct padding. For unknown length values, use varbinary to avoid issues with incorrect padding resulting from differing value lengths.