0

I want to have a simple search box in my web application using Javascript/Node.js and Postgresql. In it I have a form which is open for the public to make searches for names. I am not using any ORM so how do I make this type of search query safe in the backend, while using .then() ?

I want to use sort of SELECT * FROM spotters WHERE last_name ilike '%Burns%' but safely. The code below doesn't work, but how can I solve this? It feels like it should be quite easy.

// req.query.name = 'Burns' --- Could be "';DROP TABLE"!
pg_client.query("SELECT * FROM spotters WHERE last_name ilike '%?%'", [req.query.name])
  .then((sqlResult) => {
// handle result
  })

I want to have code similar to the above where it can ilike-search for column last name with a query received from the search input.

Christoffer
  • 2,271
  • 3
  • 26
  • 57
  • Just a thought but won't it work if you put % round req.query.name and then bind that string so your prepared statement is just normal `where last_name ilike ?` – Chris Jun 09 '22 at 21:09
  • No, that doesn't work either unfortunately – Christoffer Jun 09 '22 at 21:33
  • *Obviously*, it does – Your Common Sense Jun 10 '22 at 04:50
  • In that case, I would like to understand exactly how that code would look because I cannot make it work. – Christoffer Jun 10 '22 at 06:38
  • 1
    @Christoffer I found a better answer in a different thread so I deleted my answer below. Have a look: https://stackoverflow.com/questions/58174695/prevent-sql-injection-with-nodejs-and-postgres This answer wraps the solution in an async function, which is nicer. You're very close. – Lucretius Jun 10 '22 at 17:27
  • Thanks! That's very close to get what I need. For some reason using '?' does not work but $1 does. Now I just need to understand how to get the % around the $1 so that it works. – Christoffer Jun 10 '22 at 20:58
  • I solved this by declaring the query variable as name = '%' + query + '%' – Christoffer Jun 10 '22 at 21:41

0 Answers0