Mysql: Question about MySQL AES_ENCRYPT -> Node.js AES decipher BUFFER

[innc]

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]

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

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

[dresende]

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]

@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

[dresende]

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]

@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]

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]

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