Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Question about MySQL AES_ENCRYPT -> Node.js AES decipher BUFFER #419

Closed
innc opened this issue Mar 21, 2013 · 7 comments
Closed

Question about MySQL AES_ENCRYPT -> Node.js AES decipher BUFFER #419

innc opened this issue Mar 21, 2013 · 7 comments

Comments

@innc
Copy link

innc commented Mar 21, 2013

Hello :)
thank you for this project! I have a little problem with AES decipher Buffer and i hope you have an idea, what i am doing wrong...

Example:
MySQL table with AES_ENCRYPT for username and password

CREATE TABLE Accounts
(
    id            INT(4) NOT NULL AUTO_INCREMENT,
    username    VARBINARY(128) NOT NULL,
    password    VARBINARY(128) NOT NULL,
    PRIMARY KEY(id)
);

DELIMITER |
CREATE TRIGGER encodeAccounts BEFORE INSERT ON Accounts
FOR EACH ROW BEGIN
    SET NEW.username = AES_ENCRYPT(NEW.username, 'password');
    SET NEW.password = AES_ENCRYPT(NEW.password, 'password');
END;

Node.js:
sending query: SELECT * FROM Accounts;

Then I get such values for password and username:
-> <Buffer 07 86 95 ee 77 df 86 50 ae 18 4c d5 3e 48 42 75

How to decode it? My tries failed all... to deciper something should look like this:
I hope AES-128-ECB is choosen correctly...

var decipher = crypto.createDecipher('AES-128-ECB', 'password');
var dec = decipher.update(rows[i].username); // an example, value look like <Buffer ...
dec += decipher.final('utf8');
console.log('dec: ' + dec);
@dresende
Copy link
Collaborator

Why don't you use mysql to do the oposite?

connection.query("SELECT AES_DECRYPT(password, 'password') AS password FROM Accounts", ...);

@dresende
Copy link
Collaborator

If you think your decoding is right, you can also just convert the Buffer to string using .toString() and see if that's ok.

@innc
Copy link
Author

innc commented Mar 22, 2013

@dresende
Surely i could use AES_DECRYPT inside a query, but if i send such a SELECT statement with an AES_DECRYPT inside from Node.JS to MySQL server, then my encrypted username and password would be sent back in plaintext or decrypted at least :( and i wanted to avoid this... and probably this request would be logged at MySQL server too.
Thats the reason, why i tried AES encrypt on MySQL server side and AES decrypt on Node.js server side.
(sorry for my english language knowledge, i hope you will understand)

I don't know how to decipher the returned <Buffer 07 86... value or openssl doesnt support the correct needed AES decryption for MySQL AES encryption (meanwhile i believe this..,)

@dresende
Copy link
Collaborator

If you're worried that your AES_DECRYPT query will show up in logs or will be visible by any man-in-the-middle, you should also worry about AES_ENCRYPT (when you send it in the first place). If that's your concern, you should just avoid encryption stuff on the database and just do it (encrypt/decrypt) in nodejs.

About <Buffer ...>, I'm not sure I understand your doubt. You know what a nodejs Buffer is right?

http://nodejs.org/api/buffer.html#buffer_buf_tostring_encoding_start_end

@innc
Copy link
Author

innc commented Mar 22, 2013

@dresende
yep right, i only thought that i can use Node.JS crypto module to realize easily the MySQL AES ENCRYPT and DECRYPT on Node.JS server side, but it seems that the AES-128-ECB algorithm of openssl for the Node.JS cipher/decipher crypto module is not identically with MySQL AES-128-ECB algorithm. So like you mentioned it, now i changed my code to encrypt/decrypt only in Node.JS and i will store encrypted values at database.

Yep, i know the Node.JS Buffer module, i believe i failed because MySQL using a different AES-128-ECB algorithm, which I cannot realize so easily with Node.Js crypto module like i hoped.

Thank you for trying to help :)

@dbogatz
Copy link

dbogatz commented Jun 23, 2013

As I already quoted on stackoverflow - here again because it might be useful for other people:

Your passwords must be converted the same way MySQL will do it internally.
Must results in a 16Byte-length password XORd for longer Passwords or padded with 0-Bytes.
This function should do the job:

function convertCryptKey(strKey) {
    var newKey = new Buffer([0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]);
    strKey = new Buffer(strKey);
    for(var i=0;i<strKey.length;i++) newKey[i%16]^=strKey[i];
    return newKey;
}

MySQL-compatible encryption - note to use "createCipheriv" with an empty IV. (createCipher creates an own IV which is not the same as in MySQL)

var c = crypto.createCipheriv("aes-128-ecb", convertCryptKey("myPassword"), "");
var crypted = c.update('Take a trip at galaxytrek.com :)', 'utf8', 'hex') + c.final('hex');
console.log(crypted.toUpperCase());
>> 92068A5DAECE1E080EF4BA08A45CFF8D2262401F988A9241F1E4CEAAAB80BEAF7E0B50A1D5FD57CA56E92621622F018D

Which is the same like:

mysql> select hex(aes_encrypt('Take a trip at galaxytrek.com :)','myPassword'));
+--------------------------------------------------------------------------------------------------+
| hex(aes_encrypt('Take a trip at galaxytrek.com :)','myPassword'))                                |
+--------------------------------------------------------------------------------------------------+
| 92068A5DAECE1E080EF4BA08A45CFF8D2262401F988A9241F1E4CEAAAB80BEAF7E0B50A1D5FD57CA56E92621622F018D |
+--------------------------------------------------------------------------------------------------+

Decryption

var dc = crypto.createDecipheriv("aes-128-ecb", convertCryptKey("myPassword"), "");
var decrypted = dc.update('92068A5DAECE1E080EF4BA08A45CFF8D2262401F988A9241F1E4CEAAAB80BEAF7E0B50A1D5FD57CA56E92621622F018D', 'hex', 'utf8') + dc.final('utf8');
console.log(decrypted);
>> Take a trip at galaxytrek.com :)

@magicdawn
Copy link

Thanks to @dbogatz , I create a tiny npm module based on code presents here.
https://github.com/magicdawn/node-mysql-aes

dveeden pushed a commit to dveeden/mysql that referenced this issue Jan 31, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Development

No branches or pull requests

5 participants