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)

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?

CodePudding user response：

Try to explicitly replace the wildcards:

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

CodePudding user response：

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.