Postgres Node search query using LIKE, how to set %

Question

I have a weird problem I encountered using Postgresql and Node. I would like to use LIKE in my query together with % at the beginning and end of searched term. I have no issue using it in the plain SQL:

THIS WORKS:

SELECT * FROM vehicle WHERE module_imei LIKE '%searchterm%' OR custom_id LIKE '%searchterm%'

However, using it in Node is a bit of challenge. I haven't been successful in resolving it yet: THIS DOES NOT WORK:

  getVehiclesSearch: async function({ search }) {
    let response;
    try {
      response = await pool.query(`SELECT * FROM vehicle WHERE module_imei LIKE %$1% OR custom_id LIKE %$1%`, [search]);
      if(response) return response.rows;
    } catch(error) {
      // handle error
      console.error(error);
      // do not throw anything
    }
  },

Doing above will produce: syntax error at or near "%"

SELECT * FROM vehicle WHERE module_imei LIKE '%${$1}%' OR custom_id LIKE '%${$1}%

Doing above will produce: $1 is not defined

SELECT * FROM vehicle WHERE module_imei LIKE '%$1%' OR custom_id LIKE '%$1%'

Doing above will produce: bind message supplies 1 parameters, but prepared statement "" requires 0

I kind of struggle factoring the % in so it won't crash the query. Simply run out of ideas after trying above and variables of those. Thanks for your kind help.

score 2 · Accepted Answer · answered Feb 17 '20 at 07:46

2

This has been already answered over here: Go postgresql LIKE query

In this particular case:

      response = await pool.query(`SELECT * FROM vehicle WHERE module_imei LIKE '%'||$1||'%' OR custom_id LIKE '%'||$1||'%'`, [search]);

This would work.

answered Feb 17 '20 at 07:46

xyz83242

629
14
27

score 0 · Answer 2 · answered Feb 17 '20 at 07:32

0

You are missing single quotes, also I don't recognize a db adapter you use, but you can use template literals (watch out for sql injection!!!)

response = await pool.query(`SELECT * FROM vehicle WHERE module_imei LIKE '%${search}%' OR custom_id LIKE '%${search}%'`);

answered Feb 17 '20 at 07:32

l2ysho

2,542
1
20
41

@I2ysho thank you for your help. I am using https://node-postgres.com/features/pooling and sql injection prevention is exactly in that second part of the query [search]. So your solution would work (that is if I would not be using $1, which I need exactly because of what you pointed out (SQL inections). – xyz83242 Feb 17 '20 at 07:43

Postgres Node search query using LIKE, how to set %

2 Answers2