Issue in Sqlite while using Node js. Like operator and parameters

Question

So I am trying to select rows based on user input, as shown below:

db.all(
  'SELECT * FROM houses WHERE location LIKE "%$input%"',
  {
    $input: name,
  },
  (error, rows) => {
    res.send(rows);
  }
);

However, the database responds with an undefined value. What can I do?

Answer 1

You are not using query parameters properly. Consider:

db.all(
    "SELECT * FROM houses WHERE location LIKE '%' || ? || '%",
    [name],
    (error,rows) => { ... }
);

It might be slightly more efficient to concatenate the variable in the js code:

db.all(
    "SELECT * FROM houses WHERE location LIKE ?",
    ['%' + name + '%'],
    (error,rows) => { ... }
);