CODEWITHSUNDEEP
mysqlaes
user1091949
user1091949

Reputation: 1933

Why is AES_DECRYPT returning null?

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');

Upvotes: 7

Views: 15393

Answers (3)

quardas
quardas

Reputation: 691

Check if type of your field is blob instead binary(32)

Upvotes: 0

abhinai raj
abhinai raj

Reputation: 51

When you insert binary data into a VARCHAR field there are some binary characters that a VARCHAR can't handle and they will mess up in the inserted value. And then the inserted value will not be the same when you retrieve it. 1.select hex(aes_encrypt(file,'key')); 2.select aes_decrypt(unhex(file),'key');

Upvotes: 0

user1091949
user1091949

Reputation: 1933

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.

Upvotes: 13

Related Questions