0

I'm getting this error from the block of code below when my Sql query runs:

code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  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 'client, short, address, address2, city, state, zip, phone, lead, color1, col' at line 1`

The query code does work in mySql Workbench, however I do have to put quotes around client and lead. Is that the problem here? How do I do that in a code block like this? If that's what the real problem is. I have to use the back ticks around the query. If I put quotes around client and lead, it still gives the same error. So how should that syntax look?

app.put(`/client/create`, function (req, res) {
  const client = req.body.client;
  const short = req.body.short;
  const address = req.body.address;
  const address2 = req.body.address2;
  const city = req.body.city;
  const state = req.body.state;
  const zip = req.body.zip;
  const phone = req.body.phone;
  const lead = req.body.lead;
  const color1 = '#000';
  const color2 = '#fff';
  connection.getConnection(function (err, connection) {
    connection.query(
      `INSERT INTO clients (client, short, address, address2, city, state, zip, phone, lead, color1, color2)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [client, short, address, address2, city, state, zip, phone, lead, color1, color2],
      function (error, results) {
        connection.release();
        if (error) throw error;
        res.json(results);
        console.log(`Client has been added to the database.`);
      }
    );
  });
});
Adam Norton
  • 512
  • 2
  • 5
  • 21
  • `client` is a keyword, you need to put backticks around it just like you do in Workbench. – Barmar Mar 09 '21 at 00:25
  • To include a backtick inside a template literal, escape it with backslash. – Barmar Mar 09 '21 at 00:27
  • I tested this on MySQL 5.7 and 8.0. On MySQL 5.7 it has no syntax error. On 8.0, it does return a syntax error because `lead` is a [reserved keyword](https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-L). But neither gives an error for `client`. Are you sure you're using MySQL? What does `SELECT @@version;` return? – Bill Karwin Mar 09 '21 at 00:31
  • `client` is a keyword n MySQL, but not a _reserved_ keyword. So it should not require delimiting it with backticks. – Bill Karwin Mar 09 '21 at 00:31

0 Answers0