Storing JSON object in JSON column in MySql2 using Nodejs

Question

I have a JSON object that looks like this -

{
"questions": 
            [{
               "question_id": "1",
                "question": "Choose games you're currently playing",
                "answers":
                   [{ "answer_id" : "1", "answer": "game1", "checked": false },
                    { "answer_id": "2", "answer": "game2", "checked" : true }]
             },
             { 
               //...
             }]
}

I'm trying to store it in a JSON column using MySql2.

My query -

const query = `INSERT INTO user_questions(_id, questions,created_at, updated_at, deleted) 
                VALUES(1, '${JSON.stringify(questions)}', NOW(), NOW(), 0)`;
const result = await db.query(query)

But I'm getting the following exception error -

UnhandledPromiseRejectionWarning: Error: You have an error in your SQL syntax;

What exactly am I doing wrong?

Edit: I found out it's because of the apostrophe in the question. If I try to run the query without it, it works fine.

have tried to log the query and try to execute from IDE and see if that works? — Ashish Modi
– Ashish Modi, Commented Mar 9, 2020 at 9:46

Seti · Accepted Answer · 2020-03-09 15:31:26Z

3

You shouldnt use direct inserts use instead this approach

const query = 'INSERT INTO user_questions(_id, questions,created_at, updated_at, deleted) values (?, ?, ?, ?, ?)';
const result = await db.query(query, [1, JSON.stringify(questions), NOW(), NOW(), 0)]);

which escapes all columns properly: using-prepared-statements

edited Mar 9, 2020 at 15:31

answered Mar 9, 2020 at 13:13

Seti

2,31618 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

Storing JSON object in JSON column in MySql2 using Nodejs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related