Session JSON getting escaped before going into the Database

[jnbarlow]

I'm consistently getting this error:

SyntaxError: Unexpected token
at Object.parse (native)
at Query._callback (/vagrant/server/node_modules/express-mysql-session/lib/index.js:157:34)
at Query.Sequence.end (/vagrant/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
at Query._handleFinalResultPacket (/vagrant/server/node_modules/mysql/lib/protocol/sequences/Query.js:143:8)
at Query.EofPacket (/vagrant/server/node_modules/mysql/lib/protocol/sequences/Query.js:127:8)
at Protocol._parsePacket (/vagrant/server/node_modules/mysql/lib/protocol/Protocol.js:271:23)
at Parser.write (/vagrant/server/node_modules/mysql/lib/protocol/Parser.js:77:12)
at Protocol.write (/vagrant/server/node_modules/mysql/lib/protocol/Protocol.js:39:16)
at Socket. (/vagrant/server/node_modules/mysql/lib/Connection.js:82:28)
at Socket.EventEmitter.emit (events.js:95:17)

It seems that the mysql driver is escaping the data element when the SQL is generated.

Data Element:

{"cookie":{"originalMaxAge":null,"expires":null,"httpOnly":true,"path":"/"}}

Generated SQL:

INSERT INTO sessions (session_id, expires, data) VALUES ('705L-OuRjyiksGE0iosiCj81LOfoxmQo', 1441302828, '{"cookie":{"originalMaxAge":null,"expires":null,"httpOnly":true,"path":"/"}}') ON DUPLICATE KEY UPDATE expires = VALUES(expires), data = VALUES(data)

Is there a configuration I've missed? I would think if everyone was seeing this it would be more widely known.

Config:

 mysql = require("mysql"),
    options = {
        host: "localhost",
        port: 3306,
        user: ... 
        password: ...
        database: ...
    },
    db = mysql.createConnection(options),
    sessionStore = new SessionStore({}, db),

app.use(session({
    key: "...",
    secret:"...",
    store: sessionStore,
    resave: true,
    saveUninitialized: true
}));

[jnbarlow]

So, it turns out that if you have NO_BACKSLASH_ESCAPE turned on, this errors shows up. The MySQL driver automatically escapes strings, so all the double quotes in the JSON string are being escaped. It wasn't noticed before because MySQL was ignoring the backslashes, but I think the auto-escaping of JSON is more the issue. I've suggested to the maintainers of the MySQL driver to try to detect if the string is JSON first before escaping.

[jnbarlow]

Alternatively, updating the query to concat the JSON string directly in would be another way around the problem.

[chill117]

I don't think the issue you're experiencing should be handled within this module. It uses JSON.stringify() to create a string from the session data before inserting it into the database. This module uses node-mysql module internally for creating MySQL connections. All the options you pass to the new SessionStore(options) constructor are also passed thru to the node-mysql module. So if you need to pass options to it for how to handle string data when inserting it into the database, you can do that.

Hope this helps!

[jnbarlow]

Yeah, the node-mysql people are going to put the NO_BACKSLASH_ESCAPE as a known issue :)

[chill117]

Great. Glad things got sorted out for you :)