ER_PARSE_ERROR: You have an error in your SQL syntax; NodeJS-CodePudding

I'm getting ER_PARSE_ERROR when question mark (?) is added into message variable.

My code :

 socket.on("chat message", (data) => {
      const { sender, receiver, message, ad_id, category_id } = data;


      const sql = `INSERT INTO tbl_user_chats (sender,receiver,message,ad_id,category_id) VALUES (${sender}, ${receiver}, '${message}', ${ad_id}, ${category_id})`;
      con.query(sql,data, function (err, result) {
      if (err) throw err;
        console.log("1 record inserted");
      });

Error :

 code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "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 '1531', `receiver` = 735, `message` = 'yeah fine, and you ?', `created_at` = 1663' at line 1",
  sqlState: '42000',
  index: 0,
  sql: "INSERT INTO tbl_user_chats (sender,receiver,message,ad_id,category_id) VALUES (1531, 735, 'yeah fine, and you `sender` = '1531', `receiver` = 735, `message` = 'yeah fine, and you ?', `created_at` = 1663745307202, `ad_id` = 182, `category_id` = 3', 182, 3)"

If i not add message by excluding question mark (?) it works and inserted into table.

How to fix the parse error when i include question mark character in my message ?

CodePudding user response：

You should never add external input into queries directly (search "SQL injection").

Instead, use the escaping methods provided by the mysql library:

const { sender, receiver, message, ad_id, category_id } = data;


const sql = `INSERT INTO tbl_user_chats (sender,receiver,message,ad_id,category_id) VALUES (?, ?, ?, ?, ?)`;
con.query(sql, [ sender, receiver, message, ad_id, category_id ], ...)