How to use LIMIT and placeholders in MySQL (Node.JS)

Question

When executing queries using placeholders in Node.js and MySQL, the LIMIT placeholder does not seem to work properly because the value following it should not be quoted. The following is my query: SELECT userName, clicks FROM users ORDER BY clicks DESC LIMIT ?. How can you use placeholders to achieve the behavior I need?

P粉141925181 · Answer

Here's how you might handle this in your code:

if (Number.isInteger(data.topUsersCount)) {
  let queryString = "SELECT userName, clicks FROM users ORDER BY clicks DESC LIMIT " + data.topUsersCount;

  con.query(queryString, (err, topUsersData) => {
    // Handle data
  });
} else {
  // Handle error
  console.log("Invalid limit value");
}

In the above code, use Number.isInteger(data.topUsersCount) to check whether data.topUsersCount is a safe integer before incorporating it into the query string. This is critical to preventing SQL injection attacks.