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.
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