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

Question

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);
      }
    }
  );
});

what will be the output if you put console.log(singleQuoteLocation) // Delhi or 'Delhi' — chaitanya gupta
– chaitanya gupta, Commented Dec 24, 2021 at 6:51
before connection.query... store your query string in a variable and print that in console.: select * from weather_data where city=${singleQuoteLocation} then you will see what's wrong with your formed query. — Nikhil Pandit
– Nikhil Pandit, Commented Dec 24, 2021 at 6:52

Tim Biegeleisen · Accepted Answer · 2021-12-24 06:52:29Z

2

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.

answered Dec 24, 2021 at 6:52

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

2 Comments

susant patel Over a year ago

Thanks, it worked but can you elaborate more like where I can find the syntax or share a helpful reference about this problem. and one more question how can I put 2 variables in the query?

Tim Biegeleisen Over a year ago

Check here. You may use as many ? placeholders as you like, but ? can only appear where literal values would otherwise appear. That is, you can't use ? for things like column and table names.

chaitanya gupta · Accepted Answer · 2021-12-24 06:54:34Z

0

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

answered Dec 24, 2021 at 6:54

chaitanya gupta

3521 gold badge8 silver badges29 bronze badges

2 Comments

susant patel Over a year ago

what this backslash does

chaitanya gupta Over a year ago

it will ignore quotes

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related