MySql 1064 syntax error when LIMIT with express and react

Viewed 14

Having a hard time requesting my mysql rows with a limit through the backend.

This is my index.js file on my API.

app.get("/", (req, res) => {
  return res.send("test");
});
app.get("/Questions", async (req, res) => {
  const questions = await mySqlQuery(
    `SELECT * FROM questions WHERE answer >= :minYear AND answer <= :maxYear ORDER BY RAND() LIMIT :limit;`,
    {
      minYear: req.query.minYear,
      maxYear: req.query.maxYear,
      limit: req.query.limit,
    }
  );
  console.log("Loading question:", req.query);
  return res.json(questions);
});

This is the error

  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sql: "SELECT * FROM questions WHERE answer >= '-1500' AND answer <= '1500' ORDER BY RAND() LIMIT '5';",
  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 ''5'' at line 1"

Not sure why this happens, If remove the "LIMIT :limit" it works and if I copy the whole string and remove the ` and the : it works in my MySql workbench. I also know that it gets my variable from my client, which in this case is 5.

All help appreciated :)

1 Answers

As @tadman said, simply converting it via parseInt() solves the problem :)

Related