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) ;
        }
    }
);

instead i would say stringfy the json and put it in a varchar column of mysql and then at time of retrival just parse the string using json.parse — Rahul Singh
– Rahul Singh, Commented Nov 27, 2017 at 5:46
What leads you to believe \n is the problem? How are you actually doing the insert? Placeholders? (e.g. VALUES(?))? — Michael - sqlbot
– Michael - sqlbot, Commented Nov 27, 2017 at 6:44
@Michael-sqlbot just added my actual code.. I am using placeholders here. Also it tells me the offending line # of the object. I suppose it could be the colon in front of the \n instead. — James Gentes
– James Gentes, Commented Nov 27, 2017 at 17:31
Thanks @RahulSingh that's a good alternative approach if I can't get this working, although I'd like to take advantage of the optimizations that a JSON column offers over varchar. — James Gentes
– James Gentes, Commented Nov 27, 2017 at 17:32
You are not using placeholders. A placeholder is a literal ? inside VALUES(). Use this: db.query('INSERT INTO mydb ' + fields + ' VALUES (?)', [ JSON.stringify(field.contents || {}) ], err ... You should never concatenate string literals into queries. It is unsafe and (as you now see) error-prone. — Michael - sqlbot
– Michael - sqlbot, Commented Nov 27, 2017 at 18:37

Michael - sqlbot · Accepted Answer · 2017-11-28 01:22:45Z

8

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.

answered Nov 28, 2017 at 1:22

Michael - sqlbot

180k28 gold badges388 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Melero Over a year ago

Multiple statement queries are disabled by default to prevent SQLi. github.com/mysqljs/mysql#multiple-statement-queries

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related