MYSQL: Invalid JSON text: "Invalid escape character in string."

Question

I'm trying to insert a JSON object into a MySQL JSON column and I'm getting: Invalid JSON text: "Invalid escape character in string."

The offending character is the \n in this part of the object:

"summary":"Summary of Qualifications:\nAccomplished and results-driven General Business Director with a consistent track record."

I've tried JSON.stringify, JSON_OBJECT, JSON_QUOTE, and my most recent attempts included:

summary.replace("\n", "\\n") and variations on replacing the existing escape characters with proper? escape characters.

I'm using Node, Express and JawsDB if that may have any bearing.

EDIT: Here's the actual insert code:

const fields = '(myField)';
const values = `('${JSON.stringify(field.contents || {})}')`;
db.query('INSERT INTO mydb ' + fields + ' VALUES ' + values,
    err => { 
        if (err) {
            console.log(values);
            throw(err) ;
        }
    }
);

Answer 1

You're building your query with string concatenation, which is a dangerous (SQL injection) and error-prone (unexpected/incorrect string-escaping, problems with embedded quote ' marks in strings) practice.

Your query, rewritten to use placeholders, should work correctly:

db.query('INSERT INTO mydb ' + fields + ' VALUES (?)', [ JSON.stringify(field.contents || {}) ], err ...

The underlying problem is that in a MySQL literal string expression, \n doesn't mean \ + n, it means ASCII 0x10 (newline). In JSON, a newline character inside a quoted string is invalid -- a newline in a JSON string is serialized as two characters, \ + n -- and the fact that MySQL's string parser recognizes this pattern to mean the same thing it means in JSON is essentially a coincidence, though this convention is common. Here, it bites you because you're intending to send two characters to MySQL but the parser interprets it.

You could have worked hacked around this by doubling up the \ to \\ in the string, which the server would interpret as a literal \ + n, but allowing the database driver module to handle any necessary quoting and escaping for you is the more correct approach.