CODEWITHSUNDEEP
javascriptmysqljsonnode.js
RainingChain
RainingChain

Reputation: 7746

MySql: String Becomes An Object? Strange Behaviour

Anyone can explain me why the string I put in the MySql database becomes an unusable object? How can I fix it?

Note: I'm using Nodejs. The table item is (id = VARCHAR and data = BLOB)

JSONf is simliar to JSON but it can be used for functions as well.

item = {"id":"myitem",
        "option":[  (function(key){ craftItem(key,{"piece":"armor"},{"item":[]});}),
                    (function(key){ test2(key);})
        ]};

str = JSONf.stringify(item); //str = {"id":"myitem","option":["function (key){ craftItem(key,{\"piece\":\"armor\"},{\"item\":[]});}","function (key){ test2(key);}"]}
console.log(typeof str);    //string
obj = JSONf.parse(str);
obj.option[1]();    //calling the method as expected

//Note: data is blob type in MySql
client.query("INSERT INTO item(id,data) VALUES ('" + item.id + "','" +     JSONf.stringify(item) + "')",
function(err, results) {    if(err) throw err 
    client.query("SELECT * FROM item WHERE id='" + item.id + "'" ,function(err,     results) {
        str = results[0].data;
        console.log(str);   //<Buffer 7b 22 69 64 22 3a 22 6d 79 69 74 65 6d 22 2c 22 6f 70 74 69 6f 6e 22 3a 5b 22 66 75 6e...
        console.log(typeof str);    //Object?...

        obj = JSONf.parse(str); //ERROR: Unexpected token p
        //Note: p is probably from the ' craftItem(key,{\"piece\":\"armor\"} '
        obj.option[1](); //never called because program crashed...

});

});

JSONf.stringify = function(obj) {
return JSON.stringify(obj,function(key, value){
    return (typeof value === 'function' ) ? value.toString() : value;
});
}

JSONf.parse = function(str) {
return JSON.parse(str,function(key, value){
    if(typeof value != 'string') return value;
    return ( value.substring(0,8) == 'function') ? eval('('+value+')') : value;
});
}

Upvotes: 0

Views: 298

Answers (2)

Andrey Sidorov
Andrey Sidorov

Reputation: 25456

If you don't want to change schema to a type which is serialized to string by driver (this is based on field type, like TEXT vs BLOB and on a charset - if it's "binary", Buffer is used) then you could convert result to a string:

str = results[0].data.toString(); // array of bytes interpreted as utf8 sequence and converted to JS string

Also, you are concatenating your data with query manually. You need to add escaping (connection.escape()) or, much better, use built in parameterization support:

instead of 

client.query("SELECT * FROM item WHERE id='" + item.id + "'" ,function(err, res) {
   ///
});

you should do

client.query("SELECT * FROM item WHERE id=?", [item.id] ,function(err, res) {
   ///
});

or 

client.execute("SELECT * FROM item WHERE id=?", [item.id] ,function(err, res) {
   ///
});

if you want to use prepared statements (supported in node-mysql2)

Upvotes: 1

djbrick
djbrick

Reputation: 213

Well your saving to a blob field which is just a collection of bytes. In node this is represented as a buffer object. What you probably want to do is change the mysql datatype of the field from BLOB to TEXT this way character encoding is imposed on the data. This way the returned object should be a string that you can then parse.

On a related note are you sure you want to be serializing data into a database. This sort of defeats the purpose of using a database in the first place, as you lose being able to index / sort / etc. on the serialized data efficiently.

EDIT:

Also note the object isn't unusable you could convert the Buffer back into a string object if you lookup buffer objects on the node documentation. However, if your storing text and not binary data like an image it's probably just best to use TEXT

Upvotes: 1

Related Questions