PostgreSQL: Prevent SQL injection for like query with % input

postgresqlquery

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.

Best Answer

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 ;
 ?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.

Related Question