We are allowing the user to post the contents on our application, in a case user entered the % in the title of the post and we are running like query to fetch the data from the database. Here is the snippet from the post table

id |         title           | short_description |   long_description
 1   how many % amounts you       ----------          --------------
     should save                --------------       -----------------

Query we are running:

SELECT id, title from post where post.title LIKE '%string entered by user%'

Now if the user enters amounts then query works fine and returns something like.

SELECT id, title from post where post.title LIKE '%amounts%'

and returning the desired result.

But, when a user enters how many % amounts in the search field, it is returning all the rows which are starting from how many and contains amounts in the upcoming words, which is wrong because the generated query is.

SELECT id, title from post where post.title LIKE '%how many % amounts%'

And another thing is if the user enters only % the query returns all the results.

If we try escaping the % or other special characters and the result is way more different than it should be.

Please suggest a solution if you have any.

  • @Colin'tHart using sequelize ORM for PostgreSQL with NodeJS. And can you please explain, how replacing % with %% can overcome the situation? Mar 7, 2020 at 9:51
  • @Colin'tHart nope! not working. Mar 7, 2020 at 10:17

1 Answer 1


Quoting the like expression

The percent or underscore characters in the user-supplied string must be quoted as documented:

To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one can be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.

For instance:

If the user submits ab%, the code must do:

where title like '%ab#%%' escape '#';

Be aware that the user might submit a string with your escape character in it. For this reason it has to be escaped itself. If for instance the user submits abc%def#, the code must do:

where title like '%abc#%def##%' escape '#';

Using strpos

Another solution is to check the result of strpos instead of using LIKE to find if a substring is in a larger string. strpos does not have wildcards characters and returns a non-zero number if the substring is found.

select strpos('abcdef', 'cd') > 0 ;

strpos also uses the Boyer Moore Horspool algorithm which be faster in some cases, especially since PostgresQL 12 where it applies it directly to UTF-8 contents without converting them first to wide chars internally.

  • That worked like a charm! Mar 7, 2020 at 14:12
  • This doesn't answer the question IMO. SQL injection requires quoting the ' to avoid breaking out of the string!
    – user239558
    Apr 28, 2021 at 22:19
  • @user239558: even though the asker mentioned "SQL injection" in the title, that's not what the question is about. It's asking how to neutralize wildcards characters in the argument following LIKE. Apr 29, 2021 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.