getting sql error unknown column in where clause when trying to filter data-CodePudding

In the SQL query where city clause when I put string 'delhi' I get results but when I put a variable like below I get error unknown column delhi in where clause. how to solve the issue?

router.get('/filter/:city', (req, res) => {
  const location = req.params.city;
  const singleQuoteLocation = location.replace(/"/g, "'");
  connection.query(
    `select * from weather_data where city=${singleQuoteLocation}`,
    (err, results, field) => {
      if (err) {
        console.log(err);
      } else {
        res.status(200).send(results);
        console.log(results);
      }
    }
  );
});

CodePudding user response：

You should be using a prepared statement with a ? placeholder for the city value. Then, bind a JS variable to the ?.

router.get('/filter/:city', (req, res) => {
  const location = req.params.city;
  connection.query(
    "SELECT * FROM weather_data WHERE city = ?", [location],
    (err, results, field) => {
      if (err) {
        console.log(err);
      }
      else {
        res.status(200).send(results);
        console.log(results);
      }
    }
  );
});

Note that when using a prepared statement there is no need to massage the location variable by doing things like wrapping in single quotes. Instead, let your MySQL driver worry about how to handle this, via the prepared statement.

CodePudding user response：

`select * from weather_data where city = \'${singleQuoteLocation}\' `