Sanitizing Search Query Strings

I have a search field that includes the following code:

wildcard_search = “%#{search_term}%”
list |> where([b], ilike(field(b, :manufacturer_id), ^wildcard_search))

It works well, but I noticed that a user could enter % in the search box and return all the items in the database. I was wondering, what is the best way to prevent this? Should I just create a function to remove % and other wildcards?

1 Like

I think you need to step back and figure out what limits you need to place on a wildcard search. For instance, is it an issue if a user can extract all items in your database with a relatively small set of queries (a-z, 0-9)? If so, then you also need to disallow 1-character queries. But is that an acceptable constraint, etc…

1 Like

Have you considered escaping % and _?

I’m not sure how easy it is with ecto to use the ESCAPE keyword, but doing Regex.replace(~r/([%_]/, input, ~S"[\1]") should work pretty well as a first level of sanitising.

But please do not forget to filter the empty string as input.

Careful! That’s not enough! ilike/like is powerful enough to bring down a database with certain bad data! ilike/like should never ever be used for user searches!

@tonarie
If you are using PostgreSQL then setup a proper search, a ts_* set of searches if you are searching for text. You will get better results, it will be faster (especially if you setup a ts* index too!), and it’s safe!
https://www.postgresql.org/docs/11/textsearch.html

Abide by this rule though: Never allow untrusted content into ilike/like

If you want something even more powerful than full text search then PostgreSQL has even more options too!

EDIT: If you really want a simple field search though then don’t use ilike/like, instead use the string comparison operators based on whatever specific criteria you want, like contains might be enough.

5 Likes

Do you have a reference for Postgresql contains? I’m not able to find it in the docs anywhere.

I think @OvermindDL1 was referring to this https://www.postgresql.org/docs/10/functions-textsearch.html

1 Like

Can you provide some examples or an article about this?

1 Like

It’s part of the OWASP attack listings:
https://www.owasp.org/index.php/Testing_for_SQL_Wildcard_Attacks_(OWASP-DS-001)

1 Like