Authenticate user with AES Encrypted Password MySQL

Question

I'm implementing AES encryption for passwords stored in my application's user table, however, I can't seem to authenticate whether the user exists or not when I run the following query. I can't see what's wrong with it. Any help would be appreciated.

SELECT * FROM user_table WHERE username='users-name' AND password=AES_DECRYPT('users-password','encryption-key');

@Drew: `insert into user_table values ('username', aes_encrypt('password','hashedEncryptionKey'));` — Shahraiz T., Oct 31 '15 at 20:23
@TGrif Sure. I have a PHP application containing student records and when a user enters their login credentials in a login form, I get the credentials via POST and try to verify if they match up with values from a database table that I've created. — Shahraiz T., Oct 31 '15 at 20:38
in that case, steer toward something like [this](http://stackoverflow.com/a/32556010) I wrote up, or a better one — Drew, Oct 31 '15 at 20:45
What's going wrong with the given code? What have you tried to make it work? — Nico Haase, Aug 04 '20 at 15:25

score 1 · Answer 1 · answered Oct 31 '15 at 20:36

1

drop table user_table;
create table user_table
(   id int auto_increment primary key,
    username varchar(50) not null,
    password varbinary(256) not null    -- up to you, 128, 256, variable, see link below
);

insert into user_table (username,password) values ('Shahraiz T.', aes_encrypt('secret','hashedEncryptionKey'));

SELECT * FROM user_table 
WHERE username='Shahraiz T.' 
AND AES_DECRYPT(password,'hashedEncryptionKey')='secret';
-- hurray, 1 row

SELECT * FROM user_table 
WHERE username='Shahraiz T.' 
AND AES_DECRYPT(password,'hashedEncryptionKey')='imposter';
-- evil imposter, 0 rows

Mysql Encryption and Compression Functions manual page.

where you have, and what you do with the hashedEncryptionKey is beyond the scope of this question.

answered Oct 31 '15 at 20:36

Drew

24,851
10
43
78

Thanks, but why use varbinary as compared to blob for the password datatype? – Shahraiz T. Oct 31 '15 at 20:39
both are binary. That is the main thing. varchar would blow up. – Drew Oct 31 '15 at 20:40
As you seem to be an Android programmer, the bigger question is, how is this performed for you. That is where middleware comes into play. Such as PHP and its built in hashing power. And as such, one would not mix PHP's with mysql's. Most would just go with PHP – Drew Oct 31 '15 at 20:41
1

thanks! I think this just did the trick. But what I still can't get is why this isn't working when I use blob as the password datatype. I read many online implementations pointing to the use of blob for encrypted value storage in a db. Btw, I don't program much for Android now, it's just something I never bothered to change :) – Shahraiz T. Oct 31 '15 at 20:46

codelone · Answer 2 · 2020-08-04T15:56:44.267

Hope this might be useful.

Here first I select the number of columns which I need in later use (aid, username and password) but in my case the whole password column AES_DECRYPT with the single YOUR_KEY_HERE.

By adding WHERE username= 'admin' AND password=AES_ENCRYPT('YOUR_PASSWORD','YOUR_KEY_HERE') this will allow you to enter encrypted password from the form at the time when you enter the password.

SELECT aid,username, AES_DECRYPT(password,'YOUR_KEY_HERE') as pwd 
FROM admin_login WHERE username= 'admin' AND 
password= AES_ENCRYPT('YOUR_PASSWORD','YOUR_KEY_HERE');

Note: YOUR_PASSWORD is Password entered in input field.

OUTPUT COLUMNS

aid, username, pwd

What have you changed, and why does this solve the problem? Please add some explanation to your answer such that others can learn from it — Nico Haase, Aug 04 '20 at 15:26

Authenticate user with AES Encrypted Password MySQL

2 Answers2