Storing JSON array in MySQL Database Nodejs

Question

I want to store an array of languages a user knows in the database.

My code:

 router.post('/create-user', async (req, res) =>{
      const query = `INSERT INTO users(name, languages, created_at, updated_at, deleted) 
                     VALUES ('${name}', ${languages}, NOW(), NOW(), 0)`;
      const result = await db.query(query);
      // rest of code..
    });

I defined a column languages in the database of type JSON.

I call this API in postman with body:

{
  "name" : "test",
  "languages" : "['English', 'German']"
}

But I get the following error:

 "error": "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'English', 'German'].

How can I solve this issue?

Answer 1

You are directly writing query - which is VERY BAD practice. Instead use:

  const query = 'INSERT INTO users(name, languages, created_at, updated_at, deleted) VALUES (?, ?, NOW(), NOW(), ?)';
  const result = await db.query(query, [name, language, 0]);

Which will escape all problematic characters in the query, more you can read here: https://www.npmjs.com/package/mysql2#using-prepared-statements

Answer 2

It looks like you're trying to pass multiple values at once. Try:

 router.post('/create-user', async (req, res) =>{
      languages.forEach((language) => {

      const query = `INSERT INTO users(name, languages, created_at, updated_at, deleted) 
                     VALUES ('${name}', ${language}, NOW(), NOW(), 0)`;
      const result = await db.query(query);
      })

      // rest of code..
    });