PostgreSQL: Prevent SQL injection for like query with % input
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') &gt; 0 ;
 ?column? 
----------
 t


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.