AES encryption on mysql and node.js

Question

I have struggle below question with days, and posted same question earlier and didn't get any positive feedback.

Im using mysql in build aes_encrypt method to encrypt new and existing data. https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html

SET @@SESSION.block_encryption_mode = 'aes-256-ecb';
INSERT INTO test_aes_ecb ( column_one, column_two )
values ( aes_encrypt('text','key'), aes_encrypt('text', 'key'));

I used ecb ciper, so It no need to use iv for that. Issue is I can't decrypt it from node.js side.

Im using sequelize and tried to call data through model --> decrypt from node side.

I tried with below libraries,

"aes-ecb": "^1.3.15",
"aes256": "^1.1.0",
"crypto-js": "^4.1.1",
"mysql-aes": "0.0.1",

Below are code snippets from sequelize call

async function testmysqlAESModel () {
    const users = await test.findAll();
    console.log('users', users[0].column_one);
    var decrypt = AES.decrypt( users[0].column_one, 'key' );
}

Its returning buffer data and couldn't decrypt from node side, Can someone provide proper example for that? Im struggling for days.

EDIT

Inserted record to mysql as below query.

SET @@SESSION.block_encryption_mode = 'aes-256-ecb';
INSERT INTO test_aes_ecb ( id, column_one, column_two )
VALUES (1, 2,AES_ENCRYPT('test',UNHEX('gVkYp3s6v9y$B&E)H@McQeThWmZq4t7w')));

In nodejs called like this,

testmysqlAESModel();
async function testmysqlAESModel () {
    const users = await test.findAll();
    console.log('users', users[0].column_one);
    var decipher = crypto.createDecipheriv(algorithm, Buffer.from("gVkYp3s6v9y$B&E)H@McQeThWmZq4t7w", "hex"), "");
    var encrypted = Buffer.from(users[0].column_one); // Note that this is what is stored inside your database, so that corresponds to users[0].column_one
    var decrypted = decipher.update(encrypted, 'binary', 'utf8');
    decrypted += decipher.final('utf8');
    console.log(decrypted);

}

Im getting below error,

I used below link to create 256bit key.

https://www.allkeysgenerator.com/Random/Security-Encryption-Key-Generator.aspx

Still couldn't fix, can you provide sample project or any kind of supporting code snippet for that ?

my ket is alpha numeric key similer to this format 8d6754e1-4524-4b1b-b0b8-81156c11c3a1 — Dilantha Wijesekara
– Dilantha Wijesekara, Commented Sep 21, 2021 at 18:25

Daniel Klischies · Accepted Answer · 2021-09-21 19:05:16Z

1

There are multiple issues here:

Ensure that your key has the correct length. AES is specified for certain key length (i.e. 128, 196 and 256 bit). if you use any other key length, then your key will be padded (zero extended) or truncated by the crypto library. This is a non-standard process, and different implementations will do this differently. To avoid this, use a key in the correct length and store it has hex instead of ascii (to avoid charset issues)
Potential issues regarding password to key inference. Some AES implementations use methods to infer keys from passwords/passphrases. Since you are using raw keys in MySQL, you do not want to infer anything but want to use raw keys in NodeJS as well. This means that if you are using the native crypto module, that you want to use createDecipheriv instead of createDecipher.

Caution: The AES mode you are using (ECB) is inherently insecure, because equal input leads to equal output. There are ways around that using other AES modes, such as CBC or GCM. You have been warned.

Example:

MySQL SELECT AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3')) as test; returns the buffer [145,108,16,83,247,49,165,147,71,115,72,63,152,29,218,246];

Decoding this in Node could look like this:

var crypto = require('crypto');
var algorithm = 'aes-128-ecb';
var decipher = crypto.createDecipheriv(algorithm, Buffer.from("F3229A0B371ED2D9441B830D21A390C3", "hex"), "");
var encrypted = Buffer.from([145,108,16,83,247,49,165,147,71,115,72,63,152,29,218,246]); // Note that this is what is stored inside your database, so that corresponds to users[0].column_one
var decrypted = decipher.update(encrypted, 'binary', 'utf8');
decrypted += decipher.final('utf8');
console.log(decrypted);

This prints text again.

Note that F3229A0B371ED2D9441B830D21A390C3 is the key in this example, you would obviously have to create your own. Just ensure that your key has the same length as the example, and is a valid hex string.

answered Sep 21, 2021 at 19:05

Daniel Klischies

1,1358 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dilantha Wijesekara Over a year ago

Thank you for the answer, I edited question with new tryout codes, Can you please look in to that ?

Daniel Klischies Over a year ago

Your key must be Hex-Encoded (there is a checkbox for that on the website you are using), yours is not. I would strongly recommend not using somebody elses webservice to generate keys. Instead, you can use OpenSSL to do that: openssl enc -aes-256-ecb -k replacethiswithsomethingrandom -P -md sha256. Also, what is algorithm in your example Javascript code? If you want aes 256, then that must be aes-256-ecb.

Dilantha Wijesekara Over a year ago

Thank you for the response , I have been struggle for days.

Dilantha Wijesekara Over a year ago

Hi, I have small question, Is this the method to decrypt above mysql insert data. select AES_DECRYPT (UNHEX(column_two), 'key' ) from test_aes_ecb;

Collectives™ on Stack Overflow

AES encryption on mysql and node.js

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related