为什么AES_DECRYPT返回null? [英] 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;

此语句执行得很好:

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

但是，这将返回NULL:

But, this returns NULL:

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

为什么这会返回NULL?

Why does this return NULL?

首先，这将按预期返回"testValue":

Fwiw, this returns "testValue" as expected:

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

推荐答案

答案是，当列应为varbinary时，列为binary. 本文对此进行了解释:

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

因为AES_DECRYPT()检测到无效数据还是不正确 填充，它将返回NULL.

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

binary列类型为固定长度时，必须知道输入值的长度以确保正确填充.对于未知的长度值，请使用varbinary来避免因值长度不同而导致填充错误的问题.

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.

这篇关于为什么AES_DECRYPT返回null?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！