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.

xyz83242 · Accepted Answer · 2020-02-17 07:46:45Z

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, 2020 at 7:46

xyz83242

8463 gold badges19 silver badges33 bronze badges

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

Comments

l2ysho · Accepted Answer · 2020-02-17 07:32:03Z

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, 2020 at 7:32

l2ysho

3,1131 gold badge26 silver badges44 bronze badges

1 Comment

xyz83242 Over a year ago

@I2ysho thank you for your help. I am using 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).

Collectives™ on Stack Overflow

Postgres Node search query using LIKE, how to set %

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related