MySql 1064 syntax error when LIMIT with express and react

Question

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 :)

Does converting to an int (via parseInt()?) fix the issue? It could be the limit is a string, as it comes from params, and is being escaped accordingly, which isn't valid syntax. — tadman
– tadman, Commented Sep 20, 2022 at 1:02

WikDao · Accepted Answer · 2022-09-20 01:18:46Z

1

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

answered Sep 20, 2022 at 1:18

WikDao

212 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql 1064 syntax error when LIMIT with express and react

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related