Binary data stored in MySQL is getting corrupted with Node

Question

I'm using node-mysql2 and storing string-encoded binary data in MySQL. The column has a type of binary(16). The data stored in MySQL is being corrupted, for example:

NodeJS output: 47 23 43 14 ed 86 14 dc 12 f3 b8 6c dc 31 fb fa
MySQL HEX()  : 47 23 43 14 c3 ad c2 86 14 c3 9c 12 c3 b3 c2 b8

Code:

var binaryString = randomBinaryString();
db.sqlQuery("INSERT INTO test(binaryString) VALUES(" + db.escape(binaryString) + ")");
console.log(new Buffer(binaryString, 'binary').toString('hex'));

function randomBinaryString(){
    var str = "";
    for(var i = 0; i < 16; i++){
        str += String.fromCharCode(Math.floor(Math.random()*256));
    }
    return str;
}

How should a string that actually encodes binary data (each character being a byte) be stored in MySQL using node-mysql2?

Answer 1

You're not inserting a raw binary string but a UTF-8 encoded string. In UTF-8, codepoints after 127 are encoded using multiple bytes. The fifth byte ed (237) is encoded using 2 bytes.

Buffer(String.fromCharCode(0xed)) produces : <Buffer c3 ad>

Sending the random string as a Buffer with binary encoding should fix the problem. node-mysql will convert the buffer to a hex string when inserting.

db.query('INSERT INTO test VALUES (?)', [ Buffer(randomString(), 'binary') ]);

Also, the crypto module has a randomBytes method that generates n random bytes.

db.query('INSERT INTO test VALUES (?)', [crypto.randomBytes(16)]);