Question 113974 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Escape LIKE parameters containing wildcard characters like % (and _)

In GORM documentation there is an example how to use LIKE conditions:

// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)
// SELECT * FROM users WHERE name LIKE '%jin%';

The problem that I have with this approach is that if jin is user input variable it can contain wildcard symbols like %. How can I escape such wildcard symbols?

For me these symbols must be used in the left part like:

db.Where("name LIKE %?%", "jin").Find(&users)

OR

db.Where("name LIKE '%' || ? || '%'", "jin").Find(&users)

and the ORM should escape automatically the parameter, but this is not how it behaves.

What is the right solution?

Tags:

2

Answers


  1. 0

    Escape explicitly.

    db.Where("name like '%'||?||'%' escape ''", userinput)

    and escape wildcard symbols in the user input upfront before passing to db.Where. Backslashes in the user input shall be doubled too.

    Unrelated but I would much prefer the regexp ~ and ~* operators instead of like.

    Login or Signup to reply.
  2. 0

    Try to explicitly replace the wildcards:

    db.Where("name LIKE '%' || replace(replace(?, '%', '%), '_', '_') || '%'", "jin").Find(&users)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search