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);
}
}
);
});
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.
`select * from weather_data where city = \'${singleQuoteLocation}\' `