MYSQL AES DECRYPT, result is null?

Question

create table usuario (
nombre varchar(10),
contraseña varchar(10)
);

INSERT INTO usuario 
VALUES ('manuel', AES_ENCRYPT('conejo seco', '10')
);

INSERT INTO usuario VALUES (
'conejo','1234567890'
);

select * from usuario;

Perfect, the user manuel contraseña is encrypted, now to decryption

SELECT AES_DECRYPT(contraseña, '10') FROM usuario WHERE nombre = 'manuel';

Hm? null? oh well, lets see if is unlocked.

select * from usuario;

Nope, still encrypted.

Thats how it went, how i can "unlock" the encrypted user "contraseña"? and see it when i call it with a select after i use decrypt.

Edit:

update usuario set contraseña = AES_DECRYPT(contraseña, '10') WHERE nombre = 'manuel';

result is null.

SELECT *, CAST(AES_DECRYPT(contraseña, '10') AS CHAR(50)) contraseña_decrypt FROM usuario where nombre = 'manuel';

result is null.

SELECT contraseña = AES_DECRYPT(contraseña, '10') FROM usuario WHERE nombre = 'manuel';

result is null.

You have to store the decrypted value back in your table. ATM you are just calculating it but this is temporary... — maxhb, Jan 25 '16 at 07:51
Possible duplicate of [How to use AES\_ENCRYPT and AES\_DECRYPT in mysql](http://stackoverflow.com/questions/16556375/how-to-use-aes-encrypt-and-aes-decrypt-in-mysql) — maxhb, Jan 25 '16 at 08:27
Indeed, looks like what i am looking, let me try it, if i understand it and make it work, i will delete this one, else you are free to show me how it will work with my current example. — manuel villarroel, Jan 25 '16 at 08:38
nope, my brain is unable to understand the sintax, the times i can make it work the result is still null. — manuel villarroel, Jan 25 '16 at 08:58

Álvaro González · Answer 1 · 2016-01-25T10:40:20.540

0

You try to store the output of AES_ENCRYPT(), which is binary, into a varchar(10) column (which is text). This is likely to corrupt your data. Try instead one of the binary types. To do so replace this:

create table usuario (
  nombre varchar(10),
  contraseña varchar(10)
);

... with e.g. this:

create table usuario (
  nombre varchar(10),
  contraseña varbinary(500)
);

edited Jan 25 '16 at 10:40

answered Jan 25 '16 at 09:05

Álvaro González

142,137
41
261
360

Still result is null even when setting it as binary. – manuel villarroel Jan 25 '16 at 09:39
You are right, took me a while to find it, but it was that way. – manuel villarroel Jan 25 '16 at 10:39
The updated question doesn't try my suggested fix. I've updated my answer and tried to be more specific. – Álvaro González Jan 25 '16 at 10:40

score 0 · Answer 2 · edited May 23 '17 at 12:15

create table usuario (nombre varchar(10), contraseña varbinary(18));

INSERT INTO usuario VALUES ('manuel', AES_ENCRYPT('ConejoSeco', '10'));
INSERT INTO usuario VALUES ('conejo','1234567890');

select * from usuario;

So VARCHAR(10) should be converted to VARBINARY(18) minimum, if AES_DECRYPT() detects invalid data or incorrect padding, it will return NULL. But it is also possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.

why 18?

So if your address field structure is= VARCHAR(10) ; //10 length of varchar, Then before your encryption it should be converted 16 * ((10/ 16) + 1) = 16 * (0.625 + 1) = 16 * 1.625 = 17.625

The answer was outside of stackoveflow, but it was an answer from a previous question done in stackoverflow.

outside: http://thinkdiff.net/mysql/encrypt-mysql-data-using-aes-techniques/

stackoverflow: How to use AES_ENCRYPT and AES_DECRYPT in mysql

MYSQL AES DECRYPT, result is null?

2 Answers2